0409-0411

Previous Table of Contents Next

Page 409

Full-Database Mode

The full-database option of Import does not quite work the same way as the full-database option of Export. If you specify the full-database option in Export, the entire database is exported, and to import the entire file, you must have the role IMP_FULL_DATABASE assigned to you. If the export file is a user export or table export, the entire export file is imported (whatever is in it), and you do not need to have the role IMP_FULL_DATABASE assigned to you.

Interactive Versus Command Line

Both Import and Export can operate in command-line mode and in interactive mode. Both modes have their advantages, but Oracle recommends that you use the command-line mode with a parameter file. The two modes are outlined in this section. I'm sure you will find good reasons for using both in your own situations.

Interactive Method

The interactive method is an easy way to import data from export files, but you do not have all the options of the parameter or command-line method. For example, you cannot create an index file. The interactive prompts might change depending on the response to previous prompts. The interactive prompts also have the benefit of showing the pre- chosen defaults. To use Import in the interactive mode, type imp or imp USERID= userid /password at the command line.

NOTE
The interactive method continues to exist only for backward compatibility.

An interactive import might resemble the following:

 /usr/local/p3016dk >  imp scott/tiger Import: Release 7.1.4.1.0 - Production on Sun Oct  1 10:12:54 1995 Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. Connected to: Oracle7 Server Release 7.1.4.1.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.0.0 - Production Import file: ./expdat.dmp > scott.dmp Enter insert buffer size (minimum is 4096) 30720> Export file created by EXPORT:V07.01.04 List contents of import file only (yes/no): no > 

Page 410

 Ignore create error due to object existence (yes/no): yes > n Import grants (yes/no): yes > y Import table data (yes/no): yes > y Import entire export file (yes/no): yes > y 
Command-Line Method

The same import can be accomplished by entering the following command-line options:

 imp USERID=scott/tiger FILE=scott.dmp ignore=N 

Import responds with the following:

 Import: Release 7.1.4.1.0 - Production on Sun Oct  1 10:19:12 1995 Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved. Connected to: Oracle7 Server Release 7.1.4.1.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.0.0 - Production Export file created by EXPORT:V07.01.04 . importing SCOTT's objects into SCOTT . . importing table "BONUS"                                    0 rows imported . . importing table "CUSTOMER"                                 9 rows imported . . importing table "DEPT"                                     4 rows imported . . importing table "DUMMY"                                    1 rows imported . . importing table "EMP"                                     14 rows imported . . importing table "EXECUTABLES"                              0 rows imported . . importing table "FALL5"                                   30 rows imported . . importing table "ITEM"                                    64 rows imported . . importing table "ORD"                                     21 rows imported . . importing table "PRICE"                                   17 rows imported Import terminated successfully without warnings. 

Import Parameters That Affect Performance

As with Export, Import also has several parameters that affect performance. This section lists the parameters that affect performance the most along with an explanation of how they work and how to use them.

BUFFER

The BUFFER parameter for Import works the same way as the BUFFER parameter in Export. For the most part, you can specify a large value for best results.

COMMIT

The COMMIT parameter defaults to N, but if you do not have large rollback segments to use (at least as large as the largest table), set this option to Y. If you set COMMIT to Y, Import commits

Page 411

every time the buffer fills and the array is inserted. The only significant performance gain you will notice is when your import fails because of a rollback issue and you must redo an import.

LOG

Quite simply, always use the LOG parameter so you can review the log file and effectively fix anything that went wrong.

How Objects Import

Objects are created in a specific order as the import progresses. The important thing to remember is that Import first creates the table and then loads the data for each table. Import creates all the indexes on the tables after it loads all the table data. Finally, Import enables all the table constraints and triggers. In some situations, the table constraints can cause interesting results (usually undesirable). If you are in the habit of pre-creating the tables before the import, you know you should disable the constraints before importing. Re-enable the constraints when the import is finished. Don't forget!

Tables

Import first creates the table definitions and then loads the data. Import creates all the indexes for the table and then creates and enables the constraints and database triggers. The important thing to note is that all the tables are loaded and all the indexes are created a table at a time. When the tables are finished, Import creates and re-enables all the table constraints.

Stored Procedures

Packages, functions, and stored procedures are imported without updating the timestamp. This enables Oracle to use the objects without recompiling them.

Snapshots

Oracle exports and imports the master table, the master table trigger, the snapshot log (if you are using one), and the snapshot itself, similar to exporting and importing tables and database triggers. If you are using a snapshot log, only the snapshot log definition is exported and imported. What this means to you is that the first fast refresh that is attempted on that snapshot will fail. Plan on a complete refresh of all the imported snapshots after the import is complete.

Importing and Exporting with Personal Oracle7

My world has not been the same since Oracle introduced Personal Oracle7. I have been able to develop complete applications on my PC at home. Although I do not have a PC platform that compares with our Sequent, HP, or DG platforms, I can't resist the desire to develop applications on it. I haven't yet used Personal Oracle7 for any production applications, but I am sure many people have.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net