Summary of Changes
This appendix describes changes in the Oracle7 Server utility programs. It covers:
This section describes changes to the Oracle7 Import/Export utility.
Release 7.2 Changes
These are the changes to Export in Release 7.2.
FEEDBACK parameter added to Import/Export to display an onscreen progress meter during imports and exports.
Release 7.1 Changes
These are the changes to Export in Release 7.1.
Change in Export File Format
In Release 7.1, the export file format was changed to accommodate stored procedures, functions, and packages that have comments embedded among the creation-statement keywords. In some cases, a patch is required to successfully migrate these code objects between a Release 7.0 database and a Release 7.1 database.
Read-only tablespaces can be exported.
Trusted Oracle Label Mapping
Trusted Oracle7 Import allows you to list the labels in the export file and specify a mapping that converts them into the labels used by the target Trusted Oracle7 database. For details, see the Trusted Oracle7 Server Administrator's Guide.
Version Incompatibility Error Message
When a higher level version of the Export utility is used with a lower level version of the Oracle7 Server with which it is incompatible, an EXP-37 error results.
New 7.1 Objects Supported
Release 7.1 of the Export/Import utilities supports these new Oracle7 objects:
Changes in Export for Release 7.0
The new features of Oracle7 export utility are described below.
New objects in Oracle7 (roles, profiles, triggers, stored procedures, and snapshots) can be exported. See page 1 - 6.
Log files are supported in Oracle7. Systems that do not support I/O redirection can have a record of errors and warnings that occur during the export. See page 1 - 15.
In Version 6, any export that completed displayed the message "Export completed successfully". In Oracle7, export ends with one of three messages:
Export terminated successfully without warnings
Export terminated successfully with warnings
Export terminated unsuccessfully
See page 1 - 5 for more information.
The Oracle7 parameter CONSISTENT allows the creation of read-consistent exports. A read-consistent export is guaranteed not to include any partially completed transactions. It is used when exports and database updates must occur simultaneously. See page 1 - 13.
The default for GRANTS export parameter is Y. In Version 6, it was N. See page 1 - 15.
The default for the CONSTRAINTS export parameter is Y. In Version 6, it was N. See page 1 - 14.
Setting Up Export Views
In Oracle7, the script EXPVEW.SQL must be run after CATALOG.SQL to establish the export views. In Version 6, the order made no difference. See page 1 - 2.
Trusted Oracle7 Server Parameters
The Trusted Oracle7 Server parameters MLS and MLS_LABEL_FORMAT have been added. See page 1 - 16.
Changes in Import for Release 7.0
The new features of Oracle7 Import are described below.
New objects in Oracle7 (roles, profiles, triggers, stored procedures, and snapshots) can be imported. See pages 2 - 9 to 2 - 10.
The ALTER TABLESPACE statement stored in the Oracle7 export file sets REUSE=NO so that the default action on import is not to reuse the datafiles defined for the database. In Version 6, the default was REUSE=YES. This change minimizes the chance that a database can be inadvertently replaced when attempting to create a copy of it for testing or other purposes. This change resulted from the fact that the full pathname of datafiles in the database is included in the export file.
A new import parameter allows you to specify DESTROY=YES, instructing Import to erase the existing datafiles and reuse them. This option allows you to re-create your database from backups, when necessary. The default value for DESTROY is NO. See page 2 - 19.
Log files are supported in Oracle7 so that systems that do not support I/O redirection can have a record of errors and warnings that occur during the import. See page 2 - 23.
In Version 6, any import that completed displayed the message "Import completed successfully". In Oracle7, import ends with one of three messages:
Import terminated successfully without warnings
Import terminated successfully with warnings
Import terminated unsuccessfully
See page 2 - 5 for more information.
In Version 6, user definitions did not exist separately from their GRANTS. User definitions were therefore imported by importing GRANT statements. In Oracle7, importing a user definition does not automatically grant connect access.
Importing Objects for Other Users
Version 6 import adopted the target user's privileges when importing objects. If the user did not have CREATE TABLE privilege, for example, a DBA might find that a user's tables could not be imported--although the DBA had successfully exported them.
Oracle Import uses the importer's privileges to import objects into another user's schema. This method preserves the importer's privileges, allowing a fully privileged user to import objects for a limited-privilege user.
In Oracle7, the default is to report all object creation errors. Previously they were ignored by default. To suppress the reporting of error messages that occur when tables and other objects already exist, specify IGNORE=Y. See page 2 - 21.
The Oracle7 parameter INDEXFILE makes it possible to siphon off index-creation statements, so they are placed in a file instead of being applied to the database. This procedure is more efficient and makes it possible to change index storage attributes. See page 2 - 22.
Character Set Conversion
In Version 6, Import/Export provided a limited capability to convert data between ASCII-based and EBCDIC-based systems. In Oracle7, that capacity has been extended to translate data between two systems using different character encoding schemes. See page 2 - 12.
In Version 6, the CHARSET parameter was used in limited ways to control the conversion from the character set used in the export file to the database character set. In Oracle7, the Export file records the character set used when the file was created. Oracle Import then automatically converts that character set to the database character set.
In Oracle7, this parameter only checks that the Export file's character set matches the expected value. Use of the CHARSET parameter is no longer recommended as it will eventually become obsolete.
Trusted Oracle7 Server Parameter
The Trusted Oracle7 Server parameter MLS has been added. For details. See page 1 - 16 and page 2 - 23.
Incremental Import and Export
In Oracle7, an incremental import or export can be performed by any user who has been granted the EXP_FULL_DATABASE role. Previously, only SYS or SYSTEM could do an incremental import or export. See page 1 - 24.
Similarly, any user who has been granted the IMP_FULL_DATABASE role can perform an incremental import. See page 2 - 29.
This section describes the changes to SQL*Loader and the releases in which they became effective.
Oracle7 Server Release 7.1
Changes for Release 7.1
- Data can be loaded in parallel with the PARALLEL direct path load. See page 8 - 21.
- Direct loads can load data into synonyms for tables (but not synonyms for views, or synonyms for synonyms). See page 8 - 7.
- Direct loads can specify UNRECOVERABLE to improve performance by disabling redo logging. See page 8 - 15.
- In Release 7.1, direct loads can specify a datafile character set which is different from the control file using the CHARACTERSET clause. In 7.0 this was possible for a conventional load, but not for a direct load. See page 5 - 24.
As before, the control file must be in the same character set as the session so that the session can interpret it. And, as before, the database character set can be different -- data conversion happens automatically.
- The REPLACE option returns to using DELETE TABLE semantics. That was the standard behavior before Release 7.0. Release 7.1 returns to that standard, and no longer performs a table TRUNCATE when REPLACE is specified. With this change:
- Integrity constraints on the table do not have to be disabled before the load.
- Delete triggers that are defined on the table fire as rows are deleted.
- DELETE privilege on the table is sufficient if the table is not in your own schema. See page 5 - 26 for more information.
- A new option, TRUNCATE, uses fast table truncation for a load. This option does not fire delete triggers for the truncated rows. Integrity constraints on the table must be disabled before loading with TRUNCATE. Finally, if the table is not in your own schema, the DELETE ANY TABLE privilege is required. See page 5 - 25.
- Previously, only one direct path load could be performed on any given table. With Oracle Server release 7.1, you can optionally specify a PARALLEL clause within the SQL*Loader control file or on the command line to indicate that multiple SQL*Loader direct path load sessions can share access to the table. See page 8 - 21.
- The FILE parameter allows different parts of a parallel load to allocate extents from different database files.
In Oracle7, SQL*Loader is part of the standard Oracle Server release. As a result, its release number jumps from "1.1" to "7.0". New features include:
- Direct loads bypass referential integrity constraints and database triggers. See page 8 - 16.
- Input character conversion possible with the CHARACTERSET clause. See page 5 - 24.
- Multi-byte (Asian language) characters supported. See
page 5 - 24.
- Fast table truncation used for REPLACE option. See page 5 - 25.
Changes for Direct Path Loads:
- Maximum performance available with the direct path option.
See Chapter 8.
- Operating system sorts can be used for high-speed sorting, fast indexing, and reduced need for temporary storage.
See page 8 - 13.
- DIRECT command-line parameter added. See page 6 - 4.
- Meaning of ROWS parameter extended for direct loads.
See page 8 - 9.
Changes in File Management:
- CONTINUE_LOAD and table-level SKIP clauses added to continue a multiple-table direct path load after an interruption. See page 5 - 28.
- The file processing string allows optimizing datafile reads.
See page 5 - 18.
- The keywords RECLEN, STREAM, RECORD, FIXED, BLOCKSIZE, and VARIABLE are now obsolete. They are still recognized for upward compatibility, but they have no effect.
- Single quotation marks recommended for filenames.
See page 5 - 13.
- When loading records that contain only generated data, SQL*Loader skips reading of input file. See page 5 - 45.
Changes in Data Management:
- The bad file is only created if it is needed. See page 5 - 19.
- SQL string allows use of SQL operators on fields. See page 6-73.
- RAW datatype added. See page 5 - 52.
- Insert current date/time with SYSDATE keyword.
See page 5 - 46.
- Initial and trailing field delimiters can be different.
See page 5 - 58.
- Mismatches in field length specifications generate warning messages in native datatype fields and character datatype fields. See page 5 - 55 and page 5 - 61.
Changes in the Command Line:
- Default maximum field size for delimited fields is 255 bytes instead of 240 bytes. See page 5 - 58.
- Command-line specifications override control file options.
See page 5 - 12 and page 6 - 3.
- Bad file command-line specification overrides control-file specification. See page 5 - 19 and page 6 - 3.
- Discard file command-line specification overrides control-file specification. See page 5 - 21 and page 6 - 4.
- Arguments can be specified in a separate file with PARFILE command-line parameter. See page 6 - 5.
In addition, the old syntax
[ STREAM | RECORD | FIXED len [BLOCKSIZE size] | VARIABLE [len] ]
does not affect the way SQL*Loader reads the datafile, although the syntax is recognized to maintain upward compatibility.
Changes in Release 1.0.26:
- Load character fields with all whitespace intact, using the PRESERVE BLANKS option. See page 5 - 74.
- Test to see if a field of undetermined length is all blank with the BLANKS keyword. See page 5 - 38.
- All-blank numeric fields no longer automatically loaded as NULL. See page 5 - 69.
Changes in Release 1.0.22:
- ZONED DECIMAL datatype added. See page 5 - 51.
- Specifying how to handle missing fields at the end of a record with the TRAILING NULLCOLS keyword. See page 5 - 68.
- Putting special characters into quoted strings with the quoted-string escape character. See page 5 - 14.