Step by Step method to check space in Tablespace and Increase/Extend/Add the Size as required

To manage tablespaces in Oracle and ensure they have sufficient space, you need to follow a series of steps. These steps include checking the tablespace usage, verifying if auto-extend is enabled on the datafiles, ensuring there is enough space on the filesystem, and either extending existing datafiles or adding new ones.

Always make your commands in notepad and then run on database.

Here’s a detailed step-by-step method:

Step 1: Check Tablespace Usage

sqlplus / as sysdba

SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES)/1024/1024/1024, 2) AS "USED_GB",

ROUND(SUM(MAXBYTES)/1024/1024/1024, 2) AS "MAX_GB"

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME;

This query will show the current usage and maximum size of each tablespace.

Step 2: Check Autoextend Status

Check autoextend status for datafiles:

SELECT FILE_NAME,

AUTOEXTENSIBLE,

MAXBYTES/1024/1024/1024 AS "MAX_GB",

BYTES/1024/1024/1024 AS "CURRENT_GB"

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME = 'YOUR_TABLESPACE_NAME';

Replace YOUR_TABLESPACE_NAME with the name of the tablespace you want to check.

Step 3: Check Filesystem Space

Check filesystem space (assuming a Unix/Linux system):

df -h /path/to/your/datafiles

Make sure the mount point where your datafiles are located has enough space for extension. If not then we need to get space added on server before increase of tablespace size.

Like df -h /u01

Step 4: Extend Datafile or Add New Datafile

Log back in to SQL*Plus:

sqlplus / as sysdba

Extend datafiles if they are not 32GB (MAX size).

We can extend till the value we need, not necessary 32 GB.

ALTER DATABASE DATAFILE '/path/to/your/datafile.dbf' RESIZE 32G;

ALTER DATABASE DATAFILE '/path/to/your/datafile.dbf' RESIZE 15G;

Ensure you replace /path/to/your/datafile.dbf with the actual path of your datafile.

Enable autoextend if not enabled(Not Mandatory):

ALTER DATABASE DATAFILE '/path/to/your/datafile.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

If datafile cannot be extended, add a new datafile:

ALTER TABLESPACE YOUR_TABLESPACE_NAME

ADD DATAFILE '/path/to/new/datafile.dbf' SIZE 32G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

Adding Datafile without auto extend

ALTER TABLESPACE YOUR_TABLESPACE_NAME ADD DATAFILE '/path/to/new/datafile.dbf' SIZE 10G;

Replace /path/to/new/datafile.dbf with the actual path where you want to create the new datafile, and YOUR_TABLESPACE_NAME with the name of your tablespace.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Step by Step method to check space in Tablespace and Increase/Extend/Add the Size as required