Use DBMS_REDEFINITION to switch a common table to a partition table online

As the database data volume continues to grow, some tables need to be converted from normal heap tables to partition tables. There are several different methods for this operation, such as exporting table data.

As the database data volume continues to grow, some tables need to be converted from normal heap tables to partition tables. There are several different methods for this operation, such as exporting table data.

As the database data volume continues to grow, some tables need to be converted from normal heap tables to partition tables. There are several different methods for this operation, such as exporting table data, creating a partition table, and then importing data to the partition table; use the exchange partition method to convert to a PARTITION table and use DBMS_REDEFINITION to redefine a PARTITION table online. This document describes how to use DBMS_REDEFINITION. The following is an example.

For more information about the principles and steps of online redefinition of dbms_redefinition tables, see online redefinition of tables based on dbms_redefinition.

For more information about partition tables, see Oracle partition tables.

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

1. Prepare the environment

-- Create a user
SQL> create user leshami identified by xxx;

SQL> grant dba to leshami;

-- Create the tablespace required for demonstration
SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10 m autoextend on;

SQL> alter user leshami default tablespace tbs_tmp;

SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10 m autoextend on;

SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10 m autoextend on;

SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10 m autoextend on;

SQL> conn leshami/xxx

-- Create a lookup table
Create table lookup (
Id NUMBER (10 ),
Description VARCHAR2 (50)
);

-- Add primary key constraints
Alter table lookup ADD (
CONSTRAINT lookup_pk primary key (id)
);

-- Insert data
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 a large table to switch to a partition
Create table big_table (
Id NUMBER (10 ),
Created_date DATE,
Lookup_id NUMBER (10 ),
Data VARCHAR2 (50)
);

-- Fill in data to a large table
DECLARE
Rochelle lookup_id lookup. id % TYPE;
Rochelle create_date DATE;
BEGIN
FOR I IN 1 .. 1000000 LOOP
If mod (I, 3) = 0 THEN
Rochelle create_date: = ADD_MONTHS (SYSDATE,-24 );
Rochelle lookup_id: = 2;
Elsif mod (I, 2) = 0 THEN
Rochelle create_date: = ADD_MONTHS (SYSDATE,-12 );
Rochelle lookup_id: = 1;
ELSE
Rochelle create_date: = SYSDATE;
Rochelle 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;
/

-- Add primary and foreign key constraints, indexes, and triggers to a large 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)
);

Create or replace trigger tr_bf_big_table
Before update of created_date
ON big_table
FOR EACH ROW
BEGIN
: New. created_date: = TO_CHAR (SYSDATE, 'yyyymmdd hh24: mi: ss ');
END tr_bf_big_table;
/

-- Collect statistics
EXEC DBMS_STATS.gather_table_stats ('lesham', 'lookup', cascade => TRUE );
EXEC DBMS_STATS.gather_table_stats ('lesham', 'Big _ table', cascade => TRUE );

-- Index-related information of a table
SQL> @ idx_info
TABLE_NAME INDEX_NAME CL_NAM STATUS PAR
--------------------------------------------------------------------------------------
LOOKUP LOOKUP_PK ID VALID NO
BIG_TABLE BIG_TABLE_PK ID VALID NO
BIG_TABLE BITA_CREATED_DATE_ I CREATED_DATE VALID NO
BIG_TABLE BITA_LOOK_FK_ I LOOKUP_ID VALID NO