Create and Drop User account in Oracle

Create and Drop User account in Oracle

Check the USER present in Oracle database

col username for a22
col account_status for a17
col profile for a8
col default_tablespace for a10
col common for a3
SELECT USERNAME,account_status,profile,default_tablespace,common FROM DBA_USERS;

Create user commands in Oracle with different options

CREATE USER user1 IDENTIFIED BY new_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
QUOTA 50M ON data01
QUOTA 100M ON temp
PROFILE sales
ACCOUNT UNLOCK
PASSWORD EXPIRE;

Note:
USER1 is having two tablespace one default USERS and other temporary TEMP. Set quota on 3 tablespace.
USER1 is assigned profile SALES and account is unlocked and password expire means change password by user on first login.

Alter User commands in Oracle
Change existing setting of user by using alter command.

ALTER USER user1 IDENTIFIED BY new_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M on users
QUOTA 100M on temp
PROFILE hr;

Drop user command

-- It worked when user1 is empty means no object
DROP USER user1;

-- if user1 having objects then use CASCADE option to drop the user and its objects
DROP USER user1 CASCADE;

Note: If you tried to drop without empty the HR Schema then error ORA-01922 occurs as show below:

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

SQL> drop user hr;
drop user hr
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'HR'

Solution:

-- use cascade option
DROP USER HR CASCADE;

Check the resource limit

SELECT * FROM USER_PASSWORD_LIMITS;

RESOURCE_NAME LIMIT
-------------------------------- ------------
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.