Online Table Redefinition

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.

1. CAN_REDEF_TABLE – checks if the table can be redefined
2. START_REDEF_TABLE – initiates the redefinition process (the interim table must be ready and empty already)
3. 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.
4. SYNC_INTERIM_TABLE – keeps the interim and the original table synchronized
5. 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!!
6. 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.

Practical example

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?