|
|
IOTN :: Field Book :: ORACLE 
|
|
segment shrink [ alter table ... shrink space; ]
|
|
최길호
[LIST]
|
2009-03-31 22:40:26, 조회 : 32,511 |
추가
12.2 online 가능
alter table ... enable row movement;
alter table ... shrink space;
[ insert 초당 100건 이슈 없음 / 용량만큼 오래 걸림. / undo tablespace full 이슈 없음 자동 resize 됨 ]
alter table ... deallocate unused;
PURPOSE
--------------------------------------------------------------------------------
Oracle10g에서는, 세그먼트를 shrink 시킬 옵션이 존재하며,
이 기능을 활용하면 DBA가가 공간을 좀더 효율적으로 활용할 수있다 이 기능은,
또한 질의 처리 속도 개선에도 도움이 된다.
Explanation
--------------------------------------------------------------------------------
1. 준비 사항
Init.ora 파라미터인 'Compatible' 값이 10.0 이상이어야 함.
세그머트는, AUTO Segment Space Managed Tablespace에 존재해야 함.
2. 동작 방법
1) 테이블의 row movement 기능을 활성화 시킨다.
SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
2) 테이블을 shrink 시키지만, HWM (High Water Mark)을 shrink 시키지 않는다.
SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;
3) 테이블과 HWM을 shrink 시킨다.
SQL> ALTER TABLE scott.emp SHRINK SPACE;
4) 테이블 및 관련된 인덱스를 모두 shrink시킨다.
SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;
5) MView 형태의 테이블을 shrink시킨다.
SQL> ALTER TABLE <table name> SHRINK SPACE;
6) 인덱스만 shrink 시킨다.
SQL> ALTER INDEX <index nam> SHRINK SPACE;
3. 적용 대상
1) Normal Table
2) Index
3) Lob
4) IOT
5) MView
4. Query/DML Concurrency
Segment shrink를 할 때의 online 처리는, DML-호환 락을 사용한다. 따라서 DML은
shrink를 처리 하는동안에도 사용할 수 있다. space-release/HWM 조정을 하는 단계에서는,
테이블에 대해 DML고 호환되지 않는 락을 사용한다 따라서, DML은 shink가 끝날 때 까지
잠시 중단되나.
shrink를 실행함으로써 발생하는 DML 처리 관련 에러는 없다.
쿼리는 세그먼트의 HWM에 대한 캐쉬를 사용한다. 오라클은, HWM이 언제나 커질 것으로 간주한다. 따라서
CR은 세그먼트 헤더 및 익스텐트 맵 블럭에 대해서는 필요하지 않다. 세그먼트 HWM이 작아지는 유일한
경우는, drop 또는 truncate 작업 뿐이다.
오라클은, drop/truncate DDL와 질의처리가 동시에 존재할 수있게 허용하는데, 이것은 질의 처리가
락을 필요로 하지 않기 때문이다. 만약 drop/truncate 작업이 끝난 후라면, 해당 공간은 다른 세그먼트에
의해 사용되며, 질의는 "8103 - object does not exist"라는 에러 메시지와 함께 실패하게 된다.
세그먼트를 shrink하는 동안, 세그먼트의 HWM가 변경된다면, 해당 세그먼트와 관련된 비트맵 블럭과
세그먼트 헤더의 incarnation number가 변경된다. 후속 데이터 블럭 관련 작업은 새로 부여된
incarnation number를 사용한다.
만약 이 단계에서 실행되는 질의가 있었다면, "10632 - invalid rowid" 에러와 함께 실패하는데
다음과 같은 조건이 만족 되어야 한다.
1) 갱신된 비트맵 블럭을 읽는다 (새로운 inc#). 이 경우 failure는, 해당 공간이 재 사용되지 않았다면
발생하게 된다.
2) 공간이 동일한 객체 또는 다른 객체에 의해 재 활용 되었다.
5. Online Segment Shrink와 관련된 제약사항
ASSM의 세그먼트는 shrink 가능하다. 그러나, ASSM 테이블스페이스에 위치하는 객체
가운데 다음과 같은 객체에는 제약사항이 따른다 :
1) 클러스터에 속하는 테이블
2) long 컬럼을 포함한 테이블
3) on-commit materialized view와 연관된 테이블
4) rowid based materialized view와 연관된 테이블
5) Lob index
6. Shrink 수행시 의존 관계 관리와 제약사항
세그먼트를 shrink 하는동안, 고려되는 유일한 의존 관계는, 테이블 - 인덱스간 관계이다.
인덱스는, shrink 후에도 unusable 상태로 남지 않는다.
세그먼트 shink를 과정에서 압축 (compaction)단계는 insert/delete 작업을 쌍으로 처리하여
이루어진다. DML trigger는 데이터를 이동하는 단계에서는 호출되지 않는다. 데이터의 내용이
변경되는 것이 아니므로, trigger가 호출 될 필요가 없다.
ROWID에 기반을 둔 trigger는, shrink를 하기 전 disable 시켜야 하는데 이것은 shink 하는동안
트리거가 호출 되지 않도록 하기 위해서이다.
on-commit materialized view와 연관된 세그먼트는 shrink 시킬 수 없다.
Primary key를 기반으로 한 materialized view는, shrink를 시킨 이후에 refresh 또는 rebuild
할 필요가 없다.
하지만, rowid에 기반을 둔 materialized view에 대해서는 refresh 또는 rebuild를 수행해 주어야
한다.
7. 가용성
세그먼트 shrink는, 온라인중에 수행될 수 있다. 따라서, 객체에 대한 가용성이 향상 되었다.
DML 작업은 세그먼트 shrink 중에도 수행 가능하나, parallel DML을 수행될 수 없다.
세그먼트를 shrink 시키는동안 데이터는 압축(compaction) 단계에서 이동이된다. 압축 단계가 진행되는
동안 개별 row 또는 데이터 블럭에 대한 lock이 사용된다.
이 상황은, lock을 이용해, update나 delete와 같은 concurrent DML이 수행되는 상황과 유사하다.
압축은, 작은 트랜잭션 다뉘로 수행되므로, 객체에 대한 가용성은 심각하게 영향을 받지 않는다.
하지만, 세그먼트를 shrink 시키는 특정 단계에서는 (HWM을 조정하는 단계), 세그먼트는, exclusive 모드로
lock이 걸린다.
이 단계는 매우 짧은 기간이며, 객체에 대한 가용성에 미치는 영향이 최소호 된다.
8. 보안
세그먼트 shrink를 수행하기 위해서는 객체에 대해서 ALTER 시키는 권한과 동등한 권한이 필요하다.
9. 상세 예제
SQL> set serveroutput on
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 1
FS3 Blocks = 1
FS4 Blocks = 3
Full Blocks = 0
PL/SQL procedure successfully completed.
SQL> alter table t_shrink shrink space compact;
alter table t_shrink shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
SQL> ALTER TABLE t_shrink ENABLE ROW MOVEMENT;
Table altered.
SQL> ALTER TABLE t_shrink SHRINK SPACE COMPACT;
Table altered.
SQL> set serveroutput on
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 2
Full Blocks = 1
PL/SQL procedure successfully completed.
SQL> ALTER TABLE t_shrink SHRINK SPACE;
Table altered.
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 1
PL/SQL procedure successfully completed.
Example
--------------------------------------------------------------------------------
Reference Documents
--------------------------------------------------------------------------------
<Note:242090.1>
http://kr.forums.oracle.com/forums/thread.jspa?threadID=477210&tstart=30
|
18.97.9.172
|
|
|
|
|
|
ORA-10635: Invalid segment or tablespace type
SELECT TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES;
MANUAL => [ ORA-10635 ]
create tablespace ... segment space management manual; [ ORA-10635 ] |
2014-03-19
16:28:49
|
|
|
|
-- Shrink a LOB segment.
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
-- Shrink an IOT overflow segment.
ALTER TABLE iot_name OVERFLOW SHRINK SPACE;
-- Shrink table, index 추천
select c1||';' sql from table(dbms_space.asa_recommendations());
출처: https://oracle-base.com/articles/10g/space-object-transaction-management-10g |
2015-06-11
17:23:41
|
|
|
|
주의!
SQL> alter index SCOTT.EMP_UX1 shrink space;
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
Undo Tablespace 용량 모니터 필요.
추가 에러
SQL> alter table SCOTT.EMP shrink space;
alter table SCOTT.EMP shrink space
*
ERROR at line 1:
ORA-10662: Segment has long columns
Cause: Shrink was issued on a segment with long columns. This is not supported. |
2015-06-12
17:34:42
|
|
Copyright 1999-2024 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
|
|
|
11/21