Interactive

Complete Syntax Dump ( Data)

GUI Log (ASCII) Identifies & Writes the
Shell Script blocks in Log File.

Executable Files Corrupt Blocks

Loading Data into a Database

EXPORTS

Destination Type
Tape Drive (s)
File (s) Regular Incremental
NFS Vol Table (s)
Incremental
User (s) - Schema
Cumulative
DB (Full) -DBA Complete

Copyright © Oracle Corporation, 2001. All rights reserved.

Data Loading Methods

Insert
Append
Load Options Other applications
Replace
Truncate
Oracle
database
SQL Loader Export
SQL*Loader

Types of Input Files

Input File
Delimiter Fixed Position Import
Control File
Comma Log File
Oracle
Tilde Bad File database
Semicolon Direct Load
Disc File
Day - 6 Page 1
 SQL*Loader Using SQL*Loader
Loader control file
Input datafiles
$sqlldr hr/hr \
> control=case1.ctl \
Parameter file > log=case1.log
SQL*Loader Rejected
(optional)
Field processing
case1.ctl
Discarded Accepted
Record selection Bad
Selected file
SQL*Loader
Oracle server
Discard file
Inserted Rejected
(optional)
EMPLOYEES table
Log file
case1.log
Database datafiles

SQL*Loader Control File Input Data and Datafiles

The loader control file tells SQL*Loader: • SQL*Loader reads data from one or more files
• Where to find the load data specified in the control file.
• The data format • From SQL*Loader’s perspective, the data in the
datafile is organized as records.
• How to manipulate the data
• A datafile can be in one of two formats:
– Fixed-record format
– Variable-record format

Day - 6 Page 2
 Logical Records Discarded or Rejected Records

SQL*Loader can be instructed to follow one of the • Bad file

following two logical record-forming strategies: – SQL*Loader rejects records when the input format is
• Combine a fixed number of physical records to form invalid.
each logical record. – If the Oracle database finds that the row is invalid, then
the record is rejected and SQL*Loader puts it in the
• Combine physical records into logical records while
bad file.
a certain condition is true.
• Discard file
– This can be used only if it has been enabled.
– This file contains records that were filtered out
because they did not match any record-selection
criteria specified in the control file.

Oracle Export and Import Utilities

Transporting Data Between Databases Oracle

database

Export

Import
Oracle
database

Day - 6 Page 3
Copyright © Oracle Corporation, 2001. All rights reserved.
 Oracle Export and Import Utility Overview Methods of Invoking the
Export and Import Utilities

You can use these utilities to do the following: • Command-line interface

• Save table definitions to protect them from user • An interactive dialog
error failure • Parameter files
• Move data between machines and databases or
between different versions of the Oracle server
• Transport tablespaces between databases

Export Modes Invoking Export

Tablespace Full Database

Table Mode User Mode Mode Mode • Syntax:
Table Tables Table Tables exp keyword = value, value2, … ,valuen
definitions definitions definitions definitions
Table data (all or Tables data Tables data • Examples:
selected rows) exp hr/hr TABLES=employees,departments
Owner’s table Owner’s grants Grants Grants
rows=y file=exp1.dmp
grants
Owner’s table Owner’s indexes Indexes Indexes exp system/manager OWNER=hr direct=y
indexes
file=expdat.dmp
Table Tables Table Tables
constraints constraints constraints constraints exp \'username/password AS SYSDBA\'
Triggers TRANSPORT_TABLESPACE=y
TABLESPACES=ts_emp log=ts_emp.log
Day - 6
Page 4
 Uses of the Import Utility for Recovery Import Modes

• Create table definitions Mode Description

• Extract data from a valid Export file
Table Import specified tables into a schema.
• Import from a complete or cumulative Export file
• Recover from user-error failures User Import all objects that belong to a
schema

Tablespace Import all definitions of the objects

contained in the tablespace

Full Database Import all objects from the export file

Invoking Import Invoking Import as SYSDBA

• Syntax: • You need to invoke Import as SYSDBA under the

imp keyword = value or keyword = value, following conditions:
value2, … value n – At the request of Oracle technical support
• Examples: – When importing a transportable tablespace set
imp hr/hr TABLES=employees,departments • To invoke Import as SYSDBA:
rows=y file=exp1.dmp imp \'username/password AS SYSDBA\'

imp system/manager FROMUSER=hr file=exp2.dmp

imp \'username/password AS SYSDBA\'

TRANSPORT_TABLESPACE=y
TABLESPACES=ts_employees
Page 5
Day - 6
 Import Process Sequence

1. New tables are created

2. Data is imported
3. Indexes are built
4. Triggers are imported
5. Integrity constraints are enabled on the new tables
6. Any bitmap, functional, and/or domain indexes are
built

Day - 6 Page 6