Blog - comments

Hi Olivier,DELAYED_DURABILITY doesn't change the SQL Server behavior of opening the data and log fil...
Hi David,Thanks for this demo with write functions. As much I know, SQL Server always access files (...
Olivier Berthommé
Thank you for your feedback. I will build a test environment and realize a guide to deploy Grid Infr...

Hi Greg,

Thanks

great job Dave ! thanks

greg
Blog Franck Pachot How to list all Oracle system schemas

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

How to list all Oracle system schemas

Do you want to know which users come with the Oracle Database and which are the ones you have created? This is that not easy, especially prior to Oracle 12c. You know SYS and SYSTEM, but there are many others - especially when you have installed all options.

And it is important to know them all, e. g. when you import or apply a password policy, etc.

Let's see the options we have.

 

12c shows Oracle maintained users

Oracle 12c has introduced the ORACLE_MAINTAINED in the DBA_USERS dictionary view.

It is YES for the users created by oracle scripts (create database, calalog.sql, catproc.sql). So it's the right place to check when you are in 12c. 

 

12c Common users

Oracle 12c has another column to distinguish the user commonality. This is for CDB: the oracle dictionary is stored in the root container, and each PDB have links to it. And what is nice is that even when in a non-cdb the column is at 'YES' for Oracle maintained users and at 'NO' for created users. (except if you create C## common users in a cdb).

 

Default passwords

The table sys.default_pwd$ stores the default password hash value so that we can check which users can log with trivial password. Even if it includes all the Oracle maintained users, it has also some well known users which are not provided by database creation at all.

 

Sysaux occupants

Another idea that I took from Martin Bach is to crosscheck check with v$sysaux_occupants. Some Oracle provided users are missing, but the nice thing is that there is a description for the schemas that are listed.

 

Not exported by DataPump

DataPump maintains its own list of users for which the tables are not exported, and you can query sys.ku_noexp_tab for that. The old exp has also its list stored in sys.exu8usr. But both are not exhaustive.

Another exclusion list is the one used by logical standby: system.logstdby$skip which may be used as well to crosscheck (only records with 'action=0' are relevant for schema exclusion).

Let's check all that on a 12c non-cdb database. Here is the query that crosscheck all than information with outer joins:

select created,username,oracle_maintained,common,no_exp,no_expdp,no_sby,default_password,sysaux,occupant_desc
from dba_users 
left outer join 
 (select distinct name username,'Y' no_expdp from sys.ku_noexp_tab where obj_type='SCHEMA') 
 using(username)
left outer join (select distinct name username,'Y' no_exp from sys.exu8usr)
 using(username)
left outer join (select distinct name username,'Y' no_sby from system.logstdby$skip_support where action in (0,-1))
 using(username)
left outer join (select distinct user_name username,'Y' default_password from sys.default_pwd$)
 using(username)
left outer join (select schema_name username,'Y' sysaux,decode(count(*),1,min(occupant_desc)) occupant_desc from v$sysaux_occupants group by schema_name)
 using(username)
order by created,username;

and the result, ordered by creation date so that you can check which users were created by the database creation:

 
CREATEDUSERORACLE
MAINTAINED
COM-
MON
NO
EXP
NO
EXPDP
NO
SBY
DEF
PWD
SYS
AUX
28-FEB-14 08:25:02 AUDSYS Y YES Y Y Y Y Y
28-FEB-14 08:25:02 SYS Y YES Y Y Y Y Y
28-FEB-14 08:25:02 SYSBACKUP Y YES   Y Y Y  
28-FEB-14 08:25:02 SYSDG Y YES   Y Y Y  
28-FEB-14 08:25:02 SYSKM Y YES   Y Y Y  
28-FEB-14 08:25:02 SYSTEM Y YES Y   Y Y Y
28-FEB-14 08:25:05 OUTLN Y YES Y   Y Y  
28-FEB-14 08:29:33 GSMADMIN_INTERNAL Y YES   Y Y Y  
28-FEB-14 08:29:33 GSMUSER Y YES   Y Y Y  
28-FEB-14 08:29:43 DIP Y YES   Y Y Y  
28-FEB-14 08:30:39 XS$NULL Y YES   Y Y Y  
28-FEB-14 08:31:12 ORACLE_OCM Y YES   Y Y Y  
28-FEB-14 08:39:55 DBSNMP Y YES   Y Y Y Y
28-FEB-14 08:39:56 APPQOSSYS Y YES   Y Y Y  
28-FEB-14 08:40:03 ANONYMOUS Y YES   Y Y Y  
28-FEB-14 08:40:03 XDB Y YES   Y Y Y Y
28-FEB-14 08:44:29 GSMCATUSER Y YES   Y Y Y  
28-FEB-14 08:53:38 WMSYS Y YES   Y Y Y Y
28-FEB-14 08:55:31 OJVMSYS Y YES   Y Y Y  
28-FEB-14 08:59:58 CTXSYS Y YES   Y Y Y Y
28-FEB-14 09:00:44 MDSYS Y YES   Y Y Y Y
28-FEB-14 09:00:44 ORDDATA Y YES   Y Y Y Y
28-FEB-14 09:00:44 ORDPLUGINS Y YES   Y Y Y Y
28-FEB-14 09:00:44 ORDSYS Y YES   Y Y Y Y
28-FEB-14 09:00:44 SI_INFORMTN_SCHEMA Y YES   Y Y Y Y
28-FEB-14 09:09:48 OLAPSYS Y YES   Y Y Y Y
28-FEB-14 09:10:17 MDDATA Y YES Y   Y Y  
28-FEB-14 09:15:09 SPATIAL_WFS_ADMIN_USR Y YES Y   Y Y  
28-FEB-14 09:15:13 SPATIAL_CSW_ADMIN_USR Y YES Y   Y Y  
28-FEB-14 09:20:00 LBACSYS Y YES   Y Y Y  
28-FEB-14 09:20:16 APEX_040200 Y YES       Y  
28-FEB-14 09:20:16 APEX_PUBLIC_USER Y YES Y     Y  
28-FEB-14 09:20:16 FLOWS_FILES Y YES       Y  
28-FEB-14 09:45:17 DVF Y YES   Y Y Y  
28-FEB-14 09:45:17 DVSYS Y YES   Y Y Y  
21-MAR-14 18:15:19 FRANCK N NO Y        
09-APR-14 20:33:34 RMAN N NO Y     Y  
11-APR-14 09:04:55 DEMO N NO Y     Y  
23-APR-14 20:53:09 HR N NO Y   Y Y  
23-APR-14 20:53:09 IX N NO Y   Y Y  
23-APR-14 20:53:09 OE N NO Y   Y Y  
23-APR-14 20:53:09 PM N NO Y   Y Y  
23-APR-14 20:53:09 SH N NO Y   Y Y  
25-APR-14 21:26:33 SQLTXADMIN N NO Y        
25-APR-14 21:26:33 SQLTXPLAIN N NO Y        
27-MAY-14 21:48:24 SCOTT N NO Y   Y Y  

 

So you see that the 12c columns are highly reliable. They include all users that have been created during the database creatio, but do not include the users that were created afterwards.

When not in 12c, the exclusion list coming from DataPump is quite good, but not exhaustive.

Sometimes the most useful features are not those that change the future of database, but rather those little things that make life easier.

In DBA_USERS, we have two very useful new columns: ORACLE_MAINTAINED and LAST_LOGIN. That is a great improvement.

Rate this blog entry:
0

Franck Pachot is Consultant at dbi services. He has 20 years of experience in Oracle databases. Through his expertise as a DBA, Oracle expert, data architect, and performance specialist, he is able to cover all database areas: architecture, data modeling, database design, tuning, operation, and training. Franck Pachot knows how to enable an efficient collaboration between the developers and the operational team when it comes to troubleshooting issues or performance tuning. He has passed the OCP certifications from 8i to 12c, is also Certified Expert for Oracle Database 11g Performance Tuning, and now achived the highest level of certification: Oracle Master Certified OCM 11g. Prior to joining dbi services, Franck Pachot was Oracle Consultant at Trivadis in Lausanne. Previously, he worked in several countries and environements, always as a consultant. Franck Pachot holds a Master of Business Informatics from the University of Paris-Sud. His branch-related experience covers Financial Services / Banking, Public Sector, Food, Transport and Logistics, Pharma, etc.


    O_Database12c_Admin_Professional_clrOCE_ODb11gPerfTun_clr11gocm_logo

Comments

  • Nicolas Jardot
    Nicolas Jardot Monday, 23 June 2014

    Hi Franck,
    In 11g, I use another way to check the Oracle schemas attached to the options. The view dba_registry contains a column schema and other_schemas where to indicate the relation between options and schemas.

    SQL> select comp_id, comp_name, schema, other_schemas from dba_registry;

    COMP_NAME SCHEMA OTHER_SCHEMAS
    ---------------------------------- ------ --------------------------------------
    Oracle XML Database XDB ANONYMOUS,XS$NULL
    Oracle Workspace Manager WMSYS
    Oracle Database Catalog Views SYS
    Oracle Database Packages and Types SYS APPQOSSYS,DBSNMP,DIP,
    ORACLE_OCM,OUTLN,SYSTEM

Leave your comment

Guest Thursday, 02 October 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter