DanBrother的部落格

Oracle Online Table Redefinition with DBMS_REDEFINITION package

Oracle Database allows to modify table structure online without affecting the table usages.

Example: Change a normal non-partitioned table into a Hash-Partitioned Table online

-- Create the original table and fill up with random-generated data
create table redef_tab
(num number(10) primary key,
name varchar2(10) not null
) nologging;

desc redef_tab
SQL> desc redef_tab
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
NUM NUMBER(10)
NAME VARCHAR2(10)

begin
for i in 1..10000 loop
insert /*+APPEND*/ into redef_tab(num,name)
values(i,dbms_random.string('x',10));
end loop;
commit;
end;
/

select count(*) from redef_tab;
COUNT(*)
----------
10000

1. Verify that the table is a candicate for online redefinition.

conn / as sysdba
exec dbms_redefinition.can_redef_table('TESTUSER','REDEF_TAB', -
DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

2. Create the hash-partitioned interim table structure (without any constraints)
create table int_redef_tab
(num number(10),
name varchar2(10)
) partition by hash(name) partitions 10 nologging;

col partition_name format a20
select partition_name,partition_position from user_tab_partitions
where table_name = 'INT_REDEF_TAB');
>>

PARTITION_NAME PARTITION_POSITION
-------------------- ------------------
SYS_P62 1
SYS_P63 2
SYS_P64 3
SYS_P65 4
SYS_P66 5
SYS_P67 6
SYS_P68 7
SYS_P69 8
SYS_P70 9
SYS_P71 10

select table_name,partitioned from user_tables
where table_name like '%REDEF_TAB';
>>

TABLE_NAME PARTITIONED
------------------------------ -----------
REDEF_TAB NO
INT_REDEF_TAB YES

3. Start the redefinition process
exec dbms_redefinition.start_redef_table('TESTUSER','REDEF_TAB','INT_REDEF_TAB');

PL/SQL procedure successfully completed.

var nerrors number
begin
dbms_redefinition.copy_table_dependents(UNAME => 'TESTUSER',
ORIG_TABLE => 'REDEF_TAB',
INT_TABLE => 'INT_REDEF_TAB',
COPY_INDEXES => dbms_redefinition.cons_orig_params,
COPY_TRIGGERS => TRUE,
COPY_CONSTRAINTS => TRUE,
COPY_PRIVILEGES => TRUE,
IGNORE_ERRORS => FALSE,
NUM_ERRORS => :nerrors);
end;
/

print
NERRORS
----------
0

4. Complete the redefinition

exec dbms_redefinition.finish_redef_table('TESTUSER','REDEF_TAB','INT_REDEF_TAB');
PL/SQL procedure successfully completed.

Note:
According to [Doc ID: 1089860.1] ,
Not Null / Primary Key constraints are copied in NOVALIDATE mode in order to speed up the redefinition process.
The not null constraint can be reenabled for VALIDATE using the ALERT TABLE ... ENABLE VALIDATE CONSTRAINT ... command

select constraint_name,constraint_type,validated from user_constraints where table_name = 'REDEF_TAB';
>>
CONSTRAINT_NAME CONSTRAINT_TYPE VALIDATED
------------------------------ --------------- -------------
SYS_C0019868 C NOT VALIDATED
SYS_C0019867 P NOT VALIDATED

begin
for rec in (select table_name as t,constraint_name as c from user_constraints where table_name = 'REDEF_TAB' ) loop
execute immediate 'alter table '||rec.t||' enable validate constraint '||rec.c;
end loop;
end;
/

PL/SQL procedure successfully completed

select constraint_name,constraint_type,validated from user_constraints where table_name = 'REDEF_TAB';

CONSTRAINT_NAME CONSTRAINT_TYPE VALIDATED
------------------------------ --------------- -------------
SYS_C0019868 C VALIDATED
SYS_C0019867 P VALIDATED

select table_name,partitioned from user_tables where table_name like '%REDEF_TAB';
>>
TABLE_NAME PARTITIONED
------------------------------ -----------
INT_REDEF_TAB NO
REDEF_TAB YES

col partition_name format a20
select partition_name,partition_position from user_tab_partitions where table_name = 'REDEF_TAB';
>>
PARTITION_NAME PARTITION_POSITION

-------------------- ------------------
SYS_P62 1
SYS_P63 2
SYS_P64 3
SYS_P65 4
SYS_P66 5
SYS_P67 6
SYS_P68 7
SYS_P69 8
SYS_P70 9
SYS_P71 10

10 rows selected

select count(*) from REDEF_TAB;
>>
COUNT(*)
----------
10000


-- Check the error by
select object_name,base_table_name,ddl_txt from DBA_REDEFINITION_ERRORS;

drop table INT_REDEF_TAB purge;