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
Log in to SQL*Plus:
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.
Post a Comment
Post a Comment