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

4 thoughts on “upuserxt.lst lists Oracle Supplied User Schemas and Roles

  1. Could you explain how to implement this in more detail and what specifically the benefits of this are?

    Thanks.

    • Chris,

      I used this first to demonstrate external tables – and how easy you can use them.
      Sometimes it’s helpful to quickly query and exclude Oracle owned objects. But there’s another w/a – I will blog about it next week.

      Cheers,
      Mike

  2. Hi Mike,

    Thanks for that. I have also found the oracle_maintained column, which was added to dba_users, dba_roles and dba_objects in 12.1, very useful for separating oracle-supplied and end-user-developed users, roles and objects.

    Cheers

    Patrick

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.