Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle 9i Utilities

By Exforsys | on April 30, 2005 |
Oracle 9i

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 executable files in the BIN directory. And can be executed by typing their name before command prompt. In the following sections I will be discussing syntax 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:sampledept.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.

Fig 1 explains how to use EXP utility in interactive mode.

Fig 2 illustrates exporting data from multiple tables non-interactively (by giving the giving the parameters in the command).

Exporting data from a table conditionally:

Fig 3 gives the screen shot to export employees of deptno = 10 .



Suppose if you want to export data of employees with salary less than 2000 then the following query can be used.

EXP SCOTT/TIGER FILE= abc.dmp TABLES=emp QUERY=” WHERE SAL < 2000 ”

Note: QUERY parameter is available from Oracle 8i

Import Utility

This utility is used to extract objects (tables etc) from the export file (.dmp file) created using EXP utility.

IMP command can be used to invoke import utility interactively without any parameters. (Which 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:

imp username/password PARFILE = filename
(Or)
imp PARFILE = filename

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

Fig. 4 explains the usage of IMP utility with parameters (non-interactively) in the command-line.

 


Points to Ponder

It is possible to import dump created using an earlier (version 8.1.7 utility) version can be imported using the later version utility (Version 9.0.1 utility). We should not use later version utilities to export data from earlier database versions. But an earlier utility can be used to export later versions of database. For example you can export data from Oracle9i using 8.1.7 utility and can import that exported file into oracle 8i database using import utility 8.1.7 .

SQL * Loader

It is an Oracle utility used for moving bulk data from external files into the Oracle database. Data from any text file can be loaded into database. SQL*Loader reads data from an external file and loads data into an existing table while the Oracle database is open.

SQL*Loader Datatypes: SQL*Loader uses the following datatypes.

1. CHAR
2. DECIMAL
3. INTEGER

SQL*Loader require two input files a control file and another data file. The control file is a text file details the task to be carried out by the SQL*Loader. It tells the SQL*Loader where data is available how to parse and interpret it also where to insert it. The data file contains the data to be loaded.
A control file may be vaguely divided into 3 sections

1.First section contain INFILE clause in this we specify where input data is located.
2. The second section have INTO TABLE block that details the table and column names into which data is stored.
3. Third section is optional, If present it contain input data.

SQL* Loader assumes that data in data file is organized as records. Based on the record type data files could be categorized into

Fixed record files: All the records are of same(fixed) length
Variable record files: Records are of varying length and Streamed record files.

Note: If the data is specified in the control file, then we write INFILE * and the data is treated as streamed record format and the records separated by default record terminator.

Examples

The following example explains how to load data from file with fixed record format.

Creating Control File

Using DOS editor or notepad editor you can create data file as shown below.

LOAD DATA
INFILE ‘mydata1.dat ‘ “fix 18”
fields terminated by ‘,’
(sno ,sname , course )

Save this file under the name myctrl1.ctl into the current directory.

Creating Data File

In the DOS editor or Notepad you can create data file as shown below.

1001,RAJAN, ASP,
1002,KISHAN, J2EE,
1003,PRABHU, JSP,
1004,PRANAY, ORACLE,
1005,JOHN, APPS,
1006,MARTIN, ORACLE

Save the above content as file with the name “mydata1.dat”
The following figure explains how data can be loaded into a table. The data loaded here is of fixed-length records

To cross check whether loading was proper we execute simple SELECT statement as given below.


For More examples follow the links below.

http://www.pafumi.net/sql_loader.htm
http://www.oracleutilities.com/OSUtil/sqlldr.html

« « How Bluetooth Technology works?
MSAS – Introducing Analysis Services Security » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • Oracle 9i Database Triggers

    March 28, 2005 - 0 Comment
  • Oracle 9i Packages

    April 7, 2005 - 0 Comment
  • Oracle 9i Tables and Constraints

    February 2, 2005 - 0 Comment
  • Introduction to Oracle 9i SQL, PLSQL, and SQL *Plus

    October 24, 2004 - 0 Comment
  • More Oracle 9i Database Objects

    February 14, 2005 - 0 Comment
  • Oracle 9i Software Installation, SQL, PLSQL and SQL *Plus References

    October 25, 2004 - 0 Comment
  • Building PL/SQL Blocks in Oracle 9i

    February 16, 2005 - 0 Comment
  • Oracle 9i PL/SQL Control Structures

    February 26, 2005 - 0 Comment
  • Oracle 9i Cursors

    February 27, 2005 - 0 Comment
  • Download example SQL Scripts used in Oracle 9i Tutorials

    March 3, 2005 - 0 Comment
  • Oracle 9i Packages

    April 7, 2005 - 0 Comment
  • Oracle 9i Database Triggers

    March 28, 2005 - 0 Comment
  • Oracle 9i Procedures and Functions

    March 13, 2005 - 0 Comment
  • Oracle 9i PL/SQL Collections

    March 6, 2005 - 0 Comment
  • Oracle 9i Exception Handling

    March 3, 2005 - 0 Comment
  • Download example SQL Scripts used in Oracle 9i Tutorials

    March 3, 2005 - 0 Comment
  • Oracle 9i Cursors

    February 27, 2005 - 0 Comment
  • Oracle 9i PL/SQL Control Structures

    February 26, 2005 - 0 Comment
  • Building PL/SQL Blocks in Oracle 9i

    February 16, 2005 - 0 Comment
  • More Oracle 9i Database Objects

    February 14, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle 9i Packages
  • Oracle 9i Database Triggers
  • Oracle 9i Procedures and Functions
  • Oracle 9i PL/SQL Collections
  • Oracle 9i Exception Handling

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT