SQL튜닝 Migration - Online Redefinition
SYS@orcl>desc _dbms_redefinition
ERROR:
ORA-00911: invalid character
ORA-00911: invalid character
SYS@orcl>desc dbms_redefinition
PROCEDURE ABORT_REDEF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE CAN_REDEF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
TNAME VARCHAR2 IN
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE COPY_TABLE_DEPENDENTS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COPY_INDEXES BINARY_INTEGER IN DEFAULT
COPY_TRIGGERS BOOLEAN IN DEFAULT
COPY_CONSTRAINTS BOOLEAN IN DEFAULT
COPY_PRIVILEGES BOOLEAN IN DEFAULT
IGNORE_ERRORS BOOLEAN IN DEFAULT
NUM_ERRORS BINARY_INTEGER OUT
COPY_STATISTICS BOOLEAN IN DEFAULT
COPY_MVLOG BOOLEAN IN DEFAULT
PROCEDURE FINISH_REDEF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE REGISTER_DEPENDENT_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
DEP_TYPE BINARY_INTEGER IN
DEP_OWNER VARCHAR2 IN
DEP_ORIG_NAME VARCHAR2 IN
DEP_INT_NAME VARCHAR2 IN
PROCEDURE START_REDEF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COL_MAPPING VARCHAR2 IN DEFAULT
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
ORDERBY_COLS VARCHAR2 IN DEFAULT
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE SYNC_INTERIM_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE UNREGISTER_DEPENDENT_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
DEP_TYPE BINARY_INTEGER IN
DEP_OWNER VARCHAR2 IN
DEP_ORIG_NAME VARCHAR2 IN
DEP_INT_NAME VARCHAR2 IN
SYS@orcl>ed
Wrote file afiedt.buf
1 begin
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('HR','employees',
3 dbms_redefinition.cons_use_pk);
4* end;
SYS@orcl>/
PL/SQL procedure successfully completed.
SYS@orcl>create tablespace emp_tbs1
2 datafile '/u01/app/oracle/oradata/orcl/emp_tbs1.dbf' size 10m;
Tablespace created.
SYS@orcl>ed
Wrote file afiedt.buf
1 create tablespace emp_tbs2
2* datafile '/u01/app/oracle/oradata/orcl/emp_tbs2.dbf' size 10m
SYS@orcl>/
Tablespace created.
SYS@orcl>create table hr.int_employees(
2 employee_id number(6), first_name varchar2(20),
3 last_name varchar2(25), email varchar2(25),
4 phone_number varchar2(20), location number(4),
5 hire_date date default (sysdate), job_id varchar2(10),
6 salary number(8,2), commission_pct number(2,2),
7 manager_id number(6), department_id number(4))
8 partition by range(employee_id)
9 (partition emp190 values less than (190) tablespace emp_tbs1,
10 partition emp1000 values less than (1000) tablespace emp_tbs2);
Table created.
SYS@orcl>begin
2 DBMS_REDEFINITION.START_REDEF_TABLE('hr','employees','int_employees',
3 'employee_id employee_id, first_name first_name, last_name last_name,
4 email email, phone_number phone_number, 0 location,
5 hire_date hire_date, job_id job_id, salary salary, commission_pct*1.01 commission_pct, manager_id manager_id, department_id department_id',
6 dbms_redefinition.cons_use_pk);
7 end;
8 /
PL/SQL procedure successfully completed.
M-VIEW 작동 확인 HR에서
HR@orcl>UPDATE employees set salary = 25000 where employee_id = 200;
1 row updated.
HR@orcl>commit;
Commit complete.
다시 SYS로 와서 작업
SYS@orcl>set serveroutput on
SYS@orcl>DECLARE no_errors int;
2 BEGIN
3 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
4 ('hr', 'employees','int_employees',1,true,true,true,
5 false, no_errors);
6 DBMS_OUTPUT.PUT_LINE ('No of errors during copy of dependents '||no_errors);
7 end;
8 /
No of errors during copy of dependents 0
PL/SQL procedure successfully completed.
SYS@orcl>BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr','employees','int_employees');
3 end;
4 /
PL/SQL procedure successfully completed.
SYS@orcl>begin
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr','employees','int_employees');
3 end;
4 /
PL/SQL procedure successfully completed.
SYS@orcl>desc hr.int_employees
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SYS@orcl>desc hr.employees
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(25)
EMAIL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
LOCATION NUMBER(4)
HIRE_DATE DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SYS@orcl>select PARTITIONING_TYPE,PARTITION_COUNT
2 from dba_part_tables
3 where table_name = 'EMPLOYEES' and owner ='HR';
PARTITION PARTITION_COUNT
--------- ---------------
RANGE 2
SYS@orcl>desc hr.employees
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(25)
EMAIL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
LOCATION NUMBER(4)
HIRE_DATE DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SYS@orcl>drop table hr.int_employees cascade constraints purge;
Table dropped.
SYS@orcl>spool off