You are hereExample redefinition table

Example redefinition table


By surachart - Posted on 18 November 2008

Need to redefine online "SCOTT"."EMP01", and deptno column = deptno + 10 and use partitions tables

CREATE TABLE "SCOTT"."EMP01"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP01" PRIMARY KEY ("EMPNO"));

select * from "SCOTT"."EMP01";


     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

- Verify "SCOTT"."EMP01" table is a candidate for online redefinition.

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP01',
      DBMS_REDEFINITION.CONS_USE_PK);
END;
/

- Create an interim table

 CREATE TABLE "SCOTT"."EMP02"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP02" PRIMARY KEY ("EMPNO"))
        PARTITION BY RANGE(empno)
       (PARTITION emp1000 VALUES LESS THAN (7500),
        PARTITION emp2000 VALUES LESS THAN (8000));

- Start the redefinition process

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'EMP01','EMP02',
       'empno empno, ename ename, job job, deptno+10 deptno',
        dbms_redefinition.cons_use_pk);
END;
/

- Copy dependent object

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'EMP01','EMP02',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

select * from emp01;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10


select * from emp02;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             30
      7499 ALLEN      SALESMAN          40
      7521 WARD       SALESMAN          40
      7566 JONES      MANAGER           30
      7654 MARTIN     SALESMAN          40
      7698 BLAKE      MANAGER           40
      7782 CLARK      MANAGER           20
      7788 SCOTT      ANALYST           30
      7839 KING       PRESIDENT         20
      7844 TURNER     SALESMAN          40
      7876 ADAMS      CLERK             30
      7900 JAMES      CLERK             40
      7902 FORD       ANALYST           30
      7934 MILLER     CLERK             20

- Query the DBA_REDEFINITION_ERRORS view to check for errors

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

- Synchronize the interim table

BEGIN 
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMP01', 'EMP02');
END;
/

- Complete the redefinition

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMP01', 'EMP02');
END;
/


select partitioned from user_tables where table_name='EMP01';

PAR
---
YES

... Then drop interim table (EMP02)



Syndicate

Syndicate content

Follow DatabasesLA on Twitter

Who's online

There are currently 0 users and 4 guests online.

Estadisticas

Locations of visitors to this page

hidden hit counter