Chapter:SQL*Loader

Lab1.Import text file to database

assume text file is like this:

   1: 60,CONSULTING,TORONTO
   2: 70,HR,OXFORD
   3: 80,EDUCATION,

 

Then user can write a control file of import as following:

   1: LOAD DATA 
   2: INFILE 'depts.txt'
   3: BADFILE 'depts.bad'
   4: DISCARDFILE 'depts.dsc'
   5: APPEND
   6: INTO TABLE DEPT
   7: FILEDS TERMINATED BY ','
   8: TRAILING NULLCOLS
   9: (DEPTNO INTEGER EXTERNAL(2),
  10: DNAME,
  11: LOC)

execute OS command:

   1: sqlldr control=depts.ctl log=depts.log
results of select:
   1: SQL> select * from iolab.dept;
   2:  
   3:     DEPTNO DNAME                LOC
   4: ---------- -------------------- ----------
   5:         60 CONSULTING           TORONTO
   6:         70 HR                   OXFORD
   7:         80 EDUCATION

 

Hints:One can use method of “Direct Path” to load data from text file.It load content from text file and write it to datafile directly,not like normal way of generating SQL sentences to insert every row to tables.

Lab2.External table

Function:It uses textfile on OS to be queried by database and it can’t be modified by database.

  • Create directory object 
   1: CREATE DIRECTORY IOLABDIR AS '/u01/app/oracle/iolab';
   2: GRANT READ,WRITE ON DIRECTORY IOLABDIR TO IOLAB;
  • Create text file
   1: John,Watson
   2: Roopesh,Ramklass
   3: Sam,Alapati
  • Edit control file of import 
   1: LOAD DATA
   2: INFILE 'names.txt'
   3: BADFILE 'names.bad'
   4: DISCARD 'names.dsc'
   5: TRUNCATE
   6: INTO TABLE NAMES
   7: FIELDS TERMINATED BY ','
   8: TRAILING NULLCOLS
   9: (FIRST,LAST)
  • Execute OS command
   1: sqlldr iolab/iolab control=names.ctl log=names.log external_table=generate_only;
  • View the log and get the model of “CREATE EXTERNAL TABLE”
   1: CREATE TABLE "SYS_SQLLDR_X_EXT_NAMES"
   2: (
   3:   "FIRST" CHAR(20),
   4:   "LAST" CHAR(20)
   5: )
   6: ORGANIZATION external
   7: (
   8:   TYPE oracle_loader
   9:   DEFAULT DIRECTORY IOLABDIR
  10:   ACCESS PARAMETERS
  11:   (
  12:     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
  13:     BADFILE 'IOLABDIR':'names.bad'
  14:     DISCARDFILE 'IOLABDIR':'names.dsc'
  15:     LOGFILE 'names.log_xt'
  16:     READSIZE 1048576
  17:     FIELDS TERMINATED BY "," LDRTRIM
  18:     MISSING FIELD VALUES ARE NULL
  19:     REJECT ROWS WITH ALL NULL FIELDS
  20:     (
  21:       "FIRST" CHAR(255)
  22:         TERMINATED BY ",",
  23:       "LAST" CHAR(255)
  24:         TERMINATED BY ","
  25:     )
  26:   )
  27:   location
  28:   (
  29:     'names.txt'
  30:   )
  31: )REJECT LIMIT UNLIMITED
  • Edit it as you like and then create external table
  • Results of query 
   1: SQL> select * from names;
   2:  
   3: FIRST                LAST
   4: -------------------- --------------------
   5: John                 Watson
   6: Roopesh              Ramklass
   7: Sam                  Alapati
Chapter:Data Pump(summary)
Function:Data Pump utilites can import and export data from or to oracle-exclusive file.
  • Export to file(The directory object should exist)
   1: expdp system/manager@orcl11g full=y dumpfile=datadir:full_%U.dmp filesize=2G compression=all
  • Import from file(The directory object should exist) 
   1: impdp system/manager@orcl11g full=y directory=samba_dir dumpfile=full_%U.dmp
  • Transport tablespace(The outline)
   1: SQL 'ALTER TABLESPACE XXX OFFLINE/READONLY' ON SOURCE HOST
   2: EXPORT METADATA OF THE TABLESPACE BY DATAPUMP
   3: COPY DATAFILES AND METADATA FILES OF THE TABLESPACE TO DESTINATE DATABASE
   4: IMPORT METADATA AND DATAFILES OF THE TABLESPACE BY DATAPUMP ON DESTINATE HOST
   5: SQL 'ALTER TABLESPACE XXX ONLINE' ON SOURCE HOST
Hints:When it’s performing transporting tablespace,operator should focus on ENDIAN_FORMAT on different platform.
If the source and the destination databases’ ENDIAN_FORMAT are not matched,operator would use RMAN to convert datafile.
As an example:RMAN> convert datafile ‘/u02/ttsfiles/ts1.dbf’ from platform=’Linux IA (32-bit)’ format ‘/u02/ttsfiles/ts1conv.dbf’
the characters of platform is referred by query ‘SELECT * FROM TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME’;