The Import Utility can be used to
(A) Create table definitions
It creates the table definitions since the table definitions are stored in
the Export file.If we choose to import data without the rows then it
will create only the table definitions.
(B) Extract data from a valid Export file
It will extract data from a valid export file by using the
Table,User,Tablespace or Full Import modes.
(C) Recover from user-error failures
Recovers a user failure errors when a table is accidentally dropped or
truncated.
Before Using Import :
We all know that Import Utility will transfer data between tablespaces.Suppose if we want to import some data from tablespace A(Source) to tablespace B(Target) using an export file then very first point we need to check is that whether the tablespace B(i.e.,target tablespace) is existing & if its existing then check whether user is created & finally if user is also created then check whether that user has privileges or not.If the user has privileges then only the Import Utility can transfer data from source tablespace A to target tablespace B.
In Brief :
1)Create Tablespace 2)Create User 3)Grant Privilege
Brief On Import Modes :
Earlier as we have dicussed in detail about the Export Modes, here I will just give u a brief on Import Modes.
(a)Table Mode :Import specified tables into a schema.
(b)User Mode :Import all objects that belong to a schema.
(c)Tablespace Mode :Import all definitions of the objects contained in the tablespace.
(d)Full Database Mode :Import all the objects from the export file.
Note :
1)Like Export Utility,we can also perform Import Utility using command-line entries & in interactive mode.
2)Command-line options are similar to Interactive mode options but command-line entries will give us more functionality.
Command-line entries for Import :
General Syntax :
IMP username/password parameters=(value1,value2,…..valuen)
Before looking at some of the examples of Import Utility ,
First Lets have look at some Import parameters which are oftenly used by DBA’s.
So just issue the command “imp -help” to get the list of parameters with little description and default modes of it.
C:\>imp –help
Here is the screen shot for it:
Examples :
1)To import two tables called EMPLOYEES & DEPARTMENTS, that are present in HR schema including all the rows of it by using export file named export1.dmp
C:\>IMP hr/hr tables=(employees,departments) rows=y file=export1.dmp
2)To import all the objects for HR schema including all the rows of it using export file named export2.dmp
C:\>IMP system/manager fromuser=hr touser=abc file=export2.dmp owner=hr
3)To import all the object definitions belonging to the tablespace called ts_employees by using an export file named export3.dmp & also generate a logfile named ts_employees.log
C:\>IMP system/manager TRANSPORT_TABLESPACE=y FILE= export3.dmp TABLESPACES=(ts_employees) LOG=ts_employees.log
Note :
If the “file” keyword or parameter is not mentioned in the import statement then the import utility looks for the default file expdat.dmp .
Order of Import Process :
The table objects are imported as they are read from the export file.The export file contains objects in the following order :
1)Type Definitions
2)Table Definitions
3)Table Data
4)Table Indexes
5)Integrity Constraints,Views,Procedures & Triggers
6)Bitmap,Function-Based & Domain Indexes
Suppose ,while we are importing a table ,first the export file is read & then the table and data are created in the following order:
1)New tables are created
2)Data is imported
3)Indexes are built
4)Triggers are imported
5)Integrity constraints are enabled on the new tables
6)Any bitmap,functional & domain indexes are built.
This above sequence prevents data from being rejected due to the order in which the tables are imported.And it will also prevents redundant triggers from firing twice on the same data.Thatmeans,firstly while it is originally inserted and secondly during import.
Note 1 :
The order in which the tables are imported is important if you do not import all the objects that a user owns.Suppose , if the table with the foreign key has a referential check on the table with the primary key and the foreign key table is imported first ,then all rows that reference the primary key that have not been imported will be rejected if the constraints are enabled.But for a full database export this is not a problem.
For Example :
If the emp table table has a referential integrity constraint on the dept table and the emp table is imported first then all emp rows that reference departments that have not yet been imported into dept would be rejected if the constraints were enabled.
Note 2 :
If the data is imported into the existing tables,then the order of import can still produce referential integrity failures.So for the situation like this , if the emp table already exist and referential constraints are in force then many rows could be rejected.
