Oracle Data Pump - Part III

On our quest to learn about Oracle's Data Pump utility it has often been compared to the old export and import (exp & imp) utilities that we have all grown to love (or hate). This article is where where Data Pump takes a detour from these old utilities and begins to shine. This article will explore some of the export modes available and give examples on how to export selected object types and dependencies those objects

In order to use Data Pump, we learned in Part II of this series that a datapump directory was required to export and import from and to databases. Here are the three setup and authorization commands needed to get started.

A slight change to this example, changing the FULL keyword to SCHEMA, allows us to perform a SCHEMA mode export where a particular schema will be exported. Anyone familiar with the old export / import (exp / imp) utilities should feel right at home here. To export multiple schema's you need only separate each schema with commas.

Likewise we could change the SCHEMS option and export all objects in a particular tablespace by switching to the TABLESPACES export mode.C:\>expdp scott/tiger TABLESPACES=USERS DIRECTORY=datapump DUMPFILE=TSusers.dmp LOGFILE=TSusers.log

If you wanted to export a single table, you need only switch to TABLE mode and use the following export command.

The interesting point to notice when issuing these commands is to take a close look at the export logs for each of these export modes. When taking a full schema export you will notice that the export pulls out various additional object types such as grants, roles, sequences, and views. To just name a few. Here is the log from the SCHEMA export performed above.

If we then take a look at the export for a tables you will quickly notice that not all the object types that were exported for the SCHEMA mode have been exported for the TABLE mode. Some of this is because, in our example, the DEPT table does not have certain dependent objects and because other object types are not at all exported even though they would seem to have a dependency. For instance indexes, triggers, and statistics will be exported under TABLE mode but a view on the DEPT table will not. So as a caution, be careful and examine your export logs. You may not be getting everything you think is a dependent object.

One way to determine the objects that will or can be exported for the different modes is to look at the three DBA views DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS. Each of these views, if queried, will give you a list and short description on the specific paths to object types that you can expect INCLUDE or EXCLUDE to be dependent on the object you are exporting or importing. For instance if you were to query the TABLE_EXPORT_OBJECTS view with the following SQL you would get a list of all objects that are dependent on exporting a table. As you can see there is no entry for exporting views based on a table export. In actuality there are 86 INCLUCE/EXCLUDE types just in the TABLE_EXPORT_OBJECTS view and many more the other two export views. I would encourage you to select the object paths for each of the views and get acquainted with what you can export.

Through the INCLUDE/EXCLUDE options you can fine tune your exports to pull exactly what you want from your databases. So, to take advantage of these INCLUDE/EXCLUDE object types we can perform an export on a table and not include statistics with the following export command.

When playing around with Data Pump export and using the INCLUDE / EXCLUDE feature, I soon found out that it was much easier to use a parameter file (parfile) when specifying the different INCLUDE / EXCLUDE options. This is the same concept as the old export and import (exp & imp) utilities. This is easier because in the course of trying to put all of the potential options on one command line and with the fact that there are “special” characters required when specifying INCLUCE / EXCLUDE options, you will soon find it easier to add to and subtract from the export command. I tried a number of times putting these options on a single command line but had numerous issues. So I would suggest just getting use to the parfile from the start.
For an example in using the parfile I decided to export the DEPT table from the SCOTT schema and include views. Remember, as noted earlier in this article that views are not available to export under a table. So if you were to look at the DBA views, also noted above, you need to at least go up to a schema export to include views. So I created the following parfile. This will actually export all views in the SCOTT schema. If you knew the view names associated with the DEPT table you could also create in IN list much like the INCLUDE statement for the DEPT table.

Data Pump's import command is much the same as the export command. I actually just replaced the expdp with the impdp command in these examples and had no problems importing back into my database. Many times though we want to import into a different schema and this is accomplished by the REMAP_SCHEMA option. Here is an example where I imported the DEPT table into a different schema.

Oracle's Data Pump utility has many options that allow you to fine tune what you can export from a database. Just remember to query the DBA views (DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS) that dictate the dependent objects that will be exported under certain scenarios. Also keep in mind you can just as easily exclude these object types to pull out exactly what you want.