http://space.itpub.net/25801738

oracle 9i提供了dbms_redefinition包来实现数据库的表的在线重定义功能。在实际的应用上，我们可以利用这个包来进行：（1）堆表与分区之间进行转换。（2）重建表以减少HWM。10g能shrink，9i如果用move tablespace and rebuild index在move的时候会锁表，如果想实现在线降低HWM，估计只能用这个了。（3）在线更改表结构，如更改列的前后顺序，将column_a，column_b改成column_b，column_a。

 

This article presents a simple method for partitioning an existing table using the DBMS_REDEFINITION package, introduced in Oracle 9i. The contents of the article should not be used as an indication of when and how to partition objects, it simply shows the method of getting from A to B. Remember, in many cases incorrect partitioning is worse than no partitioning!

• Create a Sample Schema
• Create a Partitioned Interim Table
• Start the Redefintion Process
• Create Constraints and Indexes
• Complete the Redefintion Process

Related articles.

• Partitioned Tables And Indexes in Oracle 8i
• Partitioning Enhancements In Oracle9i
• Hash Partitioned Global Indexes in Oracle 10g
• Partitioning Enhancements in Oracle Database 11g Release 1
• Partitioning an Existing Table using EXCHANGE PARTITION

Create a Sample Schema

First we create a sample schema as our starting point.

-- Create and populate a small lookup table.
CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);

ALTER TABLE lookup ADD (
  CONSTRAINT lookup_pk PRIMARY KEY (id)
);

INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;

-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
);

DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
    
    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/

-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
  CONSTRAINT big_table_pk PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i ON big_table(created_date);

CREATE INDEX bita_look_fk_i ON big_table(lookup_id);

ALTER TABLE big_table ADD (
  CONSTRAINT bita_look_fk
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

Create a Partitioned Interim Table

Next we create a new table with the appropriate partition structure to act as an interim table.

-- Create partitioned table.
CREATE TABLE big_table2 (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
 PARTITION big_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
 PARTITION big_table_2005 VALUES LESS THAN (MAXVALUE));

With this interim table in place we can start the online redefinition.

Start the Redefintion Process

First we check the redefinition is possible using the following command.

EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'BIG_TABLE');

If no errors are reported it is safe to start the redefintion using the following command.

BEGIN
  DBMS_REDEFINITION.start_redef_table(
    uname      => USER,        
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/

Depending on the size of the table, this operation can take quite some time to complete.

Create Constraints and Indexes

If there is delay between the completion of the previous operation and moving on to finish the redefinition, it may be sensible to resynchronize the interim table before building any constraints and indexes. The resynchronization of the interim table is initiated using the following command.

-- Optionally synchronize new table with interim data before index creation
BEGIN
  dbms_redefinition.sync_interim_table(
    uname      => USER,        
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/

The constraints and indexes from the original table must be applied to interim table using alternate names to prevent errors. The indexes should be created with the appropriate partitioning scheme to suit their purpose.

-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
  CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;

CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

ALTER TABLE big_table2 ADD (
  CONSTRAINT bita_look_fk2
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE);

Complete the Redefintion Process

Once the constraints and indexes have been created the redefinition can be completed using the following command.

BEGIN
  dbms_redefinition.finish_redef_table(
    uname      => USER,        
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/

At this point the interim table has become the "real" table and their names have been switched in the data dictionary. All that remains is to perform. some cleanup operations.

-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;

-- Rename all the constraints and indexes to match the original names.
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

The following queries show that the partitioning was successful.

SELECT partitioned
FROM   user_tables
WHERE  table_name = 'BIG_TABLE';

PAR
---
YES

1 row selected.

SELECT partition_name
FROM   user_tab_partitions
WHERE  table_name = 'BIG_TABLE';

PARTITION_NAME
------------------------------
BIG_TABLE_2003
BIG_TABLE_2004
BIG_TABLE_2005

3 rows selected.

For more information see:

• Partitioned Tables And Indexes in Oracle 8i
• Partitioning Enhancements In Oracle9i
• Hash Partitioned Global Indexes in Oracle 10g
• Partitioning Enhancements in Oracle Database 11g Release 1
• Partitioning an Existing Table using EXCHANGE PARTITION
• DBMS_REDEFINITION

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25801738/viewspace-714454/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25801738/viewspace-714454/