Load data into oracle table from a text file

Load data into oracle table from a text file

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. In this example I have used PARFILE,CONTROL, and LOG files.

The SQL*Loader PARFILE

The parfile specifies

The controlfile location
The logfile location

The SQL*Loader Control File

The SQL*Loader control file is the key to any load process. The control file provides the following information to SQL*Loader:
The name and location of the input data file
The format of the records in the input data file
The name of the table or tables to be loaded

The Log File

The log file is a record of SQL*Loader's activities during a load session. It contains information such as the following:
The names of the control file, log file, bad file, discard file, and data file
The values of several command-line parameters
A detailed breakdown of the fields and datatypes in the data file that was loaded
Error messages for records that cause errors
Messages indicating when records have been discarded
A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load

Example:

create table loader_table(first_name varcahr2(100),last_name varchar2(100),id number)
/

As a normal User:

sqlldr username@server/password parfile=loader.par

sqlldr username/password@server parfile=loader.par

As sysdba

sqlldr \'sys/sys AS SYSDBA\' PARFILE=loader.par

The parfile looks like this.

[laptop@laptop.practice.com scripts]$ cat loader.par
CONTROL=loader.ctl
LOG=loader.log

The control file

[laptop@laptop.practice.com scripts]$ cat loader.ctl
LOAD DATA
infile 'loader.dat'
INTO TABLE loader_table --if loading from sys then schema.table
FIELDS TERMINATED BY ','
(first_name,last_name,id)

The actut file the data to be loaded from

[laptop@laptop.practice.com scripts]$ cat loader.dat
Nabi,Mohammad,1234
John,Miller,1254
Christine,Mfouz,1261
Feroz,Mohammad,1268

Below is the execution

[laptop@laptop.practice.com scripts]$ sqlldr \'sys/sys AS SYSDBA\' PARFILE=loader.par
SQL*Loader: Release 11.2.0.3.0 - Production on Thu Sep 19 03:23:14 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

SQL > select count(*) from loader_table;

  COUNT(*)
----------
         4

Scripts

Description,Explanation, Execution and Sample Output	Script
Sync status query to be used on Standby Database	sync_stdby.sql
Objects currently Locked	locked_obj.sql
Tablespace Free Space	ts_free.sql
Session information from Process ID	pid.sql
Database Information	db.sql
Patch applied or not?	patch.sql
RMAN backup information	rman_bkp.sql
Check invalid objects	invalids.sql
Finding Blocking sessions	locks.sql
Sql text running that is running by a Session with an SID	sql_text.sql
Particular Table Size	tab_size.sql
Applications Relese/Version	release.sql
RMAN Backup Status	rman_status.sql
Tablespaces Information	tsinfo.sql
Sync status query to be used on Primary Database	sync.sql
Session information from Session ID (SID)	sid.sql
Sessing currently connected to Database	sess_runnig.sql
Privilege associated with a User	user_privs.sql
Oracle Application Products Version	product_status.sql
RMAN Progress Sofar	rman_sofar.sql
Concurrent Manager Status from Back-end	cm_status.sql
Concurrent Request information from Request ID	cr_info.sql
Concurrent Requests Currently Running	crs.sql
Pending Concurrent Requests Count	pend_crs_cnt.sql
Pending Concurrent Requests Count with Conflict Resolution Manager(CRM)	pend_crs_cnt_crm.sql
Pending Concurrent Requests Count with Standard Manager	pend_crs_cnt_stdm.sql
Pending Concurrent Requests Information with all the Managers	pend_cr.sql
Concurrent Requests Information Pending with Conlict Resolution Manager	pend_crs_crm.sql
Concurrent Requests Information Pending with Standard Manager	pend_crs_stdm.sql

Workflow Notification Mailer Status Check

Check workflow mailer service current status

select running_processes from fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';
Please Note : Number of running processes should be greater than 0

Find current mailer status

To check the status of notification mailer, we need to do the following.

select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');
Possible values are :
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

Stop notification mailer

To stop notification mailer , we need to execute the following.

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
------------------------------
select component_id into m_mailerid from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
-- Stop Mailer
------------------------
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/
-------------------------+
Start notification mailer|
-------------------------+
To start notification mailer, we need to execute the following.
sqlplus apps/
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Start Mailer
-----------------
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/
-------------------------------+
All WF Mailer Service Stausses`|
-------------------------------+
select component_name,COMPONENT_STATUS,COMPONENT_TYPE,STARTUP_MODE,INBOUND_AGENT_NAME,OUTBOUND_AGENT_NAME from apps.fnd_svc_components;

SET PAGESIZE 400
SET LINESIZE 120
SET PAGESIZE 50
COLUMN COMPONENT_NAME FORMAT A45
COLUMN STARTUP_MODE FORMAT A15
COLUMN COMPONENT_STATUS FORMAT A15
SELECT FSC.COMPONENT_NAME,FSC.STARTUP_MODE,FSC.COMPONENT_STATUS
FROM APPS.FND_CONCURRENT_QUEUES_VL FCQ, apps.FND_SVC_COMPONENTS FSC
WHERE FSC.CONCURRENT_QUEUE_ID = FCQ.CONCURRENT_QUEUE_ID(+)
ORDER BY COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
=====================

1) Identify the concurrent tiers node where mailer runs 
by running script below:
select target_node from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%'; 

It will return for example:
TARGET_NODE
------------------------------
ebiz1                     

In this example ebiz1 is the node where java mailer runs.

2) Gather other parameters values necessary for the SMTP telnet test:
To perform the SMTP telnet test, in addition to mailer node, you will also need to know on which node is the SMTP server (this is mailer “outbound server” parameter), and what is the reply to address that is set up for the java mailer (this is mailer “reply to” parameter).

To get these values run the following:
SELECT b.component_name,
c.parameter_name,
a.parameter_value
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name like '%Mailer%'
AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;

DBMS Jobs

How to enable/disable a scheduled job? 

 Using the package DBMS_SCHEDULER one can enable/disable jobs.

 To disable job: This disables the job from running

SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');

PL/SQL procedure successfully completed.

check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name ='GATHER_STATS_JOB';

JOB_NAME   ENABL
--------------- ------
GATHER_STATS_JOB FALSE

To enable job:

SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME   ENABL
--------------- -----
GATHER_STATS_JOB TRUE

You can run the job manually via DBMS_SCHEDULER

BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'GATHER_STATS_JOB'
);
END;
/

Job Run Detailed History

select owner,job_name,status,error#,run_duration,actual_start_date from dba_scheduler_job_run_details where job_name like '%GATHER%' order by actual_start_date;

RMAN Backup Information

------------------------------------------
-This script will report on all backups 
– full, incremental and archivelog backups
-Inputs : 
-File Name: rman_bkp.sql
-Usage: @rman_bkp
-----------------------------------------

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

--------------------------------------------------------
-This script will report all 
-on full and incremental backups, not archivelog backups
--------------------------------------------------------
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

RMAN Backup sofar

----------------------------------
-RMAN Backup sofar
-Inputs : None
-File Name: rman_sofar.sql
-Usage: @rman_sofar
-----------------------------------

set line 200
select
      sid,
      to_char(start_time,'dd-Mon-yy hh24:mi:ss') Start_Time,
   to_char(sysdate,'dd-Mon-yy hh24:mi:ss') Time_Now,
      totalwork
      sofar,
     (sofar/totalwork) * 100 pct_done
    from
       v$session_longops
    where
       totalwork > sofar
    AND
       opname NOT LIKE '%aggregate%'
    AND
       opname like 'RMAN%'
    /
    

RMAN Backup Status

----------------------------------
-RMAN Status Check for Specified Days
-Inputs : RMAN backup status for no.of days
-File Name: rman_status.sql
-Usage: @rman_status
-----------------------------------

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from v$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     v$BACKUP_SET_DETAILS d
                     join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from Gv$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time
/