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 ASSELECT * 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_birBEFORE 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 A20SELECT 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 ASSELECT *
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_DESCRIPTIONprocedure andREDEF_TAB_Vview remains valid after redefinition because they reference the table, which is still present under a new name (REDEF_TAB). - Trigger Loss: The
REDEF_TAB_BIRtrigger 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.
