Oracle Database - data file / space handling

Intro

As I am already working a very long time (around 22 years) with oracle databases using file storage (no ASM, datafiles), a repetitive task always has been managing and extending the datafiles. This post is more a scratchpad of common queries I used to handle the file storage. Please refer to the official oracle documentation if you are not yet used to file handling as this blog post is no replacement for the oracle documentation nor make me responsible if you made something stupid : )

The post will be extended step by step...

CAUTION

Before extending oracle files, always make sure to check the:

• free disk space in advance
• naming of the file that it is 'in line' with the other data files

Imho, best practice for file names is the following schematics: __.dbf Naturally, the extension is free of choice, dbf for DataBaseFile

Tabelspace

A tablespace is a logical organization unit for one or more datafiles. The datafiles itself are holding the data, not the tablespace.

Space overview

SELECT df.tablespace_name "TS",
       df.total_gb "GB total",
       used_gb "GB used",
       (df.total_gb - ds.used_gb) "GB free",
       ROUND(100 * ( (df.total_gb - ds.used_gb)/ df.total_gb),2) "% free"
  FROM (SELECT tablespace_name, 
               ROUND(SUM(bytes) / 1024 / 1024 / 1024,2) total_gb
          FROM dba_data_files
         GROUP BY tablespace_name) df,
       (SELECT ROUND(SUM(bytes)/(1024*1024*1024),2) used_gb, 
               tablespace_name
          FROM dba_segments
         GROUP BY tablespace_name) ds
 WHERE df.tablespace_name = ds.tablespace_name;

Output is in GB - if you wish to use MB, just remove one 1024 in line 7 and 10.

Add

Here we add a new tablespace 'NEWTS' with one datafile:

create tablespace NEWTS
   DATAFILE '/opt/ora/data/EXA/NEWTS/EXA_NEWTS_01.dbf' SIZE 2M;

Now with two:

create tablespace NEWTS
   DATAFILE '/opt/ora/data/EXA/NEWTS/EXA_NEWTS_01.dbf' SIZE 2M,
                    '/opt/ora/data/EXA/NEWTS/EXA_NEWTS_02.dbf' SIZE 2M;

Drop

Really? : )

drop tablespace NEWTS;

If you want to drop the tablespace with ALL content:

drop tablespace NEWTS including contents;

Datafiles

Space overview

SELECT df.NAME as file_name, 
       round(df.bytes / 1024 / 1024 / 1024,2) "GB total",
       round(((df.bytes / 1024 / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024 / 1024, 0)),2) "GB used",
       round(NVL (SUM (dfs.bytes) / 1024 / 1024 / 1024, 0),2) "GB free"
  FROM v$datafile df, dba_free_space dfs
 WHERE df.file# = dfs.file_id(+)
 GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
 ORDER BY file_name;

Add

Tablespace name in this example is "NEWTS"

alter tablespace NEWTS add datafile '/opt/ora/data/EXA/NEWTS/EXA_NEWTS_03.dbf' size 32M;

Resize

alter database datafile '/opt/ora/data/EXA/NEWTS/EXA_NEWTS_03.dbf' resize 64M;

Tempfiles

Space overview

SELECT tf.NAME file_name, 
       round(tf.bytes / 1024 / 1024 / 1024,2) "GB total",
       round(((tf.bytes / 1024 / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024 / 1024, 0)),2) "GB used",
       round(NVL (SUM (dfs.bytes) / 1024 / 1024 / 1024, 0),2) "GB free"
  FROM v$tempfile tf, dba_free_space dfs
 WHERE tf.file# = dfs.file_id(+)
 GROUP BY dfs.file_id, tf.NAME, tf.file#, tf.bytes
 ORDER BY file_name;

Add

Temporary tablespace name in this example is "temp_data"

alter tablespace temp_data add tempfile '/opt/ora/temp/EXA/EXA_temp_03.dbf' size 32M;

Resize

alter database tempfile '/opt/ora/temp/EXA/EXA_temp_03.dbf' resize 64M;