Oracle online redefinition

Oracle online redefinition oracle 9i provides an online redefinition function, which can be used to redefine a table structure or storage online without affecting the use of the current application, it is a very useful feature of oracle high availability. There are two definition methods for online redefinition: one is based on the primary key and the other is based on the ORWID. The ROWID method cannot be used to index the Organizational table. After redefinition, a hidden column M_ROW $ is added. You need to manually delete the column after reconstruction. Generally, the primary key is used by default. Online redefinition Step 1. Call dbms_redefinition.can_redef_table () to determine whether the current table allows redefinition. 2. Call dbms_redefinition.start_redef_table () to start online redefinition. The PROCEDURE is defined as follows: PROCEDURE start_redef_table (uname IN VARCHAR2, ---- username orig_table IN VARCHAR2, ---- source table name int_table IN VARCHAR2, ---- intermediate table name col_mapping IN VARCHAR2: = NULL, --- ing between source table and intermediate table column, map; options_flag IN BINARY_INTEGER: = 1, --- redefinition method 1 indicates primary key redefinition 2 indicates ROWID redefinition orderby_cols IN VARCHAR2: = NULL, --- for a partitioned table, the partition column name is part_name IN VARCHAR2: = NULL); --- for a partitioned table, the partition needs to be redefined. The last two parameters are not used, because this is the 3 and Dbms_Redefinition.sync_interim_table () used to convert from a common table to a partitioned table () the process is used to synchronize the data that performs dml operations on the table during the online redefinition process. 4. The Dbms_Redefinition.finish_redef_table () process ends online redefinition. This process also performs a synchronization operation, therefore, the third step can be omitted. In this process, the original table is locked for a short period of time in the exclusive mode. The specific time is related to the table data volume. After the process is completed, the intermediate table will become the original definition table, and the original table will program the intermediate table. Example: 1. Redefine SQL> create table ou_1 as select * from dba_objects based on the primary key; Table created SQL> alter table ou_1 add primary key (object_id ); table altered SQL> create table ou_2 as select * from dba_objects where 1 = 2; Table created SQL> alter table ou_2 add primary key (object_id ); table altered SQL> Select 'ou _ 1' table_name, count (*) from ou_1 2 union all 3 Select 'ou _ 2' table_name, count (*) from ou_2; TABLE _ Name count (*) ---------- ou_1 51405ou_2 0 SQL> exec Dbms_Redefinition.can_redef_table ('Scott ', 'ou _ 1', dbms_redefinition.cons_use_pk ); PL/SQL procedure successfully completed SQL> exec Dbms_Redefinition.start_redef_table ('Scott ', 'ou _ 1', 'ou _ 2'); PL/SQL procedure successfully completed SQL> select owner, mview_name from User_Mviews; OWNER MVIEW_NAME ------------------------------------------ ------------------ SCOTT OU_2 SQL> select log_owner, master, log_table from user_mview_logs; LOG_OWNER MASTER LOG_TABLE comment Comment comment SCOTT OU_1 MLOG $ _ OU_1 SQL> exec partition ('Scott ', 'ou _ 1', 'ou _ 2'); PL/SQL procedure successfully completed SQL> select owner, mview_name from User_Mviews; OWNER MVI EW_NAME -------------------------------- -------------------------- SQL> Select 'ou _ 1 'table_name, count (*) from ou_1 2 union all 3 Select 'ou _ 2' table_name, count (*) from ou_2; TABLE_NAME COUNT (*) ---------- ou_1 51405ou_2 51405 creates a materialized view after the start is redefined online. The materialized view has the same name as the intermediate table but two different objects. After the redefinition is completed, the materialized view will be deleted. 2. ROWID redefinition SQL> create table ou_1 as select * from dba_objects; Table created SQL> create table ou_2 as select * from dba_objects where 1 = 2; table created SQL> Select 'ou _ 1' table_name, count (*) from ou_1 2 union all 3 Select 'ou _ 2' table_name, count (*) from ou_2; TABLE_NAME COUNT (*) ---------- ou_1 51409ou_2 0 SQL> exec dbms_redefinition.can_redef_table ('Scott ', 'ou _ 1', DBMS_REDEFINITI ON. CONS_USE_PK); begin dbms_redefinition.can_redef_table ('Scott ', 'ou _ 1', DBMS_REDEFINITION.CONS_USE_PK); end; ORA-12089: tables without primary keys cannot be redefined online "SCOTT ". "OU_1" ORA-06512: In "SYS. DBMS_REDEFINITION ", line 137ORA-06512: In" SYS. DBMS_REDEFINITION ", line 1478ORA-06512: In line 2 SQL> exec dbms_redefinition.can_redef_table ('Scott ', 'ou _ 1', comment); PL/SQL procedure successfully completed SQL> Exec dbms_redefinition.start_redef_table ('Scott ', 'ou _ 1', 'ou _ 2'); begin dbms_redefinition.start_redef_table ('Scott', 'ou _ 1', 'ou _ 2 '); end; ORA-12089: The table "SCOTT" without a primary key cannot be redefined online ". "OU_1" ORA-06512: In "SYS. DBMS_REDEFINITION ", line 50ORA-06512: In" SYS. DBMS_REDEFINITION ", line 1343ORA-06512: In line 2 SQL> exec dbms_redefinition.start_redef_table ('Scott ', 'ou _ 1', 'ou _ 2','', 2 ); PL/SQL procedure successfully co Mpleted SQL> insert into ou_1 select * from dba_objects where rownum = 1; 1 row inserted SQL> commit 2; Commit complete SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('Scott ', 'ou _ 1 ', 'ou _ 2'); PL/SQL procedure successfully completed, when dbms_redefinition.start_redef_table is called, you also need to specify the parameter options_flag = 2. Otherwise, an error is returned. After online redefinition, view the table structure as follows: SQL> select col #, name, type # from SYS. COL $ where obj # = (select object_id from dba_objects where object_name = 'ou _ 1' and owner = 'Scott '); COL # name type # ---------- comment ---------- 1 OWNER 1 2 OBJECT_NAME 1 3 SUBOBJECT_NAME 1 4 OBJECT_ID 2 5 DATA_OBJECT_ID 2 6 OBJECT_TYPE 1 7 CREATED 12 8 keys 12 9 TIMESTAMP 1 10 STATUS 1 11 TEMPORARY 1 12 GENERATED 1 13 SECONDARY 1 0 sys_c00014_1301_15: 44: 43 $1 14 rows selected at this time, we can find that the original table has a column sys_c00014_1301_15: 44: 43 $, the hidden columns are not added to the intermediate table. Note that hidden Columns cannot be found when you view table fields through desc tablename. How to delete a Hidden Column SQL> alter table ou_1 set unused ("sys_c00014_130000015: 44: 43 $"); Table altered SQL> alter table ou_1 drop unused columns; Table altered.