DBMS REDEFINITION package for tables online movement data in Oracle
Following are the Steps involved for On-line fragmentation removal or movement:
Note: Before start take the full database backup.
Steps to move online and remove fragmentation from table in Oracle
SQL> CREATE USER sam identified by sam; User created. SQL> CREATE TABLESPACE sam_tbs DATAFILE '/data2/SAMDB/sam_tbs01.dbf' SIZE 100M AUTOEXTEND ON; 2 Tablespace created. SQL> ALTER USER sam DEFAULT TABLESPACE sam_tbs; User altered. SQL> GRANT UNLIMITED TABLESPACE TO sam; Grant succeeded. SQL> grant connect, resource to sam; Grant succeeded.
SQL> CREATE TABLE SAM.EMP (
EMP_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(100),
DEPARTMENT_ID NUMBER,
SALARY NUMBER,
JOIN_DATE DATE
) TABLESPACE USERS; 2 3 4 5 6 7
Table created.
SQL> BEGIN
FOR i IN 1..5000000 LOOP
INSERT INTO SAM.EMP (
EMP_ID,
EMP_NAME,
DEPARTMENT_ID,
SALARY,
JOIN_DATE
) VALUES (
i,
'Employee_' || i,
MOD(i, 10),
50000 + MOD(i, 5000),
SYSDATE - MOD(i, 1000)
);
-- Commit in batches to avoid undo segment issues
IF MOD(i, 10000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
PL/SQL procedure successfully completed.
SQL> SQL> SQL>
SQL>
SQL>
SQL> CREATE INDEX SAM.EMP_IDX_DEPT ON SAM.EMP (DEPARTMENT_ID);
Index created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER SAM.TR_EMP_SALARY_CHECK
BEFORE INSERT OR UPDATE ON SAM.EMP
FOR EACH ROW
BEGIN
IF :NEW.SALARY < 30000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be less than 30,000');
END IF;
END;
/ 2 3 4 5 6 7 8 9
Trigger created.
SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE SAM.UPDATE_SALARY (
p_emp_id IN NUMBER,
p_increment IN NUMBER
) IS
BEGIN
UPDATE SAM.EMP
SET SALARY = SALARY + p_increment
WHERE EMP_ID = p_emp_id;
COMMIT;
END;
/ 2 3 4 5 6 7 8 9 10 11
Procedure created.
SQL> CREATE OR REPLACE VIEW SAM.EMP_VIEW AS
SELECT EMP_ID, EMP_NAME, DEPARTMENT_ID, SALARY
FROM SAM.EMP
WHERE SALARY > 40000; 2 3 4
View created.
SQL> CREATE OR REPLACE PACKAGE SAM.EMP_PKG AS
PROCEDURE UPDATE_DEPT(p_emp_id IN NUMBER, p_new_dept IN NUMBER);
END EMP_PKG;
/
CREATE OR REPLACE PACKAGE BODY SAM.EMP_PKG AS
PROCEDURE UPDATE_DEPT(p_emp_id IN NUMBER, p_new_dept IN NUMBER) IS
BEGIN
UPDATE SAM.EMP SET DEPARTMENT_ID = p_new_dept WHERE EMP_ID = p_emp_id;
COMMIT;
END;
END EMP_PKG;
/ 2 3 4
Package created.
SQL> SQL> 2 3 4 5 6 7 8
Package body created.
SQL>
SQL>
SQL> CREATE SYNONYM EMP_SYNONYM FOR SAM.EMP;
Synonym created.
SQL>
SQL>
SQL>
SQL> DECLARE
max_emp_id NUMBER;
BEGIN
SELECT NVL(MAX(EMP_ID), 0) INTO max_emp_id FROM SAM.EMP;
FOR i IN max_emp_id + 1..max_emp_id + 5000000 LOOP
INSERT INTO SAM.EMP (
EMP_ID,
EMP_NAME,
DEPARTMENT_ID,
SALARY,
JOIN_DATE
) VALUES (
i,
'Employee_' || i,
MOD(i, 10),
50000 + MOD(i, 5000),
SYSDATE - MOD(i, 1000)
);
-- Commit in batches
IF MOD(i, 10000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
27 /
PL/SQL procedure successfully completed.Step 1: Check the table count of record for verification at end.
Select count(*) from table_name;
SQL> select count(*) from SAM.EMP;
COUNT(*)
----------
10000000
OBJECT_NAME OWNER OBJECT_TYPE CREATED
-------------------- ---------- -------------------- -------------------------
SYS_C007759 SAM INDEX 30-NOV-24
EMP_IDX_DEPT SAM INDEX 30-NOV-24
EMP_PKG SAM PACKAGE 30-NOV-24
EMP_PKG SAM PACKAGE BODY 30-NOV-24
UPDATE_SALARY SAM PROCEDURE 30-NOV-24
EMP SAM TABLE 30-NOV-24
TR_EMP_SALARY_CHECK SAM TRIGGER 30-NOV-24
EMP_VIEW SAM VIEW 30-NOV-24
Step 2: Get the structure/DDL statement of the table with scripts:
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SAM') FROM DUAL;
CREATE TABLE "SAM"."EMP"
( "EMP_ID" NUMBER,
"EMP_NAME" VARCHAR2(100),
"DEPARTMENT_ID" NUMBER,
"SALARY" NUMBER,
"JOIN_DATE" DATE,
PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"Step 3: Check the invalid objects of the table.
--
select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;
SQL> select owner , object_name , object_type , status from dba_objects where owner='SAM';
SAM TR_EMP_SALARY_CHECK TRIGGER VALID
SAM UPDATE_SALARY PROCEDURE VALID
SAM EMP TABLE VALID
SAM SYS_C007759 INDEX VALID
SAM EMP_VIEW VIEW VALID
SAM EMP_PKG PACKAGE VALID
SAM EMP_IDX_DEPT INDEX VALID
SAM EMP_PKG PACKAGE BODY VALID
Step 4: Check the v$access view to monitor the access of the table.
Select * from v$access
what is this v$access -- what is the use of this
SQL> set lines 200
set pages 200
col owner for a15
col object_name for a20
col OBJECT for a35
col TYPE for a20
Select * from v$access where owner='SAM';SQL> SQL> SQL> SQL> SQL> SQL>
SID OWNER OBJECT TYPE CON_ID
---------- --------------- ----------------------------------- -------------------- ----------
88 SAM DBMS_APPLICATION_INFO CURSOR 0
115 SAM EMP TABLE 0
1 SAM LARGE_TABLE CURSOR 0
88 SAM V$ACCESS CURSOR 0
Step 5: Start the redefinition of the package.
exec dbms_redefinition.can_redef_table('schema_name','Table_name',dbms_redefinition.cons_use_rowid);
Purpose:
This command is used to check if a table is eligible for online redefinition using Oracle's DBMS_REDEFINITION package, which allows you to redefine a table structure or data organization while it is in use, minimizing downtime.
What This Command Does:
Checks whether the table meets the requirements for online redefinition.
Ensures that the table is not involved in complex scenarios that could prevent redefinition, such as:
Tables without ROWID support (e.g., some external or temporary tables).
Tables that are part of a cluster.
Tables with long data types.
Tables involved in materialized view logs.
It does not make any changes to the table; it only verifies eligibility.
exec dbms_redefinition.can_redef_table('SAM','EMP',dbms_redefinition.cons_use_rowid);
SQL>
SQL> exec dbms_redefinition.can_redef_table('SAM','EMP',dbms_redefinition.cons_use_rowid);
PL/SQL procedure successfully completed.
Why You Need to Run It:
Before performing online table redefinition, Oracle requires you to confirm that the table is eligible for redefinition. Running this command ensures:
Safety: Avoids issues during the redefinition process by catching potential problems upfront.
Step 6: Created the second table structure only.
create table schema_name.table_name_temp tablespace APPS_TS_ARCHIVE as select * from schema_name.table_name where 1=2;
CREATE TABLESPACE SAM_TS_ARCHIVE DATAFILE '/data2/SAMDB/SAM_TS_ARCHIVE.dbf' SIZE 100M AUTOEXTEND ON;
SQL> CREATE TABLESPACE SAM_TS_ARCHIVE DATAFILE '/data2/SAMDB/SAM_TS_ARCHIVE.dbf' SIZE 100M AUTOEXTEND ON;
Tablespace created.
SQL>
SQL>
SQL> ALTER USER SAM QUOTA UNLIMITED ON SAM_TS_ARCHIVE;
User altered.
create table SAM.EMP_ARCHIVE tablespace SAM_TS_ARCHIVE as select * from SAM.EMP where 1=2;
18:05:58 SQL>
18:05:58 SQL> create table SAM.EMP_ARCHIVE tablespace SAM_TS_ARCHIVE as select * from SAM.EMP where 1=2;
Table created.
Elapsed: 00:00:00.04
18:06:00 SQL>
18:06:00 SQL>
This command is attempting to create a new table named EMP in the schema SAM within the SAM_TS_ARCHIVE tablespace.
However, the table name EMP is the same as the source table. This will lead to an error
because Oracle does not allow two tables with the same name in the same schema.Step 7: Start the process of redefinition.
exec dbms_redefinition.start_redef_table('schema_name','table_name','table_name_temp',NULL,dbms_redefinition.cons_use_rowid);
exec dbms_redefinition.start_redef_table('SAM','EMP','EMP_ARCHIVE',NULL,dbms_redefinition.cons_use_rowid);
18:06:00 SQL>
18:06:01 SQL>
18:06:01 SQL> exec dbms_redefinition.start_redef_table('SAM','EMP','EMP_ARCHIVE',NULL,dbms_redefinition.cons_use_rowid);
PL/SQL procedure successfully completed.
Elapsed: 00:00:39.56
What This Command Does
Initiates Online Redefinition:
This begins the online redefinition of the EMP table (owned by schema SAM) using the interim table EMP_ARCHIVE.
Tracks Changes Using ROWIDs:
The DBMS_REDEFINITION.CONS_USE_ROWID argument specifies that changes to the EMP table (INSERTs, UPDATEs, DELETEs) will be tracked using the unique ROWID of each row.
This is useful when the EMP table does not have a primary key or when ROWID-based tracking is preferred.
Copies Data from Original to Interim Table:
Oracle begins copying data from the EMP table into the EMP_ARCHIVE table in the background.
The EMP table remains available for all operations (INSERT, UPDATE, DELETE, and SELECT) during this time.
What Happens in the Background
Data Synchronization Starts:
Oracle begins copying data from EMP to EMP_ARCHIVE while tracking any changes made to the EMP table.
Change Tracking:
Any DML (INSERT, UPDATE, DELETE) operations on the EMP table are logged and later synchronized with the EMP_ARCHIVE table during the process.
The EMP Table Remains Available:
The EMP table can still be queried and modified during the redefinition process.
SQL> select count(*) from EMP;
COUNT(*)
----------
10000000
SQL>
SQL> select count(*) from EMP_ARCHIVE;
COUNT(*)
----------
10000000Step 8: Start copy process of dependence.
SQL> col TRIGGER_NAME for a25
SQL> col TABLE_NAME for a25
SQL> select OWNER,TRIGGER_NAME,TABLE_NAME from dba_triggers where TABLE_NAME='EMP';
OWNER TRIGGER_NAME TABLE_NAME
--------------- ------------------------- -------------------------
SAM TR_EMP_SALARY_CHECK EMP
SQL> select OWNER,TRIGGER_NAME,TABLE_NAME from dba_triggers where TABLE_NAME='EMP_ARCHIVE';
no rows selected
-->
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'schema_name',
orig_table => 'table_name',
int_table => 'table_name_temp',
copy_indexes => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
copy_triggers => TRUE, -- Default
copy_constraints => TRUE, -- Default
copy_privileges => TRUE, -- Default
ignore_errors => TRUE, -- Default
num_errors => l_num_errors);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'SAM',
orig_table => 'EMP',
int_table => 'EMP_ARCHIVE',
copy_indexes => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
copy_triggers => TRUE, -- Default
copy_constraints => TRUE, -- Default
copy_privileges => TRUE, -- Default
ignore_errors => TRUE, -- Default
num_errors => l_num_errors);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
What Happens During Execution
Copying the Table's Dependents:
Oracle will copy the dependent objects (indexes, triggers, constraints, and privileges) from the original EMP table to the interim EMP_ARCHIVE table.
This ensures that the EMP_ARCHIVE table has the same structure and functionality as the original EMP table before proceeding with the redefinition.
Ignoring Errors:
The ignore_errors => TRUE parameter means that any issues that occur during the copying of objects will not stop the operation. Instead, the errors will be logged, and the process will continue. The number of errors will be captured in the l_num_errors variable.
Output:
After the copying is completed, the number of errors (l_num_errors) will be printed using DBMS_OUTPUT.put_line. This gives you an indication of whether any issues occurred during the copying process.
After Execution
The EMP_ARCHIVE table will have the same indexes, triggers, constraints, and privileges as the EMP table, so that the interim table is ready for the redefinition process.
If there were errors during the process, the value of l_num_errors will be displayed to let you know how many issues occurred. You can then investigate those errors and resolve them if necessary.
SQL> select OWNER,OBJECT_NAME, status from dba_objects where owner='SAM' and object_type='TABLE';
OWNER OBJECT_NAME STATUS
--------------- -------------------- -------
SAM EMP VALID
SAM EMP_ARCHIVE VALID
SAM MLOG$_EMP VALID
Step 9: Start sync process.
exec dbms_redefinition.sync_interim_table('schema_name','table_name','EMP_ARCHIVE');
exec dbms_redefinition.sync_interim_table('SAM','EMP','EMP_ARCHIVE');
21:05:26 SQL>
21:05:26 SQL> exec dbms_redefinition.sync_interim_table('SAM','EMP','EMP_ARCHIVE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
What This Command Does
This command is used to synchronize the data between the original table (EMP) and the interim table (EMP_ARCHIVE) during the online redefinition process. It ensures that any changes (such as INSERT, UPDATE, and DELETE operations) that occurred on the EMP table while the data was being copied to EMP_ARCHIVE are reflected in the interim table.Step 10: Start finish process.
exec dbms_redefinition.finish_redef_table('schema_name','table_name','table_name_temp');
After you have synchronized the interim table with the original table, you can proceed to complete the redefinition with:
sql
Copy code
EXEC DBMS_REDEFINITION.finish_redef_table('SAM', 'EMP', 'EMP_ARCHIVE');
This will complete the redefinition and switch the role of the EMP_ARCHIVE table to the new EMP table,
effectively replacing the original table with the redefined one.
exec dbms_redefinition.finish_redef_table('SAM','EMP','EMP_ARCHIVE');
21:08:27 SQL>
21:08:27 SQL> exec dbms_redefinition.finish_redef_table('SAM','EMP','EMP_ARCHIVE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.88
OWNER TABLE_NAME Size(M) Actual(M) LAST ANAL Diff(M) % Frag
---------- -------------------- ---------- ---------- ------------------ ---------- ----------
SAM EMP 688 553.131104 30-NOV-24 134 19.6030373
Step 11: drop the temp table created.
drop table schema_name.table_name_temp purge;
drop table SAM.EMP_ARCHIVE purge;Step 12: Verify the count of table.
select count(*) from SAM.EMP;
Step 13: Check the invalid objects.
select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;
SQL> col owner for a15
SQL> col OBJECT_NAME for a20
SQL> col status for a15
SQL> /
OWNER OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ----------------------- ---------------
SAM TR_EMP_SALARY_CHECK TRIGGER INVALID
SAM EMP_VIEW VIEW INVALID
SQL>
SQL> ALTER TRIGGER SAM.TR_EMP_SALARY_CHECK COMPILE;
Trigger altered.
SQL> select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ----------------------- ---------------
SAM EMP_VIEW VIEW INVALID
SQL>
SQL> ALTER VIEW SAM.EMP_VIEW COMPILE;
View altered.
SQL> select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;
no rows selected
