logo for asktheoracle.net
Home
AskTheOracle Blog
Oracle Tips & Tricks
Oracle Training
Oracle Tutorials
PL/SQL
SQL
Advanced Tutorials
Performance Tuning
Certification
Oracle 10g
Oracle 11g
Oracle and .Net
Oracle Utilities
Developer Tools
Oracle Questions?
Oracle News
Search This Site
About Us
Disclaimer
Privacy Policy
Contact Us

Subscribe To This Site
XML RSS
Add to Google
Add to My Yahoo!
Add to My MSN
Subscribe with Bloglines
leftimage for asktheoracle.net

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 database 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).

Oracle Tips & Tricks
to SKYROCKET Your Career!

Don't waste hours scouring the Internet for Oracle tips. Subscribe to our ezine and get them delivered straight to your in box.

There would be little value in just reproducing the Oracle utilities documentation here so we'll just provide overviews of a few them. 

SQL*Loader

Oracle SQL Loader has been around for a long while and along with the Oracle import utility is a great tool for transferring data from one database to another. SQL Loader has two major differences to the import utility though:-
  1. it can only load data from flat files
  2. because of that it can load data from any database (Oracle or non-Oracle) that can write flat files.
SQL Loader is also very versatile, being able to accept data in different character sets, fixed, variable-length, or streamed records, 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 export and import is that they are client-side Oracle 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).  Follow this link for more on Oracle export and import.

Oracle Data Pump

Oracle Data Pump was first introduced with Oracle 10g as a replacement for the Oracle utilities export and import.

The most significant difference between Data Pump and the export/import Oracle utilities is that Data Pump runs on the server where the database is located rather than on a client, thereby providing extra security for the dump files and improving performance as data does not have to be sent across the network.

The user interface with Data Pump is much the same as the import/export utilities but Data Pump also comes with a set of APIs which means you can export and import data via PL/SQL programs. Oracle data pump also allows the reading and writing of external tables by specifying the access driver as ORACLE_DATAPUMP.

It is important to note though that the data files containing external table data for use by the data pump utility are not compatible with external tables that can be read by the SQL*Loader driver. The data pump utility (like the export utility) creates files in a proprietary, binary format although different to the export utility format.

Follow this link for more on Oracle data pump.

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

Looking for expert Oracle training? Smartsoft offer Oracle training in the UK for both developers and dbas. Contact us today and discover how we can help you slash costs and explode productivity.

Streamline your Oracle systems with expert Oracle consulting services from Smartsoft. With skills honed to a fine edge over many years with myriad clients in a variety of industries, our consultants have the knowledge and experience to help you. Ask Smartsoft how you can reduce costs and improve uptime