➤ 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
Oracle List Users | In this post we will see how to list all users in the Oracle database? What is the query used to show all users in the Oracle database?
In the Oracle database all information including user, userid, username, and e.t.c. are stored in some tables. If we know the name of the table where user information is stored then we can easily display all the users of the Oracle database.
Oracle List All Users
In the Oracle database, all user information is stored in ALL_USERS and DBA_USERS. The ALL_USERS view displays all users that are visible to the current user. The ALL_USERS view doesn’t describe the user.
SELECT * FROM all_users;Example:-
SELECT username, user_id, created
FROM all_users;It displays,
USERNAME USER_ID CREATED
---------------- ---------- ---------
XS$NULL 2147483638 29-MAY-14
KNOW 49 05-SEP-21
KP 48 20-JUL-21
APEX_040000 47 29-MAY-14
APEX_PUBLIC_USE 45 29-MAY-14
FLOWS_FILES 44 29-MAY-14
HR 43 29-MAY-14
MDSYS 42 29-MAY-14
ANONYMOUS 35 29-MAY-14
XDB 34 29-MAY-14
CTXSYS 32 29-MAY-14
APPQOSSYS 30 29-MAY-14
DBSNMP 29 29-MAY-14
ORACLE_OCM 21 29-MAY-14
DIP 14 29-MAY-14
OUTLN 9 29-MAY-14
SYSTEM 5 29-MAY-14
SYS 0 29-MAY-14
18 rows selected.
We can also use DBA_USERS to list all users in the Oracle database. But the DBA_USERS view is accessible only to the database administrator. Therefore to execute the query below, you must log in as a database administrator.
SELECT * FROM dba_users;Unlike ALL_USERS view, DBA_USERS contains detailed reports like:- username, user_id, password, account_status, lock_date expiry_da, default_tablespace, temporary_tablespace, created, profile, initial_rsrc_consumer_group.
Oracle List Users Using ALL_USERS
The ALL_USERS view contains the following columns:- user_id, username, and created (date of user creation), common, oracle_maintained, inherited, default_collation, implicit, and all_shard. We can display all users based on their creation date. The view is visible to all users, and it gives only a little information about the user, it doesn’t describe the user.
SELECT * FROM all_users
ORDER BY created DESC;Table to show the meaning of all columns of ALL_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. |
| 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. |
Oracle List Users Using DBA_USERS
The DBA_USERS view is accessible to the database administrator, and it contains all the details about the user. It contains username, user_id, password, account_status, lock_date, expiry_da, default_tablespace, temporary_tablespace, created, profile, and initial_rsrc_consumer_group column.
SELECT * FROM dba_users
ORDER BY created DESC;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!