Free space in Temporary Tablespace

A default temporary tablespace is specified in a database at the time of the database creation,By using DEFAULT TEMPORARY TABLESPACE in the create database statement.You can drop the default temporary tablespace,then system tablespace will be used as default temporary tablespace.Default temporary tablespace cannot make permanent or take it offline.The DBA_FREE_SPACE view allows you to show about free space in a tablespace but it shows only about permanent tablespace. You have to query V$TEMP_SPACE_HEADER view to know about the free space of temporary tablespace.You can see the use of DBA_FREE_SPACE and V$TEMP_SPACE_HEADER view as follows:

1) If you have TEST as permanent tablespace and TEMP as temporary tablespace and you run query DBA_FREE_SPACE then it will show TEST in list the list but TEMP is not listed.
 
SQL>select tablespace_name from dba_free_space;

TABLESPACE_NAME
----------------
UNDO1
SYSAUX
SYSTEM
TEST

4 rows selected.

2) To know information about free space of temporary tablespace

SQL>select tablespace_name.file_id,bytes_used,bytes_free from v$temp_space_header;

TABLESPACE_NAME   FILE_ID  BYTES_USED BYTES_FREE
----------------- -------- --------- ------------
TEMP              5894714  19625687
 

People who read this post also read :



0 comments:

Post a Comment

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More