DataPump

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 are used in older Oracle versions, Data Pump is faster and more flexible.

Below are 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 discuss here. If you want to export tables from a particular tablespace then we are using the below query.

How do I export a specific table in Oracle?

In My case, the directory name is “DIR“

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

Use the below query to export tables from a particular tablespace.

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

The above query will export all the objects from the 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 export 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 the 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 the 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 the 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 the comment box, your comment motivates me to write more articles like this.

Read - Import view only using impdp

Connect with me on:

Instagram: https://www.instagram.com/digitalshripal
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