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

User · Accepted Answer

Using sqlplus  sqlplus   as sysdba  run    SELECT    FROM dba users   Should you only want the usernames do the following    SELECT username  FROM dba users

User · Answer

SELECT username FROM all users ORDER BY username

User · Answer

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

User · Answer

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

User · Answer

Either of the following SQL will return all schema in Oracle DB     select owner FROM all tables group by owner  select distinct owner FROM all tables

User · Answer

select distinct owner  from dba segments where owner in  select username from dba users where default tablespace not in   SYSTEM   SYSAUX

User · Answer

How about    SQL gt  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

[oracle] Oracle SQL Query for listing all Schemas in a DB

Examples related to oracle

Examples related to plsql