A helpful column in Oracle database 12c: ORACLE_MAINTAINED
A few days ago I saw a query from a colleague. He wanted to exclude all Oracle-supplied schema in a Oracle 11.2 database from a query on DBA_SEGMENTS. And it came to my mind that the query is much simpler in Oracle database 12c:
The query was:
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%';
The purpose of the query is quite clear: exlucde all segments which do not belong to Oracle-provided schemas which contain objects for database options and other additional functionality like Oracle Text, APEX, Spatial etc. contain.
The query is much simpler in Oracle database 12c, because there‘s a new column in DBA_USERS: ORACLE_MAINTAINED, which is either „Y“ or „N“:
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.
Taking this new column, the query above can be modified to:
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%';
This query has the advantage that you cannot forget a schema in the exception list.
In the view DBA_ROLES, the column ORACLE_MAINTAINED serves the same purposed: Distinguish Oracle-provided roles from the other roles:
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.
The ORACLE MAINTAINED column is available in the view DBA_OBJECTS (and ALL_OBJECTS etc.), too. The Oracle documentation says „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.“ But beware: The assumption „Object created by an Oracle script always has ORACLE_MAINTAINED=Y“ is not true.
The query
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'
;
returns lots of objects from AWR or Advanced Queuing
However, these objects (with ORACLE_MAINTAINED=Y) cannot be exported using DataPump (MOS-Note 2114233.1).
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