Tablespace Management

Tablespace Management:
-----------------------------
A tablespace is a logical group of data-files in a database.
A database is divided into one or more logical storage called tablespace. A tablespace must be at least one datafile,which is physically located in the system.

by default oracle creates system,sysaux datafile at the time of database creation.
Using sql statements we can create,alter and drop the tablespace.

1.Create tablespace Syntax :

CREATE TABLESPACE DATAFILE <'/LOCATION/'> SIZE AUTOEXTEND ON;

EX : CREATE TABLESPACE TEST DATAFILE '/DISK1/ORADATA/ORCL/TEST.DBF' SIZE 100M AUTOEXTEND ON;

Tablespace views:

Desc DBA_TABLESPACE
Desc DBA_DATA_FILES
Desc DBA_TEMP_FILES
Desc DBA_FREE_SPACE

we can't drop a tablespace which holds data in normal drop command.
 
we can drop the datafile which holds the data by specifying the keyword "including contents".
there are 3 types of table spaces : permanent, temporary and undo tablespace.


Example commands:

-- drop tablespace: 
DROP TABLESPACE <tablespace_name>;
DROP TABLESPACE tspace1;
 
-- drop tablespace including contents: 
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS;
DROP TABLESPACE tspace1 INCLUDING CONTENTS;
 
-- drop tablespace including contents & datafiles:      
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tspace1 INCLUDING CONTENTS AND DATAFILES;
 
-- drop tablespace including contents & datafiles 
   
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
DROP TABLESPACE tspace1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
 

In a tablespace we can have up to 1024 data files.
depending on the storage we have 2 table spaces one is smallest table space which is default.

In a big file tablespace we can have one data file. and the datafile size grow up to 128TB.

create table space datafile  size ;
example sql statements:

SELECT NAME FROM V$CONTROLFILE;

SELECT MEMBER FROM V$LOGFILE;

SELECT FILE NAME FROM DBA_DATA_FILES;

SELECT NAME FROM V$DATAFILES;

Alter tablespace add datafile '' size 100M;

EX: ALTER TABLESPACE TEST1 ADD DATAFILE '/ORADATA/ORCL/TEST1.DBF' SIZE 100M;

DESC DBA_DATA_FILES;

Resizing the datafile :

ALTER DATABASE DATAFILE '' RESIZE 10M;

EX: ALTER DATABASE DATAFILE '/ORADATA/ORCL/TEST.DBF' RESIZE 10M;

Drop Tablespace :

ALTER TABLESPACE DROP DATAFILE ''; " WE CANT DROP FIRST DATAFILE"

DEFAULT VIEW:
SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%DEFAULT%';

ALTER DATABASE DEFAULT TABLESPACE ;
TO MAKE A TABLESPACE AS DEFAULT TABLESPACE.

TEMP Tablespace :

CREATE TEMPORARY TABLESPACE TEMPFILE '' SIZE 10M;

EX: ALTER TABLESPACE TEMP3 ADD TEMPFILE '/ORADATA/ORCL/TEMP.DBF' SIZE 10M;

ALTER TABLESPACE TEMP DROP TEMPFILE '/ORADATA/ORCL/TEMP.DBF' ;