CREATING AND DELETING USER IN ORACLE

CREATING AND DELETING USER IN ORACLE

Oracle with Linux,  Commands

Command for creating a new user in oracle : 

CREATE USER <<user name>> IDENTIFIED BY <<user name>> DEFAULT TABLESPACE <<table space name >> TEMPORARY TABLESPACE  temp QUOTA 50M ON <<table-space name>>;

 In oracle users can be created by the above syntax  ,but before creating a new user one have to create table space .

Table space : Space which is obtained by tables for particular  user.

Syntax for creating TableSpace :

CREATE TABLESPACE  <<table space name >> DATAFILE  'diskb:tbs_<<table space name >>.dat' SIZE 50M REUSE  AUTOEXTEND ON NEXT 50M MAXSIZE 100M;

Now your table space and user space is defined. while connecting your user-name is name of user which you have given in create user command and default password is also same as user- name.

Now you have to give some permissions to the new user :

GRANT create session TO <<user-name>>;

GRANT create table TO <<user-name>>;
GRANT create view TO <<user-name>>;

Now if you want to delete the existing user , the command is :

DROP USER <<user name>> CASCADE;

and if you to delete the user including all its table-space and data files :

firstly drop the user :

DROP USER <<user name>> CASCADE;

 Now after droping user for each including table-space you have to take the table-space offline and then drop it.

ALTER TABLESPACE <<table space name >> OFFLINE;
DROP TABLESPACE <<table space name >> INCLUDING CONTENTS;

Now the user and all of its table -spaces are deleted. and the space assigned to them are free to be re-used.