User Management

User Management

User Management:-In Oracle sometime, user name is also known as schema. Schema is a collection of all objects under one user.

Create a User:- create user command is used for creation of user. Below is the shortest syntax for creation of user  

create user IDENTIFIED by ;
Example:- create user abc identified by abc;

User creation with default and temporary Tablespace:-
Example:-
create user abc IDENTIFIED by abc
default temporary tablespace temp
default tablespace users;

User creation with default and temporary Tablespace and quota on tablespace:-

create user abc IDENTIFIED by abc
default temporary tablespace temp
default tablespace users
quota 10M on users;

create user abc IDENTIFIED by abc
default temporary tablespace temp
default tablespace users
quota unlimited on users;

Drop a User:- drop user command for dropping a user . 
Below is the syntax for drop a user when no objects exists under that user

drop user ;
 Example:- drop user abc;

If user have some objects then below syntax is used to drop a user
drop user cascade;
Example:- drop user abc cascade;

 Alter a User:- alter user command for change the user properties like password default and temporary tablespace, lock and unlock etc.

Examples:-
1) Lock and Unlock user account 
alter user abc account unlock;
alter user abc account lock;

2) Change the password of user account 
alter user abc identified by abc;

3) change the default and temporary tablespace
alter user abc default tablespace example;
alter user abc TEMPORARY tablespace temp1;

4) change the user profile 
 alter user abc profile MONITORING_PROFILE;

5) change the quota on tablespace
 alter user xyz quota unlimited on users;
 alter user xyz quota 5M on users;