CODEWITHSUNDEEP
oracle11gpartitioning
user1947949
user1947949

Reputation: 43

Online Partitioning

I have table with huge data and which is in production at client side. I have to do online partitionig of it means I can halt the production and the table should be partitioned. I have got the script to do it. But I dont know how to do it online. Is their any way by which I can achieve this. Please suggest..

Upvotes: 1

Views: 172

Answers (1)

Orangecrush
Orangecrush

Reputation: 1990

Follow the below steps,

  1. Create temporary table in the same structure as the main table (for example TABLE_P for TABLE) and partition this temporary table on the required column. Don't add primary keys and other indexes of the main table to this temporary table.

    create table TABLE_P
    (
      COL1        VARCHAR2(35 CHAR) not null,
      COL2        VARCHAR2(35 CHAR) not null,
    )
    --add partition (Example taken here is Range partitioning)  from here
     PARTITION BY RANGE (COL1) (
     PARTITION p0 VALUES LESS THAN (5),
     PARTITION p1 VALUES LESS THAN (10),
     PARTITION p2 VALUES LESS THAN (15),
     PARTITION p3 VALUES LESS THAN (MAXVALUE)
    --add partition till here

  2. Create an index to this table with the required column. 

    create index IX01_TABLE on TABLE_P (COL1) local;

  3. Run the below scripts for redifinition. Replace with the actual schema name.

    begin
dbms_redefinition.can_redef_table( 'SCHEMA', 'TABLE' );
end;
/

begin
dbms_redefinition.start_redef_table('SCHEMA', 'TABLE','TABLE_P' );
end;
/

declare
  error_count pls_integer := 0;
BEGIN
  dbms_redefinition.copy_table_dependents(uname      => 'SCHEMA',
                                          orig_table => 'TABLE',
                                          int_table  => 'TABLE_P',
                                          num_errors => error_count);
  dbms_output.put_line('errors := ' || to_char(error_count));
END;
/

begin
dbms_redefinition.finish_redef_table('SCHEMA', 'TABLE','TABLE_P');
end;
/

Note that this will make the dependant objects to go INVALID in the schema, so be prepared for some downtime to clear these invalids.

Upvotes: 3

Related Questions