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; 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.
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 how to use exchange, see switch a common table to a partition table using exchange.
For more information about partition tables, see Oracle partition tables.
For more information about how to import and export partition table data, see Import and Export Oracle partition table data.

 

1. Main Steps
A. Prepare the corresponding tablespace for the new Partition Table
B. Create a partition table based on the source table metadata
C. Use datapump to export the original table data and then import it to the partition table.
D. Collect statistics, verify the results, and add index constraints to the partition table.

 

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
The Export and Import method is used because the source table and target partition table are in different databases and the source table data needs to be migrated to the new partition table. Of course, using the insert method with dblink can also complete the above functions. Note: If the new partition table of the target database is the same as the source table name of the original database during import, you can skip the rename TABLE name described in this article and delete the source table name. Second, this method of switching to a partitioned table is easy to use, but it is difficult to ensure data consistency. It is usually used to operate on idle table objects.

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)