Listing Tablespaces

Overview

list_tablespaces is a script that will tell you about the tablespaces in your database. For each tablespace, it will display the following items of information:

  • The tablespace name
  • The total size (in bytes)
  • The space used (in bytes)
  • The free space (in bytes)

This script currently has one limitation. It will not list information for any tablespace that is completely empty. For this reason, you often won't see temporary tablespaces listed in the output.

Download the Script

Click the following link to download the list_tablespaces script.

list_tablespaces.sql

Running the list_tablespaces Script

To run this script, you must have access to the DBA_FREE_SPACES and DBA_EXTENTS data dictionary views.

Here is an example showing list_tablespaces in action:

    SQL> @list_tablespaces

    Tablespace             Free Bytes   Used Bytes  Total Bytes
    -------------------- ------------ ------------ ------------
    ROLLBACK_DATA             325,632    4,915,200    5,240,832
    SYSTEM                 10,098,688   21,356,544   31,455,232
    USER_DATA               2,191,360      952,320    3,143,680
                        ------------ ------------ ------------
    Totals                 12,615,680   27,224,064   39,839,744


    Commit complete.

    SQL>