upuserxt.lst lists Oracle Supplied User Schemas and Roles

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

Photo by Faustin Tuyambaze on Unsplash

 

upuserxt.lst lists Oracle Supplied User Schemas and Roles

You can access upuserxt.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_usr_ext (
    schema_role      VARCHAR2(128)
    )
    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_data
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
    schema_role      CHAR(128)
    )
    )
    LOCATION ('upuserxt.lst')
    )
    PARALLEL 8
    REJECT LIMIT UNLIMITED;
  3. Unfortunately the ROLES and SCHEMAS have no separate qualifiers making it a bit hard to deal with the file contents:
    SELECT schema_role FROM oracle_supplied_usr_ext ORDER BY 1;
  4. But you can, for instance, copy the upuserxt.lst from another release into the same directory (of course under a different name), build a 2nd external table on it and query the differences. Create the external table first:
    CREATE TABLE oracle_supplied_usr_ext_121 (
    schema_role      VARCHAR2(128)
    )
    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_data
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
    schema_role      CHAR(128)
    )
    )
    LOCATION ('upuserxt121.lst')
    )
    PARALLEL 8
    REJECT LIMIT UNLIMITED;

    And then query the difference between the two releases:

    SELECT b.schema_role "New Users and Roles in 12.2"
      FROM oracle_supplied_usr_ext b
    MINUS
    SELECT a.schema_role
      FROM oracle_supplied_usr_ext_121 a
    ORDER BY 1;

As there’s also a related blog post demonstrating how to query Oracle Supplied Objects with upobjxt.lst:

–Mike

Share this: