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
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;
36 rows selected.
Taking this new column, the query above can be modified to:
SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME
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';
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.
from dba_objects o,dba_users u
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).
- 2114233.1: Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655
- 17373592 : EXPDP DOES NOT EXPORT APPLICATION EXPRESS OBJECTS CORRECTLY