fastread homefastrread library fastread menu

Oracle/PLSQL : Find Users in Oracle/PLSQL

Tutorial by:      Date: 2016-04-15 04:48:15

❰ Previous Next ❱

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:

  • OPEN
  • EXPIRED
  • EXPIRED(GRACE)
  • EXPIRED(TIMED)
  • LOCKED
  • EXPIRED & LOCKED(TIMED)
  • EXPIRED(GRACE) & LOCKED(TIMED)
  • EXPIRED & LOCKED
  • EXPIRED(GRACE) & LOCKED

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

 

❰ Previous Next ❱


Oracle/PLSQL

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website