Oracle SQL Query for listing all Schemas in a DB

Question

I wanted to delete some unused schemas on our oracle DB.

How can I query for all schema names ?

Courteous

What's your definition of "unused"? – Hosiery

I'm working on a datamigration project, and every developer has their own set of schemas. (Some developers have left, and some schema sets are no longer used). – Courteous

Answer 1

Using sqlplus

sqlplus / as sysdba

run:

SELECT * 
FROM dba_users

Should you only want the usernames do the following:

SELECT username 
FROM dba_users

Talithatalk

Make sure you have permissions on your user however. – Cadaverine

@Andy: that's why I wrote "as a privileged user" ;) – Talithatalk

@horse Apologies I missed that. – Cadaverine

@a_horse_with_no_name does that mean a schema in oracle mean it's a user ? I mean a schema = user ? and under that user all the tables created same like MySQL ? – Trumaine

Answer 2

Most likely, you want

SELECT username
  FROM dba_users

That will show you all the users in the system (and thus all the potential schemas). If your definition of "schema" allows for a schema to be empty, that's what you want. However, there can be a semantic distinction where people only want to call something a schema if it actually owns at least one object so that the hundreds of user accounts that will never own any objects are excluded. In that case

SELECT username
  FROM dba_users u
 WHERE EXISTS (
    SELECT 1
      FROM dba_objects o
     WHERE o.owner = u.username )

Assuming that whoever created the schemas was sensible about assigning default tablespaces and assuming that you are not interested in schemas that Oracle has delivered, you can filter out those schemas by adding predicates on the default_tablespace, i.e.

SELECT username
  FROM dba_users
 WHERE default_tablespace not in ('SYSTEM','SYSAUX')

or

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

It is not terribly uncommon to come across a system where someone has incorrectly given a non-system user a default_tablespace of SYSTEM, though, so be certain that the assumptions hold before trying to filter out the Oracle-delivered schemas this way.

Barometrograph

Combine this with the where predicate from FeRtoll's query and you'll have a fairly safe (not likely to cobber SYS or SYSTEM) query. – Hartle

How is that different from select distinct owner from dba_objects ? – Trishatriskelion

Well on a clean Oracle instance, your query, @David, yields an additional PUBLIC owner – Mohammedanism

Answer 3

SELECT username FROM all_users ORDER BY username;

Millenarian

Very useful if your user don't have privileges on dba_users (e.g.: error ORA-00942 : table or view does not exist) – Collogue

but is the output same between dba_users and all_users ? – Aggravate

Answer 4

select distinct owner 
from dba_segments
where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));

Fabricant

As I understand it, this query will bring up all the schemas that contain any tables. Is that right? – Whisenhunt

This will only work reliably in older versions of Oracle. With deferred segment creation it is possible to have an object without a segment. – Avernus

Answer 5

Below sql lists all the schema in oracle that are created after installation ORACLE_MAINTAINED='N' is the filter. This column is new in 12c.

select distinct username,ORACLE_MAINTAINED from dba_users where ORACLE_MAINTAINED='N';

Violante

Answer 6

How about :

SQL> select * from all_users;

it will return list of all users/schemas, their ID's and date created in DB :

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SCHEMA1                         120 09-SEP-15
SCHEMA2                         119 09-SEP-15
SCHEMA3                         118 09-SEP-15

Mezuzah

Answer 7

Either of the following SQL will return all schema in Oracle DB.

1. select owner FROM all_tables group by owner;
2. select distinct owner FROM all_tables;

Cordelia

There might be schemas that only have non-table objects in them, which your queries would not list. – Galleon

Answer 8

IF YOU WANT TO SEARCH FOR THE SCHEMA NAME THEN

SELECT * FROM all_tables WHERE OWNER  LIKE '%SCHEMA_NAME%'

iF YOU WANT TO SEARCH FOR PERTICULAR TABLE THEN -

SELECT * FROM all_tables WHERE TABLE_NAME  LIKE '%TABLE_NAME%'

Haircloth

The question was asked 12 years ago, and there are other quality answers to the question. Your answer has some problems, first, it won't retrieve schemas without tables, more likely to be unused, second it's basically a duplicate of @Cordelia answer. – Orvas