20 | 10 | 2017
Latest Articles
Popular Articles

Administration

Succeeding with online table redefinition

User Rating:  / 1
PoorBest 

Succeeding with online table redefinition


This is how we can use online table redefinition with dbms_redefinition

Possibilities and syntax is Oracle release dependent, like always it is wise to check the online Oracle documentation

With this example we change the datatype of a field from number to column, unluckily the field belongs to the primary key

With this example we change the data type of the field online, despite a huge number of records in it,

The table to be redifined remains online, ie the table remains available for selects and DML

Disadvantage of online dbms redefinition is that we temporary need twice the space of the table to be redefined.

 

show release
release 1102000100

alter table OWNER_1.T_ORIG modify (C2 VARCHAR2(12));
alter table OWNER_1.T_ORIG modify (C2 VARCHAR2(12))
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype


We decide to use dbms_redefinition, let' s browse the indexes, constraints and triggers on the table


select index_name from dba_indexes where table_name='T_ORIG';

INDEX_NAME
------------------------------
SYS_C0010354
T_ORIG_I2
T_ORIG_I3


select constraint_name,constraint_type from dba_constraints where table_name='T_ORIG';

CONSTRAINT_NAME C
------------------------------ -
SYS_C0010353 C
SYS_C0010352 C
SYS_C0010351 C
SYS_C0010350 C
SYS_C0010354 P


select trigger_name from dba_triggers where table_name='T_ORIG';
no rows selected

 

There are no dependend objects ( check dba_dependencies )

 

select * from dba_dependencies where REFERENCED_NAME='T_ORIG';

no rows selected

 


Can we use dbms_redfinition ? Yes we can


begin
dbms_redefinition.can_redef_table( uname => 'OWNER_1', tname => 'T_ORIG' );
end;
/

PL/SQL procedure successfully completed.

 

Let' s create an empty interim table


create table OWNER_1.T_INTERIM as select * from OWNER_1.T_ORIG where 1=2;

Table created.

 

Let' s modify the data type on the interim table

 

alter table OWNER_1.T_INTERIM modify (C2 VARCHAR2(12));

 

Let's invoke parallellism to enhance performance.
( this is subject to your environment )


alter session force parallel dml parallel 8;

Session altered.

alter session force parallel query parallel 8;

Session altered.

 


Are we ready to cope with a huge amount of archived redo logs ?
You need to consider the amount of archived redo logs generated during the redefinition


alter system set db_recovery_file_dest_size=50G scope=memory;

select * from v$flash_recovery_area_usage;

See also Archivelogs in the flash recovery area

 

Column mapping between T_ORIG and T_INTERIM needs to be done otherwise ...


begin
dbms_redefinition.start_redef_table(uname => 'OWNER_1', ORIG_TABLE => 'T_ORIG', INT_TABLE => 'T_INTERIM');
end;
/
begin
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

begin
dbms_redefinition.abort_redef_table(uname => 'OWNER_1', ORIG_TABLE => 'T_ORIG', INT_TABLE => 'T_INTERIM');
end;

/

PL/SQL procedure successfully completed.

 

We need to use the ROWID method, most probably this is because the field to be refefined belongs to the primary key

 

begin
dbms_redefinition.start_redef_table(uname => 'OWNER_1',

ORIG_TABLE => 'T_ORIG',
INT_TABLE => 'T_INTERIM',
COL_MAPPING => 'C1 C1, to_char(C2) C2, C3 C3, C4 C4, C5 C5, C6 C6, STATUS STATUS');
end;
/
begin
*
ERROR at line 1:
ORA-42008: error occurred while instantiating the redefinition
ORA-12016: materialized view does not include all primary key columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

begin
dbms_redefinition.abort_redef_table(uname => 'OWNER_1', ORIG_TABLE => 'T_ORIG', INT_TABLE => 'T_INTERIM');
end;
/

PL/SQL procedure successfully completed.

 

We replace the default DBMS_REDEFINITION.CONS_USE_PK with DBMS_REDEFINITION.CONS_USE_ROWID

 

begin
dbms_redefinition.start_redef_table( UNAME => 'OWNER_1',
ORIG_TABLE => 'T_ORIG',
INT_TABLE => 'T_INTERIM',
COL_MAPPING => 'C1 C1, to_char(C2) C2, C3 C3, C4 C4, C5 C5, C6 C6, STATUS STATUS',
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID );
end;
/


PL/SQL procedure successfully completed.

 

Interim table can be resynchronized when required
( with this example it was not necessary though )


select count(*) from OWNER_1.T_ORIG;

COUNT(*)
----------------
23748472


select count(*) from OWNER_1.T_INTERIM;

COUNT(*)
----------------
23748472

begin
dbms_redefinition.sync_interim_table( uname => 'OWNER_1', ORIG_TABLE => 'T_ORIG', INT_TABLE => 'T_INTERIM');
end;
/

PL/SQL procedure successfully completed.

 

We copy over the table indexes, but we decide not to copy over the constraints
( since the not null constraints are already there, we 'll create the primary key afterwards )


set serveroutput on


DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( UNAME => 'OWNER_1',
ORIG_TABLE => 'T_ORIG',
INT_TABLE => 'T_INTERIM',
COPY_INDEXES => DBMS_REDEFINITION.cons_orig_params,
COPY_TRIGGERS => FALSE,
COPY_CONSTRAINTS => FALSE,
COPY_PRIVILEGES => TRUE,
IGNORE_ERRORS => TRUE,
COPY_STATISTICS => TRUE,
NUM_ERRORS => error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || error_count);
END;
/

errors := 0

PL/SQL procedure successfully completed.

 

Let' s list the original and interim table constraints and indexes


select table_name,constraint_name,constraint_type from dba_constraints where table_name like 'T_ORIG%' order by 1;

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
T_ORIG                         SYS_C0010351                   C
T_ORIG                         SYS_C0010352                   C
T_ORIG                         SYS_C0010353                   C
T_ORIG                         SYS_C0010350                   C
T_ORIG                         SYS_C0010354                   P
T_INTERIM                      SYS_C0080525                   C
T_INTERIM                      SYS_C0080526                   C
T_INTERIM                      SYS_C0080527                   C
T_INTERIM                      SYS_C0080528                   C

9 rows selected.

select owner,index_name,table_name from dba_indexes where table_name like 'T_ORIG%' order by 1;

OWNER                          INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------ ------------------------------
OWNER_1                        T_ORIG_I3                      T_ORIG
OWNER_1                        T_ORIG_I2                      T_ORIG
OWNER_1                        SYS_C0010354                   T_ORIG
OWNER_1                        I_SNAP$_T_INTERIM              T_INTERIM
OWNER_1                        TMP$$_T_ORIG_I20               T_INTERIM
OWNER_1                        TMP$$_SYS_C00103540            T_INTERIM
OWNER_1                        TMP$$_T_ORIG_I30               T_INTERIM

7 rows selected. 


We switch, ie the original table becomes the interim table and vice versa

 

begin
dbms_redefinition.finish_redef_table( uname => 'OWNER_1', ORIG_TABLE => 'T_ORIG', INT_TABLE => 'T_INTERIM');
end;
/

PL/SQL procedure successfully completed.

 

We add the primary key constraints, note the using index syntax ( really very fast since we address an existing index )


alter table OWNER_1.T_ORIG add constraint T_ORIG_PK primary key ("C2", "C4", "C3", "C1") using index OWNER_1.SYS_C0010354;

Table altered.

select owner,index_name,table_name from dba_indexes where table_name like 'T_ORIG%' order by 3;

OWNER                          INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------ ------------------------------
OWNER_1                        SYS_C0010354                   T_ORIG
OWNER_1                        T_ORIG_I3                      T_ORIG
OWNER_1                        T_ORIG_I2                      T_ORIG

select table_name,constraint_name,constraint_type from dba_constraints where table_name like 'T_ORIG%' order by 1;

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
T_ORIG                         T_ORIG_PK                      P
T_ORIG                         SYS_C0080533                   C
T_ORIG                         SYS_C0080530                   C
T_ORIG                         SYS_C0080531                   C
T_ORIG                         SYS_C0080532                   C

Done