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
tablespace_name=
'[TABLESPACE_NAME]';

To know the free space size of a tablespace.

select sum(bytes)/1024/1024 from dba_free_space where
tablespace_name=
'[TABLESPACE_NAME]';

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 =
'[TABLESPACE_NAME]';

No comments: