• Arun Kumar

Oracle Add Datafile to Tablespace

In this article we will be looking at ways to add space to a tablespace. First we will check tablespace utilization to see which tablespaces inside the database needs space addition.


Check Tablespace Utilization

set colsep |
set linesize 100 pages 100 trimspool on numwidth 14 
col name format a25
col owner format a15 
col "Used (GB)" format a15
col "Free (GB)" format a15 
col "(Used) %" format a15 
col "Size (M)" format a15 
SELECT d.status "Status", d.tablespace_name "Name", 
 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 
 TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", 
 TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 
 TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"
 FROM sys.dba_tablespaces d, 
 (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 
 (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE 
 d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT 
 (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 
UNION ALL 
SELECT d.status 
 "Status", d.tablespace_name "Name", 
 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 
 TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",
 TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 
 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" 
 FROM sys.dba_tablespaces d, 
 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 
 (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t 
 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 
 d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
 

Find Tablespace Datafiles


We must find the datafiles that are associated with a tablespace. Use below query to find the details

SELECT FILE_NAME, BYTES/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='&TBS_NAME';

Add Space to Tablespace


There are two ways to add space to a tablespace:

  • Extend existing datafile or

  • Add new datafile

Resize Datafile


Once you get the details of datafiles associated with a tablespace, you can resize it via below command


NOTE: Before adding space, you must check OS level space. Never proceed without checking it

ALTER DATABASE DATAFILE '&DATAFILE_NAME' RESIZE &SIZE ;

Add Datafile


You can add a new datafile to tablespace via below command

ALTER TABLESPACE &TBS_NAME ADD DATAFILE '&DATAFILE_NAME' SIZE &DATAFILE_SIZE;


Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback