Oracle Supplied Objects Listing in upobjxt.lst … … Oracle Supplied Users Listing in upuserxt.lst

Maybe not important to many people but as I didn’t find much about it via a search I’d thought I post something about the new upobjxt.lst and upuserxt.lst files. Since Oracle Database 12.1.0.1 we deliver these files in ?/rdbms/admin .

All Oracle supplied objects are kept in an external table format.
The file’s name is: upobjxt.lst

If you’d like to access it you may do the following:

  1. Create a directory object:
    CREATE OR REPLACE DIRECTORY ext_tab_data AS '/u01/app/oracle/product/12.1.0.2/rdbms/admin/';
  2. Define the external table to query it:
    CREATE TABLE oracle_supplied_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 (
    DATA_CACHE 1000000
    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. Now you can query from the table oracle_supplied_ext.
    For example:

    select count(*), object_type
    from oracle_supplied_ext
    group by object_type
    order by 2;
  4. Would you like to match object types to object codes? Try this one  …:
    select count(o.type#), o.type#,d.object_type
    from dba_objects d, obj$ o
    where d.object_id=o.obj#
    group by o.type#,d.object_type
    order by 2;

    Object mapping can be found also in the definition of OBJ$ in the dcore.bsq .

All Oracle supplied users are kept in an external table format in another file named: upuserxt.lst

-Mike

Leave a Reply

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