List Schemas in Oracle

List Schemas in Oracle | In this tutorial we will see how to show the list of schemas in the oracle database? To list all schemas in the Oracle database we can use the ALL_USERS or DBA_USERS view.

The ALL_USERS view is available for all users and contains the list of schemas. But it doesn’t describe the schemas. If we want detailed information on Schemas then we can use the DBA_USERS view. The DBA_USERS is available for only database administrators.

List Schemas in Oracle using ALL_USERS

The ALL_USERS view contains the following information:- user_id, username, and created (date of user creation), common, oracle_maintained, inherited, default_collation, implicit, and all_shard.

SELECT * FROM all_users;

Example to show schemas name in ascending order:-

SQL> SELECT username AS schema_name
     FROM all_users
     ORDER BY username;

SCHEMA_NAME
------------------------------
ANONYMOUS
APEX_040000
APEX_PUBLIC_USER
APPQOSSYS
CTXSYS
DBSNMP
DIP
FLOWS_FILES
HR
KNOW
KP
MDSYS
ORACLE_OCM
OUTLN
SYS
SYSTEM
XDB
XS$NULL

18 rows selected.

Table to show the meaning of all columns of ALL_USERS view,

ColumnDatatypeNULLDescription
SCHEMA_NAMEVARCHAR2(30)NOT NULL Name of the schema.
USER_IDNUMBERNOT NULLThe ID of the user.
CREATEDDATENOT NULLThe date on which the user was created.
COMMONVARCHAR2(3)Specifies if a user is common ( YES) or Local ( NO)
ORACLE_MAINTAINEDVARCHAR2(1)Indicates whether the user was created and maintained by Oracle-supplied scripts ( Y). Note that you should not change these users directly except modifying them by executing an Oracle-supplied script.
INHERITEDVARCHAR2(3)Denotes where a user definition was inherited from another container (YES) or not (NO).
DEFAULT_COLLATIONVARCHAR2(100)Specifies the default collation for the schema of the user.
IMPLICIT VARCHAR2(3)Denotes if a user is a common user created by an implicit application (YES) or not (NO). 
ALL_SHARDVARCHAR2(3)In a shared database, this column has either one of two possible values: YES and NO.

List ALL Schemas in Oracle Using DBA_USERS

We can also use the DBA_USERS view to list all the schemas. Generally, the DBA_USERS view is available to database administrators. The DBA_USERS view contains following information:-  username, user_id, password, account_status, lock_date, expiry_da, default_tablespace, temporary_tablespace, created, profile, and initial_rsrc_consumer_group column.

SELECT username AS schema_name
FROM dba_users;

Table to show the meaning of all columns of DBA_USERS view,

ColumnDatatypeNULLDescription
USERNAMEVARCHAR2(30)NOT NULLName of the user.
USER_IDNUMBERNOT NULLThe ID of the user.
PASSWORDVARCHAR2(30)The password of the user.
ACCOUNT_STATUSVARCHAR2(32)NOT NULLAccount status of the user. 
LOCK_DATEDATEThe date on which the account was locked.
EXPIRY_DATEDATEThe expiry date of the user.
DEFAULT_TABLESPACEVARCHAR2(30)NOT NULLThe default tablespace of the user.
TEMPORARY_TABLESPACEVARCHAR2(30)NOT NULLThe default tablespace for temporary tables.
CREATEDDATENOT NULLThe date on which the user was created.
PROFILEVARCHAR2(30)NOT NULLThe profile of the user.
INITIAL_RSRC_CONSUMER_GROUPVARCHAR2(30)The initial resource consumer group for the user.
EXTERNAL_NAME VARCHAR2(4000)The external name of the user.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published.