Throughout this series we have seen a number of improvements provided by Oracle. This module discusses the benefits to Database Administrators (DBAs). From loading data to dropping columns from tables, Oracle not only saves the DBA a lot of time and effort, but also includes new features that make the database more flexible.
By the end of this module you will be able to:
- List improvements to SQL*Loader
- Load large object (LOB) data with SQL*Loader
- Describe enhancements in table management
- Relocate a table
- Describe why and how to create a temporary table
- Find and remove an unused column from a table
- List new database limits
In the next lesson we will look at the improved SQL*Loader utility.
SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following:
- Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.
- Load data from multiple datafiles during the same load session.
- Load data into multiple tables during the same load session.
- Specify the character set of the data.
- Selectively load data (you can load records based on the records' values).
- Manipulate the data before loading it, using SQL functions.
- Generate unique sequential key values in specified columns.
- Use the operating system's file system to access the datafiles.
- Load data from disk, tape, or named pipe.
- Generate sophisticated error reports, which greatly aid troubleshooting.
- Load arbitrarily complex object-relational data.
- Use secondary datafiles for loading LOBs and collections.
- Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance
A typical SQL*Loader session takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. The output of SQL*Loader is an Oracle
database (where the data is loaded), a log file, a bad file, and potentially, a discard file. An example of the flow of a SQL*Loader session is shown in Figure 5-1.