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.
- Create a directory object:
CREATE OR REPLACE DIRECTORY ext_tab_data AS '/u01/app/oracle/product/12.2.0.1/rdbms/admin/';
- 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;
- 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;
- 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
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
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
Patrick,
thanks – this is my planned blog post for early next week 😉
Cheers,
Mike