Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle File Watcher Tips

Oracle Database Tips by Donald BurlesonOctober 15, 2015

There are many times when an Oracle DBA needs to start a database process when an external event happens:

  • Upon arrival of a redo log from another instance

  • Upon arrival of an external file to an TEL feed

  • Upon arrival of a new file for becoming an external table

  • Upon arrival of a new object for BFILE inclusion

Before the Oracle file watcher utility, the DBA would have to write UNIX/Linux shell scripts to watch directories for new files, nohupping a daemon processes to sleep for a few seconds, and check for a condition, ad infinitum, until the condition is net.

The Oracle file watcher utility is designed to fire off a daemon process to continually search for new files within a filesystem directory.  

The filewatcher is a great tool for detecting data feeds from remote locations, when you want to fire-off an event to processing the incoming file:

  • Load a BLOB/CLOB into a table as a BFILE
  • Load a rowset into a table with SQL*Loader
  • Define a delimited file as an external table  

The Oracle filewatcher is identical to the running a repeated UNIX/Linux ls –alt|head command to watch for new files within a directory.  

The Oracle file watcher fires off a background process and file watcher messages are written to a flat file log at the $ORACLE_HOME/data/agent.log location

Starting in Oracle 11g, we see a new procedure in the dbms_scheduler package called dbms_scheduler.create_file_watcher that allows you to step outside of the database and execute code when a specific external event occurs.

Note:  The Oracle file watcher is not yet very sophisticated and it will only trigger code to execute upon the arrival of a new data file into the target directory.

Also note that there are limitations when the code execution time exceeds the arrival time for new files.  By default, the arrival of new files will be ignored if the triggered job is already running. If you need the job to fire for each new arrival, regardless of whether the job is already running or not, set parallel_instances=true.

Creating a Oracle file watcher

Oracle says that creating a file watcher involves these five steps.  Creating a file watcher is not immediately obvious and the steps are a tad convoluted:

  1. Create a Credential

  2. Create a File Watcher

  3. Create a Program Object with a Metadata Argument

  4. Create an Event-Based Job That References the File Watcher

  5. Enable All Objects

Here are code samples for each of the five steps, per the Oracle docs: 

1.  Create a credential:

BEGIN
  DBMS_SCHEDULER.CREATE_CREDENTIAL('WATCH_CREDENTIAL', 'salesapps', 'sa324w1');
END;
/

2.  Create a file watcher

BEGIN
  DBMS_SCHEDULER.CREATE_FILE_WATCHER(
    FILE_WATCHER_NAME => 'EOD_FILE_WATCHER',
    DIRECTORY_PATH    => '?/eod_reports',
    FILE_NAME         => 'eod*.txt',
    CREDENTIAL_NAME   => 'WATCH_CREDENTIAL',
    DESTINATION       => NULL,
    ENABLED           => FALSE);
END;
/

3.  Create a program object:

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    PROGRAM_NAME        => 'DSSUSER.EOD_PROGRAM',
    PROGRAM_TYPE        => 'STORED_PROCEDURE',
    PROGRAM_ACTION      => 'EOD_PROCESSOR',
    NUMBER_OF_ARGUMENTS => 1,
    ENABLED             => FALSE);
END;
/

4a.  Define the metadata argument using the event_message attribute.

BEGIN
  DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT(
    PROGRAM_NAME       => 'DSSUSER.EOD_PROGRAM',
    METADATA_ATTRIBUTE => 'event_message',
    ARGUMENT_POSITION  => 1);
END;
/

4b.  Prepare an event-based job:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    JOB_NAME        => 'DSSUSER.EOD_JOB',
    PROGRAM_NAME    => 'DSSUSER.EOD_PROGRAM',
    EVENT_CONDITION => NULL,
    QUEUE_SPEC      => 'EOD_FILE_WATCHER',
    AUTO_DROP       => FALSE,
    ENABLED         => FALSE);
END;
/

5.  Enable objects:

BEGIN
   DBMS_SCHEDULER.ENABLE('DSSUSER.EOD_PROGRAM,DSSUSER.EOD_JOB,EOD_FILE_WATCHER');
END;
/

You can view information about file watchers by querying dba_scheduler_file_watchers:

set linesize 100

column file_watcher_name format a20
column destination       format a15
column directory_path    format a15
column file_name         format a10
column credential_name   format a20

select
   file_watcher_name,
   destination,
   directory_path,
   file_name,
   credential_name
from
   dba_scheduler_file_watchers;


If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.