Thursday, March 21, 2019

How to rebuild a table online without down time

I used this method to rebuild a table into a hash partitioned table. It will take an exclusive lock at the very end when it does the table flip. So this works great for tables with not too much transactions at a time or table with transaction burst (just need to do it in that table's downtime) With this method, it seems if some column have default values, they are not copied over at table creation with CTAS. Make sure to add that.
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

create table myschema.mytable_tmp
--These are not standard values. This particular table has huge increase in write activity for short periods of time. Hence the change to a hash partitioned table and the storage parameters.
initrans 40
pctfree 50
partition by hash (id) partitions 32
as
select *
from myschema.mytable
where 1=2;

alter table myschema.table modify
(
insert_date default sysdate
);


EXEC DBMS_REDEFINITION.start_redef_table('myschema', 'mytable', 'mytable_tmp');

-- Copy table dependents
-- Ignore errors because the create table statement above creates null constraints which makes the dependencies' copy fail.
-- Verification after copying shows constraints and index in place
SET SERVEROUTPUT ON
DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname             => 'myschema',
    orig_table        => 'mytable',
    int_table         => 'mytable_tmp',
    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;
/

select *
from dba_constraints
where owner='myschema'
and table_name='mytable';

select *
from dba_constraints
where owner='myschema'
and table_name='mytable_tmp';

select *
from dba_indexes
where owner='myschema'
and table_name='mytable';

select *
from dba_indexes
where owner='myschema'
and table_name='mytable_tmp';

select *
from dba_tables
where owner='myschema'
and table_name='mytable';

select *
from dba_tab_partitions
where table_owner='myschema'
and table_name='mytable';

select *
from dba_ind_partitions
where index_owner='myschema';

EXEC DBMS_REDEFINITION.sync_interim_table('myschema', 'mytable', 'mytable_tmp'); 

-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table('myschema', 'mytable', 'mytable_tmp');

--We create a local non-prefixed index on the status_id column to help with ITL contention when we get bursts activity.

drop index myschema.ix_mytable_si;
create index myschema.ix_mytable_si on myschema.mytable (status_id) local initrans 50 pctfree 40 parallel 8;
alter index myschema.ix_mytable_si noparallel;

No comments :

Post a Comment