===============================================================================
EXCHANGE
===============================================================================
A User Table 을 B User 의 Table 로 이동하는 방법 중 Oracle EXCHANGE 를 활용할 수 있다.
Table 에 대해 Object ID 와 Data 에 대해 Data Object ID 를 가지는데,
서로 Link 되어 있다. Oracle Excchange 를 통해 위 정보를 변경하므로써,
-- scott.emp를 chlee.emp로 바꾼다고 가정
User 의 Table 을 다른 User 로 옮기는 작업이 필요할때, Oracle EXCHANGE 를 통해 Oracle Dictionary
정보의 변경을 통해 exp/imp 보다 빠르고 효율적인 결과를 얻을 수 있다.
[절차]
1. Source User 의 Table meata data 정보를 Target User 에 CONSTRAINT, INDEX 포함하여 동일하게 구성한다.
2. Oracle EXCHANGE 를 활용하여, Source Table 을 임시 Partition Table flug-in 시킨다.
3. 임시 Partition Table ==> 사용할 Table 의 로 flug-in 시킨다.
4. 확인 작업
(1) Rows Counts, Segment Count, Object Count, Object ID
(2) Invalid Object 확인
------------------------------------------------------
00. Test 를 위한 User 생성
------------------------------------------------------
create user chlee identified by chlee default tablespace USERS;
grant connect, resource to chlee;
------------------------------------------------------
01. DDL : Source A.Table => Target B.Table
------------------------------------------------------
create table chlee.emp as select * from scott.emp where rownum <=0;
==> Source Table (user : scott) 와 동일한 구조의 Table (user : chlee) 생성
set lines 250
col TABLE_NAME for a20
col COLUMN_NAME for a20
col POSITION for 9999
col CONSTRAINT_NAME for a20
col CONSTRAINT_TYPE for a10
col VALIDATED for a10
SELECT A.OWNER
, B.TABLE_NAME
, B.COLUMN_NAME
, B.POSITION
, A.CONSTRAINT_NAME
, A.CONSTRAINT_TYPE
, A.VALIDATED
FROM DBA_CONSTRAINTS A
, DBA_CONS_COLUMNS B
WHERE A.OWNER in ('chlee','SCOTT')
AND A.TABLE_NAME = 'EMP'
AND A.OWNER = B.OWNER
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
/
OWNER TABLE_NAME COLUMN_NAME POSITION CONSTRAINT_NAME CONSTRAINT VALIDATED
----------------------- -------------------- -------------------- -------- -------------------- ---------- ----------
SCOTT EMP EMPNO 1 emp_pk P VALIDATED
ALTER TABLE chlee.EMP add CONSTRAINT "emp_pk" PRIMARY KEY ("EMPNO");
set lines 300
set pages 300
col TABLE_NAME for a20
col INDEX_NAME for a20
col COLUMN_NAME for a20
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM DBA_IND_COLUMNS WHERE TABLE_NAME = 'EMP' AND TABLE_OWNER = 'SCOTT'
/
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- -------------------- -------------------- ---------------
EMP emp_pk EMPNO 1
------------------------------------------------------
02. DDL : Source Table => Temp Table
------------------------------------------------------
create table emp_temp partition by range(empno) (partition p1 values less than(maxvalue)) as select * from scott.emp where rownum <=0;
-- Temp Table 동일하게 CONSTRAINT 구성
ALTER TABLE EMP_TEMP add CONSTRAINT "emp_pk" PRIMARY KEY ("EMPNO");
------------------------------------------------------
03. EXCHANGE : Source Table => Temp Partition Table
------------------------------------------------------
ALTER TABLE emp_temp EXCHANGE PARTITION p1 WITH TABLE scott.emp WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
------------------------------------------------------
04. EXCHANGE : Temp Partition Table => Target Table
------------------------------------------------------
conn sys as sysdba
alter table scott.emp_temp exchange partition p1 with table chlee.emp WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
-- alter table emp_temp exchange partition p1 with table sh.emp including indexes without validation;
------------------------------------------------------
05. 확인 작업
------------------------------------------------------
===========================================================
참고 사항
===========================================================
===========================================================
Meta Data 이전
===========================================================
SQL> create or replace directory DUMPDIR_EXP as '/oracle/DBA/common/monitor/TEST';
Directory created.
SQL> grant read, write on directory DUMPDIR_EXP to system;
Grant succeeded.
expdp userid=\'/ as sysdba\' dumpfile=DUMPDIR_EXP:EMP.dmp LOGFILE=DUMPDIR_EXP:EMP.log TABLES=SCOTT.EMP, SCOTT.DEPT content=metadata_only
impdp userid=\'/ as sysdba\' dumpfile=DUMPDIR_EXP:EMP.dmp LOGFILE=DUMPDIR:EMP_import.log REMAP_SCHEMA=SCOTT:chlee
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
===========================================================
Metadata 축출
===========================================================
set heading off
set echo off
set flush off
set pagesize 3999
set linesize 3999
set long 3999
set longc 3999
select dbms_metadata.get_ddl('TABLE', 'EMP', 'SCOTT') from dual;
CREATE TABLE "chlee"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10) DEFAULT 'abc',
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "EMP_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "chlee"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_ASTA_D_01"
/
ORA-38029: object statistics are locked 발생 시 아래 unlock 수행
exec dbms_stats.UNLOCK_TABLE_STATS('chlee','EMP');
===========================================================
CONSTRAINT 조회, 생성, 변경, 삭제
===========================================================
------------------------------------------------------
CONSTRAINT 조회
------------------------------------------------------
set lines 250
col TABLE_NAME for a20
col COLUMN_NAME for a20
col POSITION for 9999
col CONSTRAINT_NAME for a20
col CONSTRAINT_TYPE for a10
col VALIDATED for a10
SELECT A.OWNER
, B.TABLE_NAME
, B.COLUMN_NAME
, B.POSITION
, A.CONSTRAINT_NAME
, A.CONSTRAINT_TYPE
, A.VALIDATED
FROM DBA_CONSTRAINTS A
, DBA_CONS_COLUMNS B
WHERE A.TABLE_NAME in ('EMP', 'DEPT' ,'EMP_TEMP')
AND A.OWNER = B.OWNER
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
/
OWNER TABLE_NAME COLUMN_NAME POSITION CONSTRAINT_NAME CONSTRAINT VALIDATED
------------------------------ -------------------- -------------------- -------- -------------------- ---------- ----------
SCOTT DEPT DEPTNO 1 DEPT_PK P VALIDATED
SCOTT EMP EMPNO 1 EMP_PK P VALIDATED
SCOTT EMP EMPNO SYS_C0038671 C VALIDATED
SCOTT EMP DEPTNO 1 EMP_FK R VALIDATED
------------------------------------------------------
Constraint Rename
------------------------------------------------------
ALTER TABLE chlee.emp RENAME CONSTRAINT SYS_C0014555613 TO SYS_C0038671;
------------------------------------------------------
Constraint Add
------------------------------------------------------
ALTER TABLE EMP_TEMP add CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO");
ALTER TABLE EMP_TEMP add CONSTRAINT "EMP_FK" foreign key (DEPTNO) references DEPT (DEPTNO);
------------------------------------------------------
Constraint Drop
------------------------------------------------------
ALTER TABLE EMP_TEMP DROP CONSTRAINT EMP_PK;