Logminer and SQL Loader

Logminer and SQL Loader

 

 

SQL Loader:

                    A database utility to upload data from a external files to oracle table(db).

 

Utility: sqlldr Location: $ORACLE_HOME/bin

 

Using SQL loader

Steps:

1.        create a flat file(eg .dat file)

$vi product.dat

 100,raw, 102

 101,food, 103

 209,wood, 203

: wq !

2.        create a controlfile

$vi product.ctl

Load data

Infile ‘/u01/user18/product.dat’

Into table items

Fields terminated by “,”

(Itemid, name, qty)

: wq! //save and quit

        

3.        create table with same name and columns specified in the control file

 eg: Suppose we have a schema in that create one table

  sql>create table items (itemid number, name varchar2(20),qty number);

 

4.        load the data from product.dat file to database table:

 $sqlldr userid=demo/demo control=product.ctl log=product. log

 

Logminer:

                  It’s a pl/sql based utility.

Using Logminer to mine data:

 

  Steps:

Ø  Alter database add supplemental log data;

1.      create a directory to store dictionary file.

$mkdir         /u01/user18/demo/

2.      Specify the location of dictionary file at os level.

Sql>alter system set utl_file_dir=’/u01/user18/demo’ scope=spfile;

3.      Bounce back the database.

Sql>startup force;

4.      create a dictionary file

       sql>exec dbms_logmnr_d.build(‘dfile’,’/u01/user18/demo’);

5.      connect to a sys user and specify the all logfiles to Logminer session.

Sql>exec dbms_logmnr.add_logfile(‘/u01/user18/prod/redo/redo01.log’);

Sql>exec dbms_logmnr.add_logfile(‘/u01/user18/prod/redo/redo02.log’);

Sql>exec dbms_logmnr.add_logfile(‘/u01/user18/prod/redo/redo03.log’);     

6.      Start the minning process

Sql>exec           dbms_logmnr.start_logmnr(dictfilename=>’/u01/user18/demo/dfile’);

                       Sql>spool abc.sql

Sql>select sql_undo,sql_redo from v$logmnr_contents  where seg_owner=’USER1’ and seg_name=’EMP’;

Sql>spool off

 

View

                V$logmnr_contents