Oracle Data Pump

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 10g, 11g, 12c, 18c, 19c

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

Below topics we are going to cover in this article.

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

So let’s start the practical if you have any queries regarding this article and I missed something please feel free to write in the comment box, I’ll reply as soon as possible.

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

Use below query to export tables from particular tablespace.

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

Excluding and Including objects during export

EXCLUDE: Using the EXCLUDE option you can exclude database objects while performing an export using the 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.

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.

Read – Import view only using impdp

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: 
https://www.linkedin.com/in/shripal-singh
Twitter: 
https://twitter.com/ocptechnology
Facebook: 
https://www.facebook.com/ocptechnology
YouTube:
https://www.youtube.com/ocptechnology

1 thought on “Oracle Data Pump”

Leave a Comment

%d bloggers like this: