remap_datafile="'/ora01/dbfile/O11R2/tbsp301.dbf':'/ora02/O11R2/tb3.dbf'"
When Data Pump creates the tablespaces, for any paths that match the first part of the string (to the left of the colon [:]), the string is replaced with the text to the right of the colon.
Changing Segment and Storage Attributes
When importing, you can alter the storage attributes of a table by using the TRANSFORM parameter. The general syntax for this parameter is
TRANSFORM=transform_name:value[:object_type]
When you use SEGMENT_ATTRIBUTES:n for the transformation name, you can remove the following segment attributes during an import:
• Physical attributes
• Storage attributes
322
CHAPTER 13 ■ DATA PUMP
• Tablespaces
• Logging
Here’s an example that removes the segment attributes:
$ impdp darl/foo directory=dp dumpfile=inv.dmp transform=segment_attributes:n You can remove just the storage clause by using STORAGE:n:
$ impdp darl/foo directory=dp dumpfile=inv.dmp transform=storage:n
Importing into a Different Tablespace from the Original
Sometimes you’re exporting out of one database and want to import objects into another database, but you want to change the tablespaces for the tables and indexes—in other words, create the objects in the destination database but in tablespaces different than the source database configuration.
This example remaps the user as well as the tablespaces. The original user and tablespaces are HEERA and TBSP1. This command imports the TICKET table into the CHAYA user and the V_DATA tablespace: $ impdp darl/foo directory=dp dumpfile=rm.dmp remap_schema=HEERA:CHAYA \
remap_tablespace=TBSP1:V_DATA tables=heera.ticket
The REMAP_TABLESPACE feature doesn’t re-create tablespaces. It only instructs Data Pump to place objects in different tablespaces (from where they were originally exported). When importing, if the tablespace that you’re placing the object in doesn’t exist, Data Pump throws an error.
Changing the Size of Datafiles
You can change the size of the datafiles when importing by using the TRANSFORM parameter with the PCTSPACE option. For example, if you want the tablespaces to be created at 20 percent of the original size, specify the following:
userid=darl/foo
directory=dp
dumpfile=phredstg.dmp
full=y
include=tablespace:"like 'TBSP%'"
transform=pctspace:20
The tablespaces are created with datafiles 20 percent of their original size. The extent allocation sizes are also 20 percent of their original definition. This feature is useful when used to export production data and then import it into a smaller database. In these scenarios, you may be filtering out some of the production data via the SAMPLE parameter or QUERY parameters (discussed in the next section).
323
CHAPTER 13 ■ DATA PUMP
Filtering Data and Objects
Data Pump has a vast array of mechanisms to filter data and metadata. You can influence what is excluded or included in a Data Pump export or import in the following ways:
• Use the QUERY parameter to export or import subsets of data.
• Use the SAMPLE parameter to export a percentage of the rows in a table.
• Use the CONTENT parameter to exclude or include data and metadata.
• Use the EXCLUDE parameter to specifically name items to be excluded.
• Use the INCLUDE parameter to name the items to be included (thereby excluding other non-dependent items not included in the list).
• Use parameters like SCHEMA to specify that you only want a subset of the database’s objects (those that belong to the specified user or users).
Examples of each of these techniques are described in the following subsections.
■
Note
You can’t use EXCLUDE and INCLUDE at the same time. These parameters are mutually exclusive.
Specifying a Query
You can use the QUERY parameter to instruct Data Pump to write to a dump file only rows that meet a certain criterion. You may want to do this if you’re re-creating a test environment and only need subsets of the data. Keep in mind that this technique is unaware of any foreign-key constraints that may be in place, so you can’t blindly restrict the data sets without considering parent/child relationships.
It has this general syntax for including a query:
QUERY = [schema.][table_name:] query_clause
The query clause can be any valid SQL clause. The query must be enclosed by either double quotes or single quotes. I recommend using double quotes because you may need single quotes embedded in the query to handle VARCHAR data. Also, you should use a parameter file so that there is no confusion about how the operating system interprets the quotation marks.
This example uses a parameter file and limits the rows exported for two tables. Here’s the parameter file used when exporting:
userid=darl/foo
directory=dp
dumpfile=inv.dmp
tables=inv,reg
query=inv:"WHERE inv_desc='Book'"
query=reg:"WHERE reg_id <=20"
Say you place the previous lines of code in a file named inv.par. The export job references the parameter file as shown:
324
CHAPTER 13 ■ DATA PUMP
$ expdp parfile=inv.par
The resulting dump file only contains rows filtered by the QUERY parameters. Again, be mindful of any parent/child relationships, and ensure that what gets exported won’t violate any constraints on the import.
You can also specify a query when importing data. Here’s a parameter file that limits the rows imported into the INV table based on the INV_ID column:
userid=darl/foo
directory=dp
dumpfile=inv.dmp
tables=inv,reg
query=inv:"WHERE inv_id > 10"
The previous text is placed in a file named inv2.par and is referenced during the import as follows: $ impdp parfile=inv2.par
Only the rows in the INV table that are filtered via the query are imported.
Exporting a Percentage of the Data
When exporting, the SAMPLE parameter instructs Data Pump to retrieve a certain percentage of rows based on a number you provide. Data Pump doesn’t keep track of parent/child relationships when exporting. Therefore, this approach doesn’t work well when you have tables linked via foreign-key constraints and you’re trying to randomly select a percentage of rows.
Here’s the general syntax for this parameter:
SAMPLE=[[schema_name.]table_name:]sample_percent
For example, if you want to export 10 percent of the data in a table, do so as follows: $ expdp darl/foo directory=dp tables=inv sample=10 dumpfile=inv.dmp
This next example exports two tables, but only 30 percent of the REG table’s data: $ expdp darl/foo directory=dp tables=inv,reg sample=reg:30 dumpfile=inv.dmp
■
Note
The SAMPLE parameter is only valid for exports.
Excluding Objects from the Export File
For export, the EXLUDE parameter instructs Data Pump to not export specified objects (whereas the INCLUDE parameter instructs Data Pump to only include specific objects in the export file). The EXCLUDE
parameter has this general syntax:
EXCLUDE=object_type[:name_clause] [, ...]
The OBJECT_TYPE refers to a database object like TABLE or INDEX. To see which object types can be filtered, view the OBJECT_PATH column of DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, or TABLE_EXPORT_OBJECTS. For example, if you want to view what schema-level objects can be filtered, run this query:
325
CHAPTER 13 ■ DATA PUMP
SELECT
object_path
FROM schema_export_objects
WHERE object_path NOT LIKE '%/%';
Here’s a small snippet of the output:
OBJECT_PATH
------------------
STATISTICS
SYNONYM
SYSTEM_GRANT
TABLE
TABLESPACE_QUOTA
TRIGGER
The EXCLUDE parameter instructs Data Pump export to filter out specific objects from the export. For example, say you’re exporting a table but want to exclude the indexes and grants: $ expdp darl/foo directory=dp dumpfile=inv.dmp tables=inv exclude=index,grant You can filter at a more granular level by using NAME_CLAUSE. The NAME_CLAUSE option of EXCLUDE
allows you to specify a SQL filter. To exclude indexes that have names that start with the string “INV”, you use the following:
exclude=index:"LIKE 'INV%'"
The previous line requires that you use quotation marks; in these scenarios, I recommend that you use a parameter file. This is because when you filter by the name of the object, it uses quotation marks.
Sometimes it’s hard to predict how the operating system will interpret quotation marks on the command line. Here’s a parameter file that contains an EXCLUDE clause: userid=darl/foo
directory=dp
dumpfile=inv.dmp
tables=inv
exclude=index:"LIKE 'INV%'"
A few aspects of the EXCLUDE clause may seem counterintuitive. For example, consider the following export parameter file:
userid=darl/foo
directory=dp
dumpfile=sch.dmp
exclude=schema:"='HEERA'"
If you attempt to exclude a user in this manner, an error is thrown. This is because the default mode of export is SCHEMA level, and Data Pump can’t exclude and include a schema at the same time. If you want to exclude a user from an export file, specify the FULL mode and exclude the user: userid=darl/foo
directory=dp
dumpfile=sch.dmp
exclude=schema:"='HEERA'"
full=y
326
CHAPTER 13 ■ DATA PUMP
Excluding Statistics
If you want to exclude statistics from your export job, you can do so using the EXCLUDE parameter. Here’s an example:
$ expdp darl/foo directory=dp dumpfile=invp.dmp tables=f_sales exclude=statistics By default, when you export a table object, any statistics are also exported. You can prevent statistics from being imported via the EXCLUDE parameter:
$ impdp darl/foo directory=dp dumpfile=invp.dmp tables=f_sales exclude=statistics When importing, if you attempt to exclude statistics from a dump file that didn’t originally include the statistics, then you receive this error:
ORA-39168: Object path STATISTICS was not found.
You also receive this error if the objects in the exported dump file never had statistics generated for them.
Including Only Specific Objects in an Export File
Use the INCLUDE parameter to include only certain database objects in the export file. The following example only exports the procedures and functions that a user owns:
$ expdp darl/foo dumpfile=proc.dmp directory=datapump include=procedure,function The proc.dmp file that is created only contains the DDL required to re-create any procedures and functions the user owns.
When using INCLUDE, you can also specify that only specific PL/SQL objects should be exported: $ expdp darl/foo directory=datapump dumpfile=ss.dmp \
include=function:\"=\'IS_DATE\'\"
When you’re only exporting specific PL/SQL objects, because of the issues with having to escape quote marks on the operating-system command line, I recommend using a parameter file. When you use a parameter file, you don’t have escape the quote marks. The following shows the contents of a parameter file that exports specific objects:
directory=datapump
dumpfile=pl.dmp
include=function:"='ISDATE'",procedure:"='DEPTREE_FILL'"
If you specify an object that doesn’t exist, Data Pump throws an error but continues with the export operation:
ORA-39168: Object path FUNCTION was not found.
Exporting Table, Index, Constraint, and Trigger DDL
Suppose you want to export the DDL associated with tables, indexes, constraints, and triggers in your database. To do this, use the FULL export mode and only include tables: $ expdp darl/foo directory=dp dumpfile=phredstg.dmp content=metadata_only full=y \
include=table
327
CHAPTER 13 ■ DATA PUMP
When you export an object, Data Pump also exports any dependent objects. So, when you export a table, you also get indexes, constraints, and triggers associated with the table.
Excluding Objects from Import
In general, you can use the same techniques used to filter objects in exports to exclude objects from being imported. Use the EXCLUDE parameter to exclude objects from being imported. For example, to exclude triggers and procedures from being imported:
$ impdp darl/foo dumpfile=d.dmp directory=dp exclude=TRIGGER,PROCEDURE
You can further refine what is excluded by adding a SQL clause. For example, say you want to not import triggers that begin with the letter
B
. Here’s what the parameter file looks like: userid=darl/foo
directory=dp
dumpfile=h.dmp
schemas=HEERA
exclude=trigger:"like 'B%'"
Including Objects in Import
You can use the INCLUDE parameter to reduce what is imported. Suppose you have a schema from which you want to import tables that begin with the letter
A
. Here’s the parameter file: userid=darl/foo
directory=dp
dumpfile=h.dmp
schemas=HEERA
include=table:"like 'A%'"
If you place the previous text in a file name h.par, then the parameter file can be invoked as follows: $ impdp parfile=h.par
In this example, the HEERA schema must already exist. Only tables that start with the letter
A
are imported.
Common Data Pump Tasks
This section contains common features you can use with Data Pump. Many of these features are standard parts of Data Pump, such as creating a consistent export or taking action when imported objects already exist in the database. Other features, such as compression and encryption, require the Enterprise Edition of Oracle and/or an extra license. I’ll point out these requirements (if relevant) for the Data Pump element being described.
328
CHAPTER 13 ■ DATA PUMP
Creating a Consistent Export
A
consistent
export means that all data in the export file is consistent as of a time or an system change number (SCN). When you’re exporting an active database with many parent-child tables, then you should ensure that you get a consistent snapshot of the data. You create a consistent export by using either the FLASHBACK_SCN or FLASHBACK_TIME parameter.