Release 8.1.5
A67792-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Utilities Release 8.1.5 A67792-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, "Import"). This chapter covers the following topics:
Export 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. Export extracts the object definitions and table data from an Oracle database and stores them in an Oracle binary-format Export dump file located typically on disk or tape.
Such files can then be FTPed or physically transported (in the case of tape) to a different site and used, with the Import utility, to transfer data between databases that are on machines not connected via a network or as backups in addition to normal backup procedures.
The Export and Import utilities can also facilitate certain aspects of Oracle Advanced Replication functionality like offline instantiation. See Oracle8i Replication for more information.
Note that, Export dump files can only be read by the Oracle utility, Import (see Chapter 2, "Import"). If you need to read load data from ASCII fixed-format or delimited files, see Part II, SQL*Loader of this manual.
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, and then written to the Export file. See Figure 1-1.
Note: If you are working with the Advanced Replication Option, refer to the information about migration and compatibility in Oracle8i Replication.
The following Export features are new as of this release of Oracle8i:
Export files are stored in Oracle-binary format. Export files generated by Export cannot be read by utilities other than Import. Export files created by Export cannot be read by earlier versions of the Import utility. However, Import can read files written by the current and previous releases of Export, but cannot read files in other formats. To load data from ASCII fixed-format or delimited files, see Part II of this manual for information about SQL*Loader.
You can, however, display the contents of an export file by using the Import SHOW parameter. For more information, see SHOW.
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.
Note also that the following schema names are reserved and will not be processed by Export:
The Export utility provides four modes of export. All users can export in table mode and user mode. A user with the EXP_FULL_DATABASE role (a privileged user) can export in table mode, user mode, tablespace, and full database mode. The database objects that are exported depend on the mode you choose. Tablespace mode allows you to move a set of tablespaces from one Oracle database to another. See Transportable Tablespaces and the Oracle8i Administrator's Guide for details about how to move or copy tablespaces to another database. For an introduction to the transportable tablespaces feature, see Oracle8i Concepts.
See Export Parameters for information on specifying each mode.
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 Direct Path Export.
Table 1-1 shows the objects that are exported and imported in each mode.
Table 1-1 Objects Exported and Imported in Each Mode
In table-level Export, an entire partitioned or non-partitioned table, along with its indexes and other table-dependent objects, is exported. All the partitions and subpartitions of a partitioned table are exported. (This applies to both direct path Export and conventional path Export.) All Export modes (full, user, table, transportable tablespace) support table-level Export.
In partition-level Export, the user can export one or more specified partitions or subpartitions of a table. Full database, user, and transportable tablespace mode Export do not support partition-level Export; only table mode Export does. Because incremental Exports (incremental, cumulative, and complete) can be done only in full database mode, partition-level Export cannot be specified for incremental exports.
In all modes, partitioned data is exported in a format such that partitions or subpartitions can be imported selectively.
For information on how to specify a partition-level Export, see TABLES.
This section describes how to use the Export utility, including what you need to do before you begin exporting and how to invoke Export.
To 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 file names 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:
Before 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. Table sizes can be found 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 partitions.
See the Oracle8i Reference for more information about dictionary views.
You can invoke Export in one of the following ways:
exp username/password PARFILE=filename
PARFILE is a file containing the export parameters you typically use. If you use different parameters for different databases, you can have multiple parameter files. This is the recommended method.
exp username/password
followed by the parameters you need.
Note: The number of parameters cannot exceed the maximum length of a command line on the system.
exp username/password to begin an interactive session and let Export prompt you for the information it needs. The interactive method provides less functionality than the parameter-driven method. It exists for backward compatibility.
You can use a combination of the first and second options. That is, you can list parameters both in the parameters file and 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 what parameters override others. For example, assume the parameters file params.dat contains the parameter INDEXES=Y and Export is invoked with the following line:
exp system/manager 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 PARFILE.
You can specify the username and password in the parameter file, although, for security reasons, this is not recommended. If you omit the username/password combination, Export prompts you for it.
See Export Parameters for descriptions of the parameters.
To see how to specify an export from a database that is not the default database, refer to Exporting and Importing with Net8.
Typically, you should not need to invoke Export as SYSDBA. You may need to do so at the request of Oracle technical support. To invoke Export as SYSDBA, use the following syntax:
exp username/password AS SYSDBA
or, optionally
exp username/password@instance AS SYSDBA
Note: Since the string "AS SYSDBA" contains a blank, most operating systems require that entire string 'username/password AS SYSDBA' be placed in quotes or marked as a literal by some method. Note that some operating systems also require that quotes on the command line be escaped as well. Please see your operating system-specific documentation for information about special and reserved characters on your system. Note that if either the username or password is omitted, Export will prompt you for it.
If you prefer to use the Export interactive mode, please see Interactively Invoking Export as SYSDBA for more information.
Export provides online help. Enter exp help=y on the command line to see a help screen like the one shown below.
When you invoke the help display, you will see something similar to the following:
> exp help=y Export: Release 8.1.5.0.0 - Production on Wed Oct 28 15:00:10 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. You can let Export prompt you for parameters by entering the EXP command followed by your username/password: Example: EXP SCOTT/TIGER Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords: Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR) or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL export entire file (N) BUFFER size of data buffer OWNER list of owner usernames FILE output files (EXPDAT.DMP) TABLES list of table names COMPRESS import into one extent (Y) RECORDLENGTH length of IO record GRANTS export grants (Y) INCTYPE incremental export type INDEXES export indexes (Y) RECORD track incr. export (Y) ROWS export data rows (Y) PARFILE parameter filename CONSTRAINTS export constraints (Y) CONSISTENT cross-table consistency LOG log file of screen output STATISTICS analyze objects (ESTIMATE) DIRECT direct path (N) TRIGGERS export triggers (Y) FEEDBACK display progress every x rows (0) FILESIZE maximum size of each dump file QUERY select clause used to export a subset of a table VOLSIZE number of bytes to write to each tape volume The following keywords only apply to transportable tablespaces TRANSPORT_TABLESPACE export transportable tablespace metadata (N) TABLESPACES list of tablespaces to transport Export terminated successfully without warnings.
You can specify Export parameters in three ways: from a command-line entry, allow Export to prompt you for parameter values, or in the parameter file.
You can specify all valid parameters and their values from the command line using the following syntax:
exp KEYWORD=value
or
exp KEYWORD=(value1,value2,...,valuen)
If you prefer to let Export prompt you for the value of each parameter, you can use the following syntax:
exp username/password
Export will display each parameter with a request for you to enter a value.
The parameter file allows you to specify Export parameters in a file where they can easily be modified or reused. 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=filename exp username/password PARFILE=filename
The syntax for parameter file specifications is one of the following:
KEYWORD=value KEYWORD=(value) KEYWORD=(value1, value2, ...)
The following example shows a partial parameter file listing:
FULL=Y FILE=DBA.DMP GRANTS=Y INDEXES=Y CONSISTENT=Y
Additional Information: The maximum size of the parameter file may be limited by the operating system. The name of the parameter file is subject to the file naming conventions of the operating system. See your Oracle operating system-specific documentation for more information.
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.
The following three diagrams show the syntax for the parameters that you can specify in the parameter file or on the command line. The remainder of this section describes each parameter.
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 LONG, LOB, BFILE, REF, ROWID, LOGICAL ROWID, DATE, or type columns are fetched one row at a time.
Note: The BUFFER parameter applies only to conventional path Export. It has no effect on a direct path Export.
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: Although the actual consolidation is performed upon import, you can specify the COMPRESS parameter only when you export, not when you import. The Export utility, not the Import utility, generates the data definitions, including the storage parameter definitions. Thus, if you specify COMPRESS=Y when you export, you can import the data in consolidated form only.
Note: LOB data is not compressed. For LOB data, the original values of initial extent size and next extent size 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 export 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 specify CONSISTENT=N (the default), 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.
The following chart 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 |
|
|
2 |
|
Updates TAB:P2 |
|
3 |
Ends export of TAB:P1 |
|
|
4 |
Exports TAB:P2 |
|
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, USER2's 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 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:
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.
The "snapshot too old" error occurs when rollback space has been used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.
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.
Note: You cannot specify CONSISTENT=Y with an incremental export.
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 more information about direct path exports, see Direct Path Export.
Default: 0 (zero)
Specifies that Export should display a progress meter in the form of a dot for n number of rows exported. For example, if you specify FEEDBACK=10, Export displays a dot 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. Since Export supports multiple export files (see the parameter FILESIZE), you can specify multiple filenames to be used.
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 parameter FILE 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.
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 your export file(s) requirements exceed the available diskspace, Export will abort and you will have to repeat the Export after making sufficient diskspace available.
The FILESIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits.
Note: The maximum value that can be stored in a file is operating system dependent. You should verify this maximum value in your operating-system specific documentation before specifying FILESIZE. You should also ensure that the file size you specify for Export is supported on the system on which Import will run.
The FILESIZE value can also be specified as a number followed by K (number of kilobytes). For example, FILESIZE=2K is the same as FILESIZE=2048. Similarly, M specifies megabytes (1024 * 1024) while G specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to get the final file size (FILESIZE=2048b is the same as FILESIZE=2048)
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 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 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. Note that system privilege grants are always exported.
Default: N
Displays a help message with descriptions of the Export parameters.
Default: none
Specifies the type of incremental Export. The options are COMPLETE, CUMULATIVE, and INCREMENTAL. See Incremental, Cumulative, and Complete Exports for more information.
Default: Y
Specifies whether or not the Export utility exports indexes.
Default: none
Specifies a file name to receive informational and error messages. For example:
exp system/manager LOG=export.log
If you specify this parameter, messages are logged in the log file and displayed to the terminal display.
Default: undefined
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: undefined
Specifies a filename for a file that contains a list of Export parameters. For more information on using a parameter file, see Specifying Export Parameters on the Command Line or in the Parameter File.
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 which 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 greater than 1600, he could do the following (note that this example is Unix-based):
exp scott/tiger tables=emp query=\"where job=\'SALESMAN\' and sal\<1600\"
Note: Since the value of the QUERY parameter contains blanks, most operating systems require that the entire strings where job=\'SALESMAN\' and sal\<1600 be placed in double quotes or marked as a literal by some method. Also note that operating system reserved characters need to be escaped as are single quotes, double quotes and '<' in the Unix example above. Please see your operating system-specific documentation for information about special and reserved characters on your system.
When executing this command, Export builds a select statement similar to this:
SELECT * FROM EMP where job='SALESMAN' and sal <1600;
The QUERY is applied to all tables (or table partitions) listed in the TABLE parameter. So, for example,
exp scott/tiger tables=emp,dept query=\"where job=\'SALESMAN\' and sal\<1600\"
will unload rows in both EMP and DEPT that match the query.
Again, the SQL statements that Export executes are similar to these:
SELECT * FROM EMP where where job='SALESMAN' and sal <1600; SELECT * FROM DEPT where where job='SALESMAN' and sal <1600;
Default: Y
Indicates whether or not to record an incremental or cumulative export in the system tables SYS.INCEXP, SYS.INCFIL, and SYS.INCVID. For information about these tables, see System Tables.
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 BUFSIZ. For more information about the BUFSIZ default value, see your operating system-specific documentation.
You can set RECORDLENGTH to any value equal to or greater than your system's BUFSIZ. (The highest value is 64KB.) 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.
Note: You can use this parameter to specify the size of the Export I/O buffer.
Additional Information: See your Oracle operating system-specific documentation to determine the proper value or to create a file with a different record size.
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 Oracle8i Concepts manual for information about the optimizer and the statistics it uses. See also the Import parameter RECALCULATE_STATISTICS and Importing Statistics.
In some cases, Export will place the precomputed statistics in the export file as well as the ANALYZE commands to regenerate the statistics.
However, the precomputed optimizer statistics will not be used at export time if:
Note: Specifying ROWS=N does not preclude saving the precomputed statistics in the Export file. This allows you to tune plan generation for queries in a non-production database using statistics from a production database.
Default: 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:
The syntax you use to specify the above is in the form:
schema.tablename:partitionname schema.tablename:subpartitionname
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.
See Example Export Session Using Partition-Level Export for several examples of partition-level exports.
Additional Information: Some operating systems, such as UNIX, require that you use escape characters before special characters, such as a parenthesis, so that the character is not treated as a special character. On UNIX, use a backslash (\) as the escape character, as shown in the following example:
TABLES=\(EMP,DEPT\)
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, the Export utility interprets the rest of the line as a comment, unless the table name is enclosed in quotation marks.
For example, if the parameter file contains the following line, Export interprets everything on the line after EMP# as a comment, and therefore does not export the tables DEPT and MYDATA:
TABLES=(EMP#, DEPT, MYDATA)
However, given the following line, the Export utility exports all three tables:
TABLES=("EMP#", DEPT, MYDATA)
Attention: When you specify the table name using quotation marks, the name is case sensitive. The name must exactly match the table name stored in the database. By default, table names in a database are stored as uppercase.
In the previous example, a table named EMP# is exported, not a table named emp#. Because the tables DEPT and MYDATA are not specified in quotation marks, the names are not case sensitive.
Additional Information: Some operating systems require single quotation marks rather than double quotation marks, or vice versa; 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.
Default: none
When TRANSPORT_TABLESPACE is specified as Y, use this parameter to provide a list of the tablespaces to be exported from the database into the export file.
See Transportable Tablespaces for more information.
Default: N
When specified as Y, this parameter enables the export of transportable tablespace metadata. See the Oracle8i Administrator's Guide and Oracle8i Concepts for more information.
Default: none
Specifies the username/password (and optional connect string) of the user initiating 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
See Invoking Export as SYSDBA for more information. Note also that your operating system may require you to treat AS SYSDBA as a special string requiring you to enclose the entire string in quotes as described.
Optionally, you can specify the @connect_string clause for Net8. See the user's guide for your Net8 protocol for the exact syntax of @connect_string. See also Oracle8i Distributed Database Systems.
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 Operating system-specific documentation for more information.
The VOLSIZE value can be specified as number followed by K (number of kilobytes). For example, VOLSIZE=2K is the same as VOLSIZE=2048. Similarly, M specifies megabytes (1024 * 1024) while G 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:
exp system/manager OWNER=jones TABLES=scott.emp
Similarly, OWNER and TABLE conflict with FULL=Y.
Although ROWS=N and INCTYPE=INCREMENTAL can both be used, specifying ROWS=N (no data) defeats the purpose of incremental exports, which is to make a backup copy of tables that have changed.
The following examples show you how to use the command line and parameter file methods in the full database, user, and table modes.
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/manager parfile=params.dat
The params.dat file contains the following information:
FILE=dba.dmp GRANTS=y FULL=y ROWS=y
> exp system/manager full=Y file=dba.dmp grants=Y rows=Y
Export: Release 8.1.5.0.0 - Production on Fri Oct 30 09:34:00 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC 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 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 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 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 . 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 referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 Export terminated successfully without warnings.
Exports in user mode can 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.dmp OWNER=scott GRANTS=y ROWS=y COMPRESS=y
> exp scott/tiger file=scott.dmp owner=scott grants=Y rows=Y compress=y
Export: Release 8.1.5.0.0 - Production on Fri Oct 30 09:35:33 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC NCHAR character set . exporting pre-schema procedural objects and actions . exporting foreign function library names for user SCOTT . 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 snapshots . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions 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=schema.table
If schema is not specified, Export defaults to the previous schema 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/manager tables=(a, scott.b, c, mary.d)
A user without the EXP_FULL_DATABASE role can export only tables that the user owns. A user with the EXP_FULL_DATABASE role can export dependent objects that are owned by other users. A non-privileged 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/manager 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/manager tables=(scott.emp,blake.dept) grants=Y indexes=Y
Export: Release 8.1.5.0.0 - Production on Fri Oct 30 09:35:59 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC 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 8.1.5.0.0 - Production on Fri Oct 30 09:36:08 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC 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 partition-level export, you can specify the partitions and subpartitions of a table that you want to export.
Assume EMP is a partitioned table with two partitions M and Z (partitioned on employee name). 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 8.1.5.0.0 - Production on Fri Oct 30 09:36:23 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC 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 partitioned table with two partitions M and Z (partitioned on employee name). 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 8.1.5.0.0 - Production on Fri Oct 30 09:36:29 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC 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 composite method. M has subpartitions sp1 and sp2, and 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 partfile=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 8.1.5.0.0 - Development on Fri Oct 30 09:36:29 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Development With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC NCHAR character set About to export specified tables via Conventional Path ... . . exporting table EMP . . exporting composite partition M . . exporting subpartition SP1 4 rows exported . . exporting subpartition SP2 0 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 interactive method does not provide prompts for all Export functionality. The interactive method is provided only for backward compatibility.
If you do not specify a username/password combination on the command line, the Export utility prompts you for this information.
Typically, you should not need to invoke Export as SYSDBA. However, you may have occasion to do so under specific circumstances at the request of Oracle Technical Support.
If you use the Export interactive mode, you will not be prompted to specify whether you want to connect as SYSDBA or @instance. You must specify "AS SYSDBA" and/or "@instance" with the username.
The response to the Export interactive username prompt could be for example:
username/password@instance as sysdba username/password@instance username/password as sysdba username/password username@instance as sysdba (prompts for password) username@instance (prompts for password) username (prompts for password) username AS sysdba (prompts for password) / as sysdba (no prompt for password, OS authentication is used) / (no prompt for password, OS authentication is used) /@instance as sysdba (no prompt for password, OS authentication is used) /@instance (no prompt for password, OS authentication is used)
Note: 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.
Then, Export displays the following prompts:
Enter array fetch buffer size: 30720 > Export file: expdat.dmp > (1)E(ntire database), (2)U(sers), or (3)T(ables): (1)E > Export grants (yes/no): yes > Export table data (yes/no): yes > Compress extents (yes/no): yes > Export done in WE8DEC character set and WE8DEC 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 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 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 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 . 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 referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 Export terminated successfully without warnings.
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 [Return].
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 BETH's 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 discusses the messages that Export issues in certain situations.
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 Export's output to a file. The Export utility writes a log of detailed information about successful unloads and any errors that may occur. Refer to the operating system-specific Oracle documentation for information on redirecting output.
Export does not terminate after non-fatal 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 non-fatal errors are known as warnings.
Export issues a warning whenever it encounters an invalid object.
For example, if a non-existent 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, as shown in the following listing:
> exp scott/tiger tables=xxx,emp Export: Release 8.1.5.0.0 - Production on Fri Oct 30 09:38:11 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC NCHAR character set About to export specified tables via Conventional Path ... EXP-00011: SCOTT.XXX does not exist . . exporting table EMP 14 rows exported Export terminated successfully with warnings.
Some errors are fatal 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 fatal error message:
EXP-00024: Export views not installed, please notify your DBA
Additional Information: Messages are documented in the Oracle8i Error Messages manual and in your Oracle operating system-specific documentation.
When Export completes without errors, Export displays the message "Export terminated successfully without warnings." If one or more non-fatal errors occurs but Export is able to continue to completion, Export displays the message "Export terminated successfully with warnings." If a fatal error occurs, Export terminates immediately with the message "Export terminated unsuccessfully."
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 evaluation 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 extracts data much faster than a conventional path export. Direct path Export achieves this performance gain by reading data directly, bypassing the SQL Command Processing layer and saves on data copies whenever possible.
Figure 1-2 shows how data extraction differs between conventional path and direct path Export.
In a direct path Export, 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.
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.
Note: The Export parameter BUFFER applies only to conventional path exports. For direct path Export, use the parameter RECORDLENGTH to specify the size of the buffer that Export uses for writing to the export file.
Restrictions: You cannot export certain tables using direct path. For example, you cannot export tables using object features on LOBs. If you specify direct path for export, tables containing objects and LOBs will be exported using conventional path.
Direct path Export exports in the database server character set only. If the character set of the export session is not the same as the database character set when an export is initiated, Export displays a warning and aborts. Using the NLS_LANG parameter, specify the session character set to be the same as that of the database before retrying the export.
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 varies depending upon the following factors:
When using direct path Export, set the RECORDLENGTH parameter equal to the DB_BLOCK_SIZE database parameter, so that each table scan returns a full database block worth of data. If the data does not fit in the export I/O buffer, the Export utility performs multiple writes to the export file for each database block.
The following values are generally recommended for RECORDLENGTH:
Restriction: You cannot use the interactive method to invoke direct path Export.
Important: Incremental, cumulative, and complete Exports are obsolete features that will be phased out in a subsequent release. You should begin now to migrate to Oracle's Backup and Recovery Manager for database backups. See Oracle8i Backup and Recovery Guide for more information.
Restrictions:
If you use cumulative and incremental Exports, you should periodically perform a complete Export to create a base backup. Following the complete Export, perform frequent incremental Exports and occasional cumulative Exports. After a given period of time, you should begin the cycle again with another complete Export.
An incremental Export backs up only tables that have changed since the last incremental, cumulative, or complete Export. An incremental Export exports the table definition and all its data, not just the changed rows. Typically, you perform incremental Exports more often than cumulative or complete Exports.
Assume that a complete Export was done at Time 1. Figure 1-3 shows an incremental Export at Time 2, after three tables have been modified. Only the modified tables and associated indexes are exported.
Figure 1-4 shows another incremental Export at Time 3, after two tables have been modified since Time 2. Because Table 3 was modified a second time, it is exported at Time 3 as well as at Time 2.
A cumulative Export backs up tables that have changed since the last cumulative or complete Export. A cumulative Export compresses a number of incremental Exports into a single cumulative export file. It is not necessary to save incremental export files taken before a cumulative export because the cumulative export file replaces them.
Figure 1-5 shows a cumulative Export at Time 4. Tables 1 and 6 have been modified since Time 3. All tables modified since the complete Export at Time 1 are exported.
This cumulative export file includes the changes from the incremental Exports from Time 2 and Time 3. Table 3, which was modified at both times, occurs only once in the export file. In this way, cumulative exports save space over multiple incremental Exports.
A complete Export establishes a base for incremental and cumulative Exports. It is equivalent to a full database Export, except that it also updates the tables that track incremental and cumulative Exports.
Figure 1-6 shows a complete Export at Time 5. With the complete Export, all objects in the database are exported regardless of when (or if) they were modified.
The scenario described in this section shows how you can use cumulative and incremental Exports.
Assume that as manager of a data center, you do the following tasks:
Your export schedule follows:
DAY: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 X I I I I I I C I I I I I I C I I I I I I X Sun Sun Sun Sun
To restore through day 18, first you import the system information from the incremental Export taken on day 18. Then, you import the data from:
The incremental Exports on days 2 through 7 can be discarded on day 8, after the cumulative Export is done, because the cumulative Export incorporates all the incremental Exports. Similarly, the incremental Exports on days 9 through 14 can be discarded after the cumulative Export on day 15.
Note: The section INCTYPE explains the syntax to specify incremental, cumulative, and complete Exports.
The purpose of an incremental or cumulative Export is to identify and export only those database objects (such as clusters, tables, views, and synonyms) that have changed since the last Export. Each table is associated with other objects, such as the data, indexes, grants, audits, triggers, and comments.
The entire grant structure for tables or views is exported with the underlying base tables. Indexes are exported with their base table, regardless of who created the index. If the base view is included, "instead of" triggers on views are included.
Any modification (UPDATE, INSERT, or DELETE) on a table automatically qualifies that table for incremental Export. When a table is exported, all of its inner nested tables and LOB columns are exported also. Modifying an inner nested table column causes the outer table to be exported. Modifying a LOB column causes the entire table containing the LOB data to be exported.
Also, the underlying base tables and data are exported if database structures have changed in the following ways:
In addition to the base tables and data, the following data is exported:
Note: Export does not export grants on data dictionary views for security reasons that affect Import. If such grants were exported, access privileges would be changed and the user would not be aware of this. Also, not forcing grants on import allows the user more flexibility to set up appropriate grants on import.
The following example shows an incremental Export session after the tables SCOTT.EMP and SCOTT.DEPT are modified:
> exp system/manager full=y inctype=incremental Export: Release 8.1.5.0.0 - Production on Fri Oct 30 09:40:11 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production Export done in WE8DEC character set and WE8DEC 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 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 ... . about to export OUTLN's tables via Conventional Path ... . about to export DBSNMP's tables via Conventional Path ... . about to export SCOTT's tables via Conventional Path ... . . exporting table DEPT 8 rows exported . . exporting table EMP 23 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 referential integrity constraints . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting snapshots . 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 information about dropped objects Export terminated successfully without warnings.
The user SYS owns three tables (INCEXP, INCFIL, and INCVID) that are maintained by Export. These tables are updated when you specify RECORD=Y (the default). You should not alter these tables in any way.
The table SYS.INCEXP tracks which objects were exported in specific exports
This table contains the following columns:
The userid of the schema containing the table.
The object name. The primary key consists of OWNER#, NAME, and TYPE.
The type of the object (a code specifying INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, DIMENSION, OPERATOR, INDEXTYPE, SNAPSHOT, SNAPSHOT LOG, or PACKAGE BODY.
The date and time of the last cumulative export that included this object.
The date and time of the last incremental export that included this object.
The ID of the incremental or cumulative export, also found in the table SYS.INCFIL.
You can use this information in several ways. For example, you could generate a report from SYS.INCEXP after each export to document the export file. You can use the views DBA_EXP_OBJECTS, DBA_EXP_VERSION, and DBA_EXP_FILES to display information about incremental exports.
The table SYS.INCFIL tracks the incremental and cumulative exports and assigns a unique identifier to each.
This table contains the following columns:
The ID of the incremental or cumulative export, also found in the table SYS.INCEXP.
The type of export (incremental or cumulative).
The name of the export file.
The date of the export.
The USERNAME of the individual who initiated the export.
When you export with the parameter INCTYPE = COMPLETE, all the previous entries are removed from SYS.INCFIL and a new row is added specifying an "x" in the column EXPTYPE.
The table SYS.INCVID contains one column for the EXPID of the last valid export. This information determines the EXPID of the next export.
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 Net8 (and SQL*Net V2), 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 Export with Net8, include the @connect_string after the username/password when you enter the exp command, as shown in the following example:
exp scott/tiger@SUN2 FILE=export.dmp FULL=Y
Additional Information: For the exact syntax of this clause, see the user's guide for your Net8 or SQL*Net protocol. For more information on Net8 or Oracle Names, see the Net8 Administrator's Guide.
This section describes the behavior of Export and Import with respect to National Language Support (NLS).
In conventional mode, the Export utility writes to the export file using the character set specified for the user session, such as 7-bit ASCII, IBM Code Page 500 (EBCDIC), or an Oracle NLS character set like JA16EUC converting from the database server character set as necessary. Import then converts character data to the user-session character set if that character set is different from the one in the export file.
The export file identifies the character encoding scheme used for the character data in the file. If that character set is any single-byte character set (for example, EBCDIC or USASCII7), and if the character set used by the target database is also a single-byte character set, the data is automatically converted to the character encoding scheme specified for the user session during import, as specified by the NLS_LANG environment variable. After the data is converted to the session character set, it is then converted to the database character set.
During the conversion, any characters in the export file that have no equivalent in the target character set are replaced with a default character. (The default character is defined by the target character set.) To guarantee 100% conversion, the target character set should be a superset or equivalent of the source character set.
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 machine 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.
When you use direct path Export, the character set of the user's session must be the same as the database character set.
For more information, see the Oracle8i National Language Support Guide.
The Export utility always exports NCHAR data in the national character set of the Export server. (You specify the national character set with the NATIONAL character set statement at database creation.)
The Import utility automatically converts the data to the national character set of the Import server.
An export file that is produced with a multi-byte character set (for example, Chinese or Japanese) must be imported on a system that has the same character set or where the ratio of the width of the widest character in the import character set to the width of the smallest character in the export character set is 1. If the ratio is not 1, Import cannot translate the character data to the Import character set.
Caution: When the character set width differs between the export client and the export server, truncation of data can occur if conversion causes expansion of data. If truncation occurs, Export displays a warning message.
If you use instance affinity to associate jobs with instances in databases you plan to import/export, you should refer to the information in the Oracle8i Administrator's Guide, the Oracle8i Reference, and Oracle8i Parallel Server Concepts and Administration for information about use of instance affinity with the Import/Export utilities and, if you are using both release 8.0 and 8.1, to Oracle8i Migration for possible compatibility issues.
You can export tables with fine-grain access policies enabled.
Note, however, that the user who imports from an export file containing such tables must have the appropriate privileges (specifically, 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.
The following sections describe points you should take into consideration 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.
Note: 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. The database administrator must move the library and update the library specification if the database is moved to a new location.
If the data you are exporting contains offline bitmapped tablespace(s), 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 precreate the offline bitmapped tablespace(s) 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 must move the directories containing the specified files to a new location where they can be accessed.
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 SCOTT's table TABLE1 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 is 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.
See Oracle8i Application Developer's Guide - Advanced Queuing for more information.
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/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 binary files, such as the operating system copying facility, binary-mode FTP, or publishing on CDs.
After copying the datafiles and exporting the metadata, you can optionally put the tablespaces in read-write mode. See Transportable Tablespaces for more information about importing from an export file that contains transportable tablespace metadata.
Export provides the following parameter keywords you can use to enable export of transportable tablespace metadata.
See TRANSPORT_TABLESPACE and TABLESPACES for more information.
Additional Information: See the Oracle8i Administrator's Guide for details about managing transportable tablespaces. For an introduction to the transportable tablespaces feature, see Oracle8i Concepts.
This section describes the general behavior and restrictions of running an Export version that is different from Oracle8i.
In general, you can use the Export utility from any Oracle release 7 to export from an Oracle8i server and create an Oracle release 7 export file. (This procedure is described in Creating Oracle Release 7 Export Files from an Oracle8i Database.)
Oracle Version 6 (or earlier) Export cannot be used against an Oracle8i database.
Whenever a lower version Export utility runs with a higher version of the Oracle Server, categories of database objects that did not exist in the lower version are excluded from the export. (See Excluded Objects for a complete list of Oracle8i objects excluded from an Oracle release 7 Export.)
Attention: When backward compatibility is an issue, use the earlier release or version of the Export utility against the Oracle8i database, and use conventional path export.
Attention: Export files generated by Oracle8i Export, either direct path or conventional path, are incompatible with earlier releases of Import and can be imported only with Oracle8i Import.
Attempting to use a higher version of Export with an earlier Oracle server often produces the following error:
EXP-37: Database export views not compatible with Export utility
EXP-0: Export terminated unsuccessfully
The error occurs because views that the higher version of Export expects are not present. To avoid this problem, use the version of the Export utility that matches the Oracle server.
You do not need to take any special steps to create an Oracle Release 8.0 export file from an Oracle8i database, however, certain features are not supported.
You can create an Oracle release 7 export file from an Oracle8i database by running Oracle release 7 Export against an Oracle8i server. To do so, however, the user SYS must first run the CATEXP7.SQL script, which creates the export views that make the database look, to Export, like an Oracle release 7 database.
Note: An Oracle8i Export requires that the CATEXP.SQL script is run against the database before performing the Export. CATEXP.SQL is usually run automatically when the user SYS runs CATALOG.SQL to create the necessary views. CATEXP7.SQL, however, is not run automatically and must be executed manually. CATEXP7.SQL and CATEXP.SQL can be run in any order; after one of these scripts has been run, it need not be run again.
The Oracle release 7 Export utility produces an Oracle release 7 export file by issuing queries against the views created by CATEXP7.SQL. These views are fully compatible with Oracle release 7 and consequently do not contain the new Oracle8i objects listed in Creating Oracle Release 8.0 Export Files from an Oracle8i Database or the following Oracle8 objects:
If you want to use Enterprise Manager to export 7.3.2 databases, you must use Enterprise Manager version 1.4.0 or above.
