The PARTITION_OPTIONS parameter determines how partitions will be handled during export and import operations. The syntax is shown below.
Syntax:
PARTITION_OPTIONS={none | departition | merge}
NONE: The partitions are created exactly as they were on the system the export was taken from.
DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-)partition name.
MERGE: This option will import all partition data into single table.
The NONE and MERGE options are not possible if the export was done using the TRANSPORTABLE parameter with a partition or subpartition filter. If there are any grants on objects being departitioned, an error message is generated and the objects are not loaded.
Partition Table creation:
SQL> create table employee (emp_id number,sal INT NOT NULL) PARTITION BY RANGE (sal)( PARTITION employee_e1 VALUES LESS THAN (001),PARTITION employee_e2 VALUES LESS THAN (002)); Table created.
SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = 'EMPLOYEE'; PARTITION_NAME ---------------- EMPLOYEE_E1 EMPLOYEE_E2
[oracle@18c empdata]$ expdp hari/oracle dumpfile=employee.dmp directory=test_dir tables=employee Export: Release 18.0.0.0.0 - Production on Fri Aug 24 23:32:41 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "HARI"."SYS_EXPORT_TABLE_01": hari/******** dumpfile=employee.dmp directory=test_dir tables=employee Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "HARI"."EMPLOYEE":"EMPLOYEE_E1" 0 KB 0 rows . . exported "HARI"."EMPLOYEE":"EMPLOYEE_E2" 0 KB 0 rows Master table "HARI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ********************************************************** Dump file set for HARI.SYS_EXPORT_TABLE_01 is: /u01/empdata/employee.dmp Job "HARI"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 24 23:33:00 2018 elapsed 0 00:00:18
DEPARTITION :-
Drop EMPLOYEE table,
SQL> conn hari/oracle; Connected. SQL> drop table employee purge; Table dropped.
[oracle@18c empdata]$ impdp hari/oracle dumpfile=employee.dmp directory=test_dir partition_options=DEPARTITION Import: Release 18.0.0.0.0 - Production on Fri Aug 24 23:48:09 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "HARI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "HARI"."SYS_IMPORT_FULL_01": hari/******** dumpfile=employee.dmp directory=test_dir partition_options=DEPARTITION Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "HARI"."EMPLOYEE_EMPLOYEE_E1" 0 KB 0 rows . . imported "HARI"."EMPLOYEE_EMPLOYEE_E2" 0 KB 0 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "HARI"."SYS_IMPORT_FULL_01" successfully completed at Fri Aug 24 23:48:12 2018 elapsed 0 00:00:03
SQL> select * from tab where tname like 'EMP%'; TNAME TABTYPE CLUSTERID -------------------- ------------- ---------- EMPLOYEE TABLE EMPLOYEE_EMPLOYEE_E1 TABLE EMPLOYEE_EMPLOYEE_E2 TABLE SQL> select partition_name from user_tab_partitions where table_name='EMPLOYEE'; no rows selected
MERGE :-
[oracle@18c empdata]$ impdp hari/oracle dumpfile=employee.dmp directory=test_dir partition_options=MERGE
Import: Release 18.0.0.0.0 - Production on Sat Aug 25 00:36:31 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "HARI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HARI"."SYS_IMPORT_FULL_01": hari/******** dumpfile=employee.dmp directory=test_dir partition_options=MERGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HARI"."EMPLOYEE":"EMPLOYEE_E1" 0 KB 0 rows
. . imported "HARI"."EMPLOYEE":"EMPLOYEE_E2" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HARI"."SYS_IMPORT_FULL_01" successfully completed at Sat Aug 25 00:36:35 2018 elapsed 0 00:00:02
SQL> select * from tab where tname like 'EMP%'; TNAME TABTYPE CLUSTERID -------------------- ------------- ---------- EMPLOYEE TABLE EMPLOYEE_EMPLOYEE_E1 TABLE EMPLOYEE_EMPLOYEE_E2 TABLE SQL> select partition_name from user_tab_partitions where table_name='EMPLOYEE'; no rows selected SQL> select table_name , partitioned from dba_tables where table_name='EMPLOYEE'; TABLE_NAME PAR -------------------- --- EMPLOYEE NO
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
