How To See Free Space of Each Tablespace?

Submitted by: Administrator
One of the important DBA tasks is to watch the storage usage of all the tablespaces to make sure there are enough free space in each tablespace for database applications to function properly. Free space information can be monitored through the USER_FREE_SPACE view. Each record in USER_FREE_SPACE represents an extent, a contiguous area of space, of free space in a data file of a tablespace.

Here is SQL script example on how to see free space of a tablespace:

SQL> connect HR/globalguideline
Connected.

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('USERS', 'MY_SPACE');
Submitted by: Administrator

<pre>TABLESPACE_NAME FILE_ID BYTES
------------------------------ ---------- ----------
MY_SPACE 5 10354688
USERS 4 101974016
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536</pre>
This tells us that:

► MY_SPACE has a single free extent of 10MB.
► USERS has one big free extent of 100MB, and many small free extents of 64KB.

Submitted by: Administrator

Read Online Oracle Database Job Interview Questions And Answers