The Geek Diary

How To Convert A Partitioned Table To A Non-Partitioned Table Using DataPump In Oracle 11g and 12c

by

The Basics

A new import DataPump parameter PARTITION_OPTIONS has been introduced with 11g. The allowed values are:

  • NONE – Creates tables as they existed on the system from which the export operation was performed. This is the default value.
  • DEPARTITION – Promotes each partition or subpartition to a new individual table. The default name of the new table will be the concatenation of the table and partition name or the table and subpartition name, as appropriate.
  • MERGE – Combines all partitions and subpartitions into one table.

The parameter PARTITION_OPTIONS specifies how table partitions should be created during an import operation. To convert a partitioned table to a non-partitoned table we have to use PARTITION_OPTIONS=MERGE during the process of import.

Example

The below example illustrates how to convert partitioned table to a non-partitioned table using expdp/impdp.

1. Create a partitioned table and insert values into the partitioned tableconnect scott/tiger:

create table part_tab
(
   year    number(4),
   product varchar2(10),
   amt     number(10,2)
)
partition by range (year)
(
   partition p1 values less than (1992) tablespace u1,
   partition p2 values less than (1993) tablespace u2,
   partition p3 values less than (1994) tablespace u3,
   partition p4 values less than (1995) tablespace u4,
   partition p5 values less than (MAXVALUE) tablespace u5
);
select * from PART_TAB;

YEAR       PRODUCT    AMT
---------- ---------- ----------
      1992 p1                100
      1993 p2                200
      1994 p3                300
      1995 p4                400
      2010 p5                500
select OWNER, TABLE_NAME, PARTITIONED 
from   dba_tables 
where  table_name = 'PART_TAB' and owner = 'SCOTT';

OWNER                          TABLE_NAME PAR
------------------------------ ---------- ---
SCOTT                          PART_TAB   YES
select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME 
from   dba_tab_partitions 
where  TABLE_NAME = 'PART_TAB' and TABLE_OWNER = 'SCOTT';

TABLE_OWNER                    TABLE_NAME PARTITION_ TABLESPACE
------------------------------ ---------- ---------- ----------
SCOTT                          PART_TAB   P1         U1
SCOTT                          PART_TAB   P2         U2
SCOTT                          PART_TAB   P3         U3
SCOTT                          PART_TAB   P4         U4
SCOTT                          PART_TAB   P5         U5

2. Export the partitioned table:

$ expdp TABLES=scott.part_tab USERID="' / as sysdba'" DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=part_tab.log

Export: Release 11.2.0.2.0 - Production on Thu Dec 23 08:27:24 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": TABLES=scott.part_tab USERID="/******** AS SYSDBA" DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=part_tab.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."PART_TAB":"P2" 5.898 KB 1 rows
. . exported "SCOTT"."PART_TAB":"P3" 5.898 KB 1 rows
. . exported "SCOTT"."PART_TAB":"P4" 5.898 KB 1 rows
. . exported "SCOTT"."PART_TAB":"P5" 5.914 KB 2 rows
. . exported "SCOTT"."PART_TAB":"P1" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /tmp/part_tab.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:28:02

3. Import the table in user “BHAKTA” to convert the partitioned table into a non-partitioned table:

$ impdp USERID="'/ as sysdba'" TABLES=scott.part_tab DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=imp_part_tab.log REMAP_SCHEMA=scott:bhakta PARTITION_OPTIONS=merge

Import: Release 11.2.0.2.0 - Production on Thu Dec 23 08:39:08 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": USERID="/******** AS SYSDBA" TABLES=scott.part_tab DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=imp_part_tab.log REMAP_SCHEMA=scott:bhakta PARTITION_OPTIONS=merge
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BHAKTA"."PART_TAB":"P2" 5.898 KB 1 rows
. . imported "BHAKTA"."PART_TAB":"P3" 5.898 KB 1 rows
. . imported "BHAKTA"."PART_TAB":"P4" 5.898 KB 1 rows
. . imported "BHAKTA"."PART_TAB":"P5" 5.914 KB 2 rows
. . imported "BHAKTA"."PART_TAB":"P1" 0 KB 0 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 08:39:17
select * from bhakta.part_tab;

YEAR       PRODUCT    AMT
---------- ---------- ----------
      1992 p1                100
      1993 p2                200
      1994 p3                300
      1995 p4                400
      2010 p5                500
select OWNER, TABLE_NAME, PARTITIONED
from   dba_tables 
where  table_name = 'PART_TAB' and owner = 'BHAKTA';

OWNER                          TABLE_NAME PAR
------------------------------ ---------- ---
BHAKTA                         PART_TAB   NO
select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME
from   dba_tab_partitions 
where  TABLE_NAME = 'PART_TAB' and TABLE_OWNER = 'BHAKTA';

no rows selected
Note:: If there is a local or global prefixed index created on the partitioned table, import with PARTITION_OPTIONS=merge also converts the index to non-partitioned.

– local prefixed index:

CREATE INDEX part_tab_loc_idx ON part_tab(year) LOCAL;

After import with REMAP_SCHEMA=scott:bhakta PARTITION_OPTIONS=merge, the local prefixed index is also converted to a non-partitioned index:

select OWNER, INDEX_NAME, PARTITIONED
from dba_indexes
where index_name='PART_TAB_GLOB_IDX';
OWNER INDEX_NAME PAR
---------- -------------------- ---
SCOTT PART_TAB_LOC_IDX YES
BHAKTA PART_TAB_LOC_IDX NO

OR

– global prefixed index:

CREATE INDEX part_tab_glob_idx ON part_tab(year)
GLOBAL PARTITION BY RANGE (year)
(partition p1 values less than (1992),
partition p2 values less than (1993),
partition p3 values less than (1994),
partition p4 values less than (1995),
partition p5 values less than (MAXVALUE)
);

After import with REMAP_SCHEMA=scott:bhakta PARTITION_OPTIONS=merge, the local prefixed index is also converted to a non-partitioned index:

select OWNER, INDEX_NAME, PARTITIONED
from dba_indexes
where index_name='PART_TAB_GLOB_IDX';
OWNER INDEX_NAME PAR
---------- -------------------- ---
SCOTT PART_TAB_GLOB_IDX YES
BHAKTA PART_TAB_GLOB_IDX NO
Note: The DEPARTITION option is applicable to Transportable tablespace.
Oracle Tablespace Transport for a Single Partition

You May Also Like