Tuesday, December 12, 2006

RDBMS:Tablespace Size revisited

At times, we encounter issues around tablespace size. Here are few handy queries using which we can do a first hand analysis of the tablespace size and other related information.

Sample error message related to tablespace, while uploading huge data comes as below..

ORA-01653: unable to extend table [TABLE_NAME] by 128 in tablespace [TABLESPACE_NAME]

To know the size of a tablespace.

select sum(bytes)/1024/1024 tbspace_size from dba_data_files where

To know the free space size of a tablespace.

select sum(bytes)/1024/1024 from dba_free_space where

Identify the Temp Tablespace size used for sorting,hashing etc

select decode(count(1),0,'Empty Temp Tablespace',sum(bytes)) Temp_Table_Space_Size_In_Bytes
from dba_temp_files where status =

No comments: