Lesson 2 | Export and import improvements |
Objective | Describe the Improvements in Export and Import. |
Oracle Export and Import Components
In this lesson, we will explore all the changes made within the export and import utilities to improve on their earlier versions.
Within Export, the user now has the ability to specify a subset of rows from a set of tables, when performing the table mode of export. The query parameter is a string that contains a WHERE
clause for a SQL SELECT
statement,
which is applied to all the tables listed within the TABLE
parameter.
EXP PETSTORE/GREATPETS TABLES=PRODUCT_OBJ_TABLE QUERY
=\"WHERE PET_FLAG = 'Y'\"
For example, if user PETSTORE
wants to export only those records from the PRODUCT_OBJ_TABLE
where the PET_FLAG
is 'Y
,' the export command will be as follows. View the Code below.
SELECT * FROM PRODUCT_OBJ_TABLE
WHERE PET_FLAG = 'Y';
WHERE Clause
The
WHERE
clause starts with
"\ and ends with \"
. When executing this command, Export builds a
SELECT
statement similar to the following code.
The following are some restrictions to the
QUERY
parameter:
- This parameter cannot be specified for full, user, or transportable tablespace mode exports
- This parameter must be applicable to all specified tables
- This parameter cannot be specified within a direct path export (
DIRECT=Y
)
- This parameter cannot be specified for tables with inner nested tables
Export and import Utilities in Oracle
When performing export and import, you can now specify the number of files that should be exported and the size of each file.
As soon as the first file reaches its limit (specified within
FILESIZE
), the export utility closes the file and starts writing to the next file. The export utility continues writing until complete or the maximum value of
FILESIZE
is again reached. If sufficient export filenames to complete the export are not specified, Export will prompt you to provide additional filenames. Because of this enhancement, a user can avoid the problems encountered during export when the file size exceeds two gigabytes. Within Oracle, the export utility enables you to export tables that contain LOBs and objects, even if a direct path is specified on the command line. On export, the LONG datatypes are fetched into sections. However, sufficient memory must be available to hold all the contents of each row, including the LONG data.
Optimization is the process of choosing the most efficient way to execute a SQL statement. This is an important step in processing any data manipulation language (DML) statement such as
SELECT
,
INSERT
,
UPDATE
, or
DELETE
. A part of Oracle called the
optimizer calculates the most efficient way to execute a SQL statement.
The optimizer evaluates several factors to select among alternative access paths. It can use a
- cost-based approach or
- rule-based approach.
The cost-based approach relies on statistics.
Hence, it is necessary to generate statistics for all tables, clusters, and all types of indexes accessed by SQL statements before using the cost-based approach. If the size and data distribution of tables change frequently, statistics must be generated regularly to ensure that the statistics accurately represent the data within the tables. The generation of statistics is an extensive and resource consuming process. Oracle provides the ability to export and import pre-calculated optimizer statistics, instead of recomputing the statistics at the time of import.
SQL Optimizer
Optimization is a process of choosing the most efficient way to execute a SQL statement. A part of Oracle called the optimizer calculates the most efficient way to execute a SQL statement. The optimizer evaluates several factors to select among alternative access paths. The next lesson describes implementing duplex or multiple archive logs.
Ad Oracle Backup Recovery