📋Steps in this guide1/5

How to utilize PARFILE parameter in DATAPUMP Exports and Imports

Learn how to use PARFILE in Oracle Data Pump (expdp/impdp) to automate and simplify database export and import operations with real examples.

oracle configurationintermediate

by OracleDba

15 views

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

After creating the parameter file you can execute the expdp export utility using PARFILE parameter. Export completed successfully .

Code/Command (click line numbers to comment):

[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

[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

Dataump import using Parfile.

Code/Command (click line numbers to comment):

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

Code/Command (click line numbers to comment):

[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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!