Eine hilfreiche Spalte in Oracle 12c: ORACLE_MAINTAINED

Vor einigen Tagen habe ich die Abfrage eines Kollegen gesehen, mit der er in einer Oracle 11.2-Datenbank aus der DBA_SEGMENTS alle die Objekte raussuchen wollte, die nicht einem von Oracle mitgelieferten Schema gehören. Und da ist mir eingefallen, dass das mit Oracle 12c etwas einfacher geht.

Die Abfrage war:

SQL> SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME
 2 FROM DBA_SEGMENTS
 3 WHERE OWNER NOT IN
 4 ('SYSTEM','DBSNMP','SYS','SYSMAN','APEX_030200','MDSYS','EXFSYS',
 5 'ORDDATA','XDB','WMSYS','CTXSYS','ORDSYS','OUTLN','TSMSYS','OLAPSYS')
 6 AND TABLESPACE_NAME LIKE 'SYS%';

 

 

Sinn und Zweck der Abfrage ist ganz klar: alle Segmente (und die zugehörigen Tablespaces) ausgeben, die nicht irgendwelchen Usern gehören, die von ORACLE selbst installiert wurden und die Objekte für irgendwelche Oracle-Optionen und Funktionalitäten wie Oracle Text, APEX, Spatial etc. enthalten.

Mit Oracle 12c geht das einfacher, denn dort gibt es die neue Spalte ORACLE_MAINTAINED in DBA_USERS:

 

SQL> select username from dba_users where oracle_maintained='Y'
 2* order by username;

USERNAME
---------------------------------------------------------------
ANONYMOUS
APEX_040200
APEX_PUBLIC_USER
[..]|
SYS
SYSBACKUP
SYSDG
SYSKM
SYSTEM
WMSYS
XDB
XS$NULL

36 rows selected.

 

Die obige Abfrage reduziert sich also auf

SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE OWNER NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y') 
 AND TABLESPACE_NAME NOT LIKE 'SYS%';

 

Diese Variante hat auch den Vorteil, das man nicht versehentlich ein Schema vergisst.

In der View DBA_ROLES erfüllt die Spalte ORACLE_MAINTAINED den gleichen Zweck: „Oracle-Rollen“ und „Nicht-Oracle-Rollen“ zu unterscheiden:

SQL> select role from dba_roles where oracle_maintained='Y';

ROLE
-----------------------------------------------
CONNECT
RESOURCE
DBA
AUDIT_ADMIN
AUDIT_VIEWER
[..]
DV_AUDIT_CLEANUP
DV_DATAPUMP_NETWORK_LINK

84 rows selected.

 

 

Die Spalte ORACLE_MAINTAINED gibt es auch in der View DBA_OBJECTS (und ALL_OBJECTS etc.). Die Oracle-Dokumentation sagt dazu „Denotes whether the object was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). An object for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.“ Aber Achtung: Die Aussage „was einem Oracle-Maintained-Schema gehört, aber selbst nicht „Oracle-Maintained“ ist, hat da nichts zu suchen“, oder in SQL formuliert

 

select u.username,o.object_type,o.object_name
from dba_objects o,dba_users u
where o.owner=u.username
and o.oracle_maintained='N'
and u.oracle_maintained='Y'
;

stimmt nicht so ganz, denn die obige Abfrage liefert jede Menge Objekte vom AWR oder vom Advanced Queueing zurück.

Allerdings gilt für Objekte, die „ORACLE_MAINTAINED“ sind (SELECT .. FROM DBA_OBJECTS WHERE ORACLE_MAINTAINED=‘Y‘) gilt lt. MOS-Note 2114233.1, dass sie nicht exportiert werden können.

MOS-Notes:

• 2114233.1: Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655

Bugs:

• 17373592 : EXPDP DOES NOT EXPORT APPLICATION EXPRESS OBJECTS CORRECTLY

---

Werbung (Amazon-Partner-Link)