Home
Oracle Training
Oracle Tutorials
Oracle 10g
Oracle 11g
Oracle App Server
Backup and recovery
Contact Us
Oracle Utilities
Oracle Certification
Oracle 9i
Oracle Resources
Free SQL Tutorial
Oracle Secrets Signup
PL/SQL
SQL

Oracle Utilities Overview

There are a number of Oracle utilities supplied with the database, the majority of which are concerned with loading data into or extracting data from the database (SQL*Loader, Oracle Data Pump, Oracle export and import).

There are also other utilities for tasks such as

  • analysing the redo logs (Log Miner);
  • performing physical data structure checks on databases and backup files (dbverify);
  • changing the database name and the internal database id to enable a cloned database and the original database to be managed in the same Recovery Manager catalog (dbnewid);
  • and a command interpreter to enable the viewing of diagnostic data in the automatic diagnostic repository (ADRCI).

There is little value in reproducing the Oracle utilities documentation here so we'll just have quick look at a few them. There are more detailed discussions of SQL*Loader, Oracle Data Pump and Oracle export and import elsewhere on the site.

SQL*Loader

Oracle SQL Loader has been around for a long while and like Oracle export and import is a good tool for transferring data from one database to another. The difference is that SQL Loader loads data from flat files and so is useful for loading data from non-Oracle database.

SQL Loader is a very versatile utility. It is able to accept data in different character sets and an almost infinite variety of formats, load or discard records depending on values in data fields and load multiple files at once.

In Oracle 9i the SQL*Loader technology was adapted in order for Oracle databases to be able to treat flat files as if they were read-only tables and thereby run SQL select statements on the data without having to load it into the database.

Follow this link for more on SQL Loader.

Oracle Export and Import

Oracle Export is useful for taking logical backups of (parts of) the database to supplement physical backups, for transferring data from one Oracle database to another, or for upgrading to a new version of Oracle.

The import utility is the mirror image of the export utility - it can be used to load data from another Oracle database or data that was previously exported as a backup. The import utility can only read files written by the export utility as the files are written in a format proprietary to Oracle.

One disadvantage of using Oracle export and import is that they are client-side utilities which means there is an extra overhead on the network to transfer data backwards and forwards and the export files are not secure. With Oracle 10g and above these disadvantages have been eliminated with the introduction of Oracle Data Pump (see below). Oracle Import utility. Follow this link for more details on Oracle export and Import.

Oracle Data Pump

Oracle Data Pump was introduced with Oracle 10g as a replacement for the Oracle Export and Import utilities. The user interface is much the same but with Data Pump there is a set of APIs which means you can use PL/SQL to export and import data. The performance of exports and imports is also improved. Oracle data pump also allows the reading and writing of external tables by specifying the access driver as ORACLE_DATAPUMP.

That was just a brief overview of some of what might be called the Oracle dba utilities in the sense that they are designed to be used primarily by dbas. Full details on all of these utilities are available in the Oracle database documentation.

Return from Oracle utilities to home page


footer for oracle utilities page