How to add space to a Tablespace in oracle?

STEP 1: Identify the current Tablespace usage.

SQL> set lines 250
SELECT a.tablespace_name, ROUND(a.totsize/1024/1024,0) "Tot Size (MB)",
ROUND(NVL(b.used,0)/1024/1024,0) "Used (MB)",
100 - ROUND(((a.totsize - NVL(b.used,0)) / a.totsize) * 100,0) "% Used", ROUND(((a.totsize -
NVL(b.used,0)) / a.totsize) * 100,0) "% Free"
FROM
(SELECT tablespace_name, SUM(bytes) totsize FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) used FROM dba_segments
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name (+)
and a.tablespace_name='&Tablespace_name';

TABLESPACE_NAME Tot Size (MB) Used (MB) % Used % Free
------------------------------ ------------- ---------- ---------- ----------
SIEBEL 1198080 1044565 87 13

STEP 2: Gather the datafile details.

SQL> column file_name format a70
column bytes format a30
set linesize 150
set pages 3330
select file_name,bytes/1024/1024/1024 size_GB,file_id,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&name';

* Data file using ASM

FILE_NAME SIZE_GB FILE_ID AUT
---------------------------------------------------------------------- ---------- ---------- ---
+DATA/rsiebeldb/datafile/siebel.356.853242799 1170 17 YES

* Data file without using ASM

FILE_NAME SIZE_GB FILE_ID AUT
---------------------------------------------------------------------- ---------- ---------- ---
/u01/rsiebeldb/datafile/siebel.356.853242799 1170 17 YES

STEP 3: Gather the Diskgroup / Filesystem details (Space availablity)

SQL> col NAME for A14
Set lin 500
SELECT group_number,NAME,state, TOTAL_MB/1024, FREE_MB/1024,USABLE_FILE_MB/1024, ROUND((1- (free_mb / total_mb))*100, 2) pct_used FROM v$asm_diskgroup ORDER BY name;

GROUP_NUMBER NAME STATE TOTAL_MB/1024 FREE_MB/1024 USABLE_FILE_MB/1024 PCT_USED
------------ ------------------------------ ----------- ------------- ------------ ------------------- ----------
1 DATA CONNECTED 72744 16990.1875 3299.09375 76.64
2 DBFS MOUNTED 2365.78125 401.125 31.578125 83.04
3 RECO CONNECTED 18210.9375 13369.6172 5384.02734 26.58

or use df -h (for data file without using ASM and verify the mount point has enough space)

STEP 4: Verifiy whether its a bigfile tablespace

SQL> select name,bigfile from v$tablespace where name='&name';
NAME BIG
------------------------------ ---
SIEBEL YES

STEP 5: Add space to the existing tablespace

* For Bigfile Tablespace

SQL> alter tablespace SIEBEL 1250G;
Tablespace altered.

* For tablepsace using ASM

alter tablespace <TBS_NAME> add datafile '+<DG_NAME>' size <Datafile_Size> autoextend on maxsize 34359721984;

* For tablespace not using ASM

alter tablespace <TBS_NAME> add datafile '<Filesystem path>' size <Datafile_Size> autoextend on maxsize 34359721984;

* to modify/re-szie the existing datafile

alter database datafile '<Datafile_path> resize <Datafile_size>;

STEP 6: Validate the current tablespace usage by repeating the STEP 1.

*** NOTE : Above steps may slightly differ depending upon the environment / requirements ***

Leave a Reply