upobjxt.lst lists Oracle Supplied Objects

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.

1. Create a directory object:

   CREATE OR REPLACE DIRECTORY ext_tab_data AS '/u01/app/oracle/product/19/rdbms/admin/';

2. 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;

3. 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:

• https://MikeDietrichDE.com/upobjxt-lst-lists-oracle-supplied-objects-and-users

–Mike