How to utilize PARFILE parameter in DATAPUMP Exports and Imports
What is parfile ?
Parfile is a parameter in datapump exports and imports . The name of the parfile can give in any format but extension should be .par .
The PARFILE parameter in Oracle Data Pump (both for export expdp and import impdp) is used to specify a file containing all the parameters needed for the Data Pump operation, instead of passing them directly on the command line. This method is useful when there are many parameters, making the command simpler and more readable.
Steps to Utilize PARFILE in Data Pump Exports and Imports
- Create the Parameter File (parfile)
- The parameter file should be a plain text file.
- Each parameter should be placed on a separate line in the form parameter=value.
We can make a simple text file anywhere you want in your system and name it as filename.par
DATAPUMP Export using PARFILE:
create a sample text file with the extension .par , and include all the parameters which we use for export operation
for export operation
[oracle@oracle ~]$ cat >EXP_MYSCHEMA_EXPORT.par
directory=dpump_dir1
dumpfile=EXP_MYSCHEMA_10092024.dmp logfile=MYSCHEMA_10092024.log schemas=MYSCHEMA parallel=4 job_name=MYSCHEMA_EXPORT
After creating the parameter file you can execute the expdp export utility using PARFILE parameter.
[oracle@oracle ~]$ expdp parfile=EXP_MYSCHEMA.par Export: Release 12.1.0.2.0 - Production on Tue Sep 10 07:13:15 2024 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."MYSCHEMA_EXPORT": /******** AS SYSDBA parfile=EXP_MYSCHEMA.par Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 7.425 MB . . exported "MYSCHEMA"."PRODUCT_DESCRIPTIONS" 3.912 MB 10240 rows Processing object type SCHEMA_EXPORT/USER . . exported "MYSCHEMA"."CUSTOMERS" 101.17 KB 400 rows . . exported "MYSCHEMA"."PRODUCT_INFORMATION" 99.01 KB 325 rows . . exported "MYSCHEMA"."PRODUCT_REF_LIST_NESTEDTAB" 16.58 KB 325 rows . . exported "MYSCHEMA"."CATEGORIES_TAB" 19.43 KB 30 rows . . exported "MYSCHEMA"."SUBCATEGORY_REF_LIST_NESTEDTAB" 8.796 KB 29 rows . . exported "MYSCHEMA"."INVENTORIES" 31.77 KB 1500 rows . . exported "MYSCHEMA"."ORDERS" 16.89 KB 200 rows . . exported "MYSCHEMA"."ORDER_ITEMS" 28.32 KB 900 rows . . exported "MYSCHEMA"."PROMOTIONS" 9.123 KB 3 rows Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/XMLSCHEMA/XMLSCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/VIEW/COMMENT Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/VIEW/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER . . exported "MYSCHEMA"."PURCHASEORDER" 377.7 KB 200 rows . . exported "MYSCHEMA"."WAREHOUSES" 18.55 KB 15 rows Master table "SYS"."MYSCHEMA_EXPORT" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.MYSCHEMA_EXPORT is: /u01/oradata/myschema/dpump/EXP_MYSCHEMA_10092024.dmp Job "SYS"."MYSCHEMA_EXPORT" successfully completed at Tue Sep 10 07:14:15 2024 elapsed 0 00:01:00
Export completed successfully .
Dataump import using Parfile.
[oracle@oracle ~]$ cat > IMP_MYSCHEMA.par directory=dpump_dir1 dumpfile=EXP_MYSCHEMA_10092024.dmp logfile=IMP_MYSCHEMA_10092024.log schemas=MYSCHEMA parallel=4 job_name=MYSCHEMA_IMPORT
Now perform IMPDP utility using above PARFILE parameter.
[oracle@oracle ~]$ impdp parfile=IMP_MYSCHEMA.par Import: Release 12.1.0.2.0 - Production on Tue Sep 10 08:45:15 2024 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."MYSCHEMA_IMPORT": /******** AS SYSDBA parfile=IMP_MYSCHEMA.par Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "MYSCHEMA"."PRODUCT_DESCRIPTIONS" 3.912 MB 10240 rows . . imported "MYSCHEMA"."CUSTOMERS" 101.17 KB 400 rows . . imported "MYSCHEMA"."ORDERS" 16.89 KB 200 rows . . imported "MYSCHEMA"."WAREHOUSES" 18.55 KB 15 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/VIEW/VIEW Master table "SYS"."MYSCHEMA_IMPORT" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.MYSCHEMA_IMPORT is: /u01/oradata/myschema/dpump/EXP_MYSCHEMA_10092024.dmp Job "SYS"."MYSCHEMA_IMPORT" successfully completed at Tue Sep 10 08:46:15 2024 elapsed 0 00:01:00