Page 606
TIP |
If you intend to import into preexisting tables with constraints, you may want to temporarily disable them because data may be loaded into tables in any order. Once the backup is finished, you can re-enable the constraints. |
RECORDLENGTH is used for moving export files to an Oracle database on another operation system. Basically, if you are moving from system A to system B, you must know what the record size is on system B. This information is found in the operating system documentation supplied by Oracle.
GRANTS determines if permission settings are imported with objects.
INCTYPE accepts either RESTORE or SYSTEM as input. SYSTEM imports the system's objects (that is, only SYSTEM schema objects) from the last incremental export file. RESTORE imports user objects that have changed since the last time you ran an export.
INDEXES indicates if indexes for imported tables should also be imported.
COMMIT indicates if IMP should make multiple commits while importing each table. If COMMIT=N, a single commit occurs after each table is loaded. COMMIT=Y reduces the chance that rollbacks will overflow. The advantage to COMMIT=N is that if the import fails on a particular table, a rollback will occur that will completely empty the table. This is desirable because it returns the table to the state it was in before the import, thereby making it easy to restart the import without risk of duplicate records.
ROWS determines if the rows of imported tables are also loaded. Setting ROWS=N is useful if all you want to do is move a schema definition with no data in the tables.
PARFILE is used identically with IMP as it is with EXP.
LOG is used to log all output normally sent to the terminal to a file. Provide a standard filename where you would like the output to be logged. Using this option will not prevent output from being displayed to the screen.
DESTROY is valid when a full export is being imported. When a new tablespace is created, DESTROY=Y effectively allows any existing datafiles to be overwritten. This is useful if you are replacing an instance entirely. However, if you are creating a duplicate copy of a database on the same machine, you want to use DESTROY=N. You don't want to destroy your original database! In this case, you create all of the necessary tablespaces manually and then perform the import.
INDEXFILE is the name of a file where the SQL code needed to create the indexes in the export file is written. When it's used, no changes to the database are performed. This is useful if you want to change the initial or next extent values in your new system. If you will be manually creating indexes from this output, be sure to import with INDEXES=N to prevent them from automatically being created.
Page 607
CHARSET is not to be used! It is provided for DBAs moving data from Oracle6 to Oracle7 systems. If you are migrating from Oracle6 to Oracle7, this parameter should specify the character set used by the database system that created the export file. Oracle will soon no longer support this parameter; you are encouraged to work with Oracle Support when character sets are being changed in an Oracle6 to Oracle7 upgrade.
Just as with EXP, you can supply as many keywords as you want with IMP. If IMP needs more information than you provided, it will prompt you for it. To run fully interactively, just type imp on the command line.
You will either be performing a full import or a partial import. The former completely re- creates a database on a target instance. Obviously, your export file must have all the information in it that you'll need to import. You can always import a subset of the data in an export file. These two different modes will be covered separately in the following subsections.
A full logical restore is typically used to re-create a copy of an existing system on either the same machine or a different machine. Because export files are platform independent, they are the tool of choice for this operation. Export files are also fairly version independent. In fact, a logical backup/restore is often recommended when you are upgrading Oracle versions.
The only requirement for a successful restore is that a database instance exists. All table-spaces, users, profiles, tables, indexes, and so forth will be re-created with IMP. You must exercise some caution, however. Allowing IMP to create tablespaces could present a serious problem. The tablespaces will be re-created with identically named data files (including their paths). You may want to have the same tablespaces on different databases, but you will also likely need to have different data file names, have them located in different directories, make them different sizes, and so forth. In this case, you would want to manually create tablespaces on the target system. This SQL statement will give you some guidance with each tablespace name and its size (in bytes):
SELECT TABLESPACE_NAME,SUM(BYTES) FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
The following SQL statement will help you decide on tablespace sizes. It indicates the amount of space (bytes) in each tablespace that has not been allocated to a segment (table, index, and so on):
SELECT TABLESPACE_NAME,SUM(BYTES) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
CAUTION |
If you are copying one instance into another on the same machine, you must manually create tablespaces on the target instance. If you don't, IMP may destroy the data files in your original database instance when it tries to create tablespaces in the target instance. |
Page 608
The syntax of a typical full logical restore is fairly simple. The following is an example:
imp USERID=SYSTEM/MANAGER FULL=Y IGNORE=Y FILE=<export filename>
Here, you're assuming that an instance is already running and that the appropriate tablespaces already exist.
In most cases, you will be using the IMP utility to restore a particular set of objects (or user schemas) to your database. You should have the appropriate tablespaces, rollback segments, and users set up before attempting to use IMP to restore a portion of the database. Keep in mind that if a tablespace does not exist or cannot hold the object being imported, it will be loaded into the user's default tablespace. Also, when importing, if a FROMUSER listed user does not exist on the current database, the user specified in USERID will be used to store the object(s).
Let's recall the scenarios we proposed when demonstrating why a DBA should use logical backups. After each brief recount, the IMP command line needed to restore the object(s)/schema(s) in question will be shown.
Suppose that you need to rename the user SKLEIN to SJONES. You already exported the original user schema to a file. The new user ID SJONES has been created, and you are ready to restore the objects from the old schema to the new:
imp USERID=SYSTEM/MANAGER FILE=SKLEIN2SJONES.DMP FROMUSER=SKLEIN TOUSER=SJONES
You were asked last week to drop the user TGASPER from the database. You did so after running a FULL=Y export of the database (after hours, of course). Now marketing needs to look at the tables that were in the TGASPER schema. You have just re-created the TGASPER schema and are ready to import the objects:
imp USERID=SYSTEM/MANAGER FILE=/dev/rmt0 FROMUSER=TGASPER
A common application uses tables in the APAS schema for its configuration information. Last month, version 2.3 was upgraded to version 3.0. At that time the tables STACONF and USRCONF were no longer needed. Being the shrewd DBA that you are, you exported those tables before dropping them. Version 3.01 just came out andyou guessed itthey need the USRCONF table back:
imp USERID=SYSTEM/MANAGER FILE=table_STACONF.dmp TABLE
Without a doubt, you will have to deal with far more complex import/export issues. You should have a fairly good idea what to expect from EXP and IMP. If at all possible, always try out your imports on a test instance before attempting them on the production systemespecially if you use IGNORE=Y. You could conceivably damage a database table beyond repair with an incorrect import. Remember that you're paying for Oracle's support program. Be sure to use it in dealing with import/recovery issues if you have any doubts.