Previous | Table of Contents | Next

Page 317

Parameter Default Value Description
FULL N The entire database will be imported if FULL=Y.
GRANTS Y Specifies whether all grants will be created for the objects that were exported.
HELP N No other parameters are needed if you specify HELP=Y. A basic help screen is displayed.
IGNORE N If IGNORE=Y, object creation errors are ignored and records are inserted into the table. Be aware that duplicate records may result if no unique constraints exist for the table. Note that non-object creation errors will still be reported, such as operating-system problems.
INCTYPE If importing an incremental export, tables are dropped and re-created. You must first restore from the last SYSTEM export (specify INCTYPE=SYSTEM). Then, import every incremental export (specify INCTYPE=RESTORE) until the desired changes are applied to the database.
INDEXES Y Specifies whether user-defined indexes are imported. System indexes created with constraints (primary key, unique key) and OID indexes are automatically imported, regardless of the value of the INDEXES parameter.
INDEXFILE The INDEXFILE parameter specifies the name of the file to generate CREATE INDEX statements. Unless otherwise specified, Oracle appends a .SQL extension to the file. You can find the INDEXFILE parameter described in more detail later in this chapter.
                                          continues

Page 318

Table 13.3Continued

Parameter Default Value Description
LOG The LOG parameter specifies the name of the file to spool the feedback from the import session. Unless otherwise specified, Oracle appends a .LOG extension to the file.
PARFILE Instead of entering all parameters on the command line, some or all may be kept in a parameter file. The PARFILE parameter specifies which file to use, if desired. This parameter is especially useful for non-
interactive import sessions.
POINT_IN_TIME_RECOVER N Performs a Point-in-time Recovery for the tablespace exported with the TABLESPACES parameter.
RECORD_LENGTH OS-Dependent The RECORD_LENGTH parameter is used only when importing on a machine with a different byte count of the file than on the machine where the export occurred. In most import sessions, the default should be used.
SHOW N The SHOW parameter displays each SQL statement to the screen, and does not modify the database. When used with the FILE parameter, the SQL statements may be viewed and modified. You can find a more detailed description of the SHOW parameter later in this chapter.
SKIP_UNUSABLE_INDEXES N The SKIP_UNUSABLE_INDEXES parameter enables you to postpone index creation until the record data has been imported. The indexes affected are only those set to an unusable state; all other indexes will be created if INDEXES=Y is set (which is the default value).

Page 319

Parameter Default Value Description
TABLES Specifies a comma-separated list of all tables to be imported. This parameter should be used in conjunction with the FROMUSER parameter. In a non-UNIX environment such as Windows NT, you must enclose the table list within parentheses.
TOUSER The TOUSER parameter specifies the user account into which tables should be imported, if it is desired to be different than the original owner of the tables. This parameter needs to be used in conjunction with the FROMUSER parameter.
USERID Specifies the username and password for the user conducting the import. The format for the command is username/password. You can also use Net8's @connect_string format if desired.

Taking Walkthroughs of Import and Export Sessions

This section steps you through several walkthroughs of Import and Export sessions. These walkthroughs demonstrate some of the powerful features of the two utilities and point out some pitfalls that you should avoid.

Identifying Behavior when a Table Exists

If an import is started that attempts to import a table that already exists in the database, an error occurs. Import skips the table (along with the foreign keys and indexes on that table) and proceeds with the import process, importing all other specified tables and objects, as shown in Figure 13.3.

If you wish to import data into a table that already exists, use the IGNORE=Y parameter specification. This causes the data to be appended into the table. If there is a constraint violation, such as duplicate records based on the primary key, those rows which violate the constraint are not loaded. The existence of constraints is helpful in preventing the loading of duplicate records in a table.

Assume, for example, that the EMPLOYEE table, owned by the DEMO user account, has records in the table. An import is started that attempts to load a duplicate record into the table. Figure 13.4 shows the result.

Page 320

FIG. 13.3
Errors resulting from
importing a table that
already exists in the
database.



FIG. 13.4
Example of attempting
to load duplicate
records against a table
with a unique constraint.


Reorganizing a Fragmented Tablespace

A fragmented tablespace has many blocks of free space that are not contiguous. Fragmentation may cause performance and space problems. Performance is affected because Oracle has to scan across multiple object extents and possibly over multiple physical disk drives. When the data is defragmented, objects may be compressed from many extents into one. This reduces internal Oracle overhead when scanning data.

A fragmented tablespace affects the object storage adversely. With many small blocks of free space spread out across the tablespace, some objects may not be able to be created that otherwise could fit if all the free space were contiguous. By defragmenting the tablespace, the data is reorganized so that all the small free blocks form one free block, as shown in Figure 13.5.

Page 321

FIG. 13.5
Example of a fragmented
tablespace and an
unfragmented tablespace.


Fragmentation occurs when objects such as tables and indexes are created, deleted, enlarged, and reduced in size over time. Because Oracle can only create an extent of an object within a contiguous free space within the tablespace, pockets of free space may start to appear. When an object is deleted, its free space may likely be in an isolated spot within the tablespace, and only another object of the same or lesser size may be created within that free space.

To check for tablespace fragmentation, you can check to see how many segments of free space there are in a given tablespace, along with their sizes. This is done by running CHP13_1.SQL, passing the tablespace name, as shown in Listing 13.1. You may run the script in SQL*Plus, Server Manager, or any third-party product that can connect to the Oracle8 database.

Listing 13.1CHP13_1.sql—Script to Determine the Number and Sizes of Free Space Within a Given Tablespace

SQL> START CHP13_1.SQL
SQL> COLUMN FILE_NAME FORMAT A40
SQL> SELECT A.TABLESPACE_NAME, B.FILE_NAME, A.BYTES
  2  FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
  3  WHERE  A.TABLESPACE_NAME = `&tablespace_name' AND
4 A.FILE_ID = B.FILE_ID 5 ORDER BY BYTES DESC When you run the script, the server will prompt you to enter the name of the tablespace: Enter value for tablespace_name: USERS The server will return the free space information: TABLESPACE_NAME FILE_NAME BYTES
----------------- ----------------------------- ----------- USERS /u02/oradata/TEST/users01.dbf 32768 USERS /u02/oradata/TEST/users01.dbf 114688 USERS /u04/oradata/TEST/users02.dbf 122400 USERS /u04/oradata/TEST/users02.dbf 101908480 SQL>

Previous | Table of Contents | Next