Oracle / PLSQL: Find Users in Oracle / PLSQL
This Oracle tutorial explains how to find all users that are created in the Oracle database with syntax and examples.
Description
You can find all users created in Oracle by running a query from a command prompt. The user information is stored in various system tables - ALL_USERS and DBA_USERS, depending on what user information you wish to retrieve.
ALL_USERS
If you need to find all users that are visible to the current users, you can query the ALL_USERS table. The syntax to retrieve user information from the ALL_USERS table in Oracle/PLSQL is:
SELECT * FROM ALL_USERS;
The ALL_USERS table contains the following columns:
| Column | Explanation |
|---|---|
| USERNAME | Name of the user |
| USER_ID | Numeric ID assigned to the user |
| CREATED | Date that user was created |
DBA_USERS
If you need to find out all users that exist in Oracle or require more information about the user, there is also another system table called DBA_USERS.
The syntax to retrieve user information from the DBA_USERS table in Oracle/PLSQL is:
SELECT * FROM DBA_USERS;
The DBA_USERS table contains the following columns:
| Column | Explanation |
|---|---|
| USERNAME | Name of the user |
| USER_ID | Numeric ID assigned to the user |
| PASSWORD | Deprecated |
| ACCOUNT_STATUS | Status of the user such as:
|
| LOCK_DATE | Date that User was locked (if applicable) |
| EXPIRY_DATE | Date that User was expired |
| DEFAULT_TABLESPACE | Default tablespace for the user |
| TEMPORARY_TABLESPACE | Temporary tablespace for the user |
| CREATED | Date that user was created |
| PROFILE | User resource profile name |
| INITIAL_RSRC_CONSUMER_GROUP | Initial resource consumer group for the user |
| EXTERNAL_NAME | External name for the user |
| PASSWORD_VERSIONS | List of versions of the password hashes |
| EDITIONS_ENABLED | Y/N indicating whether editions have been enabled for the user |
| AUTHENTICATION_TYPE | Authentication method for the user |
| PROXY_ONLY_CONNECT | Y/N indicating whether a user can connect directly or by proxy only |
| COMMON | YES/NO indicating whether a user is common |
| LAST_LOGIN | Last login time |
| ORACLE_MAINTAINED | Y/N indicating whether a user was created and maintained by Oracle-suppllied scripts |
Advertisements