Oracle Data Pump

Oracle Data Pump

Oracle Data Pump 10g, 11g, 12c, 18c, 19c

Hello friends my name is Shripal Singh and in this article i’m going to show you three topics related to Oracle Data Pump EXPORT & IMPORT, listed below.

Oracle Data pump is alternative to the “exp” and “imp” utilities which is used in older Oracle versions, Data Pump is faster and more flexible.

Please subscribe me on YouTube.

  1. Export tables from a particular tablespace.
  2. Excluding and Including objects during export.
  3. Filter table rows during export.

So lets start the practical, if you have any query regarding this article and i missed something please feel free write in the comment box, i’ll be reply as soon as possible.

1. Export tables from a particular tablespace.

As you know Oracle introduced so many features for EXPORT and IMPORT. One of them we are going to discussing here. If you want to export tables from a particular tablespace then we are using the below query.

In My case the directory name is “DIR

If you don’t know how to create directory and take full export click here ?.

$expdp ocp/ocp DIRECTORY=dir DUMPFILE=tbs.dmp TABLESPACES=tbs1, tbs2, tbs4

The above query will export all the objects from above listed tablespaces in tbs1, tbs2, tbs4.

You must know how to split the data pump dumpfile ?.

2. Excluding and Including objects during export.

EXCLUDE: Using EXCLUDE option you can exclude database objects while performing a export using Data-Pump utility. For example you are going to exporting a database schema and don’t want to export tables which name starts with “H” then you can type the following command.

$ expdp ocp/ocp DIRECTORY=dir DUMPFILE=ocp_schema.dmp SCHEMAS=OCP EXCLUDE=TABLE:”like ‘H%’”

Then all tables in OCP’s Schema which name starts with “H“ will not be exported.

INCLUDE: Similarly you can also use INCLUDE option to only export certain objects like this

$ expdp ocp/ocp DIRECTORY=dir DUMPFILE=ocp_schema.dmp SCHEMAS=OCP INCLUDE=TABLE:”like ‘H%’”

The INCLUDE option is opposite of EXCLUDE option i.e. it will export only those tables of OCP’s schema whose name starts with “H”

Similarly you can also exclude CONSTRAINTS,INDEXES,SCHEMA, GRANTS.

You also must know how to use REMAP TABLE parameter in IMPDP ?.

3. Filter table rows during export.

Using QUERY option, you can export only required rows from the table.
For Example, We want to export only those rows of employees table, whose dept id is 50 and whose salary is above 5000.

$ expdp ocp/ocp QUERY=emp:'"WHERE dept_id = 50 AND sal > 5000"' NOLOGFILE=y DIRECTORY=dir DUMPFILE=exp.dmp

I hope you got this article useful, if yes please write your fillings in comment box, your comment is motivate me to write more article like this.

Share
Share