Tablespace Management

Tablespace

Tablespace :- Tablespace is a logical term is Oracle. Every Tablespace attach with one or more datafiles.

Types of Tablespace
1) Permanent (Default)
2) Temporary
3) Undo

Data Dictionary for Tablespace:-

SQL>select * from dba_tablespaces;
SQL>select * from DBA_DATA_FILES;

Creation of Tablespace:-

1) Permanent :- Permanent tablespace is a tablespace where stored all data of tables and indexes.

SQL>create tablespace example datafile 'E:\APP\ORADATA\ORCL\example.DBF' size 20M;

SQL>create tablespace example datafile 'E:\APP\ORADATA\ORCL\example.DBF' size 20M AUTOEXTEND ON NEXT 10M MAXSIZE 100M;

SQL>CREATE TABLESPACE example1 DATAFILE 'E:\example1.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Note:-AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM.
If you omit SIZE, then the default size is 1M. The following example creates a Locally managed
tablespace with uniform extent size of 256K


SQL>CREATE TABLESPACE example2 DATAFILE 'E:\APP\ORADATA\ORCL\example2.DBF' SIZE 50M EXTENT MANAGEMENT   LOCAL UNIFORM SIZE 256K;

To Create Dictionary Managed Tablespace:-
SQL>CREATE TABLESPACE ica_lmt DATAFILE 'E:\APP\ORADATA\ORCL\example5.DBF' SIZE 50M EXTENT MANAGEMENT DICTIONARY;

Bigfile Tablespaces (Introduced in Oracle Ver. 10g):-

SQL>CREATE BIGFILE TABLESPACE example4  DATAFILE 'E:\APP\ORADATA\ORCL\example4.DBF' SIZE 10G;


2) Temporary :- TEMPORARY Tablespace is used for sorting , joining and grouping of data.

SQL>create TEMPORARY tablespace temp1 TEMPFILE 'E:\APP\ORADATA\ORCL\TEMP1.DBF' size 500M;

Multiple Temporary Tablespaces:-Create group by adding existing tablespace.

SQL>ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;

Add a new tablespace to the group:-

SQL>CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/TEMP2.dbf' SIZE 20M TABLESPACE GROUP temp_ts_group;

To see the tablespace group details:-
SQL>SELECT * FROM dba_tablespace_groups;
 
3) Undo TABLESPACE:- Undo tablespace is used for rollback segment.

SQL>create undo tablespace undo1 datafile 'E:\APP\ORADATA\ORCL\undo02.DBF' size 500M;

Alter command for Tablespace:-Alter tablespace command is used for adding new datafile to tablespace.

SQL>alter TABLESPACE example1 add datafile 'E:\APP\ORADATA\ORCL\example3.DBF' size 500M;
SQL>alter database datafile 'E:\APP\ORADATA\ORCL\example2.DBF' resize 200M;


Rename the tablespace:-

SQL>ALTER TABLESPACE example RENAME example_new;

Setting the default tablespace for users:-
SQL>ALTER DATABASE DEFAULT TABLESPACE users;

To see the default and temporary tablespace configured in system:-
SQL>SELECT * FROM database_properties WHERE property_name like '%TABLESPACE';

DROP Tablespace:- drop tablespace command is used to drop the tablespace.

SQL>drop tablespace example1;

Drop Tablespace with All Datafiles :-

SQL>drop tablespace example2 INCLUDING datafiles;
SQL>drop tablespace example3 INCLUDING CONTENTS AND DATAFILES;

Check How Many Datafiles in a Tablespace :-

SQL>SELECT file_name, tablespace_name FROM dba_data_files WHERE tablespace_name ='EXAMPLE1';

How to resize the Datafile to Minimum Size:-

SQL>alter database datafile 'E:\APP\ORADATA\ORCL\example5.DBF' resize 10M;


Other important information:-
SELECT * FROM V$LOG;                              --To know Redolog information
SELECT * FROM V$LOGFILE;                     --To know Redolog information

SELECT * FROM V$CONTROLFILE;          --To know control file information

SELECT * FROM V$PARAMETER;              --To know parameter file information

Classroom Practical -

--DATA DICTIONARIES FOR TABLESPACE AND DATAFILES INFORMATION --- 

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%FILE%';

SELECT * FROM DBA_TABLESPACES;

SELECT * FROM DBA_DATA_FILES;

SELECT * FROM V$DATAFILE;

SELECT * FROM V$TEMPFILE;

----DATAFILES OPERATIONS-------

DROP TABLESPACE EXAMPLE;

DROP TABLESPACE EXAMPLE INCLUDING CONTENTS; 

DROP TABLESPACE EXAMPLE INCLUDING CONTENTS AND DATAFILES; 

CREATE TABLESPACE example 

      DATAFILE 'C:\ORACLE\ORADATA\ORCL\EXAMPLE2.DBF' size 10M;

CREATE TABLESPACE test1

      DATAFILE 'C:\ORACLE\ORADATA\ORCL\TEST01.DBF' SIZE 10M

      AUTOEXTEND ON

      NEXT 512K

      MAXSIZE 250M;

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\TEST01.DBF' 

    AUTOEXTEND OFF;

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\TEST01.DBF'

   RESIZE 20M;

   

ALTER DATABASE example 

      ADD DATAFILE 'C:\ORACLE\ORADATA\ORCL\EXAMPLE02.DBF' size 10M;

-----TEMPORARY FILES OPERATIONS------

DROP TABLESPACE TEMP02;

DROP TABLESPACE TEMP02 INCLUDING CONTENTS; 

DROP TABLESPACE TEMP02 INCLUDING CONTENTS AND DATAFILES; 

CREATE TEMPORARY TABLESPACE TEMP02 

      TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP02.DBF' size 10M;

CREATE TEMPORARY TABLESPACE TEMP03

      TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP03.DBF' SIZE 10M

      AUTOEXTEND ON

      NEXT 512K

      MAXSIZE 250M;

ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP03.DBF' 

    AUTOEXTEND OFF;

ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP03.DBF'

   RESIZE 20M;

   

----UNDO FILE OPERATIONS-------

DROP TABLESPACE UNDO02;

DROP TABLESPACE UNDO02 INCLUDING CONTENTS; 

DROP TABLESPACE UNDO02 INCLUDING CONTENTS AND DATAFILES; 

DROP TABLESPACE UNDO03 INCLUDING CONTENTS AND DATAFILES; 

CREATE UNDO TABLESPACE UNDO02 

      DATAFILE 'C:\ORACLE\ORADATA\ORCL\UNDO02.DBF' size 10M;

CREATE UNDO TABLESPACE UNDO03

      DATAFILE 'C:\ORACLE\ORADATA\ORCL\UNDO03.DBF' SIZE 10M

      AUTOEXTEND ON

      NEXT 512K

      MAXSIZE 250M;

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\UNDO02.DBF' 

    AUTOEXTEND OFF;

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\UNDO03.DBF'

   RESIZE 20M;