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.
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
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
0 comments:
Post a Comment