Generally speaking, to execute the ALTER USER statement, your account must have the ALTER USER system privilege. However, you’ll change your own password using the ALTER USER statement without having the ALTER USER system privilege.

Let’s create a user named dolphin and grant the CREATE SESSION system privilege to dolphin:

CREATE USER dolphin IDENTIFIED BY abcd1234;

GRANT CREATE SESSION TO dolphin;

1) Using Oracle ALTER USER statement to vary the password for a user

The following example uses the ALTER USER statement to vary the password for the user dolphin:

ALTER USER dolphin IDENTIFIED BY xyz123;

Enter user-name: dolphin@pdborcl

Enter password:

The user dolphin should be ready to authenticate to the Oracle Database using the new password xyz123

2) Using Oracle ALTER USER statement to lock/unlock a user

This example uses the ALTER USER statement to lock the user dolphin:

ALTER USER dolphin ACCOUNT LOCK;

If you employ the user dolphin to log in to the Oracle Database, you ought to see a message indicating that the user is locked:

Enter user-name: dolphin@pdborcl

Enter password:

ERROR:

ORA-28000: the account is locked

To unlock the user dolphin, you employ the subsequent statement:

ALTER USER dolphin ACCOUNT UNLOCK;

Now, the user dolphin should be ready to log in to the Oracle Database.

3) Using Oracle ALTER USER statement to line user’s password expired

To set the password of the user dolphin expired, you employ the subsequent statement:

ALTER USER dolphin PASSWORD EXPIRE;

When you use the user dolphin to log in to the database, Oracle issues a message indicating that the password has expired and requests for the password change as follows:

Enter user-name: dolphin@orclpdb

Enter password:

ERROR:

ORA-28001: the password has expired

Changing password for dolphin

New password:

Retype new password:

Password changed

4) Using Oracle ALTER USER statement to line the default profile for a user

This statement returns the profile of the user dolphin:

SELECT

username,

profile

FROM

dba_users

WHERE

username =’DOLPHIN’;

When you create a replacement user without specifying a profile, Oracle will assign the DEFAULT profile to the user.

Let’s create a replacement user profile called ocean:

CREATE PROFILE ocean LIMIT

SESSIONS_PER_USER UNLIMITED

CPU_PER_SESSION UNLIMITED

CPU_PER_CALL 3000

CONNECT_TIME 60;

and assign it to the user dolphin:

ALTER USER dolphin

PROFILE ocean;

Now, the default profile of the user dolphin is ocean.

5) Using Oracle ALTER USER statement to line default roles for a user

Currently, the user dolphin has no assigned roles as shown within the output of the subsequent query when executing from the dolphin’s session:

SELECT * FROM session_roles;

First, create a replacement role called rescue from the user OT‘s session:

CREATE ROLES rescue;

GRANT CREATE TABLE, CREATE VIEW TO rescue;

Second, grant this role to dolphin:

GRANT rescue TO dolphin;

Third, use the user dolphin to log in to the Oracle Database. The default role of the user dolphin is rescue now.

SELECT * FROM session_roles;

Here is that the output:

ROLE

———

RESCUE

Fourth, create another role called super and grant all privileges to the present role:

CREATE ROLE super;

GRANT ALL PRIVILEGES TO super;

Fifth, grant the role super to the user dolphin:

GRANT super TO dolphin;

Sixth, set the default role of the user dolphin to super:

ALTER USER dolphin DEFAULT ROLE super;

Seventh, disconnect the present session of the user dolphin and log in to the Oracle Database again. The default role of the user dolphin should be super as shown within the output of the subsequent query:

SELECT * FROM session_roles;

The following shows the output:

ROLE

———

SUPER

Oracle DROP USER

Introduction to Oracle DROP USER statement

The DROP USER statement allows you to delete a user from the Oracle Database. If the user has schema objects, the DROP USER statement can also remove all the user’s schema objects along side the user.

The following illustrates the essential syntax of the DROP USER statement:

DROP USER username [CASCADE];

In this syntax, you would like to specify the user that you simply want to drop after the DROP USER keywords.

If the user whose schemas contain objects like views and tables, you would like to delete all schema objects of the user first then drop the user.

Deleting all schema objects of the users first before removing the user is sort of tedious. Therefore, Oracle provides you with the CASCADE option.

If you specify the CASCADE option, Oracle will remove all schema objects of the user before deleting the user.

If the schema objects of the dropped user are referenced by objects in other schemas, Oracle will invalidate these objects after deleting the user.

If a table of the dropped user is referenced by materialized views in other schemas, Oracle won’t drop these materialized views. However, the materialized views can not be refreshed because the bottom table doesn’t exist anymore.

Note that Oracle doesn’t drop roles created by the user even after it deletes the user.

Notice that if you plan to delete the user SYS or SYSTEM, your database will corrupt.

Oracle DROP USER statement examples

Let’s take some samples of removing a user from the database.

1) Using Oracle DROP USER to get rid of a user that has no schema object

First, log in to the Oracle database using the user ot using SQL*Plus:

Enter user-name: ot@pdborcl

Enter password:

Second, create a replacement user called foo:

CREATE USER foo IDENTIFIED BY abcd1234;

Third, drop the user foo using the DROP USER statement:

DROP USER foo;

You should see the subsequent message:

User dropped.

Because the user foo has no schema objects, you’ll delete it without specifying the CASCADE option.

2) Using Oracle DROP USER to delete a user that has schema objects

First, create a replacement user called bar and grant the CREATE SESSION and make TABLE system privileges to the user:

CREATE USER bar

IDENTIFIED BY abcd1234

QUOTA 5m ON users;

GRANT

CREATE SESSION,

CREATE TABLE

TO bar;

Second, use the user bar to log in to the Oracle database:

Enter user-name: bar@pdborcl

Enter password:

Third, create a replacement table named t1 within the bar user’s schema:

CREATE TABLE t1(

id NUMBER PRIMARY KEY,

v VARCHAR2(100) NOT NULL

);

INSERT INTO t1(id,v)

VALUES(1,’A’);

Fourth, return to the session of the user ot and drop user bar:

DROP USER bar;

Oracle issued the subsequent error:

ORA-01940: cannot drop a user that’s currently connected

Fifth, end the user bar’s session first using the exit command:

exit

And issue the DROP USER statement again within the user ot session:

DROP USER bar;

Oracle issued the subsequent message:

ORA-01922: CASCADE must be specified to drop ‘BAR’

You could not delete the user bar without specifying CASCADE because the user bar has the table t1 as a schema object.

Seventh, use the DROP USER statement with CASCADE choice to delete the user bar:

DROP USER bar CASCADE;

Oracle could delete the user bar and also the table t1.

So, this brings us to the end of blog. This Tecklearn ‘Oracle ALTER USER and DROP USER’ 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

533