TABLE 백업을 위해 기존 테이블을 RENAME 후 재생성하는 경우가 있다.
그런데 RENAME 대상 테이블에 TRIGGER 가 존재한다면 RENAME 시 TRIGGER 도 변경된 테이블로 바라보게 되기때문에 반드시 TRIGGER를 재생성 해줘야 한다.
그리고 CBO 환경의 통계정보가 존재하는 테이블이라면 RENAME 하기전 통계정보도 EXPORT 후 재생성후 IMPORT 해야 한다.
아래는 그에 대한 절차이다.
참고로 통계정보 이관에 대해서 더 자세하게 알고 싶으면 아래 글을 참조하기 바란다.
통계정보 이관하기 - 일반테이블 - http://dbtown.net/xe/848
통계정보 이관하기 - 파티션테이블 - http://dbtown.net/xe/851
TABLE Rename 후 TABLE 재생성 절차 (TRIGGER, 통계정보 포함)
1. Object 확인
|
conn /as sysdba select owner,object_name, object_type from dba_objects where object_name='TEST'; select owner,object_name, object_type from dba_objects where object_name='TEST_BACKUP'; |
2. INDEX & PK 확인
|
select owner, INDEX_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS from dba_indexes where table_name='TEST'; OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES ------------------------------ ------------------------------ -------- ------------------------ RCHWIN TEST_01 RCHWIN TEST TABLE NONUNIQUE RCHWIN TEST_02 RCHWIN TEST TABLE NONUNIQUE RCHWIN TEST_PK RCHWIN TEST TABLE UNIQUE select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from dba_constraints where TABLE_NAME='TEST'; OWNER CONSTRAINT_NAME C TABLE_NAME ------------------------------ --------------------------------- ------ RCHWIN SYS_C00220988 C TEST RCHWIN SYS_C00220989 C TEST RCHWIN TEST_BACKUP_PK P TEST |
3. Trigger 확인
|
SELECT OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME,STATUS FROM DBA_TRIGGERS WHERE TABLE_NAME='TEST‘ OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_NAME STATUS ------------------------------ ------------------------------ ------------------------------- RCHWIN TG_TEST_U AFTER EACH ROW UPDATE TEST ENABLE |
4. 통계정보 확인
|
SQL> select owner, table_name, num_rows from dba_tables 2 where table_name= 'TEST'; OWNER TABLE_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- RCHWIN TEST 12537139 SQL> select owner, index_name, num_rows from dba_indexes where table_name= 'TEST' OWNER INDEX_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- RCHWIN TEST_01 13076015 RCHWIN TEST_02 4797731 RCHWIN TEST_PK 12257465 |
5. 현재상태 통계정보 담을 테이블 생성 및 백업 ( 테이블 재생성후 통계정보를 넣기 위해 )
|
exec dbms_stats.create_stat_table(ownname=>'SYSTEM',stattab=>'STATS_IMSI',tblspace=>'USERS'); EXEC DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME=>'RCHWIN', TABNAME=>'TEST', STATTAB=> 'STATS_IMSI’, STATID=> 'STATS_IMSI',STATOWN=>'SYSTEM', cascade=>true); |
6. RENAME 전 OBJECT DESCRIPTION 추출 (TABLE, INDEX, PK, SYNONYM)
- RENAME 전 상용 TOOL (오렌지, TOAD) 을 이용하여 관련 OBJECT 의 DESCRIPTION 을 모두 추출한다.
7. RENAME ( TABLE, PK, INDEX )
|
conn RCHWIN/ ALTER TABLE TEST RENAME TO TEST_BACKUP; alter index TEST_PK rename to TEST_BACKUP_PK; alter index TEST_01 rename to TEST_BACKUP_01; alter index TEST_02 rename to TEST_BACKUP_02; alter table TEST_BACKUP rename constraint TEST_PK to TEST_BACKUP_PK; |
8. OBJECT 재생성 (TABLE, INDEX, PK, SYNONYM)
- RENAME 전 상용 TOOL (오렌지, TOAD) 을 이용하여 관련 OBJECT 의 DESCRIPTION 추출한 소스를 이용 (항목6번 참조)
9. INDEX & PK 확인
|
select owner, INDEX_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS from dba_indexes where table_name='TEST'; OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES ------------------------------ ----------------------------------------------------- RCHWIN TEST_01 RCHWIN TEST TABLE NONUNIQUE RCHWIN TEST_02 RCHWIN TEST TABLE NONUNIQUE RCHWIN TEST_PK RCHWIN TEST TABLE UNIQUE select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from dba_constraints where TABLE_NAME='TEST'; OWNER CONSTRAINT_NAME C TABLE_NAME ------------------------------ ------------------------------------ ------ RCHWIN SYS_C00220988 C TEST RCHWIN SYS_C00220989 C TEST RCHWIN TEST_BACKUP_PK P TEST |
10. Trigger 확인
|
SELECT OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME,STATUS FROM DBA_TRIGGERS WHERE TABLE_NAME='TEST‘ OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_NAME STATUS ------------------------------ ------------------------------ ------------------------------- RCHWIN TG_TEST_U AFTER EACH ROW UPDATE TEST_BACKUP ENABLE |
11. 트리거 재생성
- RENAME 전 상용 TOOL (오렌지, TOAD) 을 이용하여 관련 OBJECT 의 DESCRIPTION 추출한 소스를 이용 (항목6번 참조)
12. Trigger 확인
|
SELECT OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME,STATUS FROM DBA_TRIGGERS WHERE TABLE_NAME='TEST‘ OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_NAME STATUS ------------------------------ ------------------------------ ------------------------------- RCHWIN TG_TEST_U AFTER EACH ROW UPDATE TEST ENABLE |
13. 통계정보 확인
|
SQL> select owner, table_name, num_rows from dba_tables 2 where table_name= 'TEST'; OWNER TABLE_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- RCHWIN TEST SQL> select owner, index_name, num_rows from dba_indexes where table_name= 'TEST' OWNER INDEX_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- RCHWIN TEST_01 RCHWIN TEST_02 RCHWIN TEST_PK |
|
SQL> EXEC DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME=>'RCHWIN', TABNAME=>'TEST', STATTAB=> 'STATS_IMSI',STATID=> 'STATS_IMSI', STATOWN=>'SYSTEM', cascade=>true); PL/SQL procedure successfully completed. |
15. 통계정보 확인
|
SQL> select owner, table_name, num_rows from dba_tables 2 where table_name= 'TEST'; OWNER TABLE_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- RCHWIN TEST 12537139 SQL> select owner, index_name, num_rows from dba_indexes where table_name= 'TEST' OWNER INDEX_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- RCHWIN TEST_01 13076015 RCHWIN TEST_02 4797731 RCHWIN TEST_PK 12257465 |
|
conn / select owner,object_name, object_type from dba_objects where object_name='TEST'; select owner,object_name, object_type from dba_objects where object_name='TEST_BACKUP'; |
이 글의 권한은 dbtown.net 에 있습니다.
자료를 스크랩하실땐 출처를 밝혀주세요 ^^
Skin Technical Note
Sites validated CSS, XHTML & IE6, IE7, FireFox, Opera and Safari Accessiable. Generator & Powered by Zeroboard XE. This site inspired by Slabovia and developed by WTA
