Datapump in Oracle 12c

Dear Readers,

In this article, we will see the following Datapump in Oracle 12c.

Oracle Datapump was introduced in Oracle 10g. This article provides an overview of the main Data Pump enhancements in Oracle Database 12c Release 1.

Oracle Datapump have two utilities They are

1.EXPDP

2.IMPDP

EXPDP is a utility provided by ORACLE  and it exports the data from database to physical files.

Those physical files we call it as “Dumpfiles”.

IMPDP is a utility provided by ORACLE  and it imports the data from  physical files(dump files)  to database.

Prior to 10g we used traditional export and import to take the backup of database.

Due to some drawbacks in traditional export  Oracle came with DATAPUMP utility .

DATAPUMP  have so many advantages They are :

1.Data pump operations are faster compared to traditional export and import.

2.Datapump uses two plsql packages to finishes operations faster. They are

a)DBMS_DATAPUMP

b)DBMS_METADATA

3.DATAPUMP will perform read and write operations in block method.

4.We can define a job name for expdp/impdp operations.

5.Using the job name we can monitor,pause and resume the jobs.

6.we can estimate the size of a dump file before exporting using estimate_only=y.

7. For every datapump operations two background processes will invoke They are :

a)MASTER BACKGROUND PROCESS

b)WORKER BACKGROUND PROCESS

MASTER BACKGROUND PROCESS :

It creates a table in the current user schema who is performing the operations.

The table will be known as master table.

The name of the master table is same as jobname.

It maintains the status of job under master table,once the job done successfully master gets dropped automatically.

WORKER BACKGROUND PROCESS  :

This process gets created by the master’s background process.

It performs the actual operations.

DATAPUMP in CONTAINER DATABASE :

A multitenant container database (CDB) is an Oracle database that includes zero, one, or many user created pluggable databases (PDBs). A PDB is a portable set of schemas, schema objects, and non schema objects that appear to an Oracle Net client as a non-CDB.

How to take container database backup using datapump .

If you want to know what parameters we can use in datapump then we need to use impdp help=y.

Check the parameters required to perform datapump operations.

we need to set  values for the above parameters.

we need to create directory at db level and physical level.

Use expdp utility to perform export operation.

With the above syntax we exported only root database.

In order to take PDB’s backup you must define an explicit directory object with in the PDB that you are exporting or importing.

Then take export of plug5 PDB using following syntax.

NOLOGGING Option (DISABLE_ARCHIVE_LOGGING)

The TRANSFORM parameter of impdp has been extended to include a DISABLE_ARCHIVE_LOGGING option. The default setting of ‘N’ has no effect on logging behaviour.
Using a value ‘Y’ reduces logging associated with tables and indexes during the import by setting theirlogging attribute to NOLOGGING before the data is imported and resetting it to LOGGING once theoperation is complete.
TRANSFORM=DISABLE_ARCHIVE_LOGGING=Y
The effect can be limited to a specific type of object (Table or Index) by appending the object type.
TRANSFORM=DISABLE_ARCHIVE_LOGGING=Y:TABLE
TRANSFORM=DISABLE_ARCHIVE_LOGGING=Y:INDEX
Example:
Exporting table t1  of loc_u1 owner and importing into loc_u2 schema

Use remap_schema parameter to send table t1 into loc_un schema.

The DISABLE_ARCHIVE_LOGGING option has no effect if the database is running in FORCE LOGGING mode.

LOGTIME Parameter

The LOGTIME parameter determines if timestamps should be included in the output messages from
the expdp and impdp utilities.
LOGTIME=[NONE | STATUS | LOGFILE | ALL]
The allowable values are explained below.
NONE : The default value, which indicates that no timestamps should be included in the output,
making the output look similar to that of previous versions.
STATUS : Timestamps are included in output to the console, but not in the associated log file.
LOGFILE : Timestamps are included in output to the log file, but not in the associated console
messages.
ALL : Timestamps are included in output to the log file and console.

Export View as Table

 

The VIEWS_AS_TABLES parameter allows Data Pump to export the specified views as if they were tables. The table structure matches the view columns, with the data being the rows returned by the query supporting the views.

By default expdp creates a temporary table as a copy of the view, but with no data, to provide a source of the metadata for the export. Alternatively to can specify a table with the appropriate structure.

Connect to loc_u1 local user and create a view.

Perform expdp operation with views_as_tables parameter.

By using views_as_tables parameter expdp will create a temporary table as a copy of view and its export of that temporary table.

Now import that table  into another schema using remap_schemas parameter.

Connect to local user lc_u2 and check the tables.

You can watch the process of table creation from a second session using the following data dictionary view :

USER_TAB_COMMENTS

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform