Online table redefination

Table and Dependent Objects Creation:

Drop Existing Objects:

Before creating new objects, ensure no conflicts with existing ones:

sql

DROP PROCEDURE get_description;
DROP VIEW redef_tab_v;
DROP SEQUENCE redef_tab_seq;
DROP TABLE redef_tab PURGE;

Create Table:

Define the table redef_tab with a primary key:

sql

CREATE TABLE redef_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT redef_tab_pk PRIMARY KEY (id)
);

Create View:

Create a view that selects all columns from redef_tab:

sql

CREATE VIEW redef_tab_v AS
SELECT * FROM redef_tab;

Create Sequence:

Define a sequence to generate unique IDs:

sql

CREATE SEQUENCE redef_tab_seq;

Create Procedure:

Create a procedure to retrieve the description based on an ID:

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;
/

Create Trigger:

Create a trigger to automatically assign a new ID from the sequence before inserting it into redef_tab:

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;
/

Verify Object Creation:

Check the status of the created objects:

sql

COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

Expected Output:

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

2. Online Table Redefinition:

Check Table Redefinition Feasibility:

Verify if the table redef_tab can be redefined:

sql

EXEC DBMS_REDEFINITION.can_redef_table('ATOORPU', 'REDEF_TAB');

Create Interim Table:

Create a new table redef_tab2 with the same structure as redef_tab, but initially empty:

sql

CREATE TABLE redef_tab2 AS
SELECT *
FROM   redef_tab WHERE 1=2;

Start Redefinition:

Begin the online redefinition process:

sql

EXEC DBMS_REDEFINITION.start_redef_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

Synchronize Interim Table (Optional):

Synchronize redef_tab2 with any interim data:

sql

EXEC DBMS_REDEFINITION.sync_interim_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

Add New Primary Key Constraint:

Add the primary key constraint to redef_tab2:

sql

ALTER TABLE redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id));

Complete Redefinition:

Finish the redefinition process:

sql

EXEC DBMS_REDEFINITION.finish_redef_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

Drop Original Table:

Drop the original table, which has been renamed to REDEF_TAB2:

sql

DROP TABLE redef_tab2;

3. Verify the Updated Schema:

Check the status of the updated objects:

sql

COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

Expected Output:

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB2_PK        INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID

Notes:

• Procedure and View Validity: The GET_DESCRIPTION procedure and REDEF_TAB_V view remains valid after redefinition because they reference the table, which is still present under a new name (REDEF_TAB).
• Trigger Loss: The REDEF_TAB_BIR trigger is dropped because it was associated with the original table, which was renamed and subsequently dropped.

This comprehensive setup and redefinition process helps manage schema changes with minimal downtime, ensuring that your database schema evolves smoothly while maintaining data integrity.