How to use the SQL*Loader utility in Oracle ?

One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:

sqlldr username@server/password control=loader.ctl

sqlldr username/password@server control=loader.ctl

This sample control file (loader.ctl) will load an external data file containing delimited data:

load data

 infile 'c:\data\mydata.csv'

 into table emp

 fields terminated by "," optionally enclosed by '"'          

 ( empno, empname, sal, deptno )

The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40

10002,"Frank Naude", 500, 20

Optionally, you can work with tabulation delimited files by using one of the following syntaxes:

fields terminated by "\t"

fields terminated by X'09'

Additionally, if your file was in Unicode, you could make the following addition.

load data

 CHARACTERSET UTF16

 infile 'c:\data\mydata.csv'

 into table emp

 fields terminated by "," optionally enclosed by '"'          

 ( empno, empname, sal, deptno )

Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:

load data

 infile *

 replace

 into table departments

 (  dept     position (02:05) char(4),

    deptname position (08:27) char(20)

 )

begindata

COSC  COMPUTER SCIENCE

ENGL  ENGLISH LITERATURE

MATH  MATHEMATICS

POLY  POLITICAL SCIENCE

Setup Oracle Streams between two databases - Steps

Database Name and TNS name

DB1, db1 (source)

DB2, db2 (target)

Set up below parameters on both databases (db1, db2)

1. Enable ARCHIVELOG MODE on both database

2. Create Stream administrator User

Source Database: DB1

SQL> conn sys@db1 as sysdba

Enter password:

Connected.

SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant connect, resource, dba to strmadmin;

Grant succeeded.

SQL> begin dbms_streams_auth.grant_admin_privilege

  2  (grantee => 'strmadmin',

  3  grant_privileges => true);

  4  end;

  5  /

PL/SQL procedure successfully completed.

SQL> grant select_catalog_role, select any dictionary to strmadmin;

Grant succeeded.

Target Database: DB2

SQL> conn sys@db2 as sysdba

Enter password:

Connected.

SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant connect, resource, dba to strmadmin;

Grant succeeded.

SQL> begin dbms_streams_auth.grant_admin_privilege

  2  (grantee => 'strmadmin',

  3  grant_privileges => true);

  4  end;

  5  /

PL/SQL procedure successfully completed.

SQL> grant select_catalog_role, select any dictionary to strmadmin;

Grant succeeded.

3. Setup INIT parameters

Source Database: DB1

SQL> conn sys@db1 as sysdba

Enter password:

Connected.

SQL> alter system set global_names=true;

System altered.

SQL> alter system set streams_pool_size = 100 m;

System altered.

Target Database: DB2

SQL> conn sys@db2 as sysdba

Enter password:

Connected.

SQL> alter system set global_names=true;

System altered.

SQL> alter system set streams_pool_size = 100 m;

System altered.

4. Create Database Link

Target Database: DB1

SQL> conn strmadmin/strmadmin@db1

Connected.

SQL> create database link db2

  2  connect to strmadmin

  3  identified by strmadmin

  4  using 'DB2';

Database link created.

Source Database: DB2

SQL> conn strmadmin/strmadmin@db2

Connected.

SQL> create database link db1

  2  connect to strmadmin

  3  identified by strmadmin

  4  using 'DB1';

Database link created.

5. Setup Source and Destination queues

Source Database: DB1

SQL> conn strmadmin/strmadmin@db1

Connected.

SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

Target Database: DB2

SQL> conn strmadmin/strmadmin@db2

Connected.

SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

6. Setup Schema for streams

Schema: SCOTT

Table: Taj

NOTE: Unlock scott schema because in 10g scott schema is locked by default

Source Database: DB1

SQL> conn sys@db1 as sysdba

Enter password:

Connected.

SQL> alter user scott account unlock identified by tiger;

User altered.

SQL> conn scott/tiger@db1

Connected.

SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);

Table created.

Target Database: DB2

SQL> conn sys@db2 as sysdba

Enter password:

Connected.

SQL> alter user scott account unlock identified by tiger;

User altered.

SQL> conn scott/tiger@db2

Connected.

SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);

Table created.

7. Setup Supplemental logging at the source database

Source Database: DB1

SQL> conn scott/tiger@db1

Connected.

SQL> alter table taj

  2  add supplemental log data (primary key,unique) columns;

Table altered.

8. Configure capture process at the source database

Source Database: DB1

SQL> conn strmadmin/strmadmin@db1

Connected.

SQL> begin dbms_streams_adm.add_table_rules

  2  ( table_name => 'scott.taj',

  3  streams_type => 'capture',

  4  streams_name => 'capture_stream',

  5  queue_name=> 'strmadmin.streams_queue',

  6  include_dml => true,

  7  include_ddl => true,

  8  inclusion_rule => true);

  9  end;

 10  /

PL/SQL procedure successfully completed.

9. Configure the propagation process

Source Database: DB1

SQL> conn strmadmin/strmadmin@db1

Connected.

SQL> begin dbms_streams_adm.add_table_propagation_rules

  2  ( table_name => 'scott.taj',

  3  streams_name => 'DB1_TO_DB2',

  4  source_queue_name => 'strmadmin.streams_queue',

  5  destination_queue_name => 'strmadmin.streams_queue@DB2',

  6  include_dml => true,

  7  include_ddl => true,

  8  source_database => 'DB1',

  9  inclusion_rule => true);

 10  end;

 11  /

PL/SQL procedure successfully completed.

10. Set the instantiation system change number (SCN)

Source Database: DB1

SQL> CONN STRMADMIN/STRMADMIN@DB1

Connected.

SQL> declare

  2  source_scn number;

  3  begin

  4  source_scn := dbms_flashback.get_system_change_number();

  5  dbms_apply_adm.set_table_instantiation_scn@DB2

  6  ( source_object_name => 'scott.taj',

  7  source_database_name => 'DB1',

  8  instantiation_scn => source_scn);

  9  end;

 10  /

PL/SQL procedure successfully completed.

11. Configure the apply process at the destination database

Target Database: DB2

SQL> conn strmadmin/strmadmin@db2

Connected.

SQL> begin dbms_streams_adm.add_table_rules

  2  ( table_name => 'scott.taj',

  3  streams_type => 'apply',

  4  streams_name => 'apply_stream',

  5  queue_name => 'strmadmin.streams_queue',

  6  include_dml => true,

  7  include_ddl => true,

  8  source_database => 'DB1',

  9  inclusion_rule => true);

 10  end;

 11  /

PL/SQL procedure successfully completed.

12. Start the capture and apply processes

Source Database: DB1

SQL> conn strmadmin/strmadmin@db1

Connected.

SQL> begin dbms_capture_adm.start_capture

  2  ( capture_name => 'capture_stream');

  3  end;

  4  /

PL/SQL procedure successfully completed.

Target Database: DB2

SQL> conn strmadmin/strmadmin@db2

Connected.

SQL> begin dbms_apply_adm.set_parameter

  2  ( apply_name => 'apply_stream',

  3  parameter => 'disable_on_error',

  4  value => 'n');

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> begin

  2  dbms_apply_adm.start_apply

  3  ( apply_name => 'apply_stream');

  4  end;

  5  /

PL/SQL procedure successfully completed.

NOTE: Stream replication environment is ready, just needed to test it.

SQL> conn scott/tiger@db1

Connected.

SQL> --DDL operation

SQL> alter table taj add (flag char(1));

Table altered.

SQL> --DML operation

SQL> begin

  2  insert into taj values (1,'first_entry',sysdate,1);

  3  commit;

  4  end;

  5  /

PL/SQL procedure successfully completed.

SQL> conn scott/tiger@db2

Connected.

SQL> --TEST DDL operation

SQL> desc taj

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 NO                                        NOT NULL NUMBER

 NAME                                               VARCHAR2(20)

 DDATE                                              DATE

 FLAG                                               CHAR(1)

SQL> --TEST DML operation

SQL> select * from taj;

        NO NAME                 DDATE     F

---------- -------------------- --------- -

         1 first_entry          24-JAN-08 1