Transcript CLIENT/SERVER and SQL*NET

INTRODUCTION TO ORACLE
Lynnwood Brown
System Managers LLC
Data Loading
Copyright System Managers LLC 2003 all rights reserved.
DATA LOADING
Use the EXPORT/IMPORT utilities to move bulk
data from one Oracle database into another Oracle
database.
• Logical backup – Saves the data and the DDL
used to create the database objects
• Migration Tool
• Online documentation - C:\> exp{imp} help=y
Data can also be loaded using SQL*PLUS “INSERT”
commands or the Oracle utility SQL*LOADER.
DATA LOADING
Types Of Exports.
• Full Export
– EXP system/manager file = exp.dat full = y log =
exp.log
• User Level Export
– EXP system/manager file = exp.dat owner = scott log
= exp.log
• Table Level Export
– EXP scott/tiger file = exp.dat TABLES=(emp,dept)
log = exp.log
DATA LOADING
Types Of Imports.
• Full Import
– IMP system/manager file = exp.dat full = y log =
imp.log commit = y
• User Level Import
– IMP system/manager file = exp.dat fromuser=scott
touser=scott log = imp.log commit = y
• Table Level Import
– IMP scott/tiger file = exp.dat TABLES=(emp.dept) log
= imp.log commit = y
DATA LOADING cont.
SQL*LOADER - Oracle utility used to load
external file data into Oracle database tables
• Used when large amounts of data must be loaded.
• Loads data contained in a data file into one or
more tables
• Requires a SQL*LOADER control file
Copyright System Managers LLC 2003 all rights reserved.
DATA LOADING cont.
SQL*LOADER Syntax:
C:\> sqlldr scott/tiger control=my_control_file.ctl
log=my_load.log
Data File Contents – file name = ubs.dat
930|1058|Edward S.Eng|ibm
931|2157|John Ford|hp
932|1457|Melanie Goldman|msft
933|1657|Jane Williams|dell
Copyright System Managers LLC 2003 all rights reserved.
DATA LOADING cont.
SQL*LOADER Control File – Tells the loader how
to how to interpret the data file:
LOAD DATA
INFILE 'ubs.dat'
BADFILE 'ubs2.bad'
DISCARDFILE 'ubs2.dsc'
APPEND
INTO TABLE ubs.table_customer
FIELDS TERMINATED BY '|'OPTIONALLY ENCLOSED BY
'"'
TRAILING NULLCOLS
(CUST_NO, SITE_NO, CUST_NAME ,SITE_ ABBREV)
DATA LOADING cont.
SQL*LOADER Control File – Tells the loader how
to load the data:
• INFILE – File containing the data to be loaded
• BADFILE – Records that are rejected go into this file
• DISCARDFILE – Records that do not meet a loading
criteria
Data can be appended onto existing data, inserted or
replaced - APPEND/INSERT/REPLACE