A helpful column in Oracle database 12c: ORACLE_MAINTAINED

2016-06-23 Off By Markus Flechtner

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