How to add datafile into tablespace in Oracle

November 7, 2020

SCRIPTS

Add datafile into tablespace in Oracle

Most of the time due to the application’s DML operation, tablespace crosses their threshold. As everyone knows it’s a critical scenario, if we ignore/skipped adding space into tablespace it may cause the application at a hung state or the database to a hung or down.

To avoid this cause below is the query to add space(datafile) into tablespace.

Query to add datafile using file system path.

alter tablespace system add datafile '/u01/app/oracle/datafile/dbsguru10.dbf size 1024M AUTOEXTEND ON next 50M maxsize 8192M;

Query to add datafile using OMF configuration.

alter tablespace system add datafile size 1024M AUTOEXTEND ON next 50M maxsize 8192M;

Query to add datafile using ASM setup.

alter tablespace system add datafile "+DATA" size 1024M AUTOEXTEND ON next 50M maxsize 8192M;

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.

Hope so you like this script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.