Oracle : Partitioning using online Redefinition method which requires minimal downtime

spool DEPT_NonPart_to_Part.log
set timing on
set time on
set echo on

conn SCOTT/PW –> Very Important
show user

–> Create DEPT_PART table (with partitions)

EXEC DBMS_STATS.gather_table_stats(user, ‘DEPT’, cascade => TRUE);
SELECT num_rows FROM user_tables WHERE table_name = ‘DEPT’;

EXEC DBMS_STATS.gather_table_stats(user, ‘DEPT_PART’, cascade => TRUE);
SELECT num_rows FROM user_tables WHERE table_name = ‘DEPT_PART’;

EXEC Dbms_Redefinition.can_redef_table(USER, ‘DEPT’);

col partition_name for a30
SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = ‘DEPT_PART’;

Col owner for a30
Col object_name for a30
Col status for a10

select owner,OBJECT_NAME,OBJECT_ID,STATUS from dba_objects where owner=’SCOTT’ and object_name in (‘DEPT’,’DEPT_PART’);

 

BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => ‘DEPT’,
int_table => ‘DEPT_PART’);
END;
/

set linesize 300
col CONTAINER_NAME for a40
col MVIEW_NAME for a40

select mview_name,container_name, build_mode from user_mviews;

select count(*) from MLOG$_DEPT;

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (user, ‘DEPT’, ‘DEPT_PART’,
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

–> Above step will take lot of time based on indexes and FK’s.

Check below query for both tables.

SET LINESIZE 200
SET PAGESIZE 500
COL CONSTRAINT_NAME FOR A50
COL TABLE_NAME FOR A35
COL OWNER FOR A30

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = ‘&ENTER_OWNER’
and constraint_type = ‘R’
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in (‘P’, ‘U’)
and table_name = ‘&ENTER_TABLE’
and owner = ‘&ENTER_OWNER’
)
order by table_name, constraint_name;

— Run below command for Delta changes.

BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => ‘DEPT’,
int_table => ‘DEPT_PART’);
END;

— Gather stats on new partition table

begin
dbms_stats.gather_table_stats(
ownname=> ‘SCOTT’,
tabname=> ‘DEPT’ ,
estimate_percent=> dbms_stats.auto_sample_size,
cascade=> dbms_stats.auto_cascade,
degree=> 4,
no_invalidate=>false,
granularity=> ‘AUTO’,
method_opt=> ‘FOR ALL COLUMNS SIZE AUTO’);
end;
/

— Final swap of tables from non partition to partition.

SELECT partitioned FROM user_tables WHERE table_name = ‘DEPT’;
SELECT partitioned FROM user_tables WHERE table_name = ‘DEPT_PART’;

col partition_name for a30

SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = ‘DEPT_PART’;
SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = ‘DEPT’;

set linesize 300
col table_name for a30
col index_name for a30
col owner for a15
set pagesize 50

select table_name,owner,index_name,status,TO_CHAR(last_analyzed,’DD-MON-YYYYHH24:MI:SS’),tablespace_name
from dba_indexes where table_name in (‘DEPT’,’DEPT_PART’) and owner=’SCOTT’;

Check details for DEPT_PART and DEPT
SET LINESIZE 200
SET PAGESIZE 500
COL CONSTRAINT_NAME FOR A50
COL TABLE_NAME FOR A35
COL OWNER FOR A30

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = ‘&ENTER_OWNER’
and constraint_type = ‘R’
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in (‘P’, ‘U’)
and table_name = ‘&ENTER_TABLE’
and owner = ‘&ENTER_OWNER’
)
order by table_name, constraint_name;

Although No Application downtime is required But It’s

Highly recommended to do this step when Application is down.

BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => ‘DEPT’,
int_table => ‘DEPT_PART’);
END;
/

select count(*) from DEPT ;
select count(*) from DEPT_PART ;

 

SELECT partitioned FROM user_tables WHERE table_name = ‘DEPT’;
SELECT partitioned FROM user_tables WHERE table_name = ‘DEPT_PART’;

col partition_name for a30

SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = ‘DEPT_PART’;
SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = ‘DEPT’;

Col owner for a30
Col object_name for a30
Col status for a10

select owner,OBJECT_NAME,OBJECT_ID,STATUS from dba_objects where owner=’SCOTT’ and object_name in (‘DEPT’,’DEPT_PART’);

set linesize 300
col table_name for a30
col index_name for a30
col owner for a15
set pagesize 50

select table_name,owner,index_name,status,TO_CHAR(last_analyzed,’DD-MON-YYYYHH24:MI:SS’),tablespace_name
from dba_indexes where table_name in (‘DEPT’,’DEPT_PART’) and owner=’SCOTT’;

17:06:00 SQL> drop table SCOTT.DEPT_part cascade constraints;

Table dropped.

spool off

Note: If you have any questions regarding implementation please comment below.