Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Show Free Space in a Tablespace

By: FYIcenter.com

(Continued from previous topic...)

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.

(Continued on next topic...)

  1. What Is an Oracle Tablespace?
  2. What Is an Oracle Data File?
  3. How a Tablespace Is Related to Data Files?
  4. How a Database Is Related to Tablespaces?
  5. How To View Tablespaces in the Current Database?
  6. What Are the Predefined Tablespaces in a Database?
  7. How To View Data Files in the Current Database?
  8. How To Create a New Oracle Data File?
  9. How To Create a New Tablespace?
  10. How To Rename a Tablespace?
  11. How To Drop a Tablespace?
  12. What Happens to Data Files If a Tablespace Is Dropped?
  13. How To Create a Table in a Specific Tablespace?
  14. How To See Free Space of Each Tablespace?
  15. How To Bring a Tablespace Offline?
  16. How To Bring a Tablespace Online?
  17. How To Add Another Datafile to a Tablespace?
  18. What Happens If You Lost a Data File?
  19. How Remove Data Files before Opening a Database?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...