Oracle maintenance snippets

oracle_maintenance.sql

-- backup table
create table TABLE_NAME_BACKUP as select * from TABLE_NAME;

create table user_20130809 as select * from user;
create table role_20130809 as select * from role;
create table function_20130809 as select * from function;

-- disable constraints
-- disable all constraints
begin
for cur in (
  select owner, constraint_name , table_name
  from all_constraints
  where owner = upper('schema')
) loop execute immediate 'alter table '||cur.owner||'."'||cur.table_name||'" modify constraint "'||cur.constraint_name||'" disable ';
end loop;
end;

-- disable indexes
begin
for cur in (
  select owner,index_name
  from all_indexes
  where owner = upper('SCHEMA') and table_name in (
    upper('TABLE_1'),
    upper('TABLE_2'),
    upper('TABLE_3')
    -- ...
  )
) loop execute immediate 'alter index '||cur.owner||'.'||cur.index_name||' unusable';
end loop;
end;
/

-- compute table statistics
analyze table TABLE_NAME compute statistics;

-- rebuild index
alter index IX_NAME rebuild parallel 16 online;

-- rebuild all indexes
begin
for cur in (
  select owner,index_name
  from all_indexes
  where owner = upper('schema') and table_name in (
    upper('TABLE_1'),
    upper('TABLE_2'),
    upper('TABLE_3')
    -- ...
  )
) loop execute immediate 'alter index '||cur.owner||'.'||cur.index_name||' rebuild online';
end loop;
end;
/

-- enable all constraints 
begin
for cur in (
  select
    owner,
    constraint_name,
    table_name,
    case
      when lower(constraint_name) like 'pk%' then 1
      when lower(constraint_name) like 'fk%' then 2
      else 3
    end n
  from all_constraints
  where owner = upper('schema')
  order by
    4 asc
) loop execute immediate 'alter table '||cur.owner||'."'||cur.table_name||'" modify constraint "'||cur.constraint_name||'" enable ';
end loop;
end;

---
--- INDEXES
---

-- get max col length in bytes
select max(lengthb(policy_id)) from crm$policy
;

-- get index size in bytes
select
  segment_name,
  bytes,
  bytes/1024/1024 Size_Mb
from user_segments
where segment_name like 'CRM$%PK'
order by
  1 asc
;