Work With Oracle Tablespace

work-with-oracle-tablespace.md

Work With Oracle Tablespace

# Written by The Suhu (2021).

# Tested on Oracle 12c

Short description

Here I will not explain much about Oracle tablespace. This is short description:

• An Oracle database consists of one or more tablespaces.
• Tablespace is logical storage units which stores data logically.
• Each tablespace consists of one or more datafiles.
• Datafiles are files which stores data physically that correspond to each tablespace.

For more explanation about Oracle databases, tablespaces and data files, go to this link

Here are some notes we might need

Find information about existing tablespaces

Find its information by querying data dba_data_files VIEW.

SELECT tablespace_name, file_name, bytes/1024/1024 AS MB FROM dba_data_files;

Create new tablespace

Create a new tablespace named tbs01 with size 10MB.

CREATE TABLESPACE tbs01
    DATAFILE 'tbs01.dbf'
    SIZE 10m;

If you want to create datafile in specific location, use full path instead. For example /u01/app/oracle/oradata/EE/tbs01.dbf.

You can use AUTOEXTEND clause to automatically extend the size of the datafile. For example AUTOEXTEND 10m.

Extending tablespace

One day the tablespace will run out, and must be extended. There are two ways extending a tablespace by:

• Resizing the datafile. For example, Extend the size of the datafile of the tablespace to 20MB.

  ALTER DATABASE DATAFILE 'tbs01.dbf' RESIZE 20m;

• Adding a new datafile. For example, Add one more datafile with size 10MB.

  ALTER TABLESPACE tbs01 ADD
      DATAFILE 'tbs01_2.dbf'
      SIZE 10m

  You can use AUTOEXTEND ON clause to automatically extend the size of the datafile when needed.

Delete tablespace

Delete empty tablespace.

DROP TABLESPACE tbs01;

Delete tablespace which has data.

DROP TABLESPACE tbs01 INCLUDING CONTENTS;

Delete tablespace and related datafile.

DROP TABLESPACE tbs01 INCLUDING CONTENTS AND DATAFILES;

What if forget to delete the datafile? can be solved by creating a new tablespace with an existing datafile and then DROP with INCLUDING CONTENTS AND DATAFILES clause.

View tablespace size

View free space of tablespace in MB.

SELECT tablespace_name, TO_CHAR(SUM(NVL(bytes,0))/1024/1024, '99,999,990.99') AS "FREE_SPACE(MB)"
    FROM dba_free_space
    GROUP BY tablespace_name;

Below is a complete query to get tablespace usage information.

  SELECT df.tablespace_name
             "Tablespace",
         df.file_name
             "Datafile",
         ROUND (df.bytes / 1024 / 1024, 0)
             "Size (MB)",
         DECODE (u.used_bytes, NULL, 0, ROUND (u.used_bytes / 1024 / 1024, 0))
             "Used (MB)",
         DECODE (f.free_bytes, NULL, 0, ROUND (f.free_bytes / 1024 / 1024, 0))
             "Free (MB)",
         DECODE (u.used_bytes, NULL, 0, ROUND ((u.used_bytes / df.bytes) * 100, 0))
             "Used (%)"
    FROM dba_data_files df
         LEFT JOIN (SELECT file_id, SUM (bytes) used_bytes
                        FROM dba_extents
                    GROUP BY file_id) u
             ON df.file_id = u.file_id
         LEFT JOIN (SELECT MAX (bytes) free_bytes, file_id
                        FROM dba_free_space
                    GROUP BY file_id) f
             ON df.file_id = f.file_id
ORDER BY df.tablespace_name, df.file_name

...

That's all and if you find it useful please star (:star:) & share.