Oracle online redefinition

Oracle online redefinition

Before online redefinition of [@ more @] Oracle9i, table data is organized by alter table XXX move [tablespace XX. If the table is very large and IO is not fast, the process of moving may be quite long and cannot be regarded as an HA feature. Therefore, in the online maintenance of HA, moving is basically not used to reorganize large tables, and the migrated tables need to be re-indexed. Dbms_redefinition introduced in 9i. The internal principle of this process is actually using the MV mechanism, similar to creating a prebuilt MV in the original table and then incrementally refresh it to the minimum difference in data. During a very small pause, Oracle completes the incremental synchronization at the last point. After the full synchronization is achieved, the original table and the new table are renamed. Since the name change operation is only a data dictionary, therefore, the final switching time is very short.

-- Create a table test_refed to test the foreign key constraint.

SQL> create table test_refed (id number primary key );

Table created.

SQL> insert into test_refed select rownum from dba_objects where rownum <10001;

10000 rows created.

SQL> commit;

Commit complete.

-- Create the test table for which we are going to modify the table definition. It is not partitioned.

SQL> create table test (id number, fid number );

Table created.

SQL> insert into test select rownum, rownum from dba_objects where rownum <1001;

1000 rows created.

SQL> commit;

Commit complete.

-- Add primary key constraints and foreign key constraints and create indexes on Foreign keys

SQL> alter table test add constraint pk_test primary key (id );

Table altered.

SQL> alter table test add constraint fk_test_refed_id foreign key (fid) references test_refed (id );

Table altered.

SQL> create index idx_test_fid on test (fid );

Index created.

-- Create a trigger based on the test table

SQL> create trigger tr_test
2 before insert on test for each row
3 begin
4 null;
5 end;
6/

Trigger created.

-- Preparation: Call dbms_redefinition.can_redef_table to verify whether boylook. test can be redefined online. Generally, an error is reported if no primary key is available.
SQL> begin
2 dbms_redefinition.can_redef_table ('boylook', 'test ');
3 end;
4/

PL/SQL procedure successfully completed.

-- Create the transition table inter_test, which needs to be redefined. This is a partition table. In the future, all data in the original table will be transferred online to this table. Note that this table has one more field c than the original table test.

SQL> create table inter_test (id number, fid number, c number)
2 partition by range (id)
3 (partition p1 values less than (400 ),
4 partition p2 values less than (800 ),
5 partition p3 values less than (maxvalue ));

Table created.

SQL> alter table inter_test add constraint pk_inter_test primary key (id );

Table altered.

-- Execute online redefinition
Note: The create/alter/drop/lock/select any table permission is required to call this stored procedure.

SQL> exec dbms_redefinition.start_redef_table ('boylook ', 'test', 'inter _ test', 'Id id, fid, 0 C ');

PL/SQL procedure successfully completed.

-- Verify whether the data has been flushed
SQL> select count (*) from inter_test;

COUNT (*)
----------
1000

-- Continue the test operation on the original table
SQL> insert into test select rownum + 1000, rownum + 1000 from dba_objects where rownum <= 24;

24 rows created.

SQL> commit;

Commit complete.

SQL> select count (*) from test;

COUNT (*)
----------
1024

SQL> select count (*) from inter_test;

COUNT (*)
----------
1000

-- Execute table Synchronization
Note: This step is not required, but for large tables, running incremental synchronization in the middle helps reduce the switching time.

SQL> exec dbms_redefinition.sync_interim_table ('boylook ', 'test', 'inter _ test ');

PL/SQL procedure successfully completed.

-- We found that the data was synchronized.
SQL> select count (*) from inter_test;

COUNT (*)
----------
1024

-- Migrate the constraints, indexes, and triggers of the original table test.
Note: It is best to check the authorization here. Check the previous permissions of test and grant the same permissions to the intermediate table inter_test. Otherwise, the permissions of the original table will not be transferred to the new table.
SQL> alter table inter_test add constraint fk_inter_refed_id foreign key (fid) references test_refed (id );

Table altered.

SQL> create index idx_inter_test_fid on inter_test (fid );

Index created.

SQL> create or replace trigger tr_inter_test
2 before insert on inter_test for each row
3 begin
4 null;
5 end;
6/

Trigger created.

-- Execute the redefinition process. Oracle completes the name change from table test to table inter_test. It only contains all constraints. The index or trigger name remains the name of the original table.

SQL> exec dbms_redefinition.finish_redef_table ('boylook ', 'test', 'inter _ test ');

PL/SQL procedure successfully completed.

SQL> desc test;
Name Null? Type
-----------------------------------------------------------------------------
ID NOT NULL NUMBER
FID NUMBER
C NUMBER

SQL> desc inter_test;
Name Null? Type
-----------------------------------------------------------------------------
ID NOT NULL NUMBER
FID NUMBER

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'test ';

TABLE_NAME PARTITION_NAME
------------------------------------------------------------
TEST P1
TEST P2
TEST P3

SQL> select table_name, constraint_name, status from user_constraints where table_name in ('test', 'inter _ test ');

TABLE_NAME CONSTRAINT_NAME STATUS
--------------------------------------------------------------------
INTER_TEST PK_TEST ENABLED
INTER_TEST FK_TEST_REFED_ID DISABLED
TEST PK_INTER_TEST ENABLED
TEST FK_INTER_REFED_ID ENABLED

SQL> select table_name, index_name from user_indexes where table_name in ('test', 'ter _ test ');

TABLE_NAME INDEX_NAME
------------------------------------------------------------
TEST PK_INTER_TEST
TEST IDX_INTER_TEST_FID
INTER_TEST PK_TEST
INTER_TEST IDX_TEST_FID

SQL> select table_name, trigger_name from user_triggers where table_name in ('test', 'inter _ test ');

TABLE_NAME TRIGGER_NAME
------------------------------------------------------------
TEST TR_INTER_TEST
INTER_TEST TR_TEST

SQL> select * from test where rownum <= 10;

ID FID C
------------------------------
1 1 0
2 2 0
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
8 8 0
9 9 0
10 10 0

10 rows selected.

-- Delete the transition table. Close. You can also consider modifying the index. The name of the constraint trigger must be consistent with the original one.

SQL> drop table inter_test cascade constraints purge;

Table dropped.