Computer >> 컴퓨터 >  >> 프로그램 작성 >> 데이터 베이스

DBMS_REDEFINITION 패키지를 사용하는 Oracle 온라인 테이블 파티션

Oracle® 10g부터 DBMS_REDEFINITION 패키지를 사용하여 애플리케이션 다운타임 없이 테이블을 온라인으로 분할할 수 있습니다.

DBMS_REDEFINITION을 사용하여 파티션이 아닌 테이블을 파티션 테이블로 변경하려면 다음 단계를 따르십시오. 이 예에서는 파티션이 아닌 테이블인 TABLEA를 범위 간격 파티션 테이블로 변경합니다.

1단계:파티션 해제 테이블 백업

다음 코드를 실행하여 TABLEA 테이블의 전체 내보내기 백업을 만듭니다.

expdp  \"/ as sysdba\" directory=EXPDP_DIR dumpfile=tableA_UNPAR.dmp logfile=tableA_UNPAR.log TABLES=TEST.TABLEA

expdp  \"/ as sysdba\"  directory=EXPDP_DIR dumpfile=tableA_metaunpar.dmp logfile=tableA_metaunpar.log TABLES=TEST.TABLEA content=metadata_only

2단계:데이터베이스 개체 검사

테이블 삭제 시 다음 종속(D) 데이터베이스 개체를 삭제할 수 있습니다.

  • CONSTRAINT(제약) D

  • INDEX(인덱스) D

  • MATERIALIZED_VIEW_LOG(구체화된 뷰 로그) D

  • OBJECT_GRANT(객체 부여) D

  • TRIGGER(트리거) D

다음 SQL 명령을 실행하고 출력을 cons_trig_indx.txt와 같은 스풀 파일에 저장합니다. :

set LINESIZE 500
set PAGESIZE 1000
SQL> spool cons_trig_indx.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';

NAME                TYPE              OWNER
--------------      --------------    -------
PROC_TABLEA         PROCEDURE         TEST
TABLEA_TRIGG        TRIGGER           TEST
PKG_TABLEA          PACKAGE BODY      TEST


SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME
from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER   INDEX_NAME       TABLE_OWNER  TABLE_NAME   STATUS   TABLESPACE_NAME
---------------------------------------------------------------------------
TEST    TABLEA_IDX_ID01    TEST        TABLEA      VALID    TABLEA_TBL
TEST    TABLEA_IDX_ID04    TEST        TABLEA      VALID    TABLEA_TBL
TEST    TABLEA_IDX_PK      TEST        TABLEA      VALID    TABLEA_TBL


SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME  from dba_objects
where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';

no rows selected

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints
where TABLE_NAME='TABLEA' and owner='TEST';
SQL> spool off

CONSTRAINT_NAME     C
------------------  -----
SYS_C002004601      C
SYS_C002004602      C
SYS_C002004603      C
IDX_PK              P
FK01                R

3단계:TABLEA의 DDL 캡처

다음 명령을 실행하여 TABLEA의 데이터 정의 언어(DDL)를 캡처하고 스풀 파일 DEF_TABLEA.sql에 스크립트를 저장합니다. 파티션 테이블을 생성하기 전에:

set echo off
set feedback off
set linesize 160
set long 2000000
set pagesize 0
set trims on
column txt format a150 word_wrapped
SQL> spool DEF_TABLEA.sql
SQL> select DBMS_METADATA.GET_DDL('TABLE','TABLEA','TEST') txt FROM dual;
SQL> spool off

4단계:DDL 스크립트 복사

다음 명령어를 실행하여 3단계에서 생성한 DDL 스크립트를 복사합니다.

cp DEF_TABLEA.sql DEF_TABLEA_PAR.sql

5단계:비 파티션 테이블의 날짜 검토

다음 명령을 실행하여 TABLEA에서 날짜를 찾습니다.

SQL> select * from (select DT from TEST.TABLEA where rownum <15 order by DT DESC);

6단계:DEF_TABLEA_PAR.sql 파일 편집

DEF_TABLEA_PAR.sql 편집 다음 변경 사항:

  • TABLEA의 모든 항목 변경 TABLEA_PAR로 .

  • NOT NULL 또는 기타 제약 조건과 같은 모든 제약 조건을 삭제합니다.

  • 새 테이블스페이스에 테이블이 생성되도록 다음 명령을 삽입합니다.

      TABLESPACE "TABLEA_TBL_PAR" LOGGING

  • 5단계에서 식별된 날짜를 기반으로 파티션 정의를 추가하려면 다음 명령을 삽입하십시오.

      PARTITION BY RANGE(DT)
  interval (numtoyminterval(1,'MONTH'))
  (partition TABLEA_2004  values less than  (to_date('01/01/2005','DD/MM/YYYY')),
   partition TABLEA_2005 values less than  (to_date('01/01/2006','DD/MM/YYYY')));

DEF_TABLEA_PAR.sql 파일은 이제 다음 예와 같아야 합니다.

CREATE TABLE "TEST"."TABLEA_PAR"
(    "ID" NUMBER(6,0),
     "CEID" NUMBER(6,0),
     "DT" DATE,
     "AMT" NUMBER(14,4),
     "RET" NUMBER(14,4),
     "CNT" NUMBER(4,0),
     "VCNT" NUMBER(4,0),
     "EXEDT" DATE,
     "LASTUPDBY" VARCHAR2(15),
     "VENUM" NUMBER(6,0),
     "LASTUPDDT" TIMESTAMP (6))
TABLESPACE "TABLEA_TBL_PAR" LOGGING
PARTITION BY RANGE(DT)
interval (numtoyminterval(1,'MONTH'))
(partition TABLEA_2004  values less than  (to_date('01/01/2005','DD/MM/YYYY')),
 partition TABLEA_2005  values less than  (to_date('01/01/2006','DD/MM/YYYY')));

7단계:파티션 테이블 생성

다음 단계를 실행하여 DEF_TABLEA_PAR.sql 파티션 테이블을 생성합니다. 스크립트:

SQL> spool DEF_TABLEA_PAR.outp.txt
SQL> @DEF_TABLEA_PAR.sql

Table Created.

SQL> spool off

8단계:파티션 테이블 확인

다음 명령을 실행하여 파티션 테이블을 확인하고 정의된 파티션을 반환합니다.

SQL> spool verify_partition.txt
SQL> select partition_name from DBA_tab_partitions where table_name ='TABLEA_PAR' and table_owner = 'TEST';
SQL> spool off

PARTITION_NAME
-----------------
TABLEA_2004
TABLEA_2005

9단계:비 파티션 테이블에 대한 통계 수집

다음 명령을 실행하여 파티션이 없는 테이블에 대한 통계를 수집하고 스풀 파일에 저장합니다.

SQL> SPOOL gather_stats.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA',cascade => TRUE);
SQL> spool off

10단계:재정의 타당성 확인

참고 :원본 테이블(분할되지 않음)은 재정의 패키지를 사용하기 전에 기본 키가 필요하지 않습니다.

다음 명령을 실행하여 재정의가 가능한지 확인하고 결과를 스풀 파일에 저장합니다.

SQL> spool check_the_redefinition.txt
SQL> EXEC DBMS_Redefinition.can_redef_table ('TEST', 'TABLEA');
SQL> spool off

11단계:재정의 시작

check_the_redefinition.txt에 오류가 없는 경우 , 다음 장기 실행 명령을 사용하여 재정의를 시작하십시오.

SQL> spool start_redef_table.txt
SQL>begin
    dbms_redefinition.start_redef_table
    (
     uname => 'TEST',
     orig_table => 'TABLEA',
     int_table => 'TABLEA_PAR');
     end;
   /
SQL> spool off

12단계:재정의 중 테이블스페이스 오류 관찰

11단계에서 재정의 작업을 수행하면 다음 예와 같은 테이블스페이스 경고가 발생할 수 있습니다.

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TEST.TABLEA_PAR
partition SYS_P42 by 1024 in tablespace TABLEA_TBL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

앞의 예와 유사한 테이블스페이스 오류가 표시되면 다음 단계를 수행해야 합니다.

  1. 재정의 프로세스를 중지하려면 다음 명령을 실행하십시오.

     SQL> spool abort_redef_table.txt
 SQL> begin
      dbms_redefinition.abort_redef_table
      (
      uname => 'TEST',
      orig_table => 'TABLEA',
      int_table => 'TABLEA_PAR');
      end;
     /
 SQL> spool off

  2. 파티션 테이블과 구체화된 뷰를 삭제합니다.

  3. 테이블스페이스의 크기를 늘립니다. 이 예에서는 TABLEA_TBL 테이블스페이스의 크기를 늘려야 합니다.

  4. 11단계를 다시 실행하십시오.

13단계:재정의 오류 확인

재정의 프로세스가 성공적으로 완료되면 다음 명령을 실행하여 오류가 있는지 확인하십시오.

SQL> spool copy_table_dependents.txt
SQL> SET SERVEROUTPUT ON
     DECLARE
     l_num_errors PLS_INTEGER;
     BEGIN
       DBMS_REDEFINITION.copy_table_dependents(
           uname             => 'TEST',
           orig_table        => 'TABLEA',
           int_table         => 'TABLEA_PAR',
           copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
           num_errors        => l_num_errors);
           DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
     END;
/
SQL> spool off

재정의에 성공하면 copy_table_dependents.txt에 다음과 유사한 결과가 표시되어야 합니다. 파일:

l_num_errors=0
PL/SQL procedure successfully completed.

14단계:(선택 사항) 파티션 테이블 재동기화

원하는 경우 다음 명령을 실행하여 파티션 테이블을 임시 이름으로 재동기화합니다.

SQL> spool sync_interim_table.txt
SQL>
     BEGIN
       DBMS_REDEFINITION.sync_interim_table
       (
           uname => 'TEST',
           orig_table => 'TABLEA',
           int_table => 'TABLEA_PAR');
      END;
/
SQL> spool off

15단계:파티션 테이블에 대한 통계 수집

다음 명령을 실행하여 파티션 테이블에 대한 통계를 수집하십시오.

SQL> spool gather_statistics_par.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA_PAR',cascade => TRUE);
SQL> spool off

16단계:제약 조건 스크립트 생성

다음 명령을 실행하여 유효성 검사 제약 조건을 활성화하는 스크립트를 준비하십시오.

SQL> spool constraint_enable_validate.txt
SET LINESIZE 500
SET PAGESIZE 1000

SQL> select 'alter table' ||' '||OWNER||'.'||TABLE_NAME||' enable validate constraint'||' '||CONSTRAINT_NAME||';' from dba_constraints where TABLE_NAME = 'TABLEA_PAR' and OWNER='TEST';

'ALTERTABLE'||''||OWNER||'.'||TABLE_NAME||'ENABLEVALIDATECONSTRAINT'||''||CONSTR
--------------------------------------------------------------------------------
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;

SQL> spool off

17단계:제약 조건 확인 활성화

다음 예와 같이 16단계에서 생성된 스크립트와 명령을 실행합니다.

SQL> spool constraint_enable_execute.outp.txt
SQL>@constraint_enable.sql

alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;

SQL> spool off

18단계:비 파티션 테이블과 파티션 테이블 비교

파티션이 없는 원래 테이블을 새 파티션 테이블과 비교하여 모든 속성이 동일한지 확인합니다.

19단계:테이블 이름 바꾸기

임시 테이블을 실제 테이블로 설정하여 테이블 이름을 전환하려면 다음 명령을 실행하십시오.

SQL> spool finish_redef_table.txt
     BEGIN
       DBMS_REDEFINITION.finish_redef_table
      (
        uname => 'TEST',
        orig_table => 'TABLEA',
        int_table => 'TABLEA_PAR');
     END;
/

--------------------------------------------
@?/rdbms/admin/utlrp.sql
--------------------------------------------

SQL>spool off

20단계:테이블 비교

다음 명령을 실행하여 두 테이블의 레코드 수를 비교하고 일치하는지 확인하십시오.

SQL> spool table_count.outp.txt
SQL> select count(*) from TEST.TABLEA;

 COUNT(*)
----------
  890540

SQL> select count (*) from TEST.TABLEA_PAR;

 COUNT(*)
----------
  890540

SQL> spool off

21단계:파티션 성공 확인

다음 명령을 실행하여 파티션 프로세스가 성공했는지 확인하십시오.

SQL> spool check_partition.txt
SQL> select partitioned from dba_tables where table_name = 'TABLEA' and owner='TEST';

PAR
------
YES

SQL> select partition_name , SUBPARTITION_COUNT, TABLESPACE_NAME from dba_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> select table_name, partition_name, high_value, partition_position from DBA_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> spool off

22단계:데이터베이스 개체 재검토

다음 명령을 실행하여 데이터베이스 개체를 검사하고 결과를 2단계와 비교합니다.

SET LINESIZE 500
SET PAGESIZE 1000
SQL> spool cons_indx_trigg.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';

NAME                TYPE              OWNER
----------------    ---------------   ------------
PROC_TABLEA         PROCEDURE         TEST
TABLEA_TRIGG        TRIGGER           TEST
PKG_TABLEA          PACKAGE BODY      TEST

SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER  INDEX_NAME       TABLE_OWNER TABLE_NAME  STATUS   TABLESPACE_NAME
------------------------------------------------------------------------
TEST   TABLEA_IDX_ID01  TEST        TABLEA      VALID    TABLEA_TBL
TEST   TABLEA_IDX_ID04  TEST        TABLEA      VALID    TABLEA_TBL
TEST   TABLEA_IDX_PK    TEST        TABLEA      VALID    TABLEA_TBL

SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME  from dba_objects where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';

no rows selected

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where TABLE_NAME='TABLEA' and owner='TEST';

CONSTRAINT_NAME        C
-------------------		----------
SYS_C002004601         C
SYS_C002004602         C
SYS_C002004603         C
IDX_PK                 P
FK01                   R

12 rows selected.

SQL> spool off

23단계:색인 재구축

다음 명령을 실행하여 새 테이블스페이스에서 인덱스를 다시 작성하십시오.

<프리>SQL> spool rebuild_indx.txt SQL>@rebuild_index.sql ALTER INDEX TEST.TABLEA_IDX_ID01 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE; ALTER INDEX TEST.ITABLEA_IDX_ID04 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE; ALTER INDEX TEST.TABLEA_IDX_PK REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE; SQL> spool off

24단계:색인 확인

다음 명령을 실행하여 상태가 valid인지 확인하십시오. 모든 인덱스의 테이블스페이스는 TABLEA_TBL_PAR:

SQL> spool verify_indx.outp.txt
SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER  INDEX_NAME       TABLE_OWNER  TABLE_NAME   STATUS   TABLESPACE_NAME
---------------------------------------------------------------------------
TEST   TABLEA_IDX_ID01  TEST         TABLEA       VALID   	 TABLEA_TBL_PAR
TEST   TABLEA_IDX_ID04  TEST         TABLEA       VALID   	 TABLEA_TBL_PAR
TEST   TABLEA_IDX_PK    TEST         TABLEA       VALID     TABLEA_TBL_PAR

SQL>spool off

25단계:파티션이 아닌 원본 테이블 삭제

DBA가 모든 것이 좋아 보인다는 것을 확인한 후 다음 명령을 실행하여 중간 테이블 이름이 TEST.TABLEA_PAR인 원래 테이블을 제거합니다.

SQL> DROP table TEST.TABLEA_PAR cascade constraints;

결론

이전 단계에서는 중간 테이블 TEST.TABLEA_PAR을 사용하여 TEST.TABLEA 테이블을 애플리케이션 다운타임 없이 범위 간격 테이블로 분할했습니다.

피드백 탭을 사용하여 의견을 남기거나 질문하십시오.