INTRODUCTION:

The below steps shows how to drop and recreate undo tablespace, by doing this activity we can gain more space in mount points and the database size will be reduced.

 

STEP 1:

Check the DB size using the below query,

 

col “Database Size” format a20

col “Free space” format a20

col “Used space” format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –

round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”

, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p

/

 

STEP 2:

Check the list of undo tablespaces using the below query,

 

column tablespace format a20

column total_mb format 999,999,999,999.99

column used_mb format 999,999,999,999.99

column free_mb format 999,999,999.99

column pct_used format 999.99

column graph format a25 heading “GRAPH (X=5%)”

compute sum of total_mb on report

compute sum of used_mb on report

compute sum of free_mb on report

break on report

set lines 132 pages 50

select  total.ts tablespace,

total.mb total_mb,

NVL(total.mb – free.mb,total.mb) used_mb,

NVL(free.mb,0) free_mb,

DECODE(total.mb,NULL,0,NVL(ROUND((total.mb – free.mb)/(total.mb)*100,2),100)) pct_used,

CASE WHEN (total.mb IS NULL) THEN ‘[‘||RPAD(LPAD(‘OFFLINE’,13,’-‘),20,’-‘)||’]’

ELSE ‘[‘|| DECODE(free.mb,

null,’XXXXXXXXXXXXXXXXXXXX’,

NVL(RPAD(LPAD(‘X’,trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),’X’),20,’-‘),

‘——————–‘))||’]’

END as GRAPH

from

(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,

(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free

where total.ts=free.ts(+)

UNION ALL

select  sh.tablespace_name,

SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,

SUM(sh.bytes_used)/1024/1024 used_mb,

SUM(sh.bytes_free)/1024/1024 free_mb,

ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used,

‘[‘||DECODE(SUM(sh.bytes_free),0,’XXXXXXXXXXXXXXXXXXXX’,

NVL(RPAD(LPAD(‘X’,(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),’X’),20,’-‘),

‘——————–‘))||’]’

FROM v$temp_space_header sh

GROUP BY tablespace_name

order by 5 desc

/

ttitle off

 

STEP 3:

Create a new UNDO tablespace,

CREATE UNDO TABLESPACE APPS_UNDONEW DATAFILE ‘/prddata03/oracle/PROD/db/apps_st/data/apps_undonew01.dbf’ SIZE 10G;

 

STEP 4:

Set the created undo tablespace as default undo tablespace.

ALTER SYSTEM SET UNDO_TABLESPACE=APPS_UNDONEW;

STEP 5:

Drop the old undo tablespace

drop tablespace APPS_UNDOTS1 including contents and datafiles;

 

STEP 6:

Check if all he datafiles of old undo tablespace has been dropped, if not search the datafiles segments that are online and make them offline using below query,

select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’APPS_UNDOTS1′ and status=’ONLINE’;

select ‘alter rollback segment “PUBLIC ‘||segment_name||'” offline;’ from dba_rollback_segs where tablespace_name=’APPS_UNDO’ and status=’ONLINE’;

alter rollback segment “PUBLIC _SYSSMU7_1357090761$” offline;

 

STEP 7:

Now check any of the old datafiles are online using the below query,

 

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM   v$rollname a,gv$rollstat b, gv$transaction c , gv$session d

WHERE  a.usn = b.usn

AND    a.usn = c.xidusn

AND    c.ses_addr = d.saddr

AND    a.name IN (

SELECT segment_name

FROM dba_segments WHERE tablespace_name = ‘APPS_UNDO’);

 

 

STEP 8:

Now drop the tablespace using the below command

drop tablespace APPS_UNDOTS1 including contents and datafiles;

 

STEP 9:

Create existing dropped UNDO tablespace

CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE ‘/prddata03/oracle/PROD/db/apps_st/data/apps_undo01.dbf’ SIZE 10G;

 

STEP 10:

Now set the newly created undo tablespace as default undo tablespace

ALTER SYSTEM SET UNDO_TABLESPACE=APPS_UNDOTS1;

 

STEP 11:

Now drop newly created UNDO tablespace

drop tablespace APPS_UNDONEW including contents and datafiles;

 

STEP 12:

Check if the all the datafiles were dropped if not check the segments that are online and make them offline using below queries,

select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’APPS_UNDONEW’ and status=’ONLINE’;

select ‘alter rollback segment “PUBLIC ‘||segment_name||'” offline;’ from dba_rollback_segs where tablespace_name=’APPS_UNDONEW’ and status=’ONLINE’;

 

STEP 13:

Now drop the tablespace ussing the below query,

drop tablespace APPS_UNDONEW including contents and datafiles;

 

STEP 14:

Now check the size of the Database using below query,

col “Database Size” format a20

col “Free space” format a20

col “Used space” format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –

round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”

, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p

/

Recent Posts

Start typing and press Enter to search