When it comes to databases in general and oracle specifically, we hear the term ” tablespace ” when trying to create a database object. there is some question that is coming into the picture related to tablespaces in oracle, such as :

1. What are the tablespaces available in our database schema?
2. How to check the tablespace size in Oracle?
3. How to check tablespace free space in Oracle?
4. How to check tablespace datafile location in Oracle?
5. How to check tablespace growth in Oracle?

So that’s why we have created this guide. now let’s get started.

List of tablespaces name available in Oracle

To get the list of all tablespaces available in our schema, we can query the DBA_TABLESPACES data dictionary view. Still, if your user has DBA privileges, otherwise ORA-00942: table or view does not exist, an error will arise. All you have to do is switch the user login to the system or sys users or grant DBA privilege to your user to see the tablespaces pieces of information.

Or the other option is to query the USER_TABLESPACES data dictionary view, which contains the user tablespace pieces of information.

Check Tablespace Name in Oracle Using Query :

SELECT TABLESPACE_NAME,CONTENTS FROM DBA_TABLESPACES;

OR

SELECT TABLESPACE_NAME,CONTENTS FROM USER_TABLESPACES;

In the above query, we have listed all tablespaces available in our schema and the contents column, which have the value of three (undo, permanent or temporary). You can refer to the oracle database reference here to see the above table columns’ complete list with each description.

Check Tablespace Name in Oracle SQL Developer

1. Open SQL Developer, then connect to the user with DBA privileges to see the tablespace pieces of information.
2. On the SQL Developer’s left-hand side in the connections panel, do right-click on the connected user in the above step.
3. Choose Manage Database from the menu.
4. The system tab will open to display the tablespace information shown in the below screen on the right-hand side.

Check Tablespace Name in Oracle Using Toad

1. Open Toad, then connect to the user with DBA privileges to see the tablespace pieces of information.
2. From the menu bar, click on Database.
3. Choose Administer from the database menu, then choose Tablespaces.
4. The Tablespaces tab will open to display the tablespace information’s as shown in the below screen on the top toolbar.

Check Tablespace Size in Oracle

We can get the tablespace size in oracle using query or SQL Developer or TOAD; all of these options are available to get the exact size of a particular tablespace or all tablespaces in the Database.

Get Tablespace Size in Oracle Using Query

To get the size of a tablespace in oracle, we have to query a view called ()DBA_DATA_FILES). This view has the size information of tablespaces in bytes.

SELECT   TABLESPACE_NAME, ROUND (SUM (BYTES) / 1048576) "Total Size MB"
        FROM     DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME

The result of the above query will be :

In the above query, we have selected the tablespace name and size in megabytes.

Note: To get the tablespace size from SQL Developer or TOAD, follow the steps mentioned above.

Check Tablespace Free Space in Oracle

To check the free space of a tablespace in Oracle, we can query two views (DBA_DATA_FILES & DBA_SEGMENTS).

SELECT DFQ.TABLESPACE_NAME "Tablespace Name"
      ,DFQ.TOTALSPACE "Total Size MB"
      , (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) "Free Space MB"
      ,ROUND (100 * ( (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) / DFQ.TOTALSPACE)) || '%' "Free Space %"
FROM   (SELECT   TABLESPACE_NAME, ROUND (SUM (BYTES) / 1048576) TOTALSPACE
        FROM     DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) DFQ
      ,(SELECT   TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024)) TOTALUSEDSPACE
        FROM     DBA_SEGMENTS
        GROUP BY TABLESPACE_NAME) DSQ
WHERE  DFQ.TABLESPACE_NAME = DSQ.TABLESPACE_NAME(+);

The result of the above query will be :

In the above query, we have selected all tablespaces names, the total size of each tablespace, tablespace free space size in MB, and another column for showing the percentage of the free space from the entire length of the tablespace size.

Check Tablespace Datafile location in Oracle

Each tablespace that is the logical space to store the data has its datafile, which is the physical files containing the server’s data.
We have a (DBA_DATA_FILES) data dictionary view that holds the data files of each tablespace.

Query to get tablespace datafile location in Oracle

SELECT TABLESPACE_NAME
      ,FILE_ID
      ,FILE_NAME
      ,STATUS
FROM   DBA_DATA_FILES;

The result of the above query will be :

In the above query, we have selected the tablespace name, file identifier (FILE_ID), the full path of the datafile name in the server, and the status of each datafile.

Note: If you want to know more about drop and recreate, go check this article: Drop and Recreate Tempfile in Oracle

Check Tablespace Growth in Oracle

To get a tablespace’s growth, we can query the data dictionary view DBA_HIST_TBSPC_SPACE_USAGE, which holds the historical usage of a tablespace to predict future growth. See the oracle reference for this table from here. It’s available in AWR or Automatic Workload Report.

There is one important note to keep in mind, which is a retention period. The default value of this retention period is seven days. Querying the above data dictionary view will get the history usage for the last seven days. We recommend changing this default setting of the AWR retention period to 30 days. Changing this setting will help performance comparison because you look for a more considerable historical data period.

SELECT   TO_CHAR (SP.BEGIN_INTERVAL_TIME, 'DD-MM-YYYY') HISTORY_DATE
        ,TS.TSNAME TABLESPACE_NAME
        ,MAX (ROUND ( (TSU.TABLESPACE_SIZE * DT.BLOCK_SIZE) / (1024 * 1024), 2)) CUR_SIZE_MB
        ,MAX (ROUND ( (TSU.TABLESPACE_USEDSIZE * DT.BLOCK_SIZE) / (1024 * 1024), 2)) USED_SIZE_MB
FROM     DBA_HIST_TBSPC_SPACE_USAGE TSU
        ,DBA_HIST_TABLESPACE_STAT TS
        ,DBA_HIST_SNAPSHOT SP
        ,DBA_TABLESPACES DT
WHERE    TSU.TABLESPACE_ID = TS.TS#
AND      TSU.SNAP_ID = SP.SNAP_ID
AND      TS.TSNAME = DT.TABLESPACE_NAME
GROUP BY TO_CHAR (SP.BEGIN_INTERVAL_TIME, 'DD-MM-YYYY'), TS.TSNAME
ORDER BY TS.TSNAME, HISTORY_DATE;

The result of the above query will be :

In the above query, we have selected the history_date, tablespace name, and current size, and the last column is the used size. We are using view DBA_HIST_SNAPSHOT to get the BEGIN_INTERVAL_TIME column which represents the history date. Keep in mind that there are one or many snapshots for each date and if you want to narrow down the data to get each snapshot id. Just add the column SNAP_ID to select the statement above, and don’t forget to put it in the group by clause.

Summarizing query for checking Tablespace name, size, utilized space, and the value in percentage

We have grouped some of the information’s above in one query to save it in your library.

SELECT DF.TABLESPACE_NAME "Tablespace"
      ,TOTALUSEDSPACE "Used MB"
      , (DF.TOTALSPACE - TU.TOTALUSEDSPACE) "Free MB"
      ,DF.TOTALSPACE "Total MB"
      ,ROUND (100 * (TU.TOTALUSEDSPACE / DF.TOTALSPACE)) || '%' "Pct. Used"
      ,ROUND (100 * ( (DF.TOTALSPACE - TU.TOTALUSEDSPACE) / DF.TOTALSPACE)) || '%' "Pct. Free"
FROM   (SELECT   TABLESPACE_NAME, ROUND (SUM (BYTES) / 1048576) TOTALSPACE
        FROM     DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME
        UNION
        SELECT   TABLESPACE_NAME, SUM (BYTES) / 1024 / 1024 TBS_SIZE
        FROM     DBA_TEMP_FILES
        GROUP BY TABLESPACE_NAME) DF
      ,(SELECT   ROUND (SUM (BYTES) / (1024 * 1024)) TOTALUSEDSPACE, TABLESPACE_NAME
        FROM     DBA_SEGMENTS
        GROUP BY TABLESPACE_NAME) TU
WHERE  DF.TABLESPACE_NAME = TU.TABLESPACE_NAME(+);

The result of the above query will be :

Conclusion

The tablespace in the Database is a crucial part. We often want to check the total space, used space, or free space. in this article, we have demonstrated how to get this information’s whether by using queries or from two different IDE (SQL Developer & TOAD), as well as getting the datafiles locations in the server. Finally, we have checked the Database tablespaces’ growth by fetching the history data to predict how many tablespaces would reach in the future.

If you have any questions, please don’t hesitate to ask them in the comments section below.
If you have an addition to this guide to add extra value for the readers, kindly let me know.

Hopefully, it was clear and concise.