How To List Users within the Oracle Database

Last updated on Dec 20 2021
Sankalp Joshi

Table of Contents

How To List Users within the Oracle Database

TL;DR

List all users that are visible to the present user:

SELECT * FROM all_users;

List all users within the Oracle Database:

SELECT * FROM dba_users;

Show the knowledge of the present user:

SELECT * FROM user_users;

Oracle ALL_USERS

The ALL_USERS view lists all users that visible to the present user. However, this view doesn’t describe the users.

The following statement returns all users within the Oracle Database, sorted by created go back the newest to the earliest:

SELECT * FROM all_users
ORDER BY created;

Here is that the output:

 

This table illustrates the meanings of every column within the ALL_USERS view:

Column Datatype NULL Description
USERNAME VARCHAR2(128) NOT NULL The 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 simply shouldn’t 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 may be a common user created by an implicit application (YES) or not (NO)
ALL_SHARD VARCHAR2(3) In a sharded database, this column has either one among two possible values: YES and NO:

• YES: The user was created with shard DDL enabled. The user exists on all shards and therefore the shard catalog.

• NO: The user was created without shard DDL enabled. The user exists only within the database during which the user was created.

In a non-sharded database, the value is usually NO during this column.

 

Oracle DBA_USERS

The DBA_USERS view describes all user within the Oracle database.

The following statement returns all users within the Oracle Database, sorted by created go back the newest to the earliest:

SELECT * FROM DBA_USERS
ORDER BY created DESC;

This picture shows the partial output:

 

This table explains the meaning of every column within the DBA_USERS view:

Column Datatype NULL Description
USERNAME VARCHAR2(128) NOT NULL The name of the user
USER_ID NUMBER NOT NULL The ID of the user
ACCOUNT_STATUS VARCHAR2(32) NOT NULL Account status of the user. Here is the valid list:

  • OPEN
  • EXPIRED
  • EXPIRED(GRACE)
  • LOCKED(TIMED)
  • LOCKED
  • EXPIRED & LOCKED(TIMED)
  • EXPIRED(GRACE) & LOCKED(TIMED)
  • EXPIRED & LOCKED
  • EXPIRED(GRACE) & LOCKED
LOCK_DATE DATE The date on which the account was locked if the account status is LOCKED
EXPIRY_DATE DATE Expired 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
LOCAL_TEMP_TABLESPACE VARCHAR2(30) The default local temporary tablespace for the user
CREATED DATE NOT NULL The date on which the user was created
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128) The initial resource consumer group for the user
EXTERNAL_NAME VARCHAR2(4000) The external name of the user
PROXY_ONLY_CONNECT VARCHAR2(1) Specify if a user can connect directly (N) or can only be proxied (Y) by users who have proxy privileges for this account.
COMMON VARCHAR2(3) Specifies if a user is common ( YES) or Local ( NO)
ORACLE_MAINTAINED VARCHAR2(1) Indicate 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 sharded database, this column has either one of two possible values: YES and NO:

  • YES: The user was created with shard DDL enabled. The user exists on all shards and the shard catalog.
  • NO: The user was created without shard DDL enabled. The user exists only in the database in which the user was created.

In a non-sharded database, the value is always NO in this column.

 

Oracle USER_USERS

THe USER_USERS view describes the present user:

SELECT * FROM user_users;

Here is that the result set:

In this blog, you’ve got learned the way to list users within the Oracle Database using the ALL_USERS, DBA_USERS, and USER_USERS views.

So, this brings us to the end of blog. This Tecklearn ‘How to List Users in Oracle Database’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

  • The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
  • According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
  • IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

  • What is the Oracle Database
  • Pre-requisites for oracle database
  • Oracle database 12c installation
  • Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

  • Explain the Memory Structures
  • Describe the Process Structures
  • Overview of Storage Structures

Preparing the Database Environment

  • Identify the tools for Administering an Oracle Database
  • Plan an Oracle Database Installation
  • Install the Oracle Software by using Oracle Universal Installer (OUI)
  • Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

  • Setting database initialization parameters
  • Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

  • Managing Database Storage Structures
  • Overview of table space and data files
  • Overview of Primary Components
  • Identify the importance of checkpoints, redo log files, and archived log files
  • Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

  • Describe the stages of database startup and shutdown
  • Using alert log and trace files
  • Configure and Manage the Oracle Network
  • pFile and spFile
  • Tablespace Management
  • Storage and Relationship Structure

Oracle Backup & Recovery

  • Redo log File Management
  • Performing Database Recovery
  • Overview of Data Recovery Advisor
  • Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

  • Using the RMAN Recovery Catalog
  • Identify situations that require RMAN recovery catalog
  • Create and configure a recovery catalog
  • Synchronize the recovery catalog
  • Create and Use RMAN stored scripts
  • Back up the recovery catalog
  • Create and use a virtual private catalog

Configuring Backup Specifications

  • Using RMAN to Create Backups
  • Create image file backups
  • Create a whole database backup
  • Enable fast incremental backup
  • Using RMAN to Perform Recovery
  • Perform complete recovery from a critical or noncritical data file loss using RMAN
  • Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

  • Moving Data
  • Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
  • Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

  • Oracle Data Dictionary contains information related to database privileges
  • Data dictionary views
  • Media failure
  • Flashback CDB

Database Tuning

  • What is database tuning
  • How to tune the database
  • Tuning the data dictionary

Got a question for us? Please mention it in the comments section and we will get back to you.

0 responses on "How To List Users within the Oracle Database"

Leave a Message

Your email address will not be published. Required fields are marked *