How To See Free Space of Each Tablespace?
Page 1 of 1
How To See Free Space of Each Tablespace?
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/fyicenter
Connected.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('USERS', 'MY_SPACE');
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
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.
Here is SQL script example on how to see free space of a tablespace:
SQL> connect HR/fyicenter
Connected.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('USERS', 'MY_SPACE');
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
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.
Similar topics
» How To Drop a Tablespace?
» How To Add Another Datafile to a Tablespace?
» What Happens to the Data Files If a Tablespace Is Dropped?
» What Is an Oracle Tablespace?
» How To Create a New Tablespace?
» How To Add Another Datafile to a Tablespace?
» What Happens to the Data Files If a Tablespace Is Dropped?
» What Is an Oracle Tablespace?
» How To Create a New Tablespace?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal