Use exchange to switch a common table to a partitioned table

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 exchange partition. 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 online redefinition of Partitioned Tables using DBMS_REDEFINITION, see: Using DBMS_REDEFINITION to switch a common table to a partitioned table online.
For more information about partition tables, see Oracle partition tables.

 

1. Main Steps
A. Prepare the corresponding tablespace for the new Partition Table
B. Create partition tables and related indexes and Constraints Based on source table metadata.
C. Use exchange to switch a common table to a partition table
D. Correct related indexes and constraint names (may be omitted)
E. split the partition table into multiple partitions as needed.
F. Collect statistics

 

2. Prepare the environment

-- Create user SQL> create user leshami identified by xxx; SQL> grant dba to leshami; -- create a table space 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 the primary key constraint 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 table big_table (id NUMBER (10), created_date DATE, lookup_id NUMBER (10), data VARCHAR2 (50 )); -- fill in the data to the Big Table DECLARE l_lookup_id lookup. id % TYPE; l_create_date DATE; begin for I IN 1 .. 10000 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;/-- add primary and foreign key constraints for large tables, indexes, and add triggers. 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 rowbegin: new. created_date: = TO_CHAR (SYSDATE, 'yyyymmdd hh24: mi: ss'); END tr_bf_big_table;/-- collect statistics EXEC DBMS_STATS.gather_table_stats ('leshami ', 'lookup ', cascade => TRUE); EXEC DBMS_STATS.gather_table_stats ('lesham', 'Big _ table', cascade => TRUE );

3. Create a partition 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_2014 values less than (MAXVALUE) tablespace tbs3); alter table big_table2 ADD (CONSTRAINT big_table_pk2 primary key (id); create index partition 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); -- the TRIGGER also needs to be separately added to the Partition table create or replace trigger tr_bf_big_table2 -- Author: leshami before update of created_date -- Blog: http://blog.csdn.net/leshami ON big_table2 for each rowbegin: new. created_date: = TO_CHAR (SYSDATE, 'yyyymmdd hh24: mi: ss'); END tr_bf_big_table2 ;/

4. Use exchange to switch to a partition table

-- The following command directly switches a common TABLE to a partition table alter table big_table2 exchange PARTITION big_table_2014 with table big_table without validation update global indexes; SQL> select count (*) from big_table2; COUNT (*) ---------- 10000 drop table big_table; RENAME big_table2 TO big_table; alter table big_table rename constraint failed TO big_table_pk; alter table big_table rename constraint failed 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 role; ALTER TRIGGER into RENAME TO tr_bf_big_table;

5. split partitioned tables using the split method

Alter table big_table split partition big_table_2014 AT (TO_DATE ('31-DEC-2012 23:59:59 ', 'dd-MON-YYYY HH24: MI: ss') INTO (PARTITION big_table_2012 tablespace tbs1, PARTITION big_table_2014) update global indexes; alter table big_table split partition big_table_2014 AT (TO_DATE ('31-DEC-2013 23:59:59 ', 'dd-MON-YYYY HH24: MI: ss') INTO (PARTITION big_table_2013 tablespace tbs2, PARTITION big_table_2014) update global indexes; -- collect statistics. If the table is large, you need to consider using parallelism and sample value. -- for the above operations, the local partition index and data are stored in the specified tablespace, which are mixed. For global indexes, they are stored in the default tablespace. -- the two situations mentioned above, you can adjust EXEC DBMS_STATS.gather_table_stats ('lesham', 'Big _ table', cascade => TRUE) as needed; -- verify the result SQL> col HIGH_VALUE format a45 wrappedSQL> select table_name, partition_name, high_value, num_rows from rows 2 where table_name = 'Big _ table'; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS ------------- partition partitions ---------- BIG_TABLE BIG_TABLE_2012 TO_DATE ('23:59:59 ', 'syyyy-MM-DD 3333 HH24: MI: ss', 'nls _ CALENDAR = GREGORIABIG_TABLE BIG_TABLE_2013 TO_DATE ('23:59:59 ', 'syyyy-MM-DD 3334 HH24: MI: ss ', 'nls _ CALENDAR = GREGORIABIG_TABLE BIG_TABLE_2014 MAXVALUE 3333

Reference: Partitioning an Existing Table using EXCHANGE PARTITION

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in oracle rac (listener. ora tnsnames. ora)
Oracle rac listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle LISTENER password (LISTENER)
Configure the ORACLE client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
SYSTEM tablespace management and Backup Recovery
SYSAUX tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the ORACLE architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle ONLINE redo LOG FILE)
Oracle Control File)
Oracle archiving logs
Oracle ROLLBACK and UNDO)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)