3:52 AM
Deep Dey
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
There is little value in reproducing the Oracle documentation here so we'll just have quick look at a few them.
Oracle SQL Loader has been in existence for a number of years and like Oracle export/import and data pump is useful for transferring data from one database to another. The difference is that SQL Loader loads data from flat files and so can be used to load data from non-Oracle database.
SQL Loader is a versatile utility and 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. It can load multiple files at the same time into multiple tables and can load data from files on disk, on tape or from a named pipe.
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.
SQL*loader enables you to:
SQL Loader processes the input data files according to the directions in a text file called the control file which specifies the names and locations of the source data files, the format of the data to be loaded and the data transformations to be performed when loading.
As the input files are processed, any records that do not pass format checks are written to the bad file and any records that do not meet the specified selection criteria are written to the discard file.
Records that pass both format and selection criteria are written to the specified target tables but they may still be rejected because of, for example, constraint violations in which case they are written to the bad file along with those records rejected for being invalid.
A log file containing a detailed summary of the load, including a description of any errors that occurred during the load is also produced.
Oracle SQL*Loader is initiated from the command line and the various parameters such as the name of the control file and the userid can be specified at the same time but it is generally a lot easier to put all these parameters into a parameter file, thereby saving on typing and frustration when typos are made. Which of the conventional/direct path/external path load types to use is a trade off between performance and flexibility.
The default load type is the conventional load which creates and executes SQL insert statements to load the data into the target tables.
The direct-path load is initiated by specifying DIRECT=TRUE when starting SQL*Loader. This method writes formatted data blocks directly to the target tables bypassing the SQL layer making loading faster but has a number of restrictions. This method should be used when you need to load a large amount of data and need to maximise performance.
When using external-path load the source file has to be in a location accessible to the database and specified by an Oracle directory and the user must have been granted read and write access to the Oracle directory.
More information on SQL*Loader can be found in the Oracle Database Utilities reference manual
Oracle Export is a tool for making logical backups of (parts of) an Oracle 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 dump files are not secure. With Oracle 10g and above these disadvantages have been eliminated with the introduction of Oracle Data Pump.
These utilities can work on several different levels: database, tablespace, user (schema) and tables. They can only be used for logical backup and recovery (for example to recover the data of a table accidentally truncated by a user) because the export utility only takes a snapshot of the data at the time it is run and unlike with objects restored from physical backups can't be rolled forward by using the redo logs.
Backups using the export utility are performed with the database open so they are in a sense online backups, however (because recovered data can't be rolled forward) exports must be performed when there are no updates occurring on the objects being exported whilst the export is in progress. This is to ensure that the backup is consistent and can therefore be used for recovery.
The export utility is also good for capturing the database metadata as the export process generates the SQL statements to recreate the database objects (with or without the associated data) and stores them in the export dump file (by default called expdat.dmp)
The exported file is in a proprietary format which can only be read by the import utility, and not by the other utilities such as RMAN or SQL*Loader.
In Oracle 9i a new feature was added to the export utility to enable the export of a subset of the table data by use of a provided query (select clause) to retrieve a subset of the data.
Again, see the Oracle Database Utilities reference manual for more details on Oracle export and import.
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 also a set of APIs which enables you to export and import data from withing programs. 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. Refer to the Data Pump section of the Oracle Database Utilities reference manual for a user guide.
This is just a brief overview of some of the Oracle database utilities which are designed to be used primarily by database administrators. Full details on all of the utilities are available in the Oracle database utilities documentation.
For classroom-based Oracle training in the UK see Smartsoft Computing.
With our partner SkillBuilders we also offer live classroom-based Oracle training in the New York and Boston areas. And with Gogo Training we offer classroom-quality online training sessions, worldwide, 24/7.Contact us to benefit from a discount on these companies' training courses.
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);
- a command interpreter to enable the viewing of diagnostic data in the automatic diagnostic repository (ADRCI)
- and of course the Oracle database backup and recovery manager RMAN.
There is little value in reproducing the Oracle documentation here so we'll just have quick look at a few them.
SQL*Loader
Oracle SQL Loader has been in existence for a number of years and like Oracle export/import and data pump is useful for transferring data from one database to another. The difference is that SQL Loader loads data from flat files and so can be used to load data from non-Oracle database.
SQL Loader is a versatile utility and 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. It can load multiple files at the same time into multiple tables and can load data from files on disk, on tape or from a named pipe.
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.
Oracle SQL Loader Features
SQL*loader enables you to:
- load data from multiple files (from disk, tape or named pipe) into one or more target tables in the same load
- load data in any character set supported by Oracle
- load or discard records depending on values in the input fields
- transform the data before loading using SQL functions
- generate unique sequential keys for specified columns
- append to existing data or replace existing data
- load large objects (lobs), collections (nested tables and varrays) and object-relational data
How SQL*Loader Works
SQL Loader processes the input data files according to the directions in a text file called the control file which specifies the names and locations of the source data files, the format of the data to be loaded and the data transformations to be performed when loading.
As the input files are processed, any records that do not pass format checks are written to the bad file and any records that do not meet the specified selection criteria are written to the discard file.
Records that pass both format and selection criteria are written to the specified target tables but they may still be rejected because of, for example, constraint violations in which case they are written to the bad file along with those records rejected for being invalid.
A log file containing a detailed summary of the load, including a description of any errors that occurred during the load is also produced.
How To Use Oracle SQL Loader
Oracle SQL*Loader is initiated from the command line and the various parameters such as the name of the control file and the userid can be specified at the same time but it is generally a lot easier to put all these parameters into a parameter file, thereby saving on typing and frustration when typos are made. Which of the conventional/direct path/external path load types to use is a trade off between performance and flexibility.
The default load type is the conventional load which creates and executes SQL insert statements to load the data into the target tables.
The direct-path load is initiated by specifying DIRECT=TRUE when starting SQL*Loader. This method writes formatted data blocks directly to the target tables bypassing the SQL layer making loading faster but has a number of restrictions. This method should be used when you need to load a large amount of data and need to maximise performance.
When using external-path load the source file has to be in a location accessible to the database and specified by an Oracle directory and the user must have been granted read and write access to the Oracle directory.
More information on SQL*Loader can be found in the Oracle Database Utilities reference manual
Oracle Export and Import Utilities
Oracle Export is a tool for making logical backups of (parts of) an Oracle 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 dump files are not secure. With Oracle 10g and above these disadvantages have been eliminated with the introduction of Oracle Data Pump.
How Oracle import and export work
These utilities can work on several different levels: database, tablespace, user (schema) and tables. They can only be used for logical backup and recovery (for example to recover the data of a table accidentally truncated by a user) because the export utility only takes a snapshot of the data at the time it is run and unlike with objects restored from physical backups can't be rolled forward by using the redo logs.
Backups using the export utility are performed with the database open so they are in a sense online backups, however (because recovered data can't be rolled forward) exports must be performed when there are no updates occurring on the objects being exported whilst the export is in progress. This is to ensure that the backup is consistent and can therefore be used for recovery.
The export utility is also good for capturing the database metadata as the export process generates the SQL statements to recreate the database objects (with or without the associated data) and stores them in the export dump file (by default called expdat.dmp)
The exported file is in a proprietary format which can only be read by the import utility, and not by the other utilities such as RMAN or SQL*Loader.
In Oracle 9i a new feature was added to the export utility to enable the export of a subset of the table data by use of a provided query (select clause) to retrieve a subset of the data.
Again, see the Oracle Database Utilities reference manual for more details on Oracle export and import.
Oracle Data Pump Utility
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 also a set of APIs which enables you to export and import data from withing programs. 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. Refer to the Data Pump section of the Oracle Database Utilities reference manual for a user guide.
This is just a brief overview of some of the Oracle database utilities which are designed to be used primarily by database administrators. Full details on all of the utilities are available in the Oracle database utilities documentation.
For classroom-based Oracle training in the UK see Smartsoft Computing.
With our partner SkillBuilders we also offer live classroom-based Oracle training in the New York and Boston areas. And with Gogo Training we offer classroom-quality online training sessions, worldwide, 24/7.Contact us to benefit from a discount on these companies' training courses.
0 comments:
Post a Comment