Oracle Tablespace

Hey guys, in this blog I am going to explain to you about Oracle Tablespace.

Oracle Tablespace: - 

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. 

A table space is a storage structure; it contains tables, indexes, large objects, and long data. 

Type of 3 kinds Data Consists of tablespace including:  

 

• Permanent Tablespace 

• Temporary Tablespace 

• Undo Tablespace 

And on the basis of Size of Data we have 2 kinds of tablespace: 

 

• Big file tablespace 

• Small file tablespace 

To Check Tablespace Utilization: -

 

With the help of below sql query check the tablespace utilization. 

 

set colsep | 

set linesize 100 pages 100 trimspool on numwidth 14  

col name format a25 

col owner format a15  

col "Used (GB)" format a15 

col "Free (GB)" format a15  

col "(Used) %" format a15  

col "Size (M)" format a15  

SELECT d.status "Status", d.tablespace_name "Name",  

 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)",  

 TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)",  

 TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",  

 TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %" 

 FROM sys.dba_tablespaces d,  

 (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,  

 (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE  

 d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT  

 (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')  

UNION ALL  

SELECT d.status  

 "Status", d.tablespace_name "Name",  

 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)",  

 TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)", 

 TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",  

 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %"  

 FROM sys.dba_tablespaces d,  

 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,  

 (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t  

 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND  

 d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'; 

Permanent Tablespace in Oracle Database: - 

 

It is the tablespace which contains persistent schema object which means the data stored in the permanent tablespace persists beyond the duration of a session or transaction. Objects in permanent tablespaces are stored in datafiles. 

 

To check data file: - 

select  name from v$datafile; 

To create permanent Tablespace in oracle: - 

Create smallfile tablespace clota_tech Datafile ‘/u02/oradata/CLOTADB/clota_tech.dbf size’100M; 

 

Temporary Tablespace in Oracle Database: - 

 

On the contrary, temporary tablespace are the ones which contain schema objects only for the duration of a session which means that data stored in the temporary tablespace exists only for the duration of a session or a transaction. Objects in temporary tablespaces are stored in temp files. 

 

To create Temporary Tablespace in oracle: - 

 

createtemporary tablespace tablespace _name tempfile’c:\app\tablespace_name.dbf size’ 100M; 

 

To check tempfile: - 

Undo Tablespace in Oracle Database: -  

 

Then there comes Undo tablespace. Undo tablespace is a special type of tablespace used by Oracle database to manage undo data if you are running your database in automatic undo management mode. Undo tablespace stores data permanently database in automatic undo management mode. Undo tablespace stores data permanently which means that undo tablespace is permanent in nature. Undo tablespace play a vital role in providing. that undo tablespace is permanent in nature. Undo tablespace play a vital role in providing. 

Read consistency for SELECT statements that access tables which in turn consist of rows which are in

the process of being modified. 

The ability to rollback a transaction that has failed to commit. 

To create Undo Tablespace: - 

Create smallfile undo tablespace tablespace_name   Datafile’c:\app\ tablespace_name.dbf’

size 100M; 

Next, we have Big-file tablespace and small file tablespace:

- 

Big-file Tablespace in Oracle Database: - 

The new concept started from Oracle 10g. Big file tablespace is best suited for storing large amounts of data. Big file tablespace can have a maximum of 1 datafile which means big file tablespaces are built on single data files which can be as big as 232 data blocks in size. So, a big file tablespace that uses 8KB data blocks can be as much as 32TB in size. 

Small-file Tablespace In Oracle Database: -  

This is the default type of tablespace in Oracle database. Small file tablespace can have multiple datafiles and each datafile can be as much as 222 data blocks in size. A small file tablespace can have maximum up to 1022 data files but this number depends on your Operating system also.