Exforsys.com
 
Home Tutorials Oracle 9i
 

Tutorial 16: Oracle 9i: Oracle Utilities

 

Tutorial 16: Oracle 9i: Oracle Utilities

Page 1 of 3
This tutorial explains about Export Utility, Import Utility and SQL* Loader. They are supplied as part of the Oracle Software and need not have to be purchased or downloaded separately.

Oracle Utilities


The 3 utilities supplied along with Oracle Server are


  1. Export
  2. Import 
  3.  SQL* Loader

They are supplied as part of the Oracle Software and need not have to be purchased or downloaded separately. They are available as .exe files in the BIN directory. And can be executed by typing their name before command prompt. In the following sections I will be discussing syntaxes and the usage of these utilities.


Export Utility

This utility can be used to transfer data objects between oracle databases. The objects and the data in Oracle database can be moved to other Oracle database running even on a different hardware and software configurations.


The export utility copies database definitions and actual data into an operating system file (export file). The export file is an Oracle binary-format dump file (with .dmp extension), which is normally created on disk or tape. Before exporting we must ensure that there is enough space available on the disk or tape used.


Exported dump files can be read only by using the Import utility of Oracle. We cannot use earlier versions of import utility for importing the data exported using current version (Versions of Oracle utilities also change along with the Oracle Versions)

EXP command can be used to invoke export utility interactively without any parameters. (Requests the user to enter the value). Otherwise parameters can be specified in a file called parameter file. We can use more than one parameter file at a time with exp command.


Syntax:
exp PARFILE = filename

Parameter file is a simple text file creating using any text editor.

The exports are 3 types; Full, Owner, and Table. Full export exports all the objects, structures and data within the database for all schemas. Owner export exports only the objects owned by specific user account. Table export exports only tables owned by a specific user account.

To export a table we can run EXP utility either interactively or by putting all the parameters for the export on the command line. In interactive mode just type EXP before the command prompt and answer the questions when prompted, otherwise the parameters can be typed on the command line as shown below.

EXP scott/tiger file=emp.dmp tables=(EMP) log= error.log

In the above example SCOTT/TIGER is the username and password respectively.



emp.dat is the file into which exporting is done. This file is created in the current folder, to create it in a different folder we need to mention the complete path. Ex. C:\sample\dept.dmp.


Tables parameter takes table names as it value, o export more than one table their names need to be separated by a comma. Example tables = (EMP, DEPT, SALGRADE) to export tables EMP, DEPT and SALGRADE.


Log parameter is optional; we give a file name as its value. This file is used to write errors if any occur while exporting a table.


Next Page: Tutorial 16: Oracle 9i: Oracle Utilities - Page 2


Read Next: Oracle 9i: SQL, PLSQL, and SQL *Plus - Introduction

 

 

Comments


pappu singh said:

  The section is very nice and easily understandable .
September 13, 2006, 4:27 am

PRAVEEN1 said:

  THE INFORMATION PROVIDED IS VERY USEFUL. THANKS A LOT
THERE IS ONE POINT LEFT WHILE CREATING CONTROL FILE IN NOTEPAD, YOU FORGOT TO MENTION INTO TABLE BLOCK.
May 7, 2007, 12:54 am

Salman Hunzai said:

  Thank you very much for this very informative sorce... It was very helpful indeed..
August 23, 2007, 3:11 pm

vijay said:

  great tutorial thanks a lot
November 7, 2008, 8:53 am

Kasi, Vijayakumar said:

  The section is easily understandable. Thanks in anticipation.
December 3, 2008, 3:54 am

rvnagarajan said:

  thanks a lot, very useful
January 2, 2009, 3:42 am

GM said:

  its explained step by step with screenshot, any layman can easily understand thanks
January 20, 2009, 10:25 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape