Default: There is no default
Specifies that you want to perform a table-mode export.
Syntax and Description
TABLES=[schema_name.]table_name[:partition_name] [, ...]
Filtering can restrict what is exported using this mode (see "Filtering During Export Operations"). You can filter the data and metadata that is exported, by specifying a comma-delimited list of tables and partitions or subpartitions. If a partition name is specified, then it must be the name of a partition or subpartition in the associated table. Only the specified set of tables, partitions, and their dependent objects are unloaded.
If an entire partitioned table is exported, then it will be imported in its entirety, as a partitioned table. The only case in which this is not true is if
PARTITION_OPTIONS=DEPARTITION is specified during import.
The table name that you specify can be preceded by a qualifying schema name. The schema defaults to that of the current user. To specify a schema other than your own, you must have the
Use of the wildcard character, %, to specify table names and partition names is supported.
The following restrictions apply to table names:
By default, table names in a database are stored as uppercase. If you have a table name in mixed-case or lowercase, and you want to preserve case-sensitivity for the table name, then you must enclose the name in quotation marks. The name must exactly match the table name stored in the database.
Some operating systems require that quotation marks on the command line be preceded by an escape character. The following are examples of how case-sensitivity can be preserved in the different Export modes.
In command-line mode:
In parameter file mode:
Table names specified on the command line cannot include a pound sign (#), unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table name includes a pound sign (#), then the Export utility interprets the rest of the line as a comment, unless the table name is enclosed in quotation marks.
TABLES=(emp#, dept, mydata)
However, if the parameter file contains the following line, then the Export utility exports all three tables because
emp# is enclosed in quotation marks:
TABLES=('"emp#"', dept, mydata)
Some operating systems require single quotation marks rather than double quotation marks, or the reverse. See your Oracle operating system-specific documentation. Different operating systems also have other restrictions on table naming.
For example, the UNIX C shell attaches a special meaning to a dollar sign ($) or pound sign (#) (or certain other special characters). You must use escape characters to get such characters in the name past the shell and into Export.
Using the Transportable Option During Table-Mode Export
To use the transportable option during a table-mode export, specify the
TRANSPORTABLE=ALWAYS parameter with the
TABLES parameter. Metadata for the specified tables, partitions, or subpartitions is exported to the dump file. To move the actual data, you copy the data files to the target database.
If only a subset of a table's partitions are exported and the
TRANSPORTABLE=ALWAYS parameter is used, then on import each partition becomes a non-partitioned table.
Cross-schema references are not exported. For example, a trigger defined on a table within one of the specified schemas, but that resides in a schema not explicitly specified, is not exported.
Types used by the table are not exported in table mode. This means that if you subsequently import the dump file and the type does not already exist in the destination database, then the table creation will fail.
The use of synonyms as values for the
TABLES parameter is not supported. For example, if the
regions table in the
hr schema had a synonym of
regn, then it would not be valid to use
TABLES=regn. An error would be returned.
The export of tables that include a wildcard character, %, in the table name is not supported if the table has partitions.
The length of the table name list specified for the
TABLES parameter is limited to a maximum of 4 MB, unless you are using the
NETWORK_LINK parameter to an Oracle Database release 10.2.0.3 or earlier or to a read-only database. In such cases, the limit is 4 KB.
You can only specify partitions from one table if
TRANSPORTABLE=ALWAYS is also set on the export.
The following example shows a simple use of the
TABLES parameter to export three tables found in the
departments. Because user
hr is exporting tables found in the
hr schema, the schema name is not needed before the table names.
> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=employees,jobs,departments
The following example assumes that user
hr has the
DATAPUMP_EXP_FULL_DATABASE role. It shows the use of the
TABLES parameter to export partitions.
> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tables_part.dmp TABLES=sh.sales:sales_Q1_2012,sh.sales:sales_Q2_2012
This example exports the partitions,
sales_Q2_2012, from the table
sales in the schema