CleanUp Temporary Segments Occupying Permanent Tablespace

最新推荐文章于 2024-10-26 09:09:32 发布

转载最新推荐文章于 2024-10-26 09:09:32 发布 · 194 阅读

本文介绍如何识别和清理Oracle数据库中永久表空间内的大型临时段。这些临时段可能由DDL操作创建，并占用实际磁盘空间。文章提供了查找这些临时段的SQL查询，并介绍了两种清理方法：使用事件DROP_SEGMENTS和通过标记临时段为损坏再删除。

By Saurabh Sood, July 3, 2009 12:42 pm

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 and when the DDL action finishes it will be changed to permanent type.

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 SEGMENT_NAME SUM(BYTES/1024/1024)

------------------- ------------ ------------ --------------------

xxxx_DATA SYS 123.8365 137706

BDEPST_INDEX SYS 345.8756 8910

KMRPT_DATA SYS 345.87 25284.875

BILL_INDEX SYS .25

DSS_DATA SYS 798

MRKT_INDEX SYS 208

SPCT_DATA SYS 69642.25

SPCT_INDEX SYS 956.4375

Here we can see that tablespace KMRPT_DATA, SPCT_INDEX and SPCT_DATA have large temporary segments.

To know 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='345.87';

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

It will give you 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.

1. 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.

2. 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 KMRPT_DATA)

SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('KMRPT_DATA');

– Corrupt the temporary segments in tablespace KMRPT_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 = 'KMRPT_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 = 'KMRPT_DATA';

– Verify the tablespace again to update the new dictionary information:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('KMRPT_DATA');

This will remove temporary segments from permanent tablespace.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10173379/viewspace-623838/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10173379/viewspace-623838/