오라클 테이블 축소 / oracle table shrink / alter table table_name shrink

오라클 테이블 축소 / oracle table shrink / alter table table_name shrink

TOM 슨상님이 주신 방법으로 테이블을 만들도록 하자

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1132417600346069010

9999999 이라고 되어있는 부분을 원하는 양으로 수정해서 사용하도록 하면 된다

create table big_table
as
select rownum id,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
  from all_objects a
 where 1=0
/
alter table big_table nologging;

declare
    l_cnt number;
    l_rows number := 9999999;
begin
    insert /*+ append */
    into big_table
    select rownum,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
      from all_objects a
     where rownum <= 9999999;

    l_cnt := sql%rowcount;

    commit;

    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into big_table
        select rownum+l_cnt,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from big_table
         where rownum <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/

alter table big_table add constraint
big_table_pk primary key(id)
/

ANALYZE TABLE BIG_TABLE COMPUTE STATISTICS;
ANALYZE INDEX big_table_pk COMPUTE STATISTICS;
 
select count(*) from big_table;

SQL> SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('BIG_TABLE','BIG_TABLE_PK');

SEGMENT_NAME             BLOCKS         MB

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

BIG_TABLE                139264       1088

BIG_TABLE_PK              22528        176

SQL> select count(*) from big_table;

  COUNT(*)

----------

   9999999

big_table에 있는 9999999 row의 block은 139264 blocks, size는 1088MB 이다

(139264*8192)/1024/1024 = 1088MB 이다

아무튼 1기가 사이즈의 테이블을 만들어서

SQL>  ANALYZE TABLE BIG_TABLE COMPUTE STATISTICS;

테이블이 분석되었습니다.

SQL>  ANALYZE INDEX big_table_pk COMPUTE STATISTICS;

인덱스가 분석되었습니다.

통계정보를 갱신하고

데이터를 삭제해보겠다

SQL> SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('BIG_TABLE','BIG_TABLE_PK');

SEGMENT_NAME             BLOCKS         MB

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

BIG_TABLE                139264       1088

BIG_TABLE_PK              22528        176

총 9999999 중 999999 row를 삭제하겠다

SQL > DELETE FROM BIG_TABLE WHERE ROWNUM<1000000;

그럼 9999999 중 999999 row가 삭제 되었으니 

9999999 row 가 1088메가 정도 되고 999999 row는 대략 100메가 분량이 삭제 되는것이다

SQL> select count(*) from big_table;

  COUNT(*)

----------

   9000000

흠흠 9000000 row만 남았다

ANALYZE TABLE BIG_TABLE COMPUTE STATISTICS;

ANALYZE INDEX big_table_pk COMPUTE STATISTICS;

로 다시한번 analyze를 해주고 얼마나 사용중인지 확인하자

SQL> SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('BIG_TABLE','BIG_TABLE_PK');

SEGMENT_NAME             BLOCKS         MB

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

BIG_TABLE                139264       1088

BIG_TABLE_PK              22528        176

읭? 테이블스페이스에 용량이 부족해서 테이블에 데이터를 10분의 1만큼 삭제했는데

여유공간이 돌아오지 않는다!!! 왜!!!!

high water mark때문에 그렇다

커질때는 마음대로 커져도 의도한대로 줄어들지는 않는다

그래서 있는것이 'shrink'다 말그대로 '축소'

shrink를 막 때린다고 바로 줄어드는건 아니고 row movement를 enable 시켜야 한다

SQL> alter table BIG_TABLE enable row movement;

SQL> alter table BIG_TABLE shrink SPACE COMPACT;

테이블이 변경되었습니다.

경   과: 00:02:19.94

SQL> SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('BIG_TABLE','BIG_TABLE_PK');

SEGMENT_NAME             BLOCKS         MB

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

BIG_TABLE                139264       1088

BIG_TABLE_PK              22528        176

shrink space compact를 해줬다

읭? 나의 소중한 2분 19초를 뺏어가고 여유공간은 단 1블록도 반환하지 않았다

SQL> alter table BIG_TABLE shrink SPACE;

테이블이 변경되었습니다.

경   과: 00:00:01.95

SQL> SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('BIG_TABLE','BIG_TABLE_PK');

SEGMENT_NAME             BLOCKS         MB

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

BIG_TABLE                122840   959.6875

BIG_TABLE_PK              22528        176

다시 shrink SPACE를 해주니 여유공간을 반환해 주었다

shrink space가 한번에 일괄적으로 축소를 하나 shrink space를 두단계로 나눠서 한다면

shrink space compact -> shrink space 으로 두단계로 나눠서 하는거다

오라클 문서에는 이기능은 긴 하나의 단계를 짧은 두단계로 나눠서 할때 유용하다고 하는데 유용한지 모르겠다;;;

아무튼 1088MB에서 959MB로 128MB정도 축소되었다

데이터는 줄었는데 인덱스는?? 

SQL> alter table BIG_TABLE shrink SPACE CASCADE;

테이블이 변경되었습니다.

경   과: 00:00:02.39

SQL> SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('BIG_TABLE','BIG_TABLE_PK');

SEGMENT_NAME             BLOCKS         MB

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

BIG_TABLE                122840   959.6875

BIG_TABLE_PK              20264   158.3125

cascade! 

cascade 옵션으로 해당 테이블의 인덱스 까지 줄어들게 만들었다

cascade 옵션은 연관된 모든 오브젝트들에 영향을 준다

결론적으로 

shrink 는

사진에서 본것처럼

shrink space를 기본이고 선택적으로 compact, cascade 옵션을 주는거다

보통은 shrink space 한번 shrink space cascade 한번 해주면 끝

오라클 공식문서를 보면

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2192484

오늘도 영어공부좀 해야겠다

The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

의역 : shrink 절은 당신이 table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log 의 공간을 수동으로 축소할수 있게 한다.  이 shrink 절은 automatic segment management가 활성화 되어있는 tablespace에 사용가능하고. 기본적으로 오라클 데이터베이스는 세그먼트를 잘 눌러담고, high water mark도 잘 수정하고, 여유공간도 바로 반환한다.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

의역 : 세그먼트를 압축하기 위해서는 row movement를 필요로 한다. 그러므로 shrink절을 사용하기 전에 shrink를 하기원하는 해당 오브젝트에 반드시 row movement를 활성화 해야한다. 추가적으로 어플리케이션에 rowid-based 트리거가 걸려있다면 작업에 앞서 해당 트리거를 disable하는것이 좋다

직역을 했다가 다 지웠다..직역으로 읽고 이해하는 습관을 기르도록 하자 ㅎㅎ

결론

정리를 해보자

테이블 스페이스에 공간이 부족해서 여유공간을 확보하려고 테이블에 데이터를 지우고 축소를 하려면?

해당 Tablespace가 automatic segment management 가 되어있어야하고

해당 Table은 row movement가 enable 되어있어야 한다

되어있다면 

alter table table_name shrink space;

alter table table_name shrink space cascade;

로 테이블사이즈를 줄여보도록 하자

오라클 테이블 축소 / oracle table shrink / alter table table_name shrink