오라클 삭제된 부분 DEALLOCATE 방법

사용되지 않은 공간을 DEALLOCATE하는 방법

========================================

 

 

Purpose

-------

TABLE, INDEX, CLUSTER 등의 DATABASE OBJECT들은 입력되는 자료의 양이

증가함에 따라서 새로운 EXTENT들이 자동적으로 할당된다. 이 때 필요 이상

과도한 크기의 EXTENT가 할당되면 실제로 자료가 들어있지 않거나 들어갈

예정이 없는 죽은 공간이 생길 수 있게 된다.

이러한 죽은 공간은 DISK 전체의 사용 효율을 저하시키는 원인이 되므로

적절히 제거되어야 하는데 여기서는 그 중에서 DEALLOCATE라는 방법에

대해서 알아본다

 

 

Explanation

-----------

 

1. DEALLOCATE의 대상

 

TABLE, INDEX, CLUSTER들이 생성된 이후에 한번도 사용되어지지 않은 공간

ORACLE에서는 이렇게 한번도 사용되지 않은 공간을 HIGH WATER MARK라는

개념으로 설명하는데 이는 다음과 같다.

 

* HIGH WATER MARK

TABLE_A 의 EXTENT 구조

|-------------------|--------------------|-------------------|

EXTENT1 EXTENT2 | EXTENT3 |

| |

A.현재 DATA가 B.HIGH WATER MARK

차있는 위치

 

TABLE이 생성된 이후 DATA가 INSERT되어 B지점까지 공간을 점유하고

있었다고 가정을 하자. 이후 DATA가 DELETE되어 현재 DATA들이

점유하고 있는 위치는 A라고 하면 B지점이 HIGH WATER MARK가 된다.

 

DEALLOCATE 작업을 하면 HIGH WATER MARK 위의 공간만이 FREE되며,

HIGH WATER MARK 아래의 공간들(B-A)은 이후 DATA가 다시 INSERT될 것을

예상하여 그대로 빈 공간으로 남겨두게 된다.

 

 

2. DEALLOCATE의 대상이 되는 공간이 어느 정도 존재하는지 파악하는 방법

 

ANALYZE TABLE table_name COMPUTE STATISTICS ;

 

예1)

SELECT EMPTY_BLOCKS, BLOCKS

FROM DBA_TABLES

WHERE TABLE_NAME = 'table_name' ;

 

EMPTY_BLOCKS BLOCKS

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

6530 2400

 

EMPTY_BLOCKS - OBJECT에 할당된 총 SPACE중 HIGH WATER MARK 위의 공간

BLOCKS - OBJECT에 할당된 총 SPACE중 HIGH WATER MARK 아래 공간

 

여기서 EMPTY_BLOCKS + BLOCKS(해당 OBJECT의 총 SPACE)에 비해서

EMPTY_BLOCKS 이 과도하게 크고 앞으로 더 이상의 DATA INSERT도 일어나지

않을 계획이라면 DEALLOCATE를 하는 것이 좋다.

 

 

3. SYNTAX

 

ALTER TABLE table_name DEALLOCATE UNUSED [KEEP integerK] ;

ALTER INDEX index_name DEALLOCATE UNUSED [KEEP integerK] ;

ALTER CLUSTER cluster_name DEALLOCATE UNUSED [KEEP integerK] ;

 

KEEP 절을 사용하면 KEEP 절에 정의된 크기 만큼은 남겨 두고

DEALLOCATE를 한다.

 

예2)

위의 예1)에서

ALTER TABLE table_name DEALLOCATE UNUSED ;

를 수행하면 다음과 같이 SPACE가 FREE된다.

 

ANALYZE TABLE table_name COMPUTE STATISTICS ;

 

SELECT EMPTY_BLOCKS, BLOCKS

FROM DBA_TABLES

WHERE TABLE_NAME = 'table_name' ;

 

EMPTY_BLOCKS BLOCKS

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

0 2400

 

 

위의 조회 결과를 보면 HIGH WATER MARK 위 부분(EMPTY_BLOCKS)은

FREE되어 0으로 표시되고 HIGH WATER MARK 아래 부분(BLOCKS)은

그대로(2400) 남아 있다

 

 

 

 

 

ORA-01653 에러와 같은 경우입니다..

해당테이블이 단지 일반 테이블이 아니라

INDEX 테이블이라는 것만 빼고... ^^

그럼 도움이 되셨길....

 

코멘트 2을 처음 님이 의도한 방법이고

코멘트 3는 제가 말씀드린 방법입니다

No. 11875

====================================================================================================

ORA-1654 ERROR ON INDEX SEGMENT

====================================================================================================

 

PURPOSE

-------

 

이 자료는 ORA-1654 error를 해결하기 위한 방법에 대한 자료이다.

 

 

Problem Description

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

 

DML 작업 시에 다음과 같은 ORA-1654 error가 특정 tablespace에 대하여

발생하는 경우가 있다. 이에 대한 해결방법을 알아보기로 한다.

 

01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"

예) unable to extend index owner.object by 40964 in tablespace INDEX;

 

 

Workaround

----------

none

 

 

Solution Description

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

 

1. tablespace에 남아 있는 공간 중 가장 큰 연속된 공간의 사이즈를 구합니다.

 

SELECT TO_CHAR(MAX(bytes), '999,999,999,999') free_space

FROM dba_free_space

WHERE tablespace_name = 'HDMF_TM_DATA_70';

 

 

ora-1654 에러가 났던 tablespace 이름을 대문자로 위에 써줍니다.

위에 나온 수치는 연속된 block들 가운데 가장 큰 사이즈의 extent를 보여주는

것인데, next extent를 할당하기 위해서는 위에 나온 수치보다 더 큰 사이즈를

필요로 하는 것입니다.

 

"The above query returns the largest available contiguous chunk of space."

 

 

2. index의 storage parameter인 next_extent 값과 pct_increase 값을 확인합니다.

 

SELECT next_extent, pct_increase

FROM dba_indexes

WHERE index_name = 'INDEX NAME' AND owner = 'OWNER';

 

< TABLE 일경우 >

SELECT TO_CHAR(next_extent, '999,999,999') NEXT_EXTENT, pct_increase

FROM dba_tables

WHERE table_name = 'TB_KYBSMST' AND owner = 'HDMF_PRD';

 

--> 파티션 테이블에 대한 파티션 인덱스인 경우는

SELECT next_extent, pct_increase

FROM dba_ind_partitions

WHERE index_name = 'INDEX NAME' AND owner = 'OWNER';

 

 

ora-1654 에러가 발생한 index의 next extent 값과 pct_increase 값이 얼마인지

확인해 보십시오.

위에서 나타난 next_extent 값과 max(bytes) 값을 비교해 보세요.

 

 

3. 인스턴스의 db_block_size를 확인합니다.

 

vi $ORACLE_HOME/dbs/initSID.ora

 

db_block_size = 2048 또는 4096 또는 8192일 것입니다.

 

ora-1654 에러에 나타난 by 다음의 수치(예:40964) * db_block_size 만큼의

사이즈가 next_extent(byte 단위) 값과 같을 것이며, 이 만큼의 extent 영역을

할당할 수 없다는 뜻입니다.

따라서 datafile을 추가 시 이 byte 값 이상의 사이즈를 추가해야 합니다.

 

 

4. ora-1654 에러를 해결하는 방법

 

There are several options for solving failure to extend.

 

Manually Coalesce Adjacent Free Extents

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

 

ALTER TABLESPACE <tablespace name> COALESCE;

 

The extents must be adjacent to each other for this to work.

 

Add a Datafile:

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

 

ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and

file name>' SIZE <integer> < |k|m>;

 

 

Lower "next_extent" and/or "pct_increase" size:

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

 

For non temporary segment problem:

 

ALTER <object> <object name> STORAGE ( next <integer> < |k|m>

pctincrease <integer>);

 

For a temporary segment problem:

 

ALTER TABLESPACE <tablespace name> DEFAULT STORAGE

(initial <integer> next <integer> <|k|m> pctincrease <integer>);

 

 

Resize the Datafile:

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

 

ALTER DATABASE DATAFILE '<full path and file name>' RESIZE

<integer> <k|m>;

 

 

Reference Document

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

<Note:146595.1

오라클 10G부터는

SHRINK 기능을 사용하면 된다.

위의 기능은 HWM (HIGH WATER MARK) 윗부분의 사용하지 않은 공간만 반환함