Monday, May 12, 2008

Free space in Temporary Tablespace

In database there may be permanent and temporary tablespace. The view DBA_FREE_SPACE allows us to show about how much free space in a tablespace have but DBA_FREE_SPACE shows information only about permanent tablespace. It does not show information about temporary tablespace.

In order to know about temporary tablespace free space usage we have to query V$TEMP_SPACE_HEADER view. With an example it is described.

1)Create one temporary and Permanent Tablespace.
I used OMF file system. To know about it search about OMF in my blog.

SQL> ALTER SYSTEM SET db_create_file_dest='/oradata2';
System altered.

SQL> CREATE TABLESPACE PERMANENT_T;
Tablespace created.

SQL> CREATE TEMPORARY TABLESPACE TEMP_T;
Tablespace created.

2)Query DBA_FREE_SPACE and we will see PERMANENT_T is listed but TEMP_T is not listed.
SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_FREE_SPACE;


TABLESPACE_NAME
------------------------------
UNDOTBS1
SYSAUX
USERS
DATA
SYSTEM
PERMANENT_T

6 rows selected.

3)To know information about free space of temporary tablespace query V$TEMP_SPACE_HEADER.

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;

TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
------------------------------ ---------- ---------- ----------
TEMP 1 6291456 14680064
TEMP_T 2 1048576 103809024

2 comments:

Anonymous said...

You are about 10% right.
I've noticed that the bytes_free from this statement is not the true one. I've found another statement on the web that displayis it and modified it a lil bit.
Here it is

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free, t.file_name
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D,
dba_temp_files t
WHERE A.tablespace_name = D.name and a.tablespace_name = t.tablespace_name
GROUP by A.tablespace_name, D.mb_total, t.file_name;

Anonymous said...

Combination of two gives usable free space: v$sort_segment gives the already alocated and free within that. combine this with free space as given in v$temp_space_header to get total free temp space at that time