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.

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:

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

–Mike