Use datapump to switch a common table to a partition 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; using the EXCHANGEPARTITION method to convert to a partition table and using DBMS_REDEFINITION to redefine the Partition Table online. Ben

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. Ben

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 article describes how to use the Export and Import method. The following is a specific operation 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.

1. Main Steps

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_2012 values less than (TO_DATE ('1970/123', 'dd/MM/yyyy') tablespace tbs1, PARTITION big_table_2013 values less than (TO_DATE ('1970/123', 'dd/MM/YYYY ') tablespace tbs2, PARTITION big_table_2014 values less than (MAXVALUE) tablespace tbs3; -- You can directly use the Insert method to fill data in the partition table, as shown in the following insert into big_table2 SELECT * FROM big_table;

4. Export the imported data to the partition table through datapump

-- This method is mainly used to migrate data from different databases. For example, the source table of the source database is a common table, the target database is a partition Table $ expdp leshami/xxx directory = db_dump_dir dumpfile = big_table.dmp logfile = partition tables = big_table content = data_onlySQL> rename big_table to big_table_old; Table renamed. SQL> rename big_table2 to big_table; Table renamed. $ impdp leshami/xxx directory = db_dump_dir dumpfile = big_table.dmp logfile = imp _ big_tb.log tables = big_tableEXEC tables ('lesham', 'Big _ table', cascade => TRUE ); -- The following is the result SQL> select table_name, partition_name, high_value, num_rows 2 from user_tab_partitions where table_name = 'Big _ table '; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS partition tables ---------- BIG_TABLE2 BIG_TABLE_2012 TO_DATE ('2013-01-01 partition TO_DATE ('2014-01-01 partition 1_maxvalue 3333 -- if no data exception occurs, you can delete the source table to add corresponding indexes and constraint, if the source table is not deleted, separate indexes and constraint names must be used. SQL> drop Table big_table; table dropped. alter table big_table ADD (CONSTRAINT big_table_pk primary key (id); create index bita_created_date_ I ON big_table (created_date) LOCAL; create index bita_look_fk_ I ON big_table (lookup_id) LOCAL; alter table big_table ADD (CONSTRAINT bita_look_fk foreign key (lookup_id) REFERENCES lookup (id )); -- the TRIGGER also needs to be added to the Partition Table 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_table2;/5. Postscript

More references

For more information about Oracle RAC, see

For more information about the basics and concepts of Oracle network configuration, see:

For more information about user-managed backup and recovery, see

For information on RMAN backup recovery and management, see

For the ORACLE architecture, see