Using SQLloader on Oracle 11g

I taught a session on SQLloader over the weekend, so I am going to post the highlights and the exercises I created for the students on this space.

What is Sqlloader?

It is a database tool for loading large volumes of data into the database. It is faster than using an insert and more reliable too. Also, it is free because it comes with the Oracle database utilities.

How to invoke Sqlloader

Lauch sqlloader using the command below:

sqlldr username/password control=controlfile

e.g

sqlldr system/**** control=my_control_file.ctl


Valid Keywords/arguments

    userid --     ORACLE username/password
   control --     control file name
       log --       log file name
       bad --      bad file name
      data --      data file name
   discard --    discard file name
discardmax -- number of discards to allow          (Default all)
      skip --      number of logical records to skip    (Default 0)
      load --      number of logical records to load    (Default all)
    errors --     number of errors to allow            (Default 50)
      rows --     number of rows in conventional path bind array or between direct path data saves
                      (Default: Conventional path 64, Direct path all)
  bindsize --   size of conventional path bind array in bytes  (Default 256000)
    silent --      suppress messages during run (header, errors, discards, partitions)
    direct --      use direct path                      (Default FALSE)
   parfile --      parameter file: name of file that contains parameter specifications


example 1: Use sqlloader to load data into table PERSON

log in as SYSTEM

SQL> create table PERSON
(SSN number(15),
National_ID number(15),
Last_name varchar2(30),
first_name varchar2(15),
sex char(1)
);

create a control file- "person_ctl.txt"

add this.....

LOAD DATA
  INFILE 'c:\person_data.txt'
  BADFILE 'c:\person_data_bad.txt'
  APPEND
  INTO TABLE PERSON
  FIELDS TERMINATED BY ","
  TRAILING NULLCOLS
( SSN,
National_ID,
Last_name,
first_name,
sex 
)

create an infile- "person_data.txt"

add this.....

77770101,7777801,JEFFEREY,KIDMAN,m
77770102,7777802,Clinton R,miller,m
77770103,7777803,ALLEN D,IVES,m
77770104,7777804,Matthew,MAXWELL,f
77770105,7777805,ANTHONY J,REISNER,m
77770106,7777806,CLINT A,KOCH,m
77770107,7777807,ORLANDO,Desantis,m
77770108,7777808,JUAN C,ZIMMERMAN,m
77770109,7777809,STEVE J,EVANS,m
77770110,7777810,Robert D,DAVIS,m
77770111,7777811,Michael,McClendon,m
77770112,7777812,JONATHAN,CONGER,m
77770113,7777813,DAVID F,LOVITT,m
77770114,7777814,Ernesto,FLESHMAN,m
77770115,7777815,Joshua B,pierce,m
77770116,7777816,Justin,OLIVER,m
77770117,7777817,TODD J,Jacobs,m
77770118,7777818,Conner,BROWN,m
77770119,7777819,Dewey,WOOD,m
77770120,7777820,FRANK C,ARAUJO,m
77770121,7777821,SCOTT A,OBYRNE,m
77770122,7777822,JESSE W,HARDIN,m
77770123,7777823,MATTHEW D,CASTOR,m
77770124,7777824,JOSHUA,ANDREWS,m
77770125,7777825,JUSTIN B,Depew,m
77770126,7777826,MATTHEW F,FORST,m
77770127,7777827,ALFONSO L,OGBORN,m
77770128,7777828,COREY J,DINOFF,m
77770129,7777829,CHRISTOPHER J,COMETA,m
77770130,7777830,PEDRO L,WEBB,m
77770131,7777831,ZACHERY R,GUTIERREZ,m
77770132,7777832,George,SAIKU,m
77770133,7777833,WILLIAM P,FISHER,m
77770134,7777834,ANTHONY R,STOWBUNKO,m
77770135,7777835,Terry,GILCHRIST,m
77770136,7777836,ERIC M,LEE,m
77770137,7777837,CHARLES W,MONTGOMERY,m
77770138,7777838,CHRISTOPHER S,SZOKE,m
77770139,7777839,RANDALL J,Konig,m
77770140,7777840,Robert E,TURNBOW,m
77770141,7777841,JUSTIN R,GUTIERREZ,m
77770142,7777842,NICHOLAS P,klein,m
77770143,7777843,ROBERT W,ALLEY,m
77770144,7777844,PAUL D,SANTOCONO,m
77770145,7777845,KORY J,GOMEZ,m
77770146,7777846,WALTER T,STEADMAN,m
77770147,7777847,JOSHUA D,Martin,m
77770148,7777848,CHAN P,GWIZDAK,m
77770149,7777849,CHRISTOPHER W,OKKEN,m
77770150,7777850,NAQUAN D,CARMICHAEL,m


Launch sqlloader from c:\


c:\> sqlldr system/**** control=person_ctl.txt


example 2: Use sqlloader to load data into table SCHOOL

log in as SYSTEM

SQL> create table SCHOOL
(SCH_ID number(15),
SSN number(15),
state_id number(7),
county_id number(7),
county number(7),
state char(2)
);

create a control file- "sch_ctl.txt"

add this.....

LOAD DATA
  INFILE 'c:\sch_data.txt'
  BADFILE 'c:\sch_data_bad.txt'
  APPEND
  INTO TABLE PERSON
  FIELDS TERMINATED BY ","
  TRAILING NULLCOLS
( SCH_ID,
SSN,
state_id,
county_id,
county,
state
)

create an infile- "sch_data.txt"

add this.....

7777801,77770101,262,2020,2020,ga
7777802,77770102,262,2020,2020,ma
7777803,77770103,262,2020,2020,la
7777804,77770104,262,2020,2020,tx
7777805,77770105,262,2020,2020,md
7777806,77770106,262,2020,2020,mi
7777807,77770107,262,2020,2020,mo
7777808,77770108,262,2020,2020,mo
7777809,77770109,262,2020,2020,mi
7777810,77770110,262,2020,2020,tx
7777811,77770111,262,2020,2020,tx
7777812,77770112,262,2020,2020,tx
7777813,77770113,262,2020,2020,md
7777814,77770114,262,2020,2020,md
7777815,77770115,262,2020,2020,sd
7777816,77770116,262,2020,2020,id
7777817,77770117,262,2020,2020,mi
7777818,77770118,262,2020,2020,dc
7777819,77770119,262,2020,2020,dc
7777820,77770120,262,2020,2020,dc
7777821,77770121,262,2020,2020,dc
7777822,77770122,262,2020,2020,dc
7777823,77770123,262,2020,2020,dc
7777824,77770124,262,2020,2020,mi
7777825,77770125,262,2020,2020,dc
7777826,77770126,262,2020,2020,md
7777827,77770127,262,2020,2020,ms
7777828,77770128,262,2020,2020,tn
7777829,77770129,262,2020,2020,tx
7777830,77770130,262,2020,2020,oh
7777831,77770131,262,2020,2020,mi
7777832,77770132,262,2020,2020,az
7777833,77770133,262,2020,2020,ca
7777834,77770134,262,2020,2020,oh
7777835,77770135,262,2020,2020,ga
7777836,77770136,262,2020,2020,ny
7777837,77770137,262,2020,2020,ny
7777838,77770138,262,2020,2020,nj
7777839,77770139,262,2020,2020,nj
7777840,77770140,262,2020,2020,pa


Launch sqlloader from c:\


c:\> sqlldr system/**** control=sch_ctl.txt

NOTE:

To avoid errors, launch SQLldr from the path where your control file is located,
e.g if your control file is c:\database\control1.ctl, then run sqlldr from 'c:\database'

Datapump in Oracle 11g

Datapump import/export are used for data migration. You can export TABLES, SCHEMA, TABLESPACE or DATABASE and restore it on another server.

Quickfacts:

1. Datapump produces a dumpfile with extention .dmp or .imp
2. Privilege 'exp_full_database' is required to export full database
3. Privilege 'imp_full_database' is required to impport full database
4. In datapump, you can user a parfile to store your commands

Getting the help page

expdp help=y

Keyword                                  Description
------------------------------------------------------------------------------
DIRECTORY                          Directory object to be used for dumpfiles and logfiles.
DUMPFILE                             List of destination dump files (expdat.dmp),
                                                 e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
EXCLUDE                              Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FULL                                       Export entire database (N).
HELP                                       Display Help messages (N).
INCLUDE                                Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME                            Name of export job to create.
LOGFILE                                 Log file name (export.log).
NETWORK_LINK                  Name of remote database link to the source system.
NOLOGFILE                           Do not write logfile (N).
PARALLEL                             Change the number of active workers for current job.
PARFILE                                 Specify parameter file.
SCHEMAS                              List of schemas to export (login schema).
TABLES                                  Identifies a list of tables to export - one schema only.
TABLESPACES                     Identifies a list of tablespaces to export.
VERSION                              Version of objects to export where valid keywords are:
                                                (COMPATIBLE), LATEST, or any valid database version.
EXPORT

Example 1: Joe is a user on a database, Joe needs to export the whole database.
Solution:
c:\> sqlplus sys/**** as sysdba
     grant exp_full_database to joe;
     commit;
     exit;
c:\> expdp joe/**** full=y dumpfile=joe_database.dmp logfile=joe.log compression=n

^full database is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'

Example 2: System needs to export Joe's schema with compression
Solution:
c:\> expdp system/**** full=n schemas=joe dumpfile=joe_schema.dmp logfile=joe1.log compression=y

^Joe's schema is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'
^Dumpfile will be compressed

Example 3: Joe needs to export 2 tables (Student, Lecturer) to a location on disc ('c:\oracle')
Solution:
c:\> sqlplus joe/****
     create directory oracle as 'c:\oracle';
     commit;
     exit;
c:\> expdp joe/**** full=n tables=Student,Lecturer directory=oracle dumpfile=joe_tables.dmp logfile=joe2.log

^Joe's table are exported
^Dumpfile will be created in 'c:\oracle'
^There should be no space between the tables...e.g tables=(student1,student2,student3)

IMPORT

Example 4: Joe needs to import 2 tables (Student, Lecturer) from a directory called 'oracle'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp tables=Student,Lecturer logfile=joe3.log

^Joe's table will be imported

Example 5: Joe needs to import 2 tables (Student, Lecturer) and change the tables name to 'student1' and 'student2'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp logfile=joe3.log remap_table=joe.Student:student1,joe.Lecturer:student2

^Joe's table will be imported and renamed
^Tables must be separated by comma, the colon (:) must separate the oldname and new name

Example 6: System needs to import the whole database from a dumpfile on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=y dumpfile=database.dmp logfile=db.log

Example 7: System needs to import joe's schema from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db1.log

^Joe's schema will be imported


Example 8: System needs to import joe's schema and change name to DANIEL and also change the tablespace to daniel's tablespace
from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n dumpfile=database.dmp logfile=db2.log remap_schema=joe:daniel remap_tablespace=users:daniel_tbs

^Joe's schema will be imported and changed to Daniel
^Tablespace will be changed from users to Daniel_tbs
^you MUST physically create this tablespace and grant access to Daniel.

Example 9: System needs to import joe's schema using a parameter file
Solution:

create a parfile called 'joe.txt'

USERNAME=system/****
FULL=N
SCHEMA=(joe,daniel,class)
DIRECTORY=oracle
DUMPFILE=database.dmp
LOGFILE=db4.log

Save file and exit

c:\> impdp parfile=joe.txt

Parfile makes it more convenient to adjust datapump commands. It also help when you need to constantly run same commands.

Example 10: System needs to import joe's schema from dumpfile and replace all tables
Solution:
c:\> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db6.log table_exists_action=replace

^Joe's schema will be imported
^Joe's tables will be replaced if they already exist on the database prior to import