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

With Password Expire option, create an user, which will prompt for new password upon login:

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