How to Add Datafile in Tablespace in Oracle 19c

When I started as L1 Oracle Database Administrator, it was my regular task to check free space at tablespace level and maintain enough space to avoid application downtime because of “Unable to extend <tablespace> by XX Bytes” error. That’s the reason I brought this post. How to add datafile in tablespace in oracle 19c, We will discuss, what to check during additions of datafile in tablespace? How you can run your setup smoothly.

PRE-CHECKS and PRECUATIONS

Know your Setup

It’s mandatory, that before you start adding datafiles in your system you check if there are any standby database. If you find a standby database, then check how it is created.

Keep an eye on your Mount point/ ASM diskgroup

This comes under prechecks whenever you are extending a datafile/ adding new datafile, you should have enough space available on that mountpoint or diskgroup.

This should be checked at Primary as well as all Standby database you have in your environment.

ASM based database- 
select name,free_mb,total_mb from v$asm_diskgroup;

Filesystem Based Database- 
df -kh  ( linux,solaris )
df -gt  ( Aix ) 

Precautionary step would be “to set a threshold for your mount point / disk-group” . Post that you will raise a concern with storage team to allocate more storage for your database. This way you will be always safe from outage caused because of low storage at server level.

DB_FILES parameter is Equally Important –

DB_FILES – Refer this doc for more information.

DB_FILES specifies the maximum number of database files that can be opened for this database. The maximum valid value is the maximum number of files, subject to operating system constraint, that will ever be specified for the database, including files to be added by ADD DATAFILE statements.

oracle doc

If you have already reached DB_FILES parameter then you will face following error – We will discuss this in different blog. How to resolve ORA-00059: Maximum Number Of DB_FILES Exceeded

ORA-00059 Maximum number of DB_FILES exceeded

Step By Step How to add space in Tablespace

Check – How much free space in tablespace

clear breaks
clear computes

Prompt
Prompt Tablespace Usage
Prompt

SET lines 120 pages 500 
col percent_used format 999.99

SELECT a.TABLESPACE_NAME,
  NVL(ROUND((a.BYTES  /1024)/1024/1024,2),2) GB_TOTAL,
  NVL(ROUND((b.BYTES  /1024)/1024/1024,2),2) GB_FREE,
  NVL(ROUND((b.largest/1024),2),0) KB_Chunk,
  NVL(ROUND(((a.BYTES -NVL(b.BYTES,1))/a.BYTES)*100,4),0) percent_used
FROM
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),0) BYTES
  FROM dba_data_files
  GROUP BY TABLESPACE_NAME
  ) a,
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),1) BYTES ,
    NVL(MAX(BYTES),1) largest
  FROM dba_free_space
  GROUP BY TABLESPACE_NAME
  ) b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) 
--and a.tablespace_name='&TABLESPACE_NAME'
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;

Check if Tablespace is Big file or Small File

Why we are checking for type of datafile it has, due to the fact that, we can’t add datafile in big file tablespace. We can only resize it. Refer physical limit on size doc for more details.

Tablespaces	Maximum number per database	64 KNumber of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.

select name, bigfile from v$tablespace;

Check If any file can be resized

Once you have checked for type of files in your tablespace, you can check if you can resize any datafiles.

select file_name,(bytes/1024/1024/1024) size_in_gb, autoextensible,(MAXBYTES/1024/1024/1024) max_size_in_gb from dba_data_files where tablespace_name='&tablespace_name';

Option 1 – Add datafile in Tablespace

Once you understand the details about the tablespace, then you can chose follow –

ASM based –

Syntax

alter tablespace <tablespace_name> add datafile '+<diskgroup_name>' size <size_with_unit>;

For example – If I want to add 10 GB in users tablespace. And Diskgroup name is DATA which is used for storing datafiles in my environment.

alter tablespace users add datafile '+DATA' size 10g;

Example 2 – If I want to add 10 MB in system tablespace. And diskgroup name is DATA_USA which is used for storing datafiles in my environment.

alter tablespace system add datafile '+DATA_USA' size 10m;

Filesystem Based –

Syntax

alter tablespace <tablespace_name> add datafile '<absolute_path>/<file_name>.dbf' size <size_with_unit>;

For instance – If I want to add 10 GB in users tablespace. And we have Filesystem database – And datafiles are stored in /oracle_db_world/data/ .

alter tablespace users add datafile '/oracle_db_world/data/users_02.dbf' size 10g;

Option 2 – Resize datafile in Tablespace

alter database datafile '<file_name>' resize 15g;
or 
alter database datafile <file#> resize 15g;

For instance – If I want to increase the size of datafile by 10 GB, And current size of datafile is 2GB. File Name – ‘/oracle_db_world/data/users_01.dbf’ and file# – 10

alter database datafile '/oracle_db_world/data/users_01.dbf' resize 12g;
or 
alter database datafile 10 resize 12g;