CleanUp Temporary Segments Occupying Permanent Tablespace
CleanUp Temporary Segments Occupying Permanent Tablespace
There are situations when we see “temporary segments” in permanent tablespaces hanging around and not getting cleaned up.
These temporary segments in permanent tablespace can be created by DDL operations like CTAS and “alter index..rebuild” because the new object is created as a temporary segment in the target tablespace.
These temporary segments take actual disk space when SMON fails to perform its assigned job to cleanup stray temporary segments.
Following query finds out these segments:
SQL > select tablespace_name, owner, sum(bytes/1024/1024) from dba_segments
where segment_type = ‘TEMPORARY’ group by tablespace_name, owner;
TABLESPACE_NAME OWNER SUM(BYTES/1024/1024)
——————- ———— ——————–
xxxx_DATA BLD 137706
KPMGRT_INDEX BLD 8910
KPMGRT_DATA BLD 25284.875
BILL_INDEX BLD .25
CELL_DATA BLD 798
CELL_INDEX BLD 208
CSC_DATA BLD 69642.25
CSC_INDEX BLD 956.4375
Here we can see that tablespace KPMGRT_DATA, CSC_INDEX and CSC_DATA have large temporary segments.
To find out if any DDL is active which can create temporary segments we can use the following:
SQL> conn / as sysdba
SQL> select owner FROM dba_segments WHERE segment_name='<name>’;
SQL> select pid from v$process where username=’owner from above query’;
SQL> alter session set tracefile_identifier=’TEMPORARY_SEGMENTS’;
SQL> oradebug setorapid
SQL> oradebug dump errorstack 3
SQL > oradebug tracefile_name
Above will produce the tracefile name, open that file and check for the “current sql”
If it is a DDL like CTAS or index rebuild, then wait for the operation to complete. If there is no pid
returned then these segments are “stray segements” and needs to cleaned up manually.
There are two ways to force the drop of temporary segments:
1. Using event DROP_SEGMENTS
2. Corrupting the segments and dropping these corrupted segments.
A. Using DROP_segments:
Find out the tablespace number (ts#) which contains temporary segments:
SQL> select ts# from sys.ts$ where name = ‘tablespace name’;
Suppose it comes out to be 10, use the following command to cleanup temporary segments:
SQL> alter session set events ‘immediate trace name DROP_SEGMENTS level 11’;
level is ts#+1 i.e 10+1=11 in this case.
B. Corrupting temporary segments for drop:
For this following procedures are used:
– DBMS_SPACE_ADMIN.TABLESPACE_VERIFY
– DBMS_SPACE_ADMIN.SEGMENT_CORRUPT
– DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT
– Verify the tablespace that contains temporary segments (In this case it is KPMGRT_DATA)
SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY(‘KPMGRT_DATA’);– Corrupt the temporary segments in tablespace KPMGRT_DATA
SQL> select ‘exec DBMS_SPACE_ADMIN.SEGMENT_CORRUPT(‘ || chr(39) || tablespace_name || chr(39) || ‘,’ || HEADER_FILE || ‘,’ || HEADER_BLOCK || ‘);’ from dba_segments where SEGMENT_TYPE like ‘TEMP%’ and tablespace_name = ‘KPMGRT_DATA’;– Drop the corrupted temporary segments
SQL> select ‘exec DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT (‘ || chr(39) || tablespace_name || chr(39) || ‘,’ || HEADER_FILE || ‘,’ || HEADER_BLOCK || ‘);’ from dba_segments where SEGMENT_TYPE like ‘TEMP%’ and tablespace_name = ‘KPMGRT_DATA’;– Verify the tablespace again to update the new dictionary information:
SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_VERIFY(‘KPMGRT_DATA’);This will remove temporary segments from permanent tablespace.
1 Comment »
Leave a Reply
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Incredible points. Great arguments. Keep up the good work.