Alter tablespace add datafile.

Suppose our database getting full. In that situation we have to increase the size of database to add datafile in existing tablespace or creating new tablespace along with datafile.

How to add datafile to tablespace in oracle: –

We can increase space in oracle database to add datafile or resize existing datafile to existing tablespace.

1. Check the existing file in Tablespace: –

SQL>select file_name,bytes/1024/1024 SIZE_IN_MB from dba_data_files where tablespace_name=’XXCUSTOM’;

2. How to add datafile in tablespace.

SQL> alter tablespace xxcustom add datafile ‘/data01/prod/xxcustom03.dbf’ size 5g;

On ASM:-

SQL> alter tablespace xxcustom add datafile '+DATAC1' size 25G autoextend on maxsize 31G; 

3. How to resize existing datafile: –

SQL>alter database datafile ‘/path/data_filename.dbf’ resize 6g;

On ASM:-

SQL> alter database datafile '+DATAC1/oraprd/datafile/users.263.942399698' resize 25G;

How to add new tablespace.

1. Create tablespace in oracle.

SQL>CREATE TABLESPACE XXBACKUP DATAFILE '+DATA01' SIZE 1000m AUTOEXTEND ON MAXSIZE 31G;

2. Create tablespace with multiple datafile.

CREATE TABLESPACE XXBACKUP DATAFILE
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATAC1' SIZE 1024K AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Verify the tablespace using below query.

SQL>SELECT tablespace_name,file_name,bytes / 1024/ 1024 MB FROM dba_data_files;

For additional information on Administering Database size. Check how to resize the datafile in oracle database.