Oracle7 Server Utilities User's Guide | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
This chapter covers the following topics:
When Export is run against an Oracle database, objects, such as tables, are extracted followed by their related objects, like indexes, comments, and grants, if any, then written to the Export file. See Figure 1 - 1.
Figure 1 - 1. Exporting a Database
Because Export files are saved in Oracle-binary format, export files cannot be read by utilities other than Import. Similarly, Import can read files written by Export, but cannot read files in other formats. If you need to load data from ASCII fixed-format or delimited files, see Part II of this manual for information about SQL*Loader.
Note: Import automatically maps the data to the character set of its host system or to the character set specified for the user session if it is different from that used in the export file.
Additional Information: For the Trusted Oracle7 Server, export files are labeled by the operating system.
See your platform-specific Trusted Oracle7 Server documentation for details about file naming procedures.
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 have created a synonym for it.
You can, however, display the contents of an export file by using the Import SHOW option described. In addition, export files can be read only by Import. You cannot use them to transfer data to non-Oracle systems.
Additional Information: Refer to your Oracle operating system-specific documentation for information on redirecting output.
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 will issue a warning whenever an invalid object is encountered. That is, if a non-existent table was specified as part of a table-mode export, then all other tables would be exported, after which the export would issue a warning and terminate successfully, as shown in the following listing:
exp scott/tiger tables=xxx,emp
...
About to export specified tables ...
EXP-00011: SCOTT.XXX does not exist
EXP-00222:
System error message 2
. exporting table EMP 10 rows
exported
Export terminated successfully with warnings.
For example, you see the following message if you attempt to run Export without having created the necessary Export views:
EXP-00024: Export views not installed, please notify your DBA
Additional Information: Messages are documented in the Oracle7 Server Messages manual and in your Oracle operating system-specific documentation.
Table
Exports specified tables in the user's schema, rather than all tables. A privileged user can qualify the tables by specifying the schema containing them. The default is to export all tables belonging to the user doing the export.
Exports in table mode do not include cluster definitions. As a result, the data is imported into unclustered tables. Thus, you can use Export to uncluster tables when there is not enough free space in your database.
Exports all objects in a user's schema (such as tables, data, grants, and indexes). A privileged user exporting in user mode can export all objects in the schemas of a specified set of users.
Only users with the EXP_FULL_DATABASE role can export in this mode. All objects in the database are exported, except those in the schema of SYS. (Objects owned by SYS are generated during database creation for internal and administrative use. Because they are not exported, you should not create a user table or any other object under SYS.)
See "Export Parameters" for more information.
Table 1 - 1 shows the objects that are exported in each mode and the order in which they are exported:
Warning: LONG columns can be up to 2 gigabytes in length. Because they can be exported in sections, Oracle LONGs are always exportable.
To use Export or Import with SQL*Net, include the @connect_string when entering the EXP or IMP command. For the exact syntax of this clause, see the user's guide for your SQL*Net protocol. For more information on SQL*Net, see Understanding SQL*Net. If you use Oracle Names, see also the Oracle Names Administrator's Guide.
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, then the data is automatically converted to the character encoding scheme specified for the user session during import, as specified by the NLS_LANG parameter. After the data has been 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.
For multi-byte character sets, conversion is only performed if the length of the character string cannot change as a result of the conversion.
For more information, refer to the National Language Support section of the Oracle7 Server Reference.
This situation occurs because the eight-bit characters in the export file are converted to seven-bit characters via the client application. When sent to the database, the seven-bit characters are converted by the server into eight-bit characters. To avoid this situation, it is necessary to turn off one of these conversions. One possibility is to set NLS_LANG to the character set of the export file data.
When importing an Oracle version 6 export file with a character set different from that of the native operating system or the setting for NLS_LANG, you need to set the CHARSET import parameter to indicate the character set of the export file data. The CHARSET parameter is described .
Additional Information: 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 only needs to be run once. Once run, it need not be run before future exports. The following operations are performed to prepare the database for Export.
Before running Export, ensure that there is enough disk or tape storage space to write the export file to. If there is not enough space, Export will terminate 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 in the Oracle data dictionary (see the Oracle7 Server Administrator's Guide for more information).
If you omit username/password, Export will prompt you for it.
Figure 1 - 2. Export Help Screen
EXP PARFILE=filename
EXP username/password PARFILE=filename
The syntax for parameter file specifications is:
KEYWORD=value
or
KEYWORD=(value) or KEYWORD=(value1, value2, ...)
The following is an example of 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 and operating system file naming conventions will apply. See your Oracle operating system-specific documentation for more information.
Additional Information: Some operating systems require single vs. double quote marks, or vice versa. See your Oracle operating system-specific documentation.
For example, if the parameter file contains the line:
TABLES=(EMP#, DEPT, MYDATA)
nothing on the line after EMP# is seen as input by Export. As a result, DEPT and MYDATA are not exported.
Attention: When the name is specified in quotation marks, it is case-sensitive. The name must therefore exactly match the table name stored in the database. By default, database names are stored as uppercase.
USERID | RECORDLENGTH |
BUFFER | INCTYPE |
FILE | RECORD |
GRANTS | HELP |
INDEXES | LOG |
ROWS | CONSISTENT |
CONSTRAINTS | STATISTICS |
COMPRESS | FEEDBACK |
FULL | MLS* |
OWNER | MLS_LABEL_FORMAT* |
TABLES | |
*Trusted Oracle7 Server parameter | |
Default: operating system dependent
The parameter BUFFER determines the maximum number of rows in an array fetched by Export by specifying the size of the buffer. You can calculate the buffer size as follows:
buffer_size = rows_in_array * maximum_row_size
buffer_size is the size in bytes of the buffer into which data rows are fetched. If zero is specified, or if rows contain LONG data, only one row at a time is fetched.
Additional Information: See your Oracle operating system-specific documentation to determine the default value for this parameter.
Specifies how Export/Import will manage the initial extent for table data.
The default, COMPRESS=Y, causes Export to flag the table data for consolidation into one initial extent upon Import.
If COMPRESS=N is specified, Export will sum the lengths of all current extents on export and Import will use that value as the size of the initial extent for the imported table data. Note that, when a table has had many deletes, this size may be much larger than the table actually requires.
Note: Although used extents are consolidated into one extent upon import, you must request consolidation when exporting, because your request causes the table definition to be exported differently. Thus, if you request consolidation when exporting, you can import the data in consolidated form only.
Specifies whether Export will use the SET TRANSACTION READ ONLY statement to insure that the export is consistent to a single point in time. Specifying CONSISTENT=Y is important when other applications will be updating the database after an export has started because these transactions will be rolled back, if necessary.
If you specify CONSISTENT=Y, a rollback segment must be retained for the duration of the export to allow backing out of the effects of any uncommitted transactions when a table is exported. Note that, if the volume of updates is large, the rollback segment will itself be large. In addition, the export of each table will be slower, since the rollback segment must be scanned for uncommitted transactions.
Note: CONSISTENT cannot be used with an incremental export.
Suggestion: To minimize the time and space required for such exports, tables that need to remain consistent should be exported separately from those that do not.
For example, export the EMP and DEPT tables together in a consistent export, then export the remainder of the database in a second pass.
Exporting the minimum number of objects that must be guaranteed consistent helps to reduce the chances of encountering a "snapshot too old" error.
This 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, then a "snapshot too old" error results.
To avoid this error, minimize the time taken by a read-consistent export (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.
A flag to indicate whether to export table constraints.
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.
Performance can also be improved by using direct path export with the database in direct read mode. Contention for resources with other users is eliminated because database blocks are read into the private buffer cache, rather than a public buffer cache.
For more information about direct path exports, see page 1 - 32.
Default: 0 (zero)
Specifies that Export should display a progress meter in the form of a dot for x number of rows exported. For example, were you to specify FEEDBACK=10, Export would display a dot each time 10 rows had been exported. The FEEDBACK value applies to all tables being exported, it cannot be set on a per table basis.
Default: EXPDAT.DMP
The name of the export file. The default extension is .DMP, but you can specify any extension.
Specifies whether Export should export the entire database or not. Specify FULL=Y to export in full database mode (you must have the EXP_FULL_DATABASE role enabled to do this).
Specifies whether grants should be exported or not.
Displays a help message with descriptions of the Export parameters.
Specifies the type of incremental export. Options are COMPLETE, CUMULATIVE, and INCREMENTAL. See the section in this chapter called "Incremental, Cumulative and Complete Exports" for a description of these options.
Specifies whether indexes should be exported or not.
Specifies a file name to receive informational and error messages. For example:
EXP system/manager LOG=export.log
If this parameter is specified, messages are logged in the log file and displayed via the terminal display.
The Export utility can be used on any Trusted Oracle7 database to produce a normal export file. In DBMS Mandatory Access Control (MAC) mode, the MLS parameter can also be specified. With this parameter, the Multi-Level Security (MLS) labels on each row and object in the database are included in the export file. This parameter is further described in the Trusted Oracle7 Server Administrator's Guide.
MLS_LABEL_ FORMAT
This parameter allows you to override the default format specified in the database for human-readable MLS labels. The format you specify will be used in the export file instead of the format defined by the database initialization parameter. This parameter is further described in the Trusted Oracle7 Server Administrator's Guide.
Specifies a list of usernames whose objects will be exported. Specify OWNER=userlist to export in user mode.
A flag to indicate whether to record an incremental or cumulative export in database tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. See page 1 - 31.
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.
Additional Information: See your Oracle operating system-specific documentation for the system being exported to determine the proper value, or when you want to create a file with a different record size.
Specifies whether the rows of table data should be exported or not.
Specifies the type of database optimizer statistics to generate when the exported data is imported later. Options are ESTIMATE, COMPUTE, and NONE. See Chapter 13 of the Oracle7 Server Concepts manual for information about the optimizer.
Specifies list of table names to export. Specify TABLES=tablelist to export in table mode.
Specifies the username/password of the user initiating the export.
EXP system/manager OWNER=jones TABLES=scott.emp
Similarly, OWNER conflicts 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.
You may not see all the 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]. To end your input, enter a period (.) followed by [RETURN].
If you have not specified a username/password on the command line, EXPORT first prompts you for this information. Then the prompts described below are displayed.
The value specified here determines the space available to buffer rows. Export uses the array fetch program interface call to speed performance. The default is usually adequate, but the buffer must be at least as large as the longest row. If you enter zero, only one row at a time is fetched.
Specify the name of the export file (the default is EXPDAT.DMP). If you do not specify an extension, the file extension of the export file will default to .DMP.
Specify either (T)able or (U)ser mode. Privileged users can also choose (E)ntire database mode. The options above relate to the Export parameters TABLES, OWNER and FULL respectively.
Specify (Y)es to export grants or (N)o. The grants that will be exported depend on whether you are in 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.
Specify (Y)es, to export data in the tables with the table definitions. Specify (N)o, to export only the table definitions.
Specify (Y)es, to have the data for each exported table's used extents consolidated into one large initial extent when imported. Specifying (N)o, will cause all tables to be created with the storage values in effect when the table was exported.
Note: Although used extents are consolidated into one extent upon import, you must request consolidation when exporting, because your request causes the table definition to be exported differently. Thus, if you request consolidation when exporting, you can import the data in consolidated form only.
Specify the name of the schema to export. To indicate "no user" (and terminate the current Export session), press [RETURN].
Specify the name of the table to export. Entering a null table list causes all tables in the schema to be exported.
If no schema prefix is specified, Export defaults to the exporter's schema or the schema containing the last table exported in the current session.
For example, if BETH is a privileged user exporting in table mode, then Export assumes that all tables are in BETH's schema until another schema is specified. Only a privileged user (someone with BACKUP ANY TABLE privileges) can export tables in another user's schema.
Command Line Method
> exp system/manager full=Y file=dba.dmp
Interactive Method
> exp system/manager
...
Enter array fetch buffer size: 4096 > (RETURN)
Export file: EXPDAT.DMP> dba.dmp
E(ntire database), U(sers), T(ables): U> e
Export grants (Y/N): Y> y
Export table data (Y/N): Y> y
Compress extents (Y/N): Y> y
Export Messages
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 job queues
. exporting refresh groups and children
. exporting cluster definitions
. about to export SYSTEM's tables ...
. .exporting table DEF$_CALL 0 rows exported
. .exporting table DEF$_CALLDEST 0 rows exported
. .exporting table DEF$_DEFAULTDEST 0 rows exported
. .exporting table DEF$_ERROR 0 rows exported
. .exporting table DEF$_SCHEDULE 0 rows exported
. .exporting table DEF$_TRAN 0 rows exported
. .exporting table DEF$_TRANDEST 0 rows exported
. about to export SCOTT's tables ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 5 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. about to export ADAMS's tables ...
. about to export JONES's tables ...
. about to export CLARK's tables ...
. about to export BLAKE's tables ...
. exporting referential integrity constraints
. exporting posttables actions
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting triggers
. exporting default and system auditing options
Export terminated successfully without warnings.
This example shows user SCOTT exporting all his tables in user mode.
Command Line Method
exp scott/tiger file=scott.dmp
Interactive Method
exp scott/tiger
...
Enter array fetch buffer size: 4096 > (RETURN)
Export file: EXPDAT.DMP> scott.dmp
U(sers), or T(ables): U> u
Export grants (Y/N): Y> y
Export table data (Y/N): Y> y
Compress extents (Y/N): Y> y
Export Messages
About to export SCOTT's objects ...
. exporting job queues
. exporting refresh groups and children
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. exporting stored procedures
. about to export SCOTT's tables ...
. exporting table BONUS 0 rows exported
. exporting table DEPT 7 rows exported
. exporting table EMP 22 rows exported
. exporting table PROJ 0 rows exported
. exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
Export terminated successfully without warnings.
A user with the EXP_FULL_DATABASE role can use this mode to export tables from any user's schema by specifying TABLES=(schema.table). If schema is not specified, it defaults to the previous schema from which an object was exported. If there is not a previous object, it defaults to the exporter's schema. In the following example, schema defaults to SYSTEM 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 he or she owns.
Exports in table mode do not include cluster definitions. As a result, the data is imported into unclustered tables. Thus, you can use Export to uncluster tables when there is not enough free space in your database.
Command Line Method
> exp system/manager tables=(mort.bar, mary.app) grants=Y indexes=Y
Interactive Method
> exp system/manager
...
Enter array fetch buffer size: 4096 > (RETURN)
Export file: EXPDAT.DMP > (RETURN)
E(ntire database), U(sers), T(ables): U> t
Export table data (Y/N): Y> (RETURN)
Compress extents (Y/N): Y> y
Export Messages
About to export specified tables ...
Table Name: mort.bar [interactive session only]
Current user changed to MORT
. exporting table BAR 2355 rows exported
Table Name: mary.app [interactive session only]
Current user changed to MARY
. exporting table APP 14947 rows exported
Table Name: (RETURN) [interactive session only]
[Export writes file]
Command Line Method
> exp lewis/newyork file=lew.dmp tables=(credits, debits)
Interactive Method
exp lewis/newyork
...
Enter array fetch buffer size: 4096 > (RETURN)
Export file: EXPDAT.DMP> lew.dmp
U(sers), T(ables): U> T
Export table data (Y/N): Y> y
Compress extents (Y/N): Y> y
Export Messages
About to export specified tables ...
Table Name: credits [interactive session only]
. exporting table CREDITS 423 rows exported
Table Name: debits [interactive session only]
. exporting table DEBITS 423 rows exported
Table Name: (RETURN) [interactive session only]
[Export writes file]
Figure 1 - 3 shows an incremental export at time 1, after 3 tables have been modified. Only the modified tables (and associated indexes) are exported.
Figure 1 - 3. Incremental Export at Time 1
Figure 1 - 4 shows another incremental export at time 2, after 2 tables have been modified. Table 3 was modified a second time, so it is exported at time 2 as well as at time 1.
Figure 1 - 4. Incremental Export at Time 2
Note: Incremental exports cannot be specified as read-consistent.
Figure 1 - 5 shows a cumulative export at time 1, after 3 tables have been modified. The modified tables (and associated indexes) are exported. This export is equivalent to an incremental export.
Figure 1 - 5. Cumulative Export at Time 1
Figure 1 - 6 shows a cumulative export at time 2, after 2 tables have been modified. This time, the two tables modified at time 2 are exported, and the tables modified at time 1 are exported as well.
Figure 1 - 6. Cumulative Export at Time 2
This cumulative export file is a combination of the incremental exports from time 1 and time 2, except that table 3 (which was modified at both times) only occurs once in the export file. In this way, cumulative exports save space over multiple incremental exports.
Figure 1 - 7 shows a complete export at time 2. With the complete export, all objects in the database are exported regardless of when (or if) they were modified.
Figure 1 - 7. Complete Export
The benefits of incremental and cumulative exports include:
Assume that as manager of a data center, you do the following tasks:
DAY: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
X I I I I I C I I I I I I I C I I I I I X
To restore through day 18, you would first import the system information from the incremental export taken on day 18. You would then import the data from:
Note: The section "Incremental, Cumulative and Complete Imports" on page 2-29 explains how to do an incremental import.
The entire grant structure for tables or views is exported with the underlying base table(s). Indexes are exported with their base table, regardless of who created the index.
Export automatically exports a read-consistent view of a table, even if the table is being updated during the export.
Any modification (UPDATE, INSERT, or DELETE) on a table automatically qualifies that table for export.
Also, if database structures have changed in the following ways, then the underlying base tables and data are exported:
where:
INCREMENTAL
Exports all database objects that have changed since the last incremental export, as tracked by table SYS.INCEXP, then updates the table with a new ITIME and EXPID.
CUMULATIVE
Exports all database objects that have changed since the last cumulative export, as tracked by SYS.INCEXP, then updates the table with a new CTIME, ITIME, and EXPID.
COMPLETE
Exports all objects and updates the tables SYS.INCEXP and SYS.INCVID. (A FULL=Y export does not update these tables unless you specify the INCTYPE parameter.).
Note: See page 1 - 31 for definitions of ITIME, EXPID and CTIME.
Note: For incremental and cumulative exports, a row is also added to table SYS.INCFIL to identify the export file and the user doing the export. For more information on the system tables that support incremental export, see page 1 - 31. When exporting 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.
> exp system/manager inctype=incremental
...
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 job queues
. exporting refresh groups and children
. exporting cluster definitions
. exporting stored procedures
. about to export SYSTEM's tables ...
. about to export SCOTT's tables ...
. exporting table DEPT 7 rows exported
. exporting table EMP 22 rows exported
. about to export ADAMS's tables ...
. about to export JONES's tables ...
. about to export CLARK's tables ...
. about to export BLAKE's tables ...
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
. exporting synonyms
. exporting views
. exporting default and system auditing options
. exporting information about dropped objects
Export terminated successfully without warnings.
OWNER#
The userid of the schema containing the table.
NAME
The object name. With OWNER#. The primary key consists of OWNER#, NAME and TYPE.
TYPE
The type of the object (a code standing for INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, SNAPSHOT, SNAPSHOT LOG, or PACKAGE BODY).
CTIME
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 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.
EXPID
The ID of the incremental or cumulative export, also found in table SYS.INCEXP.
EXPTYPE
The type of export (incremental or cumulative).
The username of the user doing the export.
For added performance, the database can be set to direct read mode thus eliminating contention with other users for database resources because database blocks are read into the Export session's private buffer, rather than a public buffer cache.
Used in conjunction with the database in direct read mode (which causes the Export session's private buffer to be used rather than a public buffer), contention with other users for database resources is eliminated and performance improved.
Figure 1 - 8 shows how database table data extraction differs between these two methods.
Figure 1 - 8. Database Reads on Direct Path and Conventional Path
In a direct path export, data is read from disk into the buffer cache and rows are transferred directly over the network 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 over the network to the Export client which then writes the data into the export file.
Additional Information: 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 only needs to be run once. Once run, it need not be run before future exports. This script creates the export views and character set assignments necessary for direct path exports.
Before running Export, ensure that there is enough disk or tape storage space to write the export file to. If there is not enough space, Export will terminate 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 in the Oracle data dictionary. See the Oracle7 Server Reference for more information.
If you do not run CATEXP.SQL before attempting a direct path export, the following errors will occur:
EXP-00008: ORACLE error 904 encountered ORA-00904: invalid column name EXP-00222: invalid column name
Export terminated successfully with warnings.
EXPORT DIRECT=Y
DIRECT=Y specifies that table data is to be extracted using direct path mode. The default is DIRECT=N, table data is to be extracted using the conventional path.
You can also specify direct path export from a parameter file by using the command-line option PARFILE. For more information about the parameter file, see "The Parameter File" on Page 1 - 11 of this manual.
Note: The Export parameter BUFFER, used to specify the size of the fetch array, applies only to conventional path exports. It has no effect on a direct path export. The parameter RECORDLENGTH can be used to specify the size of the Export I/O buffer.
If you want to export release 7.3 database objects to a previous release, and you are concerned about backward compatibility, you may need to use conventional path export, because the direct path export dump file format is not compatible with releases prior to 7.3.
Following are factors that can affect the size differences between direct path and conventional exports.
Any character set conversion will lengthen the processing time required for an import. Therefore it is advisable to limit the number of character sets conversions to as few as possible.
The ideal scenario is one in which the import session and target database character set are the same as the source database character set requiring no character set conversion.
In the situation where the import session character set and the target character set are the same, but differ from the source database character set, one character set conversion will be required.
If you currently have differing character sets for the source and target databases and/or the import session, and you plan to export/import between these databases regularly, it is advisable to do a one-time export to align these character sets.
compatible = <db_version_number>,
where
db_version_number is 7.1.5 or higher.
You can change RECORDLENGTH to any value equal to or greater than your system's BUFSIZE. The highest value is 64k because this value is read into a 2-byte number. Changing the RECORDLENGTH parameter only affects the size of data that accumulates before writing to the disk. It does not affect the operating system file block size. If DB_BLOCK_SIZE is greater than BUFSIZE, it is more advantageous to use DB_BLOCK_SIZE, because each table scan can only return data up to DB_BLOCK_SIZE. If this does not fit in the export I/O buffer, export performs separate writes to the disk.
Consider the following values for RECORDLENGTH:
Whenever a lower version Export utility runs with a higher version of the Oracle Server, any categories of database objects that did not exist in the lower version are excluded from the export. For example, when running a Release 6 version of Export with the Oracle7 Server, snapshots (which did not exist in Release 6) are excluded from the export. (A complete list of objects excluded in this export is given in the next section.)
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.
To export from a 7.0.16 database containing code objects with comments between the creation keywords, for example:
CREATE PROCEDURE /* comment */ FOO /* comment */ AS ...
or to export from a 7.0.12 or earlier 7.0 database that contains such code objects, or that contain code objects in which the above creation keywords spanned multiple lines; then the export can be made to succeed either by appropriately re-creating the code objects, or by applying a patch that makes earlier versions of Import and Export use the new export file format.
The following table summarizes the situations and solutions:
Exporting From | Objects Affected | Situation | Solution |
7.0.12 | procedures, functions, packages, package bodies | If the creation keywords for these objects are defined on multiple lines, or if they have comments embedded among the creation keywords, Export fails with a memory violation. | Apply the patch or change the definitions so that the creation keywords are all one line to successfully export these objects. |
7.0.16 | procedures, functions, packages, package bodies | If these objects have comments embedded among the creation keywords, they cannot be imported from the export file. | Apply the patch or change the definitions to remove the embedded comments, then re-export these objects. |
7.1 | triggers, procedures, functions, packages, package bodies | These objects cannot be imported into a 7.0 release from a 7.1 export file. | Apply the patch to the earlier release in to read the new export file format or else put the objects into the proper format and use a Release 7.0 export on the 7.1 database. |
Note: A normal Oracle7 export requires CATEXP.SQL to be run by SYS after CATALOG.SQL to create the necessary views. If needed, both CATEXP.SQL and CATEXP6.SQL can be run, in any order. Once one of these scripts has been run, it does not have to be run again.
When Version 6 Import attempts to create the table, the specification of a CHAR column greater than 255 produces an error. One solution is to change the table's datatype to LONG, but be aware of the many restrictions on the Version 6 LONG datatype. See "Selecting a Datatype" in the Oracle7 Server Application Developer's Guide for details.
In Version 6.0.35.0 and later, Export truncates LONG data to 64K - 3 bytes and produces a warning message. Earlier releases truncate to 64K - 2 bytes with no warning.
Warning: When importing into a Version 6 database, such overly long data produces an error and causes the rest of the table data to be skipped.
AUDIT DBA [WHENEVER [NOT] SUCCESSFUL]
AUDIT CONNECT [WHENEVER [NOT] SUCCESSFUL]
AUDIT NOT EXISTS
AUDIT ALL [WHENEVER [NOT] SUCCESSFUL]
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |