Using Find Users in Oracle / PLSQL in Oracle

This article is written about 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 using strolling a query from a command prompt. The consumer data is saved in a variety of device tables – ALL_USERS and DBA_USERS, depending on what person facts you want to retrieve.

ALL_USERS

If you need to discover all customers that are visible to the modern-day users, you can question the ALL_USERS table. The syntax to retrieve consumer records from the ALL_USERS desk in Oracle/PLSQL is:

SELECT *
FROM ALL_USERS;

The ALL_USERS table consists of the following columns:

Column Explanation USERNAME Name of the user USER_ID Numeric ID assigned to the user CREATED Date that consumer used to be created

DBA_USERS

If you want to find out all customers that exist in Oracle or require extra facts about the user, there is also every other device table known as DBA_USERS.

The syntax to retrieve user facts from the DBA_USERS desk in Oracle/PLSQL is:

SELECT *
FROM DBA_USERS;

The DBA_USERS desk carries 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 consumer such as: OPEN EXPIRED EXPIRED(GRACE) EXPIRED(TIMED) LOCKED EXPIRED & LOCKED(TIMED) EXPIRED(GRACE) & LOCKED(TIMED) EXPIRED & LOCKED EXPIRED(GRACE) & LOCKED LOCK_DATE Date that User used to be 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 person was created PROFILE User resource profile name INITIAL_RSRC_CONSUMER_GROUP Initial useful resource patron team for the user EXTERNAL_NAME External identify for the user PASSWORD_VERSIONS List of variations of the password hashes EDITIONS_ENABLED Y/N indicating whether variations have been enabled for the user AUTHENTICATION_TYPE Authentication approach for the user PROXY_ONLY_CONNECT Y/N indicating whether a user can connect at once or by way of proxy only COMMON YES/NO indicating whether or not a user is common LAST_LOGIN Last login time ORACLE_MAINTAINED Y/N indicating whether a person used to be created and maintained by means of Oracle-suppllied scripts