➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
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,
| Column | Datatype | NULL | Description |
|---|---|---|---|
| SCHEMA_NAME | VARCHAR2(30) | NOT NULL | Name of the schema. |
| USER_ID | NUMBER | NOT NULL | The ID of the user. |
| CREATED | DATE | NOT NULL | The date on which the user was created. |
| COMMON | VARCHAR2(3) | Specifies if a user is common ( YES) or Local ( NO) | |
| ORACLE_MAINTAINED | VARCHAR2(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. | |
| INHERITED | VARCHAR2(3) | Denotes where a user definition was inherited from another container (YES) or not (NO). | |
| DEFAULT_COLLATION | VARCHAR2(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_SHARD | VARCHAR2(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,
| Column | Datatype | NULL | Description |
| USERNAME | VARCHAR2(30) | NOT NULL | Name of the user. |
| USER_ID | NUMBER | NOT NULL | The ID of the user. |
| PASSWORD | VARCHAR2(30) | The password of the user. | |
| ACCOUNT_STATUS | VARCHAR2(32) | NOT NULL | Account status of the user. |
| LOCK_DATE | DATE | The date on which the account was locked. | |
| EXPIRY_DATE | DATE | The expiry date of the user. | |
| DEFAULT_TABLESPACE | VARCHAR2(30) | NOT NULL | The default tablespace of the user. |
| TEMPORARY_TABLESPACE | VARCHAR2(30) | NOT NULL | The default tablespace for temporary tables. |
| CREATED | DATE | NOT NULL | The date on which the user was created. |
| PROFILE | VARCHAR2(30) | NOT NULL | The profile of the user. |
| INITIAL_RSRC_CONSUMER_GROUP | VARCHAR2(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!
