Do you need to change the definition of a table ( add partitions, changes partitions, …. ) but you don’t have the luxury of affording downtime? Don’t you worry about a thing! Oracle got you covered here with online table redefinition.
What is it?
Whenever you need to do any change to a table online, you can use dbms_redefinition package. Not only it can migrate the table with a new definition, but you can also make sure that all the dependencies (i.e. dependent procedures, sequences, triggers, grants, … ) are met.
This dbms_redefinition package has several methods you can use. I will describe those most commonly used for the rest are seldom seen.
- CAN_REDEF_TABLE – checks if the table can be redefined
- START_REDEF_TABLE – initiates the redefinition process (the interim table must be ready and empty already)
- ABORT_REDEF_TABLE – cleans up all errors that occurred during the redefinition. It can also be used at any time after START_REDEF_TABLE and before FINISH_REDEF_TABLE to abort the redefinition.
- SYNC_INTERIM_TABLE – keeps the interim and the original table synchronized
- COPY_TABLE_DEPENDENTS – copies the dependent objects from the original table to the interim table (grants, triggers, constraints, and privs)
- if you won’t run this you will lose the dependent objects!!
- FINISH_REDEF_TABLE – finishes the redefinition process.
How does it work?
First and foremost, you must have a table you want to redefine and an interim (a.k.a. new/post-redefined/target) table where you will sync the data to. After that, you have to check if the table can be redefined (usually all can except SYS and SYSTEM tables). Once you got that confirmed, you will start the redefinition and start syncing data. You can also copy dependent objects and in case you experience any error you can abort the redefinition. If everything is ok, you just finish the redefinition and you are good to go 🙂 Let’s elaborate a bit more on an example below.
Let’s set up the test objects (table, view, procedure, trigger, sequence) first:
CREATE TABLE employee ( id_employee NUMBER, full_name VARCHAR2(255) NOT NULL, date_of_birth DATE, title VARCHAR2(50), CONSTRAINT employee_pk PRIMARY KEY (id_employee) ); CREATE SEQUENCE employee_seq START WITH 1 INCREMENT BY 1 CACHE 100 NOCYCLE; CREATE VIEW v_employee AS SELECT id_employee , full_name FROM employee; CREATE PROCEDURE get_employee_title ( p_id IN employee.id_employee%TYPE, p_title OUT employee.title%TYPE ) BEGIN SELECT title INTO p_title FROM employee WHERE 1=1 AND id_employee = p_id; END; / CREATE TRIGGER employee_trg BEFORE INSERT ON employee FOR EACH ROW WHEN (new.id_employee IS NULL) BEGIN :new.id_employee = employee_seq.NEXTVAL; :new.title = upper(:new.title); END; /
Now, since we have those objects in place, we only need to create the interim (=target) table.
CREATE TABLE employee_redef AS SELECT * FROM employee WHERE 1=0 -- empty structure only
Let’s start our redefinition now 🙂
-- first and foremost, check if the table can be redefined (if not, you will get an error) BEGIN dbms_redefinition.can_redef_table('ORACLE_WORLD','EMPLOYEE'); -- (SCHEMA_NAME, TABLE_NAME) END; -- it's advisable to enable parallel run in case you are about to process a huge amount of data ALTER SESSION FORCE PARALLEL DML PARALLEL 4; ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4; -- if everything is ok, let's initiate the redefinition BEGIN dbms_redefinition.start_redef_table('ORACLE_WORLD', 'EMPLOYEE', 'EMPLOYEE_REDEF'); -- (SCHEMA, ORIGINAL_TABLE, TARGET_TABLE) END; -- sync data BEGIN dbms_redefinition.sync_interim_table('ORACLE_WORLD', 'EMPLOYEE', 'EMPLOYEE_REDEF'); -- (SCHEMA, ORIGINAL_TABLE, TARGET_TABLE) END; -- add the PK ALTER TABLE employee_redef ADD (CONSTRAINT employee_redef_pk PRIMARY KEY (id_employee)); -- copy dependent objects DECLARE errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.copy_table_dependents( uname => 'ORACLE_WORD', orig_table => 'EMPLOYEE', int_table => 'EMPLOYEE_REDEF', copy_indexes => 1, copy_triggers => TRUE, copy_constraints => TRUE, copy_privileges => TRUE, ignore_errors => FALSE, num_errors => errors, copy_statistics => TRUE, copy_mvlog => FALSE); dbms_output.put_line('Errors encountered: ' || errors); END; / -- in case some errors occurred, it's advisable to check this table out SELECT object_name , base_table_name , ddl_txt FROM dba_redefinition_errors; -- sync data again (just in case there were transactions) BEGIN dbms_redefinition.sync_interim_table('ORACLE_WORLD', 'EMPLOYEE', 'EMPLOYEE_REDEF'); -- (SCHEMA, ORIGINAL_TABLE, TARGET_TABLE) END; -- finish the redefinition (this operation does swap of the objects in the data dictionary; so the target table becomes obsolete and all changes are applied back to the original table) BEGIN dbms_redefinition.finish_redef_table('ORACLE_WORLD', 'EMPLOYEE', 'EMPLOYEE_REDEF'); -- (SCHEMA, ORIGINAL_TABLE, TARGET_TABLE) END; -- since we already finished the redefinition - we don't need the interim table anymore DROP TABLE employee_redef CASCADE CONSTRAINTS PURGE; -- the very last step is to check if all objects are ok SELECT object_name , object_type , status FROM dba_objects WHERE 1=1 AND object_name IN ('EMPLOYEE','EMPLOYEE_SEQ','V_EMPLOYEE','GET_EMPLOYEE_TITLE','EMPLOYEE_TRG'); -- in case some objects are INVALID (most likely the trigger and view) ALTER TRIGGER employee_trg COMPILE; ALTER VIEW v_employee COMPILE; -- lastly, don't forget to disable the parallelism ALTER SESSION FORCE PARALLEL DML PARALLEL 1; ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1;
And voila – this is how it’s done; you did it 🙂 Simple, ain’t it?