Scheduled Job in DBMS Scheduler for Purging a table in Oracle

Scheduled job for purging a table in Oracle

It’s an example to scheduled one purging job for a table in DBA SCHEDULER functionality present in Oracle

INTRODUCTION
Table is maintained for the ETL Jobs logs. It is basically used for capturing the error occurred during the ETL jobs. So, after some time data is no need.

Setup Purging for the table

1. Login into database through sys user

$sqlplus sys as sysdba

2. Make following procedure to truncate the table:

CREATE OR REPLACE PROCEDURE truncate_table(p_table_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE('TRUNCATE TABLE '||p_table_name);
END truncate_table;
/

3. Scheduled the upper procedure with TEMP_TABLE input parameter.

BEGIN
dbms_scheduler.create_job(
job_name => 'PURGE_ETL_JOBS_LOG'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin truncate_table('schema_name.table_name''); end;'
,start_date => SYSDATE +1/24/59
,repeat_interval => 'FREQ=DAILY; BYDAY=FRI; BYHOUR=20;'
,enabled => TRUE
,comments => 'Demo for job schedule.');
END;
/

Note: Schema_name and table_name should be filled

4. Check the status of the job:

SELECT job_NAME, enabled, run_count, failure_count, job_action FROM DBA_SCHEDULER_JOBS;

5. Drop the job:

EXEC dbms_scheduler.drop_job(job_name => 'PURGE_ETL_JOBS_LOG');

6. For enable or disable the job

For Enable the job:
exec dbms_scheduler.ENABLE('PURGE_ETL_JOBS_LOG');
For disable the job:
exec dbms_scheduler.DISABLE('PURGE_ETL_JOBS_LOG');

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.