Loading....

In this example we will perform an online definition of the EMP table in the SCOTT schema by performing the following tasks online:

1) Range partition the EMP table based on empno
2) Change the column name of SALARY to SAL

Verify that the table is a candidate for online redefinition

SQL> conn / as sysdba
Connected.
SQL> BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(‘SCOTT’,’EMP’);
END;
/

PL/SQL procedure successfully completed.

SQL> conn scott/tiger
Connected.

SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
DOJ DATE
SALARY NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Create an Interim Table

SQL> CREATE TABLE ONLINE_EMP
2 (empno NUMBER(5) NOT NULL,
3 ename VARCHAR2(15) NOT NULL,
4 job VARCHAR2(10),
5 mgr NUMBER(5),
6 doj DATE DEFAULT (sysdate),
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(3) NOT NULL)
10 PARTITION BY RANGE(empno)
11 (PARTITION emp1 VALUES LESS THAN (7800) ,
12 PARTITION emp2 VALUES LESS THAN (8000) );

Table created.

Start the table Redefinition process

SQL> conn / as sysdba
Connected.

SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(‘SCOTT’, ‘EMP’,’ONLINE_EMP’,
’empno empno, ename ename, job job, mgr mgr, doj doj, salary sal, comm comm, deptno deptno’,
dbms_redefinition.cons_use_pk);
END;
/

PL/SQL procedure successfully completed.

While the redefinition process in progress, dependant objects are still VALID and are in use

SQL> conn scott/tiger
Connected.
SQL> select get_sal(7934) from dual;

GET_SAL(7934)
————-
1950

Automatically create any triggers, indexes and constraints that were originally defined on EMP on interim table ONLINE_EMP

SQL> declare x binary_Integer;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(‘SCOTT’,’EMP’,’ONLINE_EMP’,
1, TRUE, TRUE, TRUE,FALSE,x);
END;
/

PL/SQL procedure successfully completed.

Perform some DML on the table while redefinition is in progress

SQL> update emp set comm =comm +salary;

14 rows updated.

SQL> commit;

Commit complete.

Synchronize the Interim and Original Tables

SQL> conn / as sysdba
Connected.
SQL> BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘SCOTT’, ‘EMP’, ‘ONLINE_EMP’);
END;
/

PL/SQL procedure successfully completed.

Complete the redefinition

SQL> BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘SCOTT’, ‘EMP’, ‘ONLINE_EMP’);
END;
/

PL/SQL procedure successfully completed.

The table ONLINE_EMP is locked in the exclusive mode only for a small window toward the end of this step. After this call the table EMP is redefined such that it has all the attributes of the ONLINE_EMP table

Drop the Interim Table

SQL> conn scott/tiger
Connected.
SQL> drop table online_emp;

Table dropped.

Check the Online Redefinition process

Check the table column names

SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(5)
ENAME NOT NULL VARCHAR2(15)
JOB VARCHAR2(10)
MGR NUMBER(5)
DOJ DATE
SAL (Originally SALARY) NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NOT NULL NUMBER(3)

Confirm the table is now partitioned

SQL> select count(*) from emp partition (emp1);

COUNT(*)
———-
8

SQL> select count(*) from emp partition (emp2);

COUNT(*)
———-
6

Check all constraints originally defined on EMP are still present

SQL> select constraint_name from user_constraints
2 where table_name=’EMP’;

CONSTRAINT_NAME
——————————
SYS_C0017251
SYS_C0017252
SYS_C0017253
PK_EMP
FK_DEPTNO

The function however will have to be recreated as the code still contained the original column name “SALARY”

SQL> alter function get_sal compile;

Warning: Function altered with compilation errors.

SQL> sho err
Errors for FUNCTION GET_SAL:

LINE/COL ERROR
——– —————————————————————–
6/8 PL/SQL: SQL Statement ignored
6/15 PL/SQL: ORA-00904: “SALARY”: invalid identifier
SQL>

Please follow and like us:

Last Update: July 19, 2009  

July 19, 2009 144 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?