Table Online Redefinition :-
- Table fragmentation deteriorate the database performance, and the rate of fragmentation in a table greatly impacts response time. It’s more likely get fragmented with high DML activity on table.These fragmentation effects operation on full table scans, instead of reading blocks with data it also reads empty blocks.
- The table online redefinition method doesn’t need maintenance time.Only the specific table gets locked during the redefinition process
Process has to done for Table Online Redefinition:
- Tables can be reorganized and redefined (evolved) on-line with the DBMS_REDEFINITION package. The original table is left on-line, while a new copy of the table is built.
- Create an interim table based on the original table. The interim table can have a different structure than the original table, and will eventually take the original table’s place in the database. While the table is redefined, DML operations on the original table are captured in a Materialized View Log table (MLOG$_%). These changes are eventually transformed and merged into the interim table. When done, the names of the original and the interim tables are swapped in the data dictionary. At this point all users will be working on the new table and the old table can be dropped.
Steps for table online redefinition:-
Grant privileges to user
The following privileges must be granted to the user:
SQL> grant connect,resource to hari; Grant succeeded. SQL> grant create view to hari; Grant succeeded. SQL> grant execute on dbms_redefinition to hari; Grant succeeded.
Create Table and dependent objects
SQL> CREATE TABLE redef_tab (id NUMBER,description VARCHAR2(50),CONSTRAINT redef_tab_pk PRIMARY KEY (id)); Table created. SQL> CREATE VIEW redef_tab_v AS SELECT * FROM redef_tab; View created. SQL> CREATE SEQUENCE redef_tab_seq; Sequence created. SQL> CREATE OR REPLACE PROCEDURE get_description ( p_id IN redef_tab.id%TYPE, p_description OUT redef_tab.description%TYPE) AS BEGIN SELECT description INTO p_description FROM redef_tab WHERE id = p_id; END; / 2 3 4 5 6 7 8 9 10 Procedure created. SQL> CREATE OR REPLACE TRIGGER redef_tab_bir BEFORE INSERT ON redef_tab FOR EACH ROW WHEN (new.id IS NULL) BEGIN :new.id := redef_tab_seq.NEXTVAL; END; / 2 3 4 5 6 7 8 Trigger created.
Check the status of objects under user HARI
COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects ORDER BY object_name; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- GET_DESCRIPTION PROCEDURE VALID REDEF_TAB TABLE VALID REDEF_TAB_BIR TRIGGER VALID REDEF_TAB_PK INDEX VALID REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB_V VIEW VALID 6 rows selected.
Check the table can be redefined
SQL> EXEC DBMS_REDEFINITION.can_redef_table('HARI', 'REDEF_TAB');
PL/SQL procedure successfully completed.
Create new table
SQL> conn hari/hari; Connected. SQL> CREATE TABLE redef_tab2 AS SELECT * FROM redef_tab; Table created.
Start the redefinition
SQL> sqlplus / as sysdba
SQL> EXEC DBMS_REDEFINITION.start_redef_table('HARI', 'REDEF_TAB', 'REDEF_TAB2');
PL/SQL procedure successfully completed.
Sync new table REDEF_TAB2 with original table REDEF_TAB.During this phase Oracle will copy (and transform) the data from the production table to the interim table.
SQL> EXEC DBMS_REDEFINITION.sync_interim_table('HARI', 'REDEF_TAB', 'REDEF_TAB2');
PL/SQL procedure successfully completed.
Adding new primary key,
SQL> ALTER TABLE hari.redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id)); Table altered.
Complete the redefinition process
During this step Oracle will lock both tables in exclusive mode, swap the names of the two tables in the data dictionary, and enable all foreign key constraints. Remember to drop the original table afterwards. One can also consider renaming the constraints back to their original names
SQL> EXEC DBMS_REDEFINITION.finish_redef_table('HARI', 'REDEF_TAB', 'REDEF_TAB2');
PL/SQL procedure successfully completed.
Remove original table which now has the name of the new table
SQL> DROP TABLE hari.redef_tab2; Table dropped.
Rename constraint back to their original names
SQL> ALTER TABLE hari.redef_tab RENAME CONSTRAINT redef_tab2_pk TO redef_tab_pk; Table altered.
Finally check the status of the schema objects.
SQL> conn hari/hari; Connected. COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects ORDER BY object_name; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- GET_DESCRIPTION PROCEDURE VALID REDEF_TAB TABLE VALID REDEF_TAB_PK INDEX VALID REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB_V VIEW VALID 5 rows selected.
From the above output after online redefinition, the trigger redef_tab_bir is missing.
Online Table Redefinition Including Dependents (COPY_TABLE_DEPENDENTS):-
COPY_TABLE_DEPENDENTS procedure was used to copy grants, triggers, constraints and privileges from the source table to the interim table. It also can optionally copy statistics and materialized view logs. Using this procedure may leave some of the dependent objects in an invalid state at the end of the redefinition process.
First check the status of the schema objects
COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects ORDER BY object_name; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- GET_DESCRIPTION PROCEDURE VALID REDEF_TAB TABLE VALID REDEF_TAB_BIR TRIGGER VALID REDEF_TAB_PK INDEX VALID REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB_V VIEW VALID 6 rows selected.
Start the redefinition
SQL> sqlplus / as sysdba
SQL> EXEC DBMS_REDEFINITION.start_redef_table('HARI', 'REDEF_TAB', 'REDEF_TAB2');
PL/SQL procedure successfully completed.
Sync new table REDEF_TAB2 with original table REDEF_TAB.During this phase Oracle will copy (and transform) the data from the production table to the interim table.
SQL> EXEC DBMS_REDEFINITION.sync_interim_table('HARI', 'REDEF_TAB', 'REDEF_TAB2');
PL/SQL procedure successfully completed.
COPY_TABLE_DEPENDENTS procedure
SQL> SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'HARI',
orig_table => 'REDEF_TAB',
int_table => 'REDEF_TAB2',
copy_indexes => 1, -- Default
copy_triggers => TRUE, -- Default
copy_constraints => TRUE, -- Default
copy_privileges => TRUE, -- Default
ignore_errors => FALSE, -- Default
num_errors => l_num_errors,
copy_statistics => FALSE, -- Default
copy_mvlog => FALSE); -- Default
DBMS_OUTPUT.put_line('num_errors=' || l_num_errors);
END;
/SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
num_errors=0
PL/SQL procedure successfully completed.
Complete redefinition process
SQL> EXEC DBMS_REDEFINITION.finish_redef_table('HARI', 'REDEF_TAB', 'REDEF_TAB2');
PL/SQL procedure successfully completed.
Remove original table which now has the name of the new table,
SQL> DROP TABLE hari.redef_tab2; Table dropped.
Check the status of schema objects
COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects ORDER BY object_name; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- GET_DESCRIPTION PROCEDURE VALID REDEF_TAB TABLE VALID REDEF_TAB2_PK INDEX VALID REDEF_TAB_BIR TRIGGER INVALID REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB_V VIEW INVALID 6 rows selected.
From above the trigger and view are both marked as invalid now., as it was cloned by the COPY_TABLE_DEPENDENTS procedure. It can be recompiled as follows.
SQL> ALTER TRIGGER redef_tab_bir COMPILE; Trigger altered. SQL> ALTER VIEW redef_tab_v COMPILE; View altered.
After recompile,check the schema objects status
COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects ORDER BY object_name; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- GET_DESCRIPTION PROCEDURE VALID REDEF_TAB TABLE VALID REDEF_TAB2_PK INDEX VALID REDEF_TAB_BIR TRIGGER VALID REDEF_TAB_SEQ SEQUENCE VALID REDEF_TAB_V VIEW VALID 6 rows selected.
Gather the statistics for the newly redefined table REDEF_TAB,
begin DBMS_STATS.GATHER_TABLE_STATS (ownname => 'HARI',tabname => 'REDEF_TAB',estimate_percent => 100); end; / 2 3 4 PL/SQL procedure successfully completed.
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
