size’ı büyük bir tabloyu aşağıdaki steplerle kontrollü olarak redefinition table yöntemi ile yeni structure’ına taşıyabilirsiniz;

 

— ORDBA.MY_TEST_TABLE -> maxvalue var -> 2014 sonuna kadar part var (P201412)

— 1) Tablonun exportu alınacak

— 2) Partitionlı yeni tablo structure’ı hazırlandı, create edildi

ORDBA.MY_TEST_TABLE_NEW.sql

— 3) yeni indexler aşağıdakiler

IX_1_NEW
IX_2_NEW
IX_3_NEW

— 4) rowid bazında redef. kontrolü
— Can redef without pk

EXEC Dbms_Redefinition.can_redef_table('ORDBA', 'MY_TEST_TABLE',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
/*
for cancel;

EXEC DBMS_REDEFINITION.abort_redef_table('ORDBA', 'MY_TEST_TABLE', 'MY_TEST_TABLE_NEW');
drop table ORDBA.MY_TEST_TABLE_NEW;
*/

— 5) Start Redefinition

set timi on
set time on
alter session force parallel dml parallel 8;
alter session force parallel query parallel 8;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
EXEC DBMS_REDEFINITION.start_redef_table('ORDBA', 'MY_TEST_TABLE', 'MY_TEST_TABLE_NEW',DBMS_REDEFINITION.CONS_USE_ROWID);

 

/*
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
 (uname=>'ORDBA',
 orig_table=>'MY_TEST_TABLE',
 int_table=>'MY_TEST_TABLE_NEW',
 options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/
*/

— 6) kontrol

SELECT COUNT (*) FROM ORDBA.MLOG$_MY_TEST_TABLE;

— 7) sqlplustan sync_interim_table

set time on
set timi on
alter session force parallel dml parallel 32;
alter session force parallel query parallel 32;
alter session force parallel ddl parallel 32;
EXEC DBMS_REDEFINITION.sync_interim_table('ORDBA', 'MY_TEST_TABLE', 'MY_TEST_TABLE_NEW');

— 8) stats

set timi on
set time on
BEGIN
 SYS.DBMS_STATS.GATHER_TABLE_STATS (
 OwnName => 'ORDBA'
 ,TabName => 'MY_TEST_TABLE_NEW'
 ,Estimate_Percent => 15
 ,Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
 ,Degree => 32
 ,Cascade => FALSE
 ,No_Invalidate => FALSE);
END;
/

— 9) Copy table dependents

alter session force parallel dml parallel 32;
alter session force parallel query parallel 32;
alter session force parallel ddl parallel 32;

SET SERVEROUTPUT ON
DECLARE
 l_num_errors PLS_INTEGER;
BEGIN
 DBMS_REDEFINITION.copy_table_dependents(
 uname => 'ORDBA',
 orig_table => 'MY_TEST_TABLE',
 int_table => 'MY_TEST_TABLE_NEW',
 copy_indexes => 0, --DBMS_REDEFINITION.cons_orig_params, -- Non-Default
 copy_triggers => TRUE, -- Default
 copy_constraints => FALSE, -- Default
 copy_privileges => TRUE, -- Default
 ignore_errors => FALSE, -- Default
 num_errors => l_num_errors); 
 DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/

— l_num_errors=0 olmalı

— 10) constraintler valid olmalı, valid yapıldığından aşağıdakine gerek kalmadı
— validate constraints

SELECT 'ALTER TABLE '
 || t.owner
 || '.'
 || t.table_name
 || ' MODIFY CONSTRAINT '
 || CONSTRAINT_NAME
 || ' VALIDATE;'
 VALIDATE_STATEMENT,
 t.*
 FROM dba_constraints t
 WHERE t.table_name = 'MY_TEST_TABLE_NEW';
 
 
alter session force parallel dml parallel 32;
alter session force parallel query parallel 32;
alter session force parallel ddl parallel 32;

ALTER TABLE ORDBA.MY_TEST_TABLE_NEW MODIFY CONSTRAINT SYS_C00368103 VALIDATE;
ALTER TABLE ORDBA.MY_TEST_TABLE_NEW MODIFY CONSTRAINT SYS_C00368102 VALIDATE;
ALTER TABLE ORDBA.MY_TEST_TABLE_NEW MODIFY CONSTRAINT SYS_C00368101 VALIDATE;
ALTER TABLE ORDBA.MY_TEST_TABLE_NEW MODIFY CONSTRAINT SYS_C00368100 VALIDATE;

— 11) yeni tablomuzu geçici olarak paralleliðe geçiriyoruz

ALTER TABLE ORDBA.MY_TEST_TABLE_NEW PARALLEL 32;

— 12) index statü kontrolümüz

select t.status, t.* from dba_indexes t where t.table_name in ('MY_TEST_TABLE','MY_TEST_TABLE_NEW') order by t.status;

— invalids

 SELECT object_name, object_type, status
 FROM dba_objects
 WHERE owner = 'ORDBA'
 AND object_name IN ('MY_TEST_TABLE',
 'MY_TEST_TABLE_NEW')
ORDER BY status;

— 13) redef errors

SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM DBA_REDEFINITION_ERRORS ;

— 14) gather index stats

set timi on
set time on
EXEC SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName=> 'ORDBA',IndName => 'IX_1_NEW',Estimate_Percent => 1,Degree=> 32,No_Invalidate => FALSE);
EXEC SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName=> 'ORDBA',IndName => 'IX_2_NEW',Estimate_Percent => 1,Degree=> 32,No_Invalidate => FALSE);
EXEC SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName=> 'ORDBA',IndName => 'IX_3_NEW',Estimate_Percent => 1,Degree=> 32,No_Invalidate => FALSE);

— 15) Complete redefinition

alter session force parallel dml parallel 32;
alter session force parallel query parallel 32;
alter session force parallel ddl parallel 32;
EXEC DBMS_REDEFINITION.finish_redef_table('ORDBA', 'MY_TEST_TABLE', 'MY_TEST_TABLE_NEW');

— 16) index kontrolümüz

select * from (select index_name from dba_indexes i where I.TABLE_NAME='MY_TEST_TABLE'
union all
select index_name from dba_indexes i where I.TABLE_NAME='MY_TEST_TABLE_NEW') t 
order by 1;

— invalids

 SELECT object_name, object_type, status
 FROM dba_objects
 WHERE owner = 'ORDBA'
 AND object_name IN ('MY_TEST_TABLE',
 'MY_TEST_TABLE_NEW')
ORDER BY status;

— 17) yeni tablomuzu noparallele geri çeviriyoruz

ALTER TABLE ORDBA.MY_TEST_TABLE_NEW NOPARALLEL;

— 18) remove yeni – interim table

--DROP TABLE ORDBA.MY_TEST_TABLE_NEW;

— invalids

 SELECT object_name, object_type, status
 FROM dba_objects
 WHERE owner = 'ORDBA'
 AND object_name IN ('MY_TEST_TABLE',
 'MY_TEST_TABLE_NEW')
ORDER BY status;

— 19) rename index

select 'alter index ORDBA.' || index_name || ' rename to ORDBA.'|| index_name ||'_old;' from dba_indexes i where I.TABLE_NAME='MY_TEST_TABLE';
/*
alter index ORDBA.IX_1 rename to ORDBA.IX_1_old;
alter index ORDBA.IX_2 rename to ORDBA.IX_2_old;
alter index ORDBA.IX_3 rename to ORDBA.IX_3_old;
*/

select 'alter index ORDBA.' || index_name || ' rename to ORDBA.'|| replace(index_name,'_NEW',';') from dba_indexes i where I.TABLE_NAME='MY_TEST_TABLE_NEW';


/*
alter index ORDBA.IX_1_NEW rename to ORDBA.IX_1;
alter index ORDBA.IX_2_NEW rename to ORDBA.IX_2;
alter index ORDBA.IX_3_NEW rename to ORDBA.IX_3;
*/