Tablespace management related commands

Tablespace is a logical structure in database.Tablespace is the grouping of one or more datafiles which are physical structures that store database data.
Create tablespace:
SQL>create tablespace tsname
datafile ‘/pathofdatafile’ size datafile-size
extent management local — default.other value is dictionary
autoallocate size — other value is uniform
segment space management auto; — default is auto.Other value is manual
To check if the datafile has been created successfully use :
SQL>select extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name=’tsname’;
Remove tablespace :
SQL>drop tablespace tsname;
If the tablespace has objects like table,index when we issue the above command we get error.So use:
sql>drop tablespace tsname including contents;
To physically remove the datafile as well use:
sql>drop tablespace tsname including contents and datafiles;
To drop referential integrity constraints use:
sql>drop tablespace tsname cascade constraints;
Alter the tablespace size – increase and decrease.If tablespace is already occupied it is not possible to decrease the size of the tablespace.
sql>alter tablespace tsname add datafile ‘/datfile-path’ size datafile-size;
To change the size of a datafile:
sql>alter database datafile ‘/datafilepath’ resize new-datafilesize