How to create & manage tablespace in oracle

Hello Everyone,

As a name suggest we are going to create and manage tablespace in oracle before starting we need to know understand  what is tablespace first for instance go through below link 

"Introduction to tablespace"

1. How to Create Tablespace on oracle   

CREATE SMALLFILE TABLESPACE "VVS" 
DATAFILE '/u01/app/oracle/oradata/vvs/vvs.dbf' SIZE 100M 
AUTOEXTEND ON NEXT 100K MAXSIZE 10M 
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

  Mainly tablespace divided into two type which are Small file Tablespace and Big file Tablespace 

 Small File Tablespace :- you can create and add more the one datafile depends on your HDD size         

 Big File Tablespace :- In Big File you are not allowed to create more then one Datafile  

2. if you want to add more datafile in existing tablespace.

    
ALTER TABLESPACE "VVS" 
ADD DATAFILE '/u01/app/oracle/oradata/vvs/vvs2.dbf' SIZE 100M 
AUTOEXTEND ON NEXT 100K MAXSIZE 10M;

3.for removing datafile from existing tablespace

  

alter tablespace vvs drop datafile '/u01/app/oracle/oradata/vvs/vvs2.dbf'

4.For resize existing datafile 

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/vvs/vvs.dbf' RESIZE 60M;
     ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/vvs/vvs2.dbf' AUTOEXTEND ON NEXT 10M;
 

5. for removing tablespace 

drop tablespace vvs;
[it will delete tablespace only but if OMF(ORACLE MANAGED FILE) in enable then it will delete datafile also.] 

6. When you need to delete datafile also even in non-OMF oracle system then follow command.

drop tablespace vvs including contents and datafiles;
if tablespace is empty then you cant use drop tablespace command without including contents otherwise 
you have to use including contents at last of command

7. to associate table with specific tablespace

create table vt1 (no number) tablespace vvs; 

8.to associate tablespace while creating user

create user vu1 identified by vu1 default tablespace vvs

9. to associate tablespace on existing user 

alter user vu1 default tablespace vvs
(you can also change the user's password while changing default tablespace)
alter user u1 identified by u1 default tablespace vvs;

10. to change default undo tablespace

show parameter undo --> then change it with alter system command.


Thanks,