Release 2 (9.2)
Part Number A96652-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Utilities Release 2 (9.2) Part Number A96652-01 |
|
This chapter describes how to use the Export utility to write data from an Oracle database into an operating system file in binary format. This file is stored outside the database, and it can be read into another Oracle database using the Import utility (described in Chapter 2).
This chapter discusses the following topics:
The Export utility provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an Export file, as illustrated in Figure 1-1.
Text description of the illustration sut81001.gif
An Export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.
Export dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.
You can also display the contents of an export file without actually performing an import. To do this, use the Import SHOW parameter. See SHOW for more information.
To load data from ASCII fixed-format or delimited files, use the SQL*Loader utility.
See Also:
|
Before you begin using Export, be sure you take care of the following items (described in detail in the following sections):
catexp.sql or catalog.sql scriptTo use Export, you must run the script catexp.sql or catalog.sql (which runs catexp.sql) after the database has been created.
|
Note: The actual names of the script files depend on your operating system. The script filenames and the method for running them are described in your Oracle operating system-specific documentation. |
catexp.sql or catalog.sql needs to be run only once on a database. You do not need to run it again before you perform the export. The script performs the following tasks to prepare the database for Export:
EXP_FULL_DATABASE roleEXP_FULL_DATABASE roleEXP_FULL_DATABASE to the DBA rolecatexp.sql that has been installedBefore you run Export, ensure that there is sufficient disk or tape storage space to write the export file. If there is not enough space, Export terminates with a write-failure error.
You can use table sizes to estimate the maximum space needed. You can find table sizes in the USER_SEGMENTS view of the Oracle data dictionary. The following query displays disk usage for all tables:
SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';
The result of the query does not include disk space used for data stored in LOB (large object) or VARRAY columns or in partitioned tables.
| See Also:
Oracle9i Database Reference for more information about dictionary views |
To use Export, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs.
If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it.
The following schema names are reserved and will not be processed by Export:
You can invoke Export and specify parameters by using any of the following methods:
Before you use one of these methods to invoke Export, be sure to read the descriptions of the available parameters. See Export Parameters.
You can specify all valid parameters and their values from the command line using the following syntax:
expusername/passwordPARAMETER=value
or
expusername/passwordPARAMETER=(value1,value2,...,valuen)
The number of parameters cannot exceed the maximum length of a command line on the system.
If you prefer to let Export prompt you for the value of each parameter, you can use the following syntax to start Export in interactive mode:
expusername/password
Export will display commonly used parameters with a request for you to enter a value. This method exists for backward compatibility and is not recommended because it provides less functionality than the other methods. See Using the Interactive Method.
You can specify all valid parameters and their values in a parameter file. Storing the parameters in a file allows them to be easily modified or reused, and is the recommended method for invoking Export. If you use different parameters for different databases, you can have multiple parameter files.
Create the parameter file using any flat file text editor. The command-line option PARFILE=filename tells Export to read the parameters from the specified file rather than from the command line. For example:
exp PARFILE=filenameexpusername/passwordPARFILE=filename
The first example does not specify the username/password on the command line to illustrate that you can specify them in the parameter file, although, for security reasons, this is not recommended.
The syntax for parameter file specifications is one of the following:
PARAMETER=valuePARAMETER=(value) PARAMETER=(value1,value2,...)
The following example shows a partial parameter file listing:
FULL=y FILE=dba.imp GRANTS=y INDEXES=y CONSISTENT=y
You can add comments to the parameter file by preceding them with the pound (#) sign. Export ignores all characters to the right of the pound (#) sign.
You can specify a parameter file at the same time that you are entering parameters on the command line. In fact, you can specify the same parameter in both places. The position of the PARFILE parameter and other parameters on the command line determines which parameters take precedence. For example, assume the parameter file params.dat contains the parameter INDEXES=y and Export is invoked with the following line:
exp username/password PARFILE=params.dat INDEXES=n
In this case, because INDEXES=n occurs after PARFILE=params.dat, INDEXES=n overrides the value of the INDEXES parameter in the parameter file.
See Also:
|
SYSDBA is used internally and has specialized functions; its behavior is not the same as for generalized users. Therefore, you should not typically need to invoke Export as SYSDBA, except in the following situations:
To invoke Export as SYSDBA, use the following syntax, adding any desired parameters or parameter filenames:
exp \'username/password AS SYSDBA\'
Optionally, you could also specify an instance name:
exp \'username/password@instance AS SYSDBA\'
If either the username or password is omitted, Export will prompt you for it.
This example shows the entire connect string enclosed in single quotation marks and backslashes. This is because the string, AS SYSDBA, contains a blank, a situation for which most operating systems require that the entire connect string be placed in quotation marks or marked as a literal by some method. Some operating systems also require that quotation marks on the command line be preceded by an escape character. In this example, backslashes are used as the escape character. If the backslashes were not present, the command-line parser that Export uses would not understand the quotation marks and would remove them before calling Export.
See your Oracle operating system-specific documentation for more information about special and reserved characters on your system.
If you prefer to use the Export interactive mode, see Using the Interactive Method for more information.
The Export utility provides four modes of export:
All users can export in table mode and user mode. Users with the EXP_FULL_DATABASE role (privileged users) can export in all modes. Table 1-1 shows the objects that are exported and imported in each mode. Also see Processing Restrictions.
To specify one of these modes, use the appropriate parameter (FULL, OWNER, TABLES, or TABLESPACES) when you invoke Export. See Export Parameters for information on the syntax for each of these parameters.
You can use conventional path Export or direct path Export to export in any of the first three modes. The differences between conventional path Export and direct path Export are described in Conventional Path Export Versus Direct Path Export.
See Also:
|
| Object | Table Mode | User Mode | Full Database Mode | Tablespace Mode |
|---|---|---|---|---|
|
Analyze cluster |
No |
Yes |
Yes |
No |
|
Analyze tables/statistics |
Yes |
Yes |
Yes |
Yes |
|
Application contexts |
No |
No |
Yes |
No |
|
Auditing information |
Yes |
Yes |
Yes |
No |
|
B-tree, bitmap, domain functional indexes |
YesFoot 1 |
YesFootref 1 |
Yes |
Yes |
|
Cluster definitions |
No |
Yes |
Yes |
Yes |
|
Column and table comments |
Yes |
Yes |
Yes |
Yes |
|
Database links |
No |
Yes |
Yes |
No |
|
Default roles |
No |
No |
Yes |
No |
|
Dimensions |
No |
Yes |
Yes |
No |
|
Directory aliases |
No |
No |
Yes |
No |
|
External tables (without data) |
Yes |
Yes |
Yes |
No |
|
Foreign function libraries |
No |
Yes |
Yes |
No |
|
Indexes owned by users other than table owner |
Yes (Privileged users only) |
Yes |
Yes |
Yes |
|
Index types |
No |
Yes |
Yes |
No |
|
Java resources and classes |
No |
Yes |
Yes |
No |
|
Job queues |
No |
Yes |
Yes |
No |
|
Nested table data |
Yes |
Yes |
Yes |
Yes |
|
Object grants |
Yes (Only for tables and indexes) |
Yes |
Yes |
Yes |
|
Object type definitions used by table |
Yes |
Yes |
Yes |
Yes |
|
Object types |
No |
Yes |
Yes |
No |
|
Operators |
No |
Yes |
Yes |
No |
|
Password history |
No |
No |
Yes |
No |
|
Postinstance actions and objects |
No |
No |
Yes |
No |
|
Postschema procedural actions and objects |
No |
Yes |
Yes |
No |
|
Posttable actions |
Yes |
Yes |
Yes |
Yes |
|
Posttable procedural actions and objects |
Yes |
Yes |
Yes |
Yes |
|
Preschema procedural objects and actions |
No |
Yes |
Yes |
No |
|
Pretable actions |
Yes |
Yes |
Yes |
Yes |
|
Pretable procedural actions |
Yes |
Yes |
Yes |
Yes |
|
Private synonyms |
No |
Yes |
Yes |
No |
|
Procedural objects |
No |
Yes |
Yes |
No |
|
Profiles |
No |
No |
Yes |
No |
|
Public synonyms |
No |
No |
Yes |
No |
|
Referential integrity constraints |
Yes |
Yes |
Yes |
No |
|
Refresh groups |
No |
Yes |
Yes |
No |
|
Resource costs |
No |
No |
Yes |
No |
|
Role grants |
No |
No |
Yes |
No |
|
Roles |
No |
No |
Yes |
No |
|
Rollback segment definitions |
No |
No |
Yes |
No |
|
Security policies for table |
Yes |
Yes |
Yes |
Yes |
|
Sequence numbers |
No |
Yes |
Yes |
No |
|
Snapshot logs |
No |
Yes |
Yes |
No |
|
Snapshots and materialized views |
No |
Yes |
Yes |
No |
|
System privilege grants |
No |
No |
Yes |
No |
|
Table constraints (primary, unique, check) |
Yes |
Yes |
Yes |
Yes |
|
Table data |
Yes |
Yes |
Yes |
No |
|
Table definitions |
Yes |
Yes |
Yes |
Yes |
|
Tablespace definitions |
No |
No |
Yes |
No |
|
Tablespace quotas |
No |
No |
Yes |
No |
|
Triggers |
Yes |
YesFoot 2 |
YesFoot 3 |
Yes |
|
Triggers owned by other users |
Yes (Privileged users only) |
No |
No |
No |
|
User definitions |
No |
No |
Yes |
No |
|
User proxies |
No |
No |
Yes |
No |
|
User views |
No |
Yes |
Yes |
No |
|
User-stored procedures, packages, and functions |
No |
Yes |
Yes |
No |
You can export tables, partitions, and subpartitions in the following ways:
In all modes, partitioned data is exported in a format such that partitions or subpartitions can be imported selectively.
In table-level Export, you can export an entire table (partitioned or nonpartitioned) along with its indexes and other table-dependent objects. If the table is partitioned, all of its partitions and subpartitions are also exported. This applies to both direct path Export and conventional path Export. You can perform a table-level export in any Export mode.
In partition-level Export, you can export one or more specified partitions or subpartitions of a table. You can only perform a partition-level export in Table mode.
For information on how to specify table-level and partition-level Exports, see TABLES.
The following restrictions apply when you process data with the Export and Import utilities:
RELY keyword lose the RELY attribute when they are exported.Export provides online help. Enter exp help=y on the command line to invoke it.
The following diagrams show the syntax for the parameters that you can specify in the parameter file or on the command line. Following the diagrams are descriptions of each parameter.
Text description of the illustration expstart.gif
Text description of the illustration expmodes.gif
Text description of the illustration exptsopt.gif
Text description of the illustration expopts.gif
Text description of the illustration expopts_cont.gif
Text description of the illustration expfilop.gif
Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default value for this parameter.
Specifies the size, in bytes, of the buffer used to fetch rows. As a result, this parameter determines the maximum number of rows in an array fetched by Export. Use the following formula to calculate the buffer size:
buffer_size = rows_in_array * maximum_row_size
If you specify zero, the Export utility fetches only one row at a time.
Tables with columns of type LONG, LOB, BFILE, REF, ROWID, LOGICAL ROWID, or DATE are fetched one row at a time.
|
Note: The |
This section shows an example of how to calculate buffer size.
The following table is created:
CREATE TABLE sample (name varchar(30), weight number);
The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weight column is 22 (the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.
Therefore, the maximum row size is 56 (30+2+22+2).
To perform array operations for 100 rows, a buffer size of 5600 should be specified.
Default: y
Specifies how Export and Import manage the initial extent for table data.
The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon Import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.
If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.
|
Note: LOB data is not compressed. For LOB data, values of initial extent size and next extent size at the time of export are used. |
Default: n
Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command. You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.
If you use CONSISTENT=n, each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. If a table is partitioned, each partition is exported as a separate transaction.
Therefore, if nested tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.
Table 1-2 shows a sequence of events by two users: user1 exports partitions in a table and user2 updates data in that table.
| TIme Sequence | User1 | User2 |
|---|---|---|
|
1 |
Begins export of TAB:P1 |
No activity |
|
2 |
No activity |
Updates TAB:P2 |
|
3 |
Ends export of TAB:P1 |
No activity |
|
4 |
Exports TAB:P2 |
No activity |
If the export uses CONSISTENT=y, none of the updates by user2 are written to the export file.
If the export uses CONSISTENT=n, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file because the update transaction is committed before the export of TAB:P2 begins. As a result, the user2 transaction is only partially recorded in the export file, making it inconsistent.
If you use CONSISTENT=y and the volume of updates is large, the rollback segment usage will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.
Keep in mind the following points about using CONSISTENT=y:
CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.SYS schema within recursive SQL. In such situations, the use of CONSISTENT=y will be ignored. Oracle Corporation recommends that you avoid making metadata changes during an export process in which CONSISTENT=y is selected.For example, export the emp and dept tables together in a consistent export, and then export the remainder of the database in a second pass.
If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results.
To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.
Default: y
Specifies whether or not the Export utility exports table constraints.
Default: n
Specifies whether you use direct path or conventional path Export.
Specifying DIRECT=y causes Export to extract data by reading the data directly, bypassing the SQL command-processing layer (evaluating buffer). This method can be much faster than a conventional path Export.
For information about direct path Exports, including security and performance considerations, see Invoking a Direct Path Export.
Default: 0 (zero)
Specifies that Export should display a progress meter in the form of a period for n number of rows exported. For example, if you specify FEEDBACK=10, Export displays a period each time 10 rows are exported. The FEEDBACK value applies to all tables being exported; it cannot be set on a per-table basis.
Default: expdat.dmp
Specifies the names of the export files. The default extension is .dmp, but you can specify any extension. Because Export supports multiple export files (see the parameter FILESIZE), you can specify multiple filenames to be used. For example:
exp scott/tiger FILE = dat1.dmp, dat2.dmp, dat3.dmp FILESIZE=2048
When Export reaches the value you have specified for the maximum FILESIZE, Export stops writing to the current file, opens another export file with the next name specified by the FILE parameter, and continues until complete or the maximum value of FILESIZE is again reached. If you do not specify sufficient export filenames to complete the export, Export will prompt you to provide additional filenames.
Default: Data is written to one file until the maximum size, as specified in Table 1-3, is reached.
Export supports writing to multiple export files, and Import can read from multiple export files. If you specify a value (byte limit) for the FILESIZE parameter, Export will write only the number of bytes you specify to each dump file.
When the amount of data Export must write exceeds the maximum value you specified for FILESIZE, it will get the name of the next export file from the FILE parameter (see FILE for more information) or, if it has used all the names specified in the FILE parameter, it will prompt you to provide a new export filename. If you do not specify a value for FILESIZE (note that a value of 0 is equivalent to not specifying FILESIZE), then Export will write to only one file, regardless of the number of files specified in the FILE parameter.
|
Note: If the space requirements of your export file exceed the available disk space, Export will abort, and you will have to repeat the Export after making sufficient disk space available. |
The FILESIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits.
Table 1-3 shows that the maximum size for dump files depends on the operating system you are using and on the release of the Oracle database server that you are using.
The FILESIZE value can also be specified as a number followed by KB (number of kilobytes). For example, FILESIZE=2KB is the same as FILESIZE=2048. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to obtain the final file size (FILESIZE=2048B is the same as FILESIZE=2048).
Default: none
Specifies the system change number (SCN) that Export will use to enable flashback. The export operation is performed with data consistent as of this specified SCN.
| See Also:
Oracle9i Application Developer's Guide - Fundamentals for more information about using flashback |
Default: none
Specifies a time. Export finds the SCN that most closely matches the specified time. This SCN is used to enable flashback. The export operation is performed with data consistent as of this SCN.
| See Also:
Oracle9i Application Developer's Guide - Fundamentals for more information about using flashback |
Default: n
Indicates that the Export is a full database mode Export (that is, it exports the entire database). Specify FULL=y to export in full database mode. You need to have the EXP_FULL_DATABASE role to export in this mode.
Default: y
Specifies whether or not the Export utility exports object grants. The object grants that are exported depend on whether you use full database mode or user mode. In full database mode, all grants on a table are exported. In user mode, only those granted by the owner of the table are exported. System privilege grants are always exported.
Default: none
Displays a description of the Export parameters. Enter exp help=y on the command line to invoke it.
Default: y
Specifies whether or not the Export utility exports indexes.
Default: none
Specifies a filename to receive informational and error messages. For example:
exp SYSTEM/password LOG=export.log
If you specify this parameter, messages are logged in the log file and displayed to the terminal display.
Default: n
Specifies whether or not the Export utility uses the SET TRANSACTION READ ONLY statement to ensure that the data exported is consistent to a single point in time and does not change during the export. If OBJECT_CONSISTENT is set to y, each object is exported in its own read-only transaction, even if it is partitioned. In contrast, if you use the CONSISTENT parameter, then there is only one read-only transaction.
Default: none
Indicates that the Export is a user-mode Export and lists the users whose objects will be exported. If the user initiating the export is the DBA, multiple users may be listed.
Default: none
Specifies a filename for a file that contains a list of Export parameters. For more information on using a parameter file, see Invoking Export.
Default: none
This parameter allows you to select a subset of rows from a set of tables when doing a table mode export. The value of the query parameter is a string that contains a WHERE clause for a SQL SELECT statement that will be applied to all tables (or table partitions) listed in the TABLE parameter.
For example, if user scott wants to export only those employees whose job title is SALESMAN and whose salary is less than 1600, he could do the following (this example is UNIX-based):
exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"
When executing this query, Export builds a SQL SELECT statement similar to the following:
SELECT * FROM emp WHERE job='SALESMAN' and sal <1600;
The values specified for the QUERY parameter are applied to all tables (or table partitions) listed in the TABLE parameter. For example, the following statement will unload rows in both emp and bonus that match the query:
exp scott/tiger TABLES=emp,bonus QUERY=\"WHERE job=\'SALESMAN\' and sal\<1600\"
Again, the SQL statements that Export executes are similar to the following:
SELECT * FROM emp WHERE job='SALESMAN' and sal <1600; SELECT * FROM bonus WHERE job='SALESMAN' and sal <1600;
If a table is missing the columns specified in the QUERY clause, an error message will be produced, and no rows will be exported for the offending table.
QUERY cannot be specified for full, user, or tablespace mode exports.QUERY must be applicable to all specified tables.QUERY cannot be specified in a direct path export (DIRECT=y)QUERY cannot be specified for tables with inner nested tables.QUERY export.Default: operating system-dependent
Specifies the length, in bytes, of the file record. The RECORDLENGTH parameter is necessary when you must transfer the export file to another operating system that uses a different default value.
If you do not define this parameter, it defaults to your platform-dependent value for buffer size. For more information about the buffer size default value, see your Oracle operating system-specific documentation.
You can set RECORDLENGTH to any value equal to or greater than your system's buffer size. (The highest value is 64 KB.) Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to the disk. It does not affect the operating system file block size.
See your Oracle operating system-specific documentation to determine the proper value or to create a file with a different record size.
Default: n
The RESUMABLE parameter is used to enable and disable resumable space allocation. Because this parameter is disabled by default, you must set RESUMABLE=y in order to use its associated parameters, RESUMABLE_NAME and RESUMABLE_TIMEOUT.
See Also:
|
Default: 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'
The value for this parameter identifies the statement that is resumable. This value is a user-defined text string that is inserted in either the USER_RESUMABLE or DBA_RESUMABLE view to help you identify a specific resumable statement that has been suspended.
This parameter is ignored unless the RESUMABLE parameter is set to y to enable resumable space allocation.
Default: 7200 seconds (2 hours)
The value of the parameter specifies the time period during which an error must be fixed. If the error is not fixed within the timeout period, execution of the statement is aborted.
This parameter is ignored unless the RESUMABLE parameter is set to y to enable resumable space allocation.
Default: y
Specifies whether or not the rows of table data are exported.
Default: ESTIMATE
Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE. See the Import parameter STATISTICS and Importing Statistics.
In some cases, Export will place the precalculated statistics in the export file as well as the ANALYZE statements to regenerate the statistics.
However, the precalculated optimizer statistics will not be used at export time if a table has columns with system-generated names.
The precalculated optimizer statistics are flagged as questionable at export time if:
NCHAR character set does not match the server character set or NCHAR character setQUERY clause is specifiedDefault: none
Specifies that the Export is a table-mode Export and lists the table names and partition and subpartition names to export. You can specify the following when you specify the name of the table:
schemaname specifies the name of the user's schema from which to export the table or partition. The schema names ORDSYS, MDSYS, CTXSYS, and ORDPLUGINS are reserved by Export.tablename specifies the name of the table or tables to be exported. Table-level export lets you export entire partitioned or nonpartitioned tables. If a table in the list is partitioned and you do not specify a partition name, all its partitions and subpartitions are exported.
The table name can contain any number of '%' pattern matching characters, which can each match zero or more characters in the table name against the table objects in the database. All the tables in the relevant schema that match the specified pattern are selected for export, as if the respective table names were explicitly specified in the parameter.
partition_name indicates that the export is a partition-level Export. Partition-level Export lets you export one or more specified partitions or subpartitions within a table.The syntax you use to specify the preceding is in the form:
schemaname.tablename:partition_nameschemaname.tablename:subpartition_name
If you use tablename:partition_name, the specified table must be partitioned, and partition_name must be the name of one of its partitions or subpartitions. If the specified table is not partitioned, the partition_name is ignored and the entire table is exported.
See Example Export Session Using Partition-Level Export for several examples of partition-level exports.
The following restrictions apply to table names:
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.
For example, if the parameter file contains the following line, Export interprets everything on the line after emp# as a comment and does not export the tables dept and mydata:
TABLES=(emp#, dept, mydata)
However, given the following line, the Export utility exports all three tables because emp# is enclosed in quotation marks:
TABLES=("emp#", dept, mydata)
For a TABLES parameter that specifies multiple schema.tablename:(sub)partition_name arguments, Export attempts to purge duplicates before processing the list of objects.
Default: none
The TABLESPACES parameter specifies that all tables in the tablespace be exported to the Export dump file. This includes all tables contained in the list of tablespaces and all tables that have a partition located in the list of tablespaces. Indexes are exported with their tables, regardless of where the index is stored.
You must have the EXP_FULL_DATABASE role to use TABLESPACES to export all tables in the tablespace.
When TABLESPACES is used in conjunction with TRANSPORT_TABLESPACE=y, you can specify a limited list of tablespaces to be exported from the database to the export file.
Default: n
When specified as y, this parameter enables the export of transportable tablespace metadata.
Default: y
Specifies whether or not the Export utility exports triggers.
Default: FALSE
When TTS_FULL_CHECK is set to TRUE, Export verifies that a recovery set (set of tablespaces to be recovered) has no dependencies (specifically, IN pointers) on objects outside the recovery set, and vice versa.
Default: none
Specifies the username/password (and optional connect string) of the user performing the export. If you omit the password, Export will prompt you for it.
USERID can also be:
username/password AS SYSDBA
or
username/password@instance AS SYSDBA
If you connect as user SYS, you must also specify AS SYSDBA in the connect string. Your operating system may require you to treat AS SYSDBA as a special string, in which case the entire string would be enclosed in quotation marks. See Invoking Export As SYSDBA for more information.
See Also:
|
Specifies the maximum number of bytes in an export file on each volume of tape.
The VOLSIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits. See your Oracle operating system-specific documentation for more information.
The VOLSIZE value can be specified as a number followed by KB (number of kilobytes). For example, VOLSIZE=2KB is the same as VOLSIZE=2048. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to get the final file size (VOLSIZE=2048B is the same as VOLSIZE=2048).
Certain parameters can conflict with each other. For example, because specifying TABLES can conflict with an OWNER specification, the following command causes Export to terminate with an error:
expSYSTEM/passwordOWNER=jonesTABLES=scott.emp
Similarly, OWNER and TABLES conflict with FULL=y.
This section provides examples of the following types of Export sessions:
In each example, you are shown how to use both the command-line method and the parameter file method.
Only users with the DBA role or the EXP_FULL_DATABASE role can export in full database mode. In this example, an entire database is exported to the file dba.dmp with all GRANTS and all data.
> exp SYSTEM/password PARFILE=params.dat
The params.dat file contains the following information:
FILE=dba.dmpGRANTS=yFULL=yROWS=y
> exp SYSTEM/password FULL=y FILE=dba.dmp GRANTS=y ROWS=y
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 16:52:15 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . . exporting table AQ$_INTERNET_AGENTS 0 rows exported . . exporting table AQ$_INTERNET_AGENT_PRIVS 0 rows exported . . exporting table DEF$_AQCALL 0 rows exported . . exporting table DEF$_AQERROR 0 rows exported . . exporting table DEF$_CALLDEST 0 rows exported . . exporting table DEF$_DEFAULTDEST 0 rows exported . . exporting table DEF$_DESTINATION 0 rows exported . . exporting table DEF$_ERROR 0 rows exported . . exporting table DEF$_LOB 0 rows exported . . exporting table DEF$_ORIGIN 0 rows exported . . exporting table DEF$_PROPAGATOR 0 rows exported . . exporting table DEF$_PUSHED_TRANSACTIONS 0 rows exported . . exporting table DEF$_TEMP$LOB 0 rows exported . . exporting table LOGSTDBY$APPLY_MILESTONE 0 rows exported . . exporting table LOGSTDBY$APPLY_PROGRESS . . exporting partition P0 0 rows exported . . exporting table LOGSTDBY$EVENTS 0 rows exported . . exporting table LOGSTDBY$PARAMETERS 0 rows exported . . exporting table LOGSTDBY$PLSQL 0 rows exported . . exporting table LOGSTDBY$SCN 0 rows exported . . exporting table LOGSTDBY$SKIP 0 rows exported . . exporting table LOGSTDBY$SKIP_TRANSACTION 0 rows exported . . exporting table REPCAT$_AUDIT_ATTRIBUTE 2 rows exported . . exporting table REPCAT$_AUDIT_COLUMN 0 rows exported . . exporting table REPCAT$_COLUMN_GROUP 0 rows exported . . exporting table REPCAT$_CONFLICT 0 rows exported . . exporting table REPCAT$_DDL 0 rows exported . . exporting table REPCAT$_EXCEPTIONS 0 rows exported . . exporting table REPCAT$_EXTENSION 0 rows exported . . exporting table REPCAT$_FLAVORS 0 rows exported . . exporting table REPCAT$_FLAVOR_OBJECTS 0 rows exported . . exporting table REPCAT$_GENERATED 0 rows exported . . exporting table REPCAT$_GROUPED_COLUMN 0 rows exported . . exporting table REPCAT$_INSTANTIATION_DDL 0 rows exported . . exporting table REPCAT$_KEY_COLUMNS 0 rows exported . . exporting table REPCAT$_OBJECT_PARMS 0 rows exported . . exporting table REPCAT$_OBJECT_TYPES 28 rows exported . . exporting table REPCAT$_PARAMETER_COLUMN 0 rows exported . . exporting table REPCAT$_PRIORITY 0 rows exported . . exporting table REPCAT$_PRIORITY_GROUP 0 rows exported . . exporting table REPCAT$_REFRESH_TEMPLATES 0 rows exported . . exporting table REPCAT$_REPCAT 0 rows exported . . exporting table REPCAT$_REPCATLOG 0 rows exported . . exporting table REPCAT$_REPCOLUMN 0 rows exported . . exporting table REPCAT$_REPGROUP_PRIVS 0 rows exported . . exporting table REPCAT$_REPOBJECT 0 rows exported . . exporting table REPCAT$_REPPROP 0 rows exported . . exporting table REPCAT$_REPSCHEMA 0 rows exported . . exporting table REPCAT$_RESOLUTION 0 rows exported . . exporting table REPCAT$_RESOLUTION_METHOD 19 rows exported . . exporting table REPCAT$_RESOLUTION_STATISTICS 0 rows exported . . exporting table REPCAT$_RESOL_STATS_CONTROL 0 rows exported . . exporting table REPCAT$_RUNTIME_PARMS 0 rows exported . . exporting table REPCAT$_SITES_NEW 0 rows exported . . exporting table REPCAT$_SITE_OBJECTS 0 rows exported . . exporting table REPCAT$_SNAPGROUP 0 rows exported . . exporting table REPCAT$_TEMPLATE_OBJECTS 0 rows exported . . exporting table REPCAT$_TEMPLATE_PARMS 0 rows exported . . exporting table REPCAT$_TEMPLATE_REFGROUPS 0 rows exported . . exporting table REPCAT$_TEMPLATE_SITES 0 rows exported . . exporting table REPCAT$_TEMPLATE_STATUS 3 rows exported . . exporting table REPCAT$_TEMPLATE_TARGETS 0 rows exported . . exporting table REPCAT$_TEMPLATE_TYPES 2 rows exported . . exporting table REPCAT$_USER_AUTHORIZATIONS 0 rows exported . . exporting table REPCAT$_USER_PARM_VALUES 0 rows exported . . exporting table SQLPLUS_PRODUCT_PROFILE 0 rows exported . about to export OUTLN's tables via Conventional Path ... . . exporting table OL$ 0 rows exported . . exporting table OL$HINTS 0 rows exported . . exporting table OL$NODES 0 rows exported . about to export DBSNMP's tables via Conventional Path ... . about to export SCOTT's tables via Conventional Path ... . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported . . exporting table SALGRADE 5 rows exported . about to export ADAMS's tables via Conventional Path ... . about to export JONES's tables via Conventional Path ... . about to export CLARK's tables via Conventional Path ... . about to export BLAKE's tables via Conventional Path ... . . exporting table DEPT 8 rows exported . . exporting table MANAGER 4 rows exported . exporting synonyms . exporting views . exporting referential integrity constraints . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting user history table . exporting default and system auditing options . exporting statistics Export terminated successfully without warnings.
User mode exports can be used to back up one or more database users. For example, a DBA may want to back up the tables of deleted users for a period of time. User mode is also appropriate for users who want to back up their own data or who want to move objects from one owner to another. In this example, user scott is exporting his own tables.
> exp scott/tiger PARFILE=params.dat
The params.dat file contains the following information:
FILE=scott.dmpOWNER=scottGRANTS=y ROWS=y COMPRESS=y
> exp scott/tiger FILE=scott.dmp OWNER=scott GRANTS=y ROWS=y COMPRESS=y
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 17:01:06 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set . exporting pre-schema procedural objects and actions . exporting foreign function library names for user SCOTT . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user SCOTT About to export SCOTT's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export SCOTT's tables via Conventional Path ... . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported . . exporting table SALGRADE 5 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings.
In table mode, you can export table data or the table definitions. (If no rows are exported, the CREATE TABLE statement is placed in the export file, with grants and indexes, if they are specified.)
A user with the EXP_FULL_DATABASE role can use table mode to export tables from any user's schema by specifying TABLES=schemaname.tablename.
If schemaname is not specified, Export defaults to the previous schema name from which an object was exported. If there is not a previous object, Export defaults to the exporter's schema. In the following example, Export defaults to the SYSTEM schema for table a and to scott for table c:
>exp SYSTEM/passwordTABLES=(a, scott.b, c, mary.d)
A user with the EXP_FULL_DATABASE role can also export dependent objects that are owned by other users. A nonprivileged user can export only dependent objects for the specified tables that the user owns.
Exports in table mode do not include cluster definitions. As a result, the data is exported as unclustered tables. Thus, you can use table mode to uncluster tables.
In this example, a DBA exports specified tables for two users.
> exp SYSTEM/password PARFILE=params.dat
The params.dat file contains the following information:
FILE=expdat.dmp TABLES=(scott.emp,blake.dept) GRANTS=y INDEXES=y
> exp SYSTEM/password FILE=expdat.dmp TABLES=(scott.emp,blake.dept) GRANTS=y-
INDEXES=y
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 17:01:35 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to SCOTT . . exporting table EMP 14 rows exported Current user changed to BLAKE . . exporting table DEPT 8 rows exported Export terminated successfully without warnings.
In this example, user blake exports selected tables that he owns.
> exp blake/paper PARFILE=params.dat
The params.dat file contains the following information:
FILE=blake.dmp TABLES=(dept,manager) ROWS=y COMPRESS=y
> exp blake/paper FILE=blake.dmp TABLES=(dept, manager) ROWS=y COMPRESS=y
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 17:01:38 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... . . exporting table DEPT 8 rows exported . . exporting table MANAGER 4 rows exported Export terminated successfully without warnings.
In this example, pattern matching is used to export various tables for users scott and blake.
> exp SYSTEM/password PARFILE=params.dat
The params.dat file contains the following information:
FILE=misc.dmp TABLES=(scott.%P%,blake.%,scott.%S%)
> exp SYSTEM/password FILE=misc.dmp TABLES=(scott.%P%,blake.%,scott.%S%)
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 17:01:40 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to SCOTT . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported Current user changed to BLAKE . . exporting table DEPT 8 rows exported . . exporting table MANAGER 4 rows exported Current user changed to SCOTT . . exporting table BONUS 0 rows exported . . exporting table SALGRADE 5 rows exported Export terminated successfully without warnings.
In partition-level export, you can specify the partitions and subpartitions of a table that you want to export.
Assume emp is a table that is partitioned on employee name. There are two partitions, m and z. As this example shows, if you export the table without specifying a partition, all of the partitions are exported.
> exp scott/tiger PARFILE=params.dat
The params.dat file contains the following:
TABLES=(emp) ROWS=y
> exp scott/tiger TABLES=emp rows=y
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 17:01:53 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... . . exporting table EMP . . exporting partition M 8 rows exported . . exporting partition Z 6 rows exported Export terminated successfully without warnings.
Assume emp is a table that is partitioned on employee name. There are two partitions, m and z. As this example shows, if you export the table and specify a partition, only the specified partition is exported.
> exp scott/tiger PARFILE=params.dat
The params.dat file contains the following:
TABLES=(emp:m) ROWS=y
> exp scott/tiger TABLES=emp:m rows=y
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 17:01:55 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... . . exporting table EMP . . exporting partition M 8 rows exported Export terminated successfully without warnings.
Assume emp is a partitioned table with two partitions, m and z. Table emp is partitioned using the composite method. Partition m has subpartitions sp1 and sp2, and partition z has subpartitions sp3 and sp4. As the example shows, if you export the composite partition m, all its subpartitions (sp1 and sp2) will be exported. If you export the table and specify a subpartition (sp4), only the specified subpartition is exported.
> exp scott/tiger PARFILE=params.dat
The params.dat file contains the following:
TABLES=(emp:m,emp:sp4) ROWS=y
> exp scott/tiger TABLES=(emp:m, emp:sp4) ROWS=y
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 17:22:47 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... . . exporting table EMP . . exporting composite partition M . . exporting subpartition SP1 1 rows exported . . exporting subpartition SP2 3 rows exported . . exporting composite partition Z . . exporting subpartition SP4 1 rows exported Export terminated successfully without warnings.
Starting Export from the command line with no parameters initiates the interactive method. The command-line interactive method does not provide prompts for all Export functionality and is provided only for backward compatibility. If you want to use an interactive interface to the Export utility, it is recommended that you use the Oracle Enterprise Manager (OEM) Export Wizard.
If you do not specify a username/password combination on the command line, the Export utility prompts you for this information.
When you invoke Export interactively, the response given by Export depends on what you enter at the command line. Table 1-4 shows the possibilities.
In Export interactive mode, you are not prompted to specify whether you want to connect as SYSDBA or @instance. You must specify AS SYSDBA and/or @instance with the username.
Additionally, if you omit the password and allow Export to prompt you for it, you cannot specify the @instance string as well. You can specify @instance only with username.
Before you invoke Export using AS SYSDBA, be sure to read Invoking Export As SYSDBA for information about correct command-line syntax.
After Export is invoked, it displays the following prompts. You may not see all prompts in a given Export session because some prompts depend on your responses to other prompts. Some prompts show a default answer. If the default is acceptable, press Enter.
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 17:02:03 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Enter array fetch buffer size: 4096 > Export file: expdat.dmp > (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > Export grants (yes/no): yes > Export table data (yes/no): yes > Compress extents (yes/no): yes > Export done in WE8DEC character set and AL16UTF16 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . . exporting table AQ$_INTERNET_AGENTS 0 rows exported . . exporting table AQ$_INTERNET_AGENT_PRIVS 0 rows exported . . exporting table DEF$_AQCALL 0 rows exported . . exporting table DEF$_AQERROR 0 rows exported . . exporting table DEF$_CALLDEST 0 rows exported . . exporting table DEF$_DEFAULTDEST 0 rows exported . . exporting table DEF$_DESTINATION 0 rows exported . . exporting table DEF$_ERROR 0 rows exported . . exporting table DEF$_LOB 0 rows exported . . exporting table DEF$_ORIGIN 0 rows exported . . exporting table DEF$_PROPAGATOR 0 rows exported . . exporting table DEF$_PUSHED_TRANSACTIONS 0 rows exported . . exporting table DEF$_TEMP$LOB 0 rows exported . . exporting table LOGSTDBY$APPLY_MILESTONE 0 rows exported . . exporting table LOGSTDBY$APPLY_PROGRESS . . exporting partition P0 0 rows exported . . exporting table LOGSTDBY$EVENTS 0 rows exported . . exporting table LOGSTDBY$PARAMETERS 0 rows exported . . exporting table LOGSTDBY$PLSQL 0 rows exported . . exporting table LOGSTDBY$SCN 0 rows exported . . exporting table LOGSTDBY$SKIP 0 rows exported . . exporting table LOGSTDBY$SKIP_TRANSACTION 0 rows exported . . exporting table REPCAT$_AUDIT_ATTRIBUTE 2 rows exported . . exporting table REPCAT$_AUDIT_COLUMN 0 rows exported . . exporting table REPCAT$_COLUMN_GROUP 0 rows exported . . exporting table REPCAT$_CONFLICT 0 rows exported . . exporting table REPCAT$_DDL 0 rows exported . . exporting table REPCAT$_EXCEPTIONS 0 rows exported . . exporting table REPCAT$_EXTENSION 0 rows exported . . exporting table REPCAT$_FLAVORS 0 rows exported . . exporting table REPCAT$_FLAVOR_OBJECTS 0 rows exported . . exporting table REPCAT$_GENERATED 0 rows exported . . exporting table REPCAT$_GROUPED_COLUMN 0 rows exported . . exporting table REPCAT$_INSTANTIATION_DDL 0 rows exported . . exporting table REPCAT$_KEY_COLUMNS 0 rows exported . . exporting table REPCAT$_OBJECT_PARMS 0 rows exported . . exporting table REPCAT$_OBJECT_TYPES 28 rows exported . . exporting table REPCAT$_PARAMETER_COLUMN 0 rows exported . . exporting table REPCAT$_PRIORITY 0 rows exported . . exporting table REPCAT$_PRIORITY_GROUP 0 rows exported . . exporting table REPCAT$_REFRESH_TEMPLATES 0 rows exported . . exporting table REPCAT$_REPCAT 0 rows exported . . exporting table REPCAT$_REPCATLOG 0 rows exported . . exporting table REPCAT$_REPCOLUMN 0 rows exported . . exporting table REPCAT$_REPGROUP_PRIVS 0 rows exported . . exporting table REPCAT$_REPOBJECT 0 rows exported . . exporting table REPCAT$_REPPROP 0 rows exported . . exporting table REPCAT$_REPSCHEMA 0 rows exported . . exporting table REPCAT$_RESOLUTION 0 rows exported . . exporting table REPCAT$_RESOLUTION_METHOD 19 rows exported . . exporting table REPCAT$_RESOLUTION_STATISTICS 0 rows exported . . exporting table REPCAT$_RESOL_STATS_CONTROL 0 rows exported . . exporting table REPCAT$_RUNTIME_PARMS 0 rows exported . . exporting table REPCAT$_SITES_NEW 0 rows exported . . exporting table REPCAT$_SITE_OBJECTS 0 rows exported . . exporting table REPCAT$_SNAPGROUP 0 rows exported . . exporting table REPCAT$_TEMPLATE_OBJECTS 0 rows exported . . exporting table REPCAT$_TEMPLATE_PARMS 0 rows exported . . exporting table REPCAT$_TEMPLATE_REFGROUPS 0 rows exported . . exporting table REPCAT$_TEMPLATE_SITES 0 rows exported . . exporting table REPCAT$_TEMPLATE_STATUS 3 rows exported . . exporting table REPCAT$_TEMPLATE_TARGETS 0 rows exported . . exporting table REPCAT$_TEMPLATE_TYPES 2 rows exported . . exporting table REPCAT$_USER_AUTHORIZATIONS 0 rows exported . . exporting table REPCAT$_USER_PARM_VALUES 0 rows exported . . exporting table SQLPLUS_PRODUCT_PROFILE 0 rows exported . about to export OUTLN's tables via Conventional Path ... . . exporting table OL$ 0 rows exported . . exporting table OL$HINTS 0 rows exported . . exporting table OL$NODES 0 rows exported . about to export DBSNMP's tables via Conventional Path ... . about to export SCOTT's tables via Conventional Path ... . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported . . exporting table SALGRADE 5 rows exported . about to export ADAMS's tables via Conventional Path ... . about to export JONES's tables via Conventional Path ... . about to export CLARK's tables via Conventional Path ... . about to export BLAKE's tables via Conventional Path ... . . exporting table DEPT 8 rows exported . . exporting table MANAGER 4 rows exported . exporting synonyms . exporting views . exporting referential integrity constraints . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting user history table . exporting default and system auditing options . exporting statistics Export terminated successfully without warnings.
Keep in mind the following points when you use the interactive method:
For example, if beth is a privileged user exporting in table mode, Export assumes that all tables are in the beth schema until another schema is specified. Only a privileged user (someone with the EXP_FULL_DATABASE role) can export tables in another user's schema.
This section describes the different types of messages issued by Export and how to save them in a log file.
You can capture all Export messages in a log file, either by using the LOG parameter (see LOG) or, for those systems that permit it, by redirecting the Export output to a file. The Export utility writes a log of detailed information about successful unloads and any errors that may occur. Refer to your Oracle operating system-specific documentation for information on redirecting output.
Export does not terminate after recoverable errors. For example, if an error occurs while exporting a table, Export displays (or logs) an error message, skips to the next table, and continues processing. These recoverable errors are known as warnings.
Export also issues a warning whenever it encounters an invalid object.
For example, if a nonexistent table is specified as part of a table-mode export, the Export utility exports all other tables. Then it issues a warning and terminates successfully.
Some errors are nonrecoverable and terminate the Export session. These errors typically occur because of an internal problem or because a resource, such as memory, is not available or has been exhausted. For example, if the catexp.sql script is not executed, Export issues the following nonrecoverable error message:
EXP-00024: Export views not installed, please notify your DBA
When an export completes without errors, Export displays the following message:
Export terminated successfully without warnings
If one or more recoverable errors occurs but Export is able to continue to completion, Export displays the following message:
Export terminated successfully with warnings
If a nonrecoverable error occurs, Export terminates immediately and displays the following message:
Export terminated unsuccessfully
| See Also:
Oracle9i Database Error Messages and your Oracle operating system-specific documentation |
Export provides the results of an export operation immediately upon completion. Depending on the platform, Export may report the outcome in a process exit code as well as recording the results in the log file. This enables you to check the outcome from the command line or script. Table 1-5 shows the exit codes that get returned for various results.
| Result | Exit Code |
|---|---|
|
Export terminated successfully without warnings |
|
|
Export terminated successfully with warnings |
|
|
Export terminated unsuccessfully |
|
For UNIX, the exit codes are as follows:
EX_SUCC 0 EX_OKWARN 0 EX_FAIL 1
Export provides two methods for exporting table data:
Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into a buffer cache, and rows are transferred to the evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file.
Direct path Export is much faster than conventional path Export because data is read from disk into the buffer cache and rows are transferred directly to the Export client. The evaluating buffer is bypassed. The data is already in the format that Export expects, thus avoiding unnecessary data conversion. The data is transferred to the Export client, which then writes the data into the export file.
Figure 1-2 illustrates how data extraction differs between conventional path Export and direct path Export.
Text description of the illustration sut81019.gif
To invoke a direct path Export, you must use either the command-line method or a parameter file. You cannot invoke a direct path Export using the interactive method.
To use direct path Export, specify the DIRECT=y parameter on the command line or in the parameter file. The default is DIRECT=n, which extracts the table data using the conventional path.
Additionally, be aware that the Export parameter BUFFER applies only to conventional path Exports. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.
In versions of Export prior to 8.1.5, you could not use direct path Export for tables containing objects and LOBs. If you tried to, their rows were not exported. This behavior has changed. Rows in tables that contain objects and LOBs will now be exported using conventional path, even if direct path was specified. Import will correctly handle these conventional path tables within direct path dump files.
Virtual Private Database (VPD) and Oracle Label Security are not enforced during direct path Exports.
The following users are exempt from Virtual Private Database and Oracle Label Security enforcement regardless of the export mode, application, or utility used to extract data from the database:
SYSEXEMPT ACCESS POLICY privilege, either directly or through a database roleThis means that any user who is granted the EXEMPT ACCESS POLICY privilege is completely exempt from enforcement of VPD and Oracle Label Security. This is a powerful privilege and should be carefully managed. This privilege does not affect the enforcement of traditional object privileges such as SELECT, INSERT, UPDATE, and DELETE. These privileges are enforced even if a user has been granted the EXEMPT ACCESS POLICY privilege.
You may be able to improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export. Your exact performance gain depends upon the following factors:
DB_BLOCK_SIZEThe following values are generally recommended for RECORDLENGTH:
This section describes factors to take into account when you use Export and Import across a network.
Because the export file is in binary format, use a protocol that supports binary transfers to prevent corruption of the file when you transfer it across a network. For example, use FTP or a similar file transfer protocol to transmit the file in binary mode. Transmitting export files in character mode causes errors when the file is imported.
With Oracle Net, you can perform exports and imports over a network. For example, if you run Export locally, you can write data from a remote Oracle database into a local export file. If you run Import locally, you can read data into a remote Oracle database.
To use Import with Oracle Net, include the connection qualifier string @connect_string when entering the username/password in the exp or imp command. For the exact syntax of this clause, see the user's guide for your Oracle Net protocol.
This section describes the behavior of Export and Import with respect to globalization support.
The Export utility always exports user data, including Unicode data, in the character sets of the Export server. The character sets are specified at database creation.
The Import utility automatically converts the data to the character sets of the Import server.
Some 8-bit characters can be lost (that is, converted to 7-bit equivalents) when you import an 8-bit character set export file. This occurs if the client system has a native 7-bit character set or if the NLS_LANG operating system environment variable is set to a 7-bit character set. Most often, you notice that accented characters lose their accent mark.
Both Export and Import provide descriptions of any required character set conversion before exporting or importing the data.
|
Note: Data definition language (DDL), such as a |
If the export character set has a different sorting order than the import character set, then tables that are partitioned on character columns may yield unpredictable results. For example, consider the following table definition, which is produced on a database having an ASCII character set:
CREATE TABLE partlist ( part VARCHAR2(10), partno NUMBER(2) ) PARTITION BY RANGE (part) ( PARTITION part_low VALUES LESS THAN ('Z') TABLESPACE tbs_1, PARTITION part_mid VALUES LESS THAN ('z') TABLESPACE tbs_2, PARTITION part_high VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_3 );
This partitioning scheme makes sense because z comes after Z in ASCII character sets.
When this table is imported into a database based upon an EBCDIC character set, all of the rows in the part_mid partition will migrate to the part_low partition because z comes before Z in EBCDIC character sets. To obtain the desired results, the owner of partlist must repartition the table following the import.
You can use instance affinity to associate jobs with instances in databases you plan to export and import. Be aware that there may be some compatibility issues if you are using a combination of releases 8.0, 8.1, and 9i.
The following sections describe points you should consider when you export particular database objects.
If transactions continue to access sequence numbers during an export, sequence numbers can be skipped. The best way to ensure that sequence numbers are not skipped is to ensure that the sequences are not accessed during the export.
Sequence numbers can be skipped only when cached sequence numbers are in use. When a cache of sequence numbers has been allocated, they are available for use in the current database. The exported value is the next sequence number (after the cached values). Sequence numbers that are cached, but unused, are lost when the sequence is imported.
On export, LONG datatypes are fetched in sections. However, enough memory must be available to hold all of the contents of each row, including the LONG data.
LONG columns can be up to 2 gigabytes in length.
All data in a LOB column does not need to be held in memory at the same time. LOB data is loaded and unloaded in sections.
The contents of foreign function libraries are not included in the export file. Instead, only the library specification (name, location) is included in full database and user mode export. You must move the library's executable files and update the library specification if the database is moved to a new location.
If the data you are exporting contains offline bitmapped tablespaces, Export will not be able to export the complete tablespace definition and will display an error message. You can still import the data; however, you must first create the offline bitmapped tablespaces before importing to prevent DDL commands that may reference the missing tablespaces from failing.
Directory alias definitions are included only in a full database mode Export. To move a database to a new location, the database administrator must update the directory aliases to point to the new location.
Directory aliases are not included in user or table mode Export. Therefore, you must ensure that the directory alias has been created on the target system before the directory alias is used.
The export file does not hold the contents of external files referenced by BFILE columns or attributes. Instead, only the names and directory aliases for files are copied on Export and restored on Import. If you move the database to a location where the old directories cannot be used to access the included files, the database administrator (DBA) must move the directories containing the specified files to a new location where they can be accessed.
The contents of external tables are not included in the export file. Instead, only the table specification (name, location) is included in full database and user mode export. You must manually move the external data and update the table specification if the database is moved to a new location.
In all Export modes, the Export utility includes information about object type definitions used by the tables being exported. The information, including object name, object identifier, and object geometry, is needed to verify that the object type on the target system is consistent with the object instances contained in the export file. This ensures that the object types needed by a table are created with the same object identifier at import time.
Note, however, that in table, user, and tablespace mode, the export file does not include a full object type definition needed by a table if the user running Export does not have execute access to the object type. In this case, only enough information is written to verify that the type exists, with the same object identifier and the same geometry, on the import target system.
The user must ensure that the proper type definitions exist on the target system, either by working with the DBA to create them, or by importing them from full database or user mode exports performed by the DBA.
It is important to perform a full database mode export regularly to preserve all object type definitions. Alternatively, if object type definitions from different schemas are used, the DBA should perform a user mode export of the appropriate set of users. For example, if table1 belonging to user scott contains a column on blake's type type1, the DBA should perform a user mode export of both blake and scott to preserve the type definitions needed by the table.
Inner nested table data is exported whenever the outer containing table is exported. Although inner nested tables can be named, they cannot be exported individually.
Queues are implemented on tables. The export and import of queues constitutes the export and import of the underlying queue tables and related dictionary tables. You can export and import queues only at queue table granularity.
When you export a queue table, both the table definition information and queue data are exported. Because the queue table data is exported as well as the table definition, the user is responsible for maintaining application-level data integrity when queue table data is imported.
You should be cautious when exporting compiled objects that reference a name used as a synonym and as another object. Exporting and importing these objects will force a recompilation that could result in changes to the object definitions.
The following example helps to illustrate this problem:
CREATE PUBLIC SYNONYM emp FOR scott.emp; CONNECT blake/paper;
CREATE TRIGGER t_emp BEFORE INSERT ON emp BEGIN NULL; END;
CREATE VIEW emp AS SELECT * FROM dual;
If the database in the preceding example were exported, the reference to emp in the trigger would refer to blake's view rather than to scott's table. This would cause an error when Import tried to reestablish the t_emp trigger.
If an export operation attempts to export a synonym named DBMS_JAVA when there is no corresponding DBMS_JAVA package or when Java is either not loaded or loaded incorrectly, the export will terminate unsuccessfully. The error messages that are generated include, but are not limited to, the following: EXP-00008, ORA-00904, and ORA-29516.
If Java is enabled, make sure that both the DBMS_JAVA synonym and DBMS_JAVA package are created and valid before rerunning the export.
If Java is not enabled, remove Java-related objects before rerunning the export.
You can export tables with fine-grained access control policies enabled. When doing so, consider the following:
EXECUTE privilege on the DBMS_RLS package so that the tables' security policies can be reinstated). If a user without the correct privileges attempts to export a table with fine-grained access policies enabled, only those rows that the exporter is privileged to read will be exported.SELECT statement, then conventional path Export may not export the entire table because fine-grained access may rewrite the query.SYS, or a user with the EXPORT_FULL_DATABASE role enabled or who has been granted EXEMPT ACCESS POLICY, can perform direct path Exports on tables having fine-grained access control.
| See Also:
Oracle9i Application Developer's Guide - Fundamentals for more information about fine-grained access control |
The transportable tablespace feature enables you to move a set of tablespaces from one Oracle database to another.
To move or copy a set of tablespaces, you must make the tablespaces read-only, copy the datafiles of these tablespaces, and use Export and Import to move the database information (metadata) stored in the data dictionary. Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying flat binary files, such as the operating system copying facility, binary-mode FTP, or publishing on CD-ROMs.
After copying the datafiles and exporting the metadata, you can optionally put the tablespaces in read/write mode.
Export provides the following parameters to enable export of transportable tablespace metadata.
See TABLESPACES and TRANSPORT_TABLESPACE for more information.
See Also:
|
To extract metadata from a source database, Export uses queries that contain ordering clauses (sort operations). For these queries to succeed, the user performing the export must be able to allocate on-disk sort segments. For these sort segments to be allocated in a read-only database, the user's temporary tablespace should be set to point at a temporary, locally managed tablespace.
| See Also:
Oracle9i Data Guard Concepts and Administration for more information on setting up this environment |
When you use the Export and Import utilities to migrate a large database, it may be more efficient to partition the migration into multiple export and import jobs. If you decide to partition the migration, be aware of the following advantages and disadvantages.
Partitioning a migration has the following advantages:
Partitioning a migration has the following disadvantages:
To perform a database migration in a partitioned manner, take the following steps:
All exports can be done in parallel. When the import of full.dmp completes, all remaining imports can also be done in parallel.
This section describes compatibility issues that relate to using different releases of Export and the Oracle database server.
Whenever you are moving data between different releases of the Oracle database server, the following basic rules apply:
For example, to create an export file for an import into a higher release database, use a version of the Export utility that is equal to the source database. Conversely, to create an export file for an import into a lower release database, use a version of the Export utility that is equal to the version of the target database. The following information is for specific versions:
catexp6.sql script on the Oracle7 database. This script creates the export views that make the database look, to Export, like an Oracle version 6 database.catexp7.sql script on the Oracle8i database. This script creates the export views that make the database look, to Export, like an Oracle8i database.
The following restrictions apply when you are using different releases of Export and Import:
Table 1-6 shows some examples of which Export and Import releases to use when moving data between different releases of the Oracle database server.
| Export from->Import to | Use Export Release | Use Import Release |
|---|---|---|
|
7.3.3 -> 8.1.6 |
7.3.3 |
8.1.6 |
|
8.1.6 -> 8.1.6 |
8.1.6 |
8.1.6 |
|
8.1.5 -> 8.0.6 |
8.0.6 |
8.0.6 |
|
8.1.7 -> 8.1.6 |
8.1.6 |
8.1.6 |
|
8.1.7 -> 7.3.4 |
7.3.4Foot 1 |
7.3.4 |
|
9.0.1 -> 8.1.6 |
8.1.6 |
8.1.6 |
|
9.0.1 -> 9.0.2 |
9.0.1 |
9.0.2 |
You do not need to take any special steps to create an Oracle release 8.0 export file from an Oracle9i database. However, the following features are not supported when you use Export release 8.0 on an Oracle9i database:
DIRECT=y).CALL statement are not exported.ROWID columns, primary key refs, or user-defined OID columns will not be exported.CLOBs, collection enhancements, and LOB-storage clauses for VARRAY columns or nested table enhancements are not exported.This section briefly discusses some of the error messages you might receive if incompatible releases or versions of the Export utility and the Oracle database server are used.
EXP-24: Export views not installed, please notify your DBA Cause: The necessary export views were not installed. Action: Ask the DBA to install the required views.
EXP-23: Import views not installed, please notify your DBA Cause: The necessary import views were not installed. Action: Ask the DBA to install the required views.
EXP-37: Export views not compatible with database version Cause: The Export utility is at a higher version than the database version. Action: Use the same version of the Export utility as the database.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|