Oracle 9i-EXPORT UTILITY « A DBA’s Journey In Oracle

Introduction To Oracle’s Export And Import Utility :

We use Oracle’s Export & Import Utility to

1)logical Backup & Recovery

2)Move the data between Databases

The Export Utility is used to transfer data objects between oracle databases.It can provide logical backup of

(a)Database Objects

(b)Tablespace

(c)Entire database

The Import Utility is used to read a valid export file for moving data into databases.We export table or complete schema ( Thatmeans all the objects within the schema like tables,view,sequences etc) into an export file called Dump file.

An Export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.

The dump file will be with .dmp extension & its default name will be expdata.dmp. Export dump files can only be read by the Oracle Import utility because it’s binary file ,we cannot read or understand that file.So we have to use the Import Utility to dump this file into any oracle database.

The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.

Note :

You can also display the contents of an export file without actually performing an import. To do this, use the Import SHOW parameter.

Before Using Export :

1)Make sure that u have run the script catalog.sql

2)Ensure that there is sufficient space in the disk to take export

3)Verify that u have access priviledges

Lets dicuss the above 3 points in detail as fudamentals always paly a vital role.

(1)Run the script catalog.sql . After creating a database , we should always run 2scripts.One is catproc.sql & another one catalog.sql.So this point is asking u to make sure that these scripts are executed & most important point is ,these must be runned only once.

The script catalog.sql performs the following tasks to prepare the database for Export:

# Creates the necessary export views in the data dictionary

# Creates the EXP_FULL_DATABASE role

# Assigns all necessary privileges to the EXP_FULL_DATABASE role

# Assigns EXP_FULL_DATABASE to the DBA role

# Records the version of catexp.sql that has been installed

2)Verifying the disk space :

In Linux :Using df & du commands :

Inorder to check disk space using command line,use the command df-h.This command will display space in Megs and Gigabytes.

For Example : [root@anupama]# df-h

The command df stands for “disk filesystem”.With the -h option (df-h) it shows the disk space in “human readable”form.

The du command shows the disk space used by the files and directories in the current directory.Again the -h option (df-h) makes the output easier to understand.

(3)Verifying Access Privileges

To use Export, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs.

NOTE :

(a)If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user’s schema. For example, you cannot export a table in another user’s schema, even if you created a synonym for it.

(b)The following schema names are reserved and will not be processed by Export :

ORDSYS

MDSYS

CTXSYS

ORDPLUGINS

LBACSYS

EXPORT :

Export can be done in 4 Modes :

The Export utility provides four modes of export :

(a)Table

(b)User (Owner)

(c)Tablespace

(d)Full

Table Mode :

Table mode exports only specified tables in the user’s schema.And only a priviledged user can export specified tables owned by other users.

User Mode :

User mode exports all objects for a user’s schema.Privileged users can export all objects in the schemas of a specified set of users.This mode can be used to take a full database export.

Tablespace Mode :

The transportable tablespace feature enables you to move a set of tablespaces from one Oracle database to another.

To move or copy a set of tablespaces, you must make the tablespaces read-only, copy the datafiles of these tablespaces, and use Export and Import to move the database information (metadata) stored in the data dictionary. Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying flat binary files, such as the operating system copying facility, binary-mode FTP, or publishing on CD-ROMs.

After copying the datafiles and exporting the metadata, you can optionally put the tablespaces in read/write mode.

Full Database Mode :

Full Database Mode exports all the database objects,except the objects which are there in SYS schema.Only privileged users can export in this mode.

Note :

All users can export in table mode and user mode. Users with the EXP_FULL_DATABASE role (privileged users) can export in all modes.

To specify one of these modes, use the appropriate parameter (FULL, OWNER, TABLES, or TABLESPACES) when you invoke Export.

EXPORT WAYS :

There are 2 ways to do export of data from Oracle Database:

1)Conventional Path Export

2)Direct Path Export

Conventional Path Export is the default mode of the Export Utility & Direct Path Export can be done by setting parameter DIRECT=y.

Let me give you brief about these 2 ways of data export :

Inorder to transfer large amount of data between databases , Conventional path export takes more time than direct path export because while doing export via conventional path ,the export utility extracts the data from the database using SQL statments and dump the results obtained (by quering) into the Database Buffer Cache.And from the Buffer cache,the data then gets evaluated in the evaluating buffer and the finally gets dumped into the external binary dump file.

Where as while taking export via Direct path , there is no “evaluation of data” step and the data gets transferred to the external dump file from Database Buffer Cache.So this elimination of step leads to speedy data transfer.

Its good that export via Direct path is fast but it has the following restrictions :

1)The direct-path option cannot be invoked in interactive manner.

2)The character set on the client and server side should be same.For this we have to use the NLS_LANG environmental variable to set the same character set as the server .

3)The BUFFER parameter of export utility has no effect on dirct-path.It’s used only by the conventional path option.

4)The rows containing LOB,BFILE,REF or object types cant be exported using direct export.Only the data definition to create the table is exported but not the data.

Export Methods :

Export can be done using the following methods :

1)Command-line entries

2)Interactive Export prompts

3)Parameter files

4)Oracle Enterprise Manager

Note :

Many options are only available by using the command-lineinterface.You can also use a parameter file with command line.

METHOD 1 :

Command-line entries for export :

General Syntax :

EXP username/passowrd parameters(value1,value2,…..valuen)

First Lets have look at some export parameters which are oftenly used by DBA’s.

Examples

1)To create a export file named export1.dmp to export two tables called

EMPLOYEES & DEPARTMENTS, that are present in HR schema including all the rows of it.

C:\>EXP hr/hr tables=(employees,departments) rows=y file=export1.dmp

2)To create a fast export file named export2.dmp that includes all the

objects for HR schema

C:\>EXP system/manager file=export2.dmp owner=hr direct=y

Note : If the file is not specified then the default export fime name will

be expdat.dmp

3)To create an export file named export3.dmp that includes all the object

definitions belonging to the tablespace called ts_employees & also generate

a logfile named ts_employees.log

C:\>EXP system/manager TRANSPORT_TABLESPACE=y TABLESPACES=(ts_emplyees) LOG=ts_emplyees.log

4)To create an export file named=export4.dmp that includes all definitions and data modified in the database since the last cummulative or complete export.

C:\>EXP system/manager FULL=y INCTYPE=cummulative FILE=export4.dmp

Method 2 :

Interactive Export :

Step 1 : Set ORACLE_SID=(your sid name)

Set the ORACLE_SID to the name of the Oracle Instance with which you want the Export untility to communicate to extract the information.

Step 2 : Start the export in the command prompt by issuing the command “EXP”

C:\>exp

Step 3: It will ask for username and password.Then we have to provide the username and password with which we connect to the database.Here i have used

username:system

password:manager

Step 4 : So after getting user credentials(login/pwd),we have to answer the questions asked by Export Utility before exporting.

The first question would be the buffer size you want to use to fetch the data.The default size is 4096k.If you want to stick with the defalut size then just press ENTER key.

Enter array fetch buffer size:4096>

Step 5 : Then the Export untility will ask for the external dump file.So we need to provide the dump file name including path where you want to store the this file.Remember the dump file extention must be .dmp where as the file can be of any name.If we dont mention the name of the dump file then default name expdat.dmp will be considered.

Export file:expdat.dmp>emp.dmp

(here emp.dmp is the name of the dump file of user’s choice)

Step 6 : Next it will ask for the data that user wants to export.It can be entire database ,tables or users.

(1)E<ntire database>,(2)U<sers>,or (3)T<ables>;(2)U >

So if you want to export entire database then select the first option.

By default its Users option .

Step 7 : It will ask about the grants

By defalut its yes.I have also selected yes

Export grants <yes/no>:yes>y

Step 8 : The it will ask for the table data to export or not

By defalut its yes.I have also selected yes

Export table data <yes/no>:yes>y

Step 9 : Then it will ask whether to compress extents or not.

By default its yes.

Compress extents (yes/No):y

Step 10 : Once all the questions are answeres correctly , the export process will be started.

I have shown the execution of all the above 10 steps in the following screen shot.

Method 3 :

PAR FILE Export :

Step 1 : Set ORACLE_SID=orange

Step 2 : Create a seperate file having all the parameters lined up & save it as abc.dat , in a location of ur choice , as shown in the screen shot.