0404-0408
Page 404
Table 17.2. Parameter list for Import.
| Keyword | Description | Default |
| USERID | Username/password of the account performing the import. | |
| BUFFER | The size of the buffer used to fetch rows. Oracle provides a good rule of thumb for estimating: buffer = rows_in_array * max_row_size Set this to a large value for faster imports. If you use the COMMIT parameter, the import commits every time the buffer fills. | |
| FILE | The name of the Export file to import. | EXPDAT.DMP |
| SHOW | If show is set to Y, no import is performed. It just shows you what it would have done. | N |
| IGNORE | When you set IGNORE=Y, the Object already exists error is overlooked. Use IGNORE=Y when importing into pre-created tables. If you specify IGNORE=N, the import for the object will fail if it exists in the database, and the import continues with the next object. Rows might be duplicated in a table if IGNORE=Y and if an import is attempted more than once without truncating the tables or dropping them first. | N |
| GRANTS | Indicates whether to import object grants. | Y |
Page 405
| Keyword | Description | Default |
| INDEXES | Indicates whether to create indexes. | Y |
| ROWS | Indicates whether to import table data. | Y |
| FULL | Indicates whether this should be considered a full Import. Must have the role IMP_FULL_DATABASE to import an export file created with the FULL=Y parameter. Usually used in conjunction with the same export parameter. You can use the FULL option with a USER or TABLE import also, importing all objects in the export file. | N |
| FROMUSER | A list of users' objects to import. This ignores all objects not owned by owners in the FROMUSER list. If any objects are imported into a database in which the original owner does not exist, the objects are imported into the importer's schema. | |
| TOUSER | This can be used to import objects from one owner to another. To use this parameter, you must have the role IMP_FULL_DATABASE. | |
| TABLES | A list of the tables to import. Use an asterisk (*) to specify all tables. Use the TABLES parameter to perform a table-mode import. Specify one table TABLES=emp, or if you want to specify several tables, use TABLES=(emp,dept). |
continues
Page 406
Table 17.2. continued
| Keyword | Description | Default |
| RECORDLENGTH | The record length of the export file. Usually not used unless you are transferring the data to another operating system. | O/S dependent |
| INCTYPE | The type of Import being performed: COMPLETE, CUMULATIVE, and INCREMENTAL. | COMPLETE |
| COMMIT | Indicates that Import should commit after each array, as set by the BUFFER size. You will need large rollback segments if COMMIT=N. Conversely, if COMMIT=Y, you need relatively small rollback segments. With COMMIT=Y, you might get only a partial import of a table if something causes the import to fail. Import rolls back only to the last commit. If Import fails with COMMIT=Y and you have the table constraints disabled, you need to remember to truncate or drop the tables and start over to prevent duplicate rows. | N |
| HELP | Displays the help screen. | N |
| PARFILE | Indicates the name of a file to read import parameters. You can use all the parameters except PARFILE in the parameter file. | NONE |
| LOG | Indicates the name of a file to log all the screen information and also error messages. |
Page 407
| Keyword | Description | Default |
| DESTROY | Indicates whether the CREATE TABLESPACE commands only found in a full export should be executed. Setting DESTROY=Y destroys the data files of the database being imported into. I suppose that one could really hurt. | N |
| INDEXFILE | This is a nice option to use to write out all the CREATE and ALTER statements of the objects in the export file. All but the CREATE INDEX commands are commented out. You can import the data with INDEXFILE=filename and use the file created by this parameter as a basis for new table creation scripts. With a bit of editing, you can move them to a new tablespace and add more appropriate sizing and storage parameters. | |
| MLS | For Trusted Oracle only, to indicate if MLS labels should be imported. | N |
Like Export, Import works in one of three ways, depending on the options and the user importing the data. The three modes of Import are full database, user, and table. All users can import their own objects in the user and table modes, and users with the IMP_FULL_DATABASE role can import full database exports. Users without the IMP_FULL_DATABASE can still use the FULL=Y parameter, too. When you import a table or user-mode export file, you can specify FULL=Y to import the entire file without prompting for any table names or usernames. You might notice that the import modes are very similar to the export modes. The modes are named the same between Import and Export. For the user mode, the parameter OWNER in Export is replaced with FROMUSER in Import.
Page 408
The three modes are invoked by using the proper parameter options:
| User mode | FROMUSER=ownerlist |
| Table mode | TABLES=tablelist |
| Full-database mode | FULL=Y |
You can import in the three modes interactively. You invoke the three modes by responding to Import's prompts. To import the entire export file, answer yes to the prompt Import entire export file (yes/no). The default for this prompt is yes. For a USER mode import, answer no. Import then asks for a username. After you enter the username, Import prompts for a list of tables. If you do not enter any table names, all the user's tables are imported one at a time. To tell Import that you are done entering table names, enter a period (.) on a new line after specifying the last table. The following code fragments illustrate the different modes. The first example is from user mode:
Import entire export file (yes/no): yes > n Username: scott
The following is from table mode:
Enter table names. Null list means all tables for user Enter table name or . if done: emp Enter table name or . if done: dept Enter table name or . if done: .
The last example is from full-database mode:
Import entire export file (yes/no): yes >Y
User Mode
The user mode of Import enables you to import tables that belong to a specific user. This can also be combined with the table mode to import specific tables from a specific schema. This is important because Import attempts to create the tables in the current schema's default tablespace if the schema specified in the export file does not exist. This can work to your advantage if you need to move tables from one schema to another.
Table Mode
You usually use table mode to import a table or a list of tables, rather than all tables in the export file. If a user has access to other schema, he can import tables from other schema by qualifying with the schema name. The default is to import all tables in the schema of the user doing the import.
EAN: 2147483647
Pages: 391