Tuesday, November 24, 2009

Reorginize table online using primary key

From Oracle9i forward, table re-organizations and redefinitions can be performed online.

What can be Redefined ONLINE on a Table?

1) A non-partitioned table can be converted into a partitioned table, and
vice versa
2) The organization of a table can be changed from a heap based to IOTs and vice versa
3) Non-primary key columns can be dropped,although this can be also done with sql
4) New columns can be added to a table,although this can be also done with sql
5) Existing columns can be renamed,although this can be also done with sql
6) Parallel support can be added or removed,although this can be also done with sql
7) Storage parameters can be modified,although this can be also done with sql

Restrictions

The table to be re-organized:

1) Cannot have User-defined data types
2) Cannot have FILE or LONG columns
3) Cannot be clustered
4) Cannot be in the SYS or SYSTEM schema
5) Cannot have materialized view logs and/or materialized views defined on them
6) Must be re-organized within the same schema


Example

Orig table : Table with hash 256 partitions , with pk , unique index , and fk
Target table : Non partitioned table

Connect as user with the privileges mentioned above.


1) Check if ORIG_TABLE can be redifined online
exec DBMS_REDEFINITION.can_redef_table('MYSCHEMA','ORIG_TABLE');
If procedure fails check ora error and apply solution if possible.

2) Create interim table

create table I_ORIG_TABLE as select * from ORIG_TABLE where 1=2;

3) Start online redefinition

exec DBMS_REDEFINITION.START_REDEF_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

4) Add constraints and indexes

ALTER TABLE MYSCHEMA.i_ORIG_TABLE ADD (
CONSTRAINT I_ORIG_TABLE_PK
PRIMARY KEY
(PK_COLUMN));

ALTER TABLE MYSCHEMA.i_ORIG_TABLE ADD (
CONSTRAINT i_ORIG_TABLE_FK
FOREIGN KEY (REF_COL)
REFERENCES MYSCHEMA.OTHER_TABLE (PK_COL)
DEFERRABLE INITIALLY IMMEDIATE);

alter table i_ORIG_TABLE MODIFY CONSTRAINT i_ORIG_TABLE_FK
DISABLE KEEP INDEX;

CREATE UNIQUE INDEX MYSCHEMA.i_ORIG_TABLE_IDX01 ON MYSCHEMA.ORIG_TABLE
(COL1, COL2);

5) Sync interim with original table

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

Do this a lot of times before finish redefinition in order to apply dml during reorganization.

6) Find orig_table grants

select * from DBA_TAB_PRIVS where table_name='ORIG_TABLE';

Give the same grants to the users

e.g

grant select on I_ORIG_TABLE to user;

7) Finish redefinition

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

8) Drop old table and rename constraints and indexes.

drop table I_ORIG_TABLE cascade constraints purge; -- At this point old ORIG_TABLE has been renamed to interim table name.

alter table ORIG_TABLE rename constraint I_ORIG_TABLE_PK to ORIG_TABLE_PK;

alter index I_ORIG_TABLE_PK rename to ORIG_TABLE_PK;

alter table ORIG_TABLE rename constraint i_ORIG_TABLE_FK to ORIG_TABLE_FK;

alter index I_ORIG_TABLE_IDX01 rename to ORIG_TABLE_IDX01;

9) Gather statistics if needed

begin
dbms_stats.gather_table_stats(ownname=>'MYSCHEMA',tabname=>'ORIG_TABLE',cascade=>true,estimate_percent=>5);
end;
/

Now you have an new unpartitioned table with all constraints and indexes and without partitions.

No comments:

Post a Comment