I blogged about upobjxt.lst
lists Oracle Supplied Objects a while ago. But I thought it’s time to refresh this blog post a bit and adjust it to be more useful. upobjxt.lst
displays the Oracle Supplied Objects since Oracle Database 12.1.0.1.

upobjxt.lst lists Oracle Supplied Objects and Users
Create an External Table
You can access upobjxt.lst
simply with an external table.
- Create a directory object:
CREATE OR REPLACE DIRECTORY ext_tab_data AS '/u01/app/oracle/product/19/rdbms/admin/';
- Define the external table to query it:
CREATE TABLE oracle_supplied_obj_ext ( owner VARCHAR2(31), name VARCHAR2(127), placeholder VARCHAR2(20), object_type NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_data ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ( owner CHAR(31), name CHAR(127), placeholder CHAR(20), object_type CHAR(2) ) ) LOCATION ('upobjxt.lst') ) PARALLEL 8 REJECT LIMIT UNLIMITED;
- You can display for instance the object counts mapped to object types:
SELECT count(*), object_type, decode(object_type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'EDITION', 59, 'RULE', 60, 'CAPTURE', 61, 'APPLY', 62, 'EVALUATION CONTEXT', 66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW', 72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN', 81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY', 90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE', 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS', 100, 'FILE WATCHER', 101, 'DESTINATION', 114, 'SQL TRANSLATION PROFILE', 115, 'UNIFIED AUDIT POLICY', 'UNDEFINED') FROM oracle_supplied_obj_ext GROUP BY object_type ORDER BY 2;
Object mapping is taken from
DBA_OBJECTS
definition in?/rdbms/admin/dcore.bsq
.
Check for specific objects
Let’s assume you’d like to check for Oracle supplied directories in Oracle Database 19c.
Then you could use the following query:
set pages 20000 column owner format a19 column name format a40 SELECT owner, name FROM oracle_supplied_obj_ext where object_type=23 order by 1,2;
This will list in Oracle Database 19c:
OWNER NAME ------------------- ---------------------------------------- SYS OPATCH_INST_DIR SYS OPATCH_LOG_DIR SYS OPATCH_SCRIPT_DIR SYS ORACLE_BASE SYS ORACLE_HOME SYS ORACLE_OCM_CONFIG_DIR SYS ORACLE_OCM_CONFIG_DIR2 SYS XSDDIR 8 rows selected.
And there are tons of other additional query ideas I’d have.
Using upobjxt.lst from a previous release
When you copy the upobjxt.lst
of a previous release under a new name into the same directory, create a second external table on it, then there are interesting comparison possibilities. You could for instance list all the 167 new views for Multitenant starting with “CDB”:
SELECT b.owner, b.name, b.object_type "New Oracle Objects in 12.2" FROM oracle_supplied_obj_ext b WHERE b.object_type=4 and b.owner='SYS' and b.name like 'CDB%' MINUS SELECT a.owner, a.name, a.object_type FROM oracle_supplied_obj_ext_121 a ORDER BY 1;
Further Links and Information
As there’s also a related blog post demonstrating how to query Oracle Supplied Users and Roles with upuserx.lst
:
–Mike