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

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/12.2.0.1/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_ext GROUP BY object_type ORDER BY 2;

    Object mapping is taken from DBA_OBJECTS definition in ?/rdbms/admin/dcore.bsq.

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;

As there’s also a related blog post demonstrating how to query Oracle Supplied Users and Roles with upuserx.lst:

–Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree