2

I want to be able to display a list of all user databases in an Oracle connection, excluding the system databases.

There is a way to differentiate between user tables and system tables within a database. But I could not find any way how I can filter out all system databases.

Does anyone know how it can be achieved ?

Mohammed Raqeeb
  • 75
  • 2
  • 12

2 Answers2

2

When connecting to oracle you will normally connect to ONE database, and within that database you will have a number of schemas. These schemas would be seen as "databases" in some other rdbms systems. Each schema is owned by a DB-User that can be a system- or normal user. All tables/objects created in a schema is "owned" by the owner of the schema.

So a rough method to separate the system tables from user tables will be to consider tables in a schemas owned by a system user as system tables.

If in a database with version 12c and above you can do like this:

select t.table_name, t.owner, u.oracle_maintained
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

If version below 12c the oracle_maintained column does not exist so you need this work-around. The 35 should be set to the highest used_id for system users. System users are normally the lowest in the database.

select t.table_name, t.owner, case when u.user_id > 35 then 'N' else 'Y' end systemgenerated
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

Also: the 'and rownum < 100' should be replaced by your own filter on tables.

F.Madsen
  • 702
  • 4
  • 6
  • thank you! My customer however is specifically interested in adding a filter on the databases / schemas rather than the tables. Is there a query (as a join of such tables) that I can run to list only the non-system databases/schemas ? thanks again. – Mohammed Raqeeb Mar 11 '19 at 10:51
  • Also as I understand, the Oracle doc says ORACLE_MAINTAINED column indicates whether the user/schema is System or non-system. If so, can we not filter by this column directly to check for non-system databases ? References: http://www.dba-oracle.com/t_oracle_maintained_dba_users.htm https://docs.oracle.com/database/121/REFRN/GUID-309FCCB2-2E8D-4371-9FC5-7F3B10E2A8C0.htm#REFRN23302 – Mohammed Raqeeb Mar 11 '19 at 11:24
  • Then it is easy. Normally there will be one schema for one user. So just query "Select username, oracle_maintained form dba_user where ..." – F.Madsen Mar 11 '19 at 16:33
  • Thank you. The precise query that I needed was slightly different however. I have quoted it below as an answer. Nevertheless, thanks for giving the initial direction. – Mohammed Raqeeb Mar 12 '19 at 10:05
0

I could come up with the exact answer to my question taking help from another post - Oracle SQL Query for listing all Schemas in a DB

Here is the query that I needed :

> SELECT * FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o
> WHERE o.owner = u.username ) AND default_tablespace not in
> ('SYSTEM','SYSAUX') and ACCOUNT_STATUS = 'OPEN'

This will list all the Non-System users/schemas (aka databases) from your database. Make sure you have logged in as SYS user or you grant "GRANT SELECT ON dba_users TO the_non_sys_user"

Mohammed Raqeeb
  • 75
  • 2
  • 12