How to utilize PARFILE parameter in DATAPUMP Exports and Imports

• Pradip
• 24 Sep, 2024
• 0 Comments
• 4 Mins Read

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

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

[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