dbms_scheduler example

dbms_scheduler example

      
prompt  Show Scheduler database objects
TTITLE 'Scheduler Objects:'
COL owner 			FORMAT A20 HEADING 'Owner'
COL object_type 	FORMAT A12 HEADING 'Object|Type'
COL object_name 	FORMAT A30 HEADING 'Name'
COL created 		FORMAT A20 HEADING 'Created On'
COL status 			FORMAT A12 HEADING 'Status'

SELECT
owner ,object_type ,object_name ,created ,status
FROM dba_objects
WHERE object_type IN ('PROGRAM', 'JOB', 'JOB CLASS', 'SCHEDULE', 'WINDOW')
ORDER BY object_type, OBJECT_name;



                     Object
Owner                Type         Name                           Created On           Status
-------------------- ------------ ------------------------------ -------------------- ------------
SYS                  JOB          BSLN_MAINTAIN_STATS_JOB        04-JAN-19            VALID
SYS                  JOB          CLEANUP_NON_EXIST_OBJ          04-JAN-19            VALID



prompt  Show Schedule objects
prompt  'Schedules:'
COL owner 				FORMAT A08 HEADING 'Owner'
COL schedule_name 		FORMAT A30 HEADING 'Schedule'
COL start_date 			FORMAT A20 HEADING 'Starts|On'
COL end_date 			FORMAT A20 HEADING 'Ends|On'
COL repeat_interval 	FORMAT A45 HEADING 'Interval'

SELECT
owner ,schedule_name ,to_char(start_date, 'mm/dd/yyyy hh24:mi:ss') start_date ,to_char(end_date, 'mm/dd/yyyy hh24:mi:ss') end_date ,repeat_interval
FROM dba_scheduler_schedules;




prompt  Show Program objects : What program objects are available?
TTITLE 'Programs:'
COL owner 			FORMAT A08 HEADING 'Owner'
COL program_name 	FORMAT A30 HEADING 'Program'
COL program_type 	FORMAT A16 HEADING 'Type'
COL program_action 	FORMAT A60 HEADING 'Action'

SELECT
owner
,program_name
,program_type
,program_action
FROM dba_scheduler_programs;


Owner    Program                        Type             Action
-------- ------------------------------ ---------------- ------------------------------------------------------------
SYS      PURGE_LOG_PROG                 STORED_PROCEDURE dbms_scheduler.auto_purge
SYS      FILE_WATCHER_PROGRAM           STORED_PROCEDURE dbms_isched.file_watch_job
SYS      PMO_DEFERRED_GIDX_MAINT        PLSQL_BLOCK      dbms_part.cleanup_gidx_internal(
   


prompt  What program's arguments are attached?
TTITLE 'Program Arguments:'
COL owner 				FORMAT A20 HEADING 'Owner'
COL program_name 		FORMAT A30 HEADING 'Program'
COL argument_name 		FORMAT A12 HEADING 'Arg Name'
COL argument_position 	FORMAT 999 HEADING 'Arg|Pos'
COL argument_type 		FORMAT A25 HEADING 'Arg Type'
COL default_value 		FORMAT A12 HEADING 'Default|Value'
COL out_argument 		FORMAT A06 HEADING 'Out|Arg?'

SELECT owner ,program_name ,argument_name ,argument_position ,argument_type ,default_value ,out_argument FROM dba_scheduler_program_args;


                                                                 Arg                           Default      Out
Owner                Program                        Arg Name      Pos Arg Type                  Value        Arg?
-------------------- ------------------------------ ------------ ---- ------------------------- ------------ ------
SYS                  HS_PARALLEL_SAMPLING                           9 SYS.HSBLKNAMLST              FALSE
SYS                  HS_PARALLEL_SAMPLING                          10 SYS.HSBLKNAMLST              FALSE




prompt  Show Job objects
COL owner 		FORMAT A20 HEADING 'Owner'
COL job_name 		FORMAT A30 HEADING 'Job'
COL program_name 	FORMAT A20 HEADING 'Program|Name'
COL job_class 		FORMAT A35 HEADING 'Job Class'
COL job_type 		FORMAT A12 HEADING 'Job|Type'
COL job_action 		FORMAT A50 HEADING 'Job|Action'

prompt  'Jobs:---- '
SELECT owner ,job_name ,state ,job_class ,job_type ,job_action FROM dba_scheduler_jobs;

                                                                                                        Job          Job
Owner                Job                            State           Job Class                           Type         Action
-------------------- ------------------------------ --------------- ----------------------------------- ------------ --------------------------------------------------
SYS                  PURGE_LOG                      SCHEDULED       DEFAULT_JOB_CLASS
SYS                  FILE_WATCHER                   DISABLED        SCHED$_LOG_ON_ERRORS_CLASS




COL owner 			FORMAT A08 HEADING 'Owner'
COL job_name 		FORMAT A30 HEADING 'Job'
COL program_name 	FORMAT A30 HEADING 'Program|Name'
COL schedule_name 	FORMAT A30 HEADING 'Schedule|Name'
COL enabled 		FORMAT A08 HEADING 'Enabled?'
COL state 			FORMAT A15 HEADING 'State'
COL restartable 	FORMAT A08 HEADING 'Restart|-able?'
COL start_date 		FORMAT A37 HEADING 'Start|Date'

prompt  'Job Components:'
SELECT
owner ,job_name ,program_name ,schedule_name ,enabled ,state ,restartable ,start_date
FROM dba_scheduler_jobs;



                                        Program                        Schedule                                                Restart  Start
Owner    Job                            Name                           Name                           Enabled? State           -able?   Date
-------- ------------------------------ ------------------------------ ------------------------------ -------- --------------- -------- -------------------------------------
SYS      PURGE_LOG                      PURGE_LOG_PROG                 DAILY_PURGE_SCHEDULE           TRUE     SCHEDULED       FALSE    08-JUL-19 03.00.00.584504 AM ETC/GMT
SYS      FILE_WATCHER                   FILE_WATCHER_PROGRAM           FILE_WATCHER_SCHEDULE          FALSE    DISABLED        FALSE



prompt  What are a Job's arguments?
COL job_name 			FORMAT A30 HEADING 'Job'
COL argument_name 		FORMAT A12 HEADING 'Arg Name'
COL argument_position 	FORMAT 999 HEADING 'Arg|Pos'
COL value 				FORMAT A32 HEADING 'Argument Value'

prompt  'Job Arguments:'
SELECT job_name ,argument_name ,argument_position ,value FROM dba_scheduler_job_args;


                                             Arg
Job                            Arg Name      Pos Argument Value
------------------------------ ------------ ---- --------------------------------
DRA_REEVALUATE_OPEN_FAILURES                   1 TRUE
DRA_REEVALUATE_OPEN_FAILURES                   2 TRUE
DRA_REEVALUATE_OPEN_FAILURES                   3 TRUE
DRA_REEVALUATE_OPEN_FAILURES                   4 900





prompt  Show Scheduled Tasks History: Show a high-level view of scheduled task execution history
COL log_id 			FORMAT 9999 HEADING 'Log#'
COL log_date 		FORMAT A37 HEADING 'Log Date'
COL owner 			FORMAT A06 HEADING 'Owner'
COL job_name 		FORMAT A30 HEADING 'Job'
COL status 			FORMAT A10 HEADING 'Status'

prompt  'Scheduled Tasks History:'
SELECT
log_id ,log_date ,owner ,job_name ,status
FROM dba_scheduler_job_log
where 1=1
and log_date>sysdate -1 ;


 Log# Log Date                              Owner  Job                            Status
----- ------------------------------------- ------ ------------------------------ ----------
 7344 14-MAR-23 10.00.00.355353 PM +00:00   SYS    ORA$AT_OS_OPT_SY_230623
 7346 14-MAR-23 10.00.00.357186 PM +00:00   SYS    ORA$AT_OS_OPT_SY_230623
 7348 14-MAR-23 10.00.00.358230 PM +00:00   SYS    ORA$AT_OS_OPT_SY_230623
 7350 14-MAR-23 10.00.00.360661 PM +00:00   SYS    ORA$AT_SA_SPC_SY_230624
 



prompt  What scheduled tasks failed during execution, and Error details
COL log_id				FORMAT 9999 		HEADING 'Log#'
COL log_date 			FORMAT A32 			HEADING 'Log Date'
COL owner 				FORMAT A06			HEADING 'Owner'
COL job_name 			FORMAT A20 			HEADING 'Job'
COL status 				FORMAT A10		 	HEADING 'Status'
COL actual_start_date 	FORMAT A32 			HEADING 'Actual|Start|Date'
COL error# 				FORMAT 999999		HEADING 'Error|Nbr'

prompt 'Scheduled Tasks That Failed:'
SELECT
log_id ,log_date ,owner ,job_name ,status ,actual_start_date ,error# FROM dba_scheduler_job_run_details
WHERE status <> 'SUCCEEDED'
ORDER BY actual_start_date;






prompt  Which jobs are running right now ?
COL owner 				FORMAT A06 			HEADING 'Owner'
COL job_name 			FORMAT A20 			HEADING 'Job'
COL session_id 			FORMAT 999999 		HEADING 'Session'
COL running_instance 	FORMAT A10 			HEADING 'Running|Instance'
COL elapsed_time 		FORMAT A12 			HEADING 'Elapsed|Time'

TTITLE 'Scheduled Tasks Running Right Now:'
SELECT owner ,job_name ,session_id ,running_instance ,elapsed_time FROM dba_scheduler_running_jobs;
      



column job_name format a20
column status format a12
column actual_start_date format a36
column run_duration format a14
 
select
        job_name, status, actual_start_date, run_duration
from
        dba_scheduler_job_run_details
where
        job_name = 'GATHER_STATS_JOB'
order by
        actual_start_date
;

select
   error#,
   status
from
 dba_scheduler_job_run_details
where
   status like '%FAIL%';




set feedback off
set echo off
set lines 205
column owner format a15
column job_name format a22
column program_name format a24
column status format a10
column state format a10
column last_start format a25
column last_start_norm format a18
column "DURATION (d:hh:mm:ss)" format a21
column next_run format a25
column next_run_norm format a18

var local_offset number
begin
   select extract(timezone_hour from systimestamp) into :local_offset from dual;
end;
/

select dsj.owner,
       dsj.job_name,
       dsj.program_name,
       dsjlmax.status,
       dsj.state,
--     to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,
       to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,
       extract(day from dsj.last_run_duration) ||':'||
          lpad(extract(hour from dsj.last_run_duration),2,'0')||':'||
          lpad(extract(minute from dsj.last_run_duration),2,'0')||':'||
          lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)",
--     to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run,
       to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm
from dba_scheduler_jobs dsj,
(select job_name, status
from dba_scheduler_job_log dsjl
where log_date =
   (select max(log_date)
    from dba_scheduler_job_log dsjl2
    where dsjl2.job_name = dsjl.job_name)
) dsjlmax
where dsj.owner in ('SYSTEM','SYS')
and dsj.job_name = dsjlmax.job_name;




==========




dbms_scheduler example
begin
dbms_scheduler.create_job(
job_name => 'SCOTT_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin dbms_stats.gather_schema_stats(ownname =>"SCOTT",
estimate_percent => 30,
cascade=>true,
method_opt=> 'for all columns size 1',
degree => 5 ,
options => 'GATHER STALE '); end; '
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');
end;




begin

dbms_scheduler.create_job (
Job_name=> 'anuj_job',
Job_type=> 'PLSQL_BLOCK',
Job_action=> 'update employees set salary = salary * 1.5;',
Start_date=> '10-dec-2011 06:00:00 pm',
Repeat_interval=> 'FREQ=YEARLY',
Comments=> 'testing..... '
);
end;
/

PL/SQL procedure successfully completed.



SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
PURGE_LOG TRUE 1
ORA$AUTOTASK_CLEAN TRUE 1
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
FGR$AUTOPURGE_JOB FALSE 0
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
ANUJ_JOB FALSE 0
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0

11 rows selected.




exec dbms_scheduler.copy_job('anuj_job','anuj_job_copy');

SQL> exec dbms_scheduler.copy_job('anuj_job','anuj_job_copy');

PL/SQL procedure successfully completed.


exec dbms_scheduler.stop_job(job_name=> 'anuj_job', force=> true);




SQL> exec dbms_scheduler.drop_job('anuj_job');

PL/SQL procedure successfully completed.

SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
ANUJ_JOB_COPY FALSE 0
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
FGR$AUTOPURGE_JOB FALSE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 1
PURGE_LOG TRUE 1
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0

11 rows selected.



exec dbms_scheduler.run_job('ANUJ_JOB_COPY', true);



exec dbms_scheduler.run_job('ANUJ_JOB_COPY', true);






SQL> exec dbms_scheduler.set_attribute ( name => 'ANUJ_JOB_COPY',attribute => 'job_priority', value => 1 );

PL/SQL procedure successfully completed.



EXEC dbms_scheduler.run_job('ANUJ_JOB_COPY') ;



BEGIN
DBMS_SCHEDULER.ENABLE('ANUJ_JOB_COPY');
END;

PL/SQL procedure successfully completed.



BEGIN
DBMS_SCHEDULER.DISABLE('ANUJ_JOB_COPY');
END;







BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'anuj_full_job1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;

PL/SQL procedure successfully completed.




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







DECLARE
l_job NUMBER := 0;
BEGIN
DBMS_JOB.SUBMIT(l_job,'anuj_full_job;',sysdate,'TRUNC(sysdate)+1+1/288');
END;




BEGIN
DBMS_SCHEDULER.ENABLE('ANUJ_FULL_JOB');
END;


EXEC dbms_scheduler.run_job('ANUJ_FULL_JOB');



EXEC dbms_scheduler.run_job('ANUJ_FULL_JOB');

PL/SQL procedure successfully completed.

SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
ANUJ_JOB_COPY TRUE 0
ANUJ_FULL_JOB TRUE 0
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1
FGR$AUTOPURGE_JOB FALSE 0
BSLN_MAINTAIN_STATS_JOB TRUE 1
DRA_REEVALUATE_OPEN_FAILURES TRUE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 1
PURGE_LOG TRUE 1
FILE_WATCHER FALSE 0
XMLDB_NFS_CLEANUP_JOB FALSE 0

12 rows selected.



SELECT job_name, operation, status, user_name
FROM dba_scheduler_job_log
WHERE operation != 'RUN'
and owner = 'SCOTT';




SELECT * FROM dba_scheduler_jobs




Monitoring job-scheduling
Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in
Oracle 10g. We will discuss some of these views here.
To show details on job run:
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details

To show running jobs:
select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;

To show job history:
select log_date
, job_name
, status
from dba_scheduler_job_log;

show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;

show all jobs and their attributes:




http://www.oracledatabase12g.com/archives/%E4%BA%86%E8%A7%A3sysdate%E5%87%BD%E6%95%B0.html



select * from dba_scheduler_jobs

show all program-objects and their attributes
select * from dba_scheduler_programs;

show all program-arguments:
select * from dba_scheduler_program_args;






select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual;


SQL> select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YEAR-MON-DAYHH24:MI:SS')
-----------------------------------------------------------------
TWENTY ELEVEN-DEC-SATURDAY 22:59:13



set linesize 200 pagesize 1400;
col sessiontimezone for a20

SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual;

CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE
------------------- ------------------- ------ --------------------
2011-08-24 19:53:09 2011-08-24 19:53:09 +08:00 +08:00


select current_date, sysdate,dbtimezone,sessiontimezone from dual;

CURRENT_D SYSDATE DBTIME SESSIONTIMEZONE
--------- --------- ------ ---------------------------------------------------------------------------
10-DEC-11 10-DEC-11 +00:00 +00:00



Window issues:
Note 742683.1 Scheduled Job Works Does Not Start In Window.

Purging Diagnostics
Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG
Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details

Schedule issues:
Note 312547.1 Advanced use of DBMS_JOB.SUBMIT 'interval' Parameter
Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null.

Issues with Statistic Jobs:
Note 430636.1 GATHER_STATS_JOB Is Not Running
Note 377143.1 How to check what automatic statistics collection is scheduled on 10g
Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of
Job GATHER_STATS_JOB

Issues with external Jobs:

http://forums.oracle.com/forums/thread.jspa?threadID=555102

NOTE:389685.1 – Items to Check when Problems Running Executables through the Scheduler
NOTE:976049.1 DBMS_SCHEDULER fails with ORA-27369 and
STANDARD_ERROR=”Launching external job failed: Invalid username or password”
NOTE:1300215.1 ORA-27369: job of type EXECUTABLE failed with exit code: 274667

Please note that this article will guide through the correct analysis for Scheduler/Job Problems.

It is also practicable to go through a list of known issues / bugs which will be listed in
NOTE:1311355.1 Known Issues And Bugs With Using the Scheduler
This article is currently under edit and will be made publish in the near future

Keywords





BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'MY_MV_REFRESH',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN mv_refresh_job(''EXEC DBMS_MVIEW.REFRESH(''MV_NAME_HERE'', ''C'')''); END;',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
END_DATE => NULL,
ENABLED => TRUE,
COMMENTS => 'Refreshes Materialized Views on a Scheduled Basis (12:00AM each day).');
END;
/



SELECT job_name, program_name FROM dba_scheduler_jobs WHERE job_style='LIGHTWEIGHT';





begin

dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin
dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 100
, cascade=>true,method_opt=> "for all columns size 1"
,degree => 5 , options => "GATHER STALE"); end;'
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');

end;



SQL> begin
2
3 dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE'
4 ,job_type => 'PLSQL_BLOCK'
5 ,job_action => 'begin
6 dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 100
7 , cascade=>true,method_opt=> "for all columns size 1"
8 ,degree => 5 , options => "GATHER STALE"); end;'
9 ,start_date => trunc(sysdate + 1) + 6/24
10 ,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
11 12 ,comments => 'customized stats gathering for risk engine');
13
14 end;
15 /

PL/SQL procedure successfully completed.



grant execute on dbms_scheduler to scott;







exec dbms_scheduler.run_job('ENGINE_JOB_SCHEDULE');


set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;



session_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;


running_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
exec dbms_scheduler.drop_job('ENGINE_JOB_SCHEDULE');



SQL> exec dbms_scheduler.drop_job('ENGINE_JOB_SCHEDULE');

PL/SQL procedure successfully completed.





'begin
dbms_stats.gather_schema_stats(ownname =>'''SCOTT''', estimate_percent => 100
, cascade=>true,method_opt=> '''for all columns size 1'''
,degree => 5 , options => '''GATHER STALE''');
end;'




To show details on job run:
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details


To show running jobs:
select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;

To show job history:
select log_date
, job_name
, status
from dba_scheduler_job_log;


show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;

show all jobs and their attributes:
select *
from dba_scheduler_jobs


show all program-objects and their attributes
select *
from dba_scheduler_programs;

show all program-arguments:
select *
from dba_scheduler_program_args;


 select 'exec dbms_scheduler.disable(' || ''''||OWNER || '.' || job_name || '''' || ');' Disable_Below_Jobs from dba_scheduler_jobs where STATE='RUNNING' and owner ='SYS';


CREATE TABLE reminders(message VARCHAR2(4000),msg_ts TIMESTAMP );




DECLARE
l_job_action VARCHAR2(500) :=
'BEGIN '
||'INSERT INTO reminders( message, msg_ts) '
||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); '
||'END;';

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'wakeup_sun',
job_type => 'PLSQL_BLOCK',
job_action => l_job_action,
start_date => SYSDATE,
repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30',
end_date => NULL,
enabled => TRUE,
comments => 'Do something automatically on Sunday morning');
END;

/



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

SELECT * FROM reminders;






DECLARE
l_job_action VARCHAR2(500) :=
'BEGIN '
||'INSERT INTO reminders( message, msg_ts) '
||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); '
||'END;';

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'wakeup_sun',
job_type => 'PLSQL_BLOCK',
job_action => l_job_action,
start_date => SYSDATE,
repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30',
end_date => NULL,
enabled => TRUE,
comments => 'Do something automatically on Sunday morning');
END;

/










begin
dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE1'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin
dbms_stats.gather_schema_stats(ownname =>''SCOTT'', estimate_percent => 100
, cascade=>true,method_opt=> ''for all columns size 1''
,degree => 5 , options => ''GATHER STALE''); end;'
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'customized stats gathering for risk engine');

end;
/



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



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

PL/SQL procedure successfully completed.




begin
dbms_scheduler.create_schedule(
schedule_name => 'DEMO_SCHEDULE'
, start_date => '01/01/2006 22:00:00'
, repeat_interval => 'FREQ=WEEKLY'
, comments => 'Weekly at 22:00');
END;



To drop the schedule:

begin
dbms_scheduler.drop_schedule( schedule_name => 'DEMO_SCHEDULE' , force => TRUE );
end;
/




begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB1'
, program_name =>'DEMO_JOB_SCHEDULE'
, schedule_name =>'DEMO_SCHEDULE'
, enabled => FALSE
, comments => 'Run demo program every week at 22:00');

dbms_scheduler.set_job_argument_value(
job_name => 'DEMO_JOB1'
, argument_position => 1
, argument_value => 'param1');
dbms_scheduler.enable('DEMO_JOB1');
commit;
end;
/


begin
dbms_scheduler.create_job
(
job_name => 'RUN_SHELL1',
schedule_name => 'DEMO_SCHEDULE',
job_type => 'EXECUTABLE',
job_action => '/home/test/run_script.sh',
enabled => true,
comments => 'Run shell-script'
);
end;
/




Create a Job

/* Run this job every 5 minutes except between 11am and 5pm) */
begin
dbms_scheduler.create_job (
job_name => 'test_job_01',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN update test1 set col1 = col1 + 1; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; byhour=17,18,19,20,21,22,23,00,01,02,03,04,05,06,07,08,09,10; interval=5',
end_date => NULL,
enabled => TRUE,
comments => 'Job to test use of dbms_scheduler');
end;
/

===================================

shell script

[oracle@rac02 scripts]$ cat SysGather.sh
#!/bin/bash
export ORACLE_SID=vihcdbd8
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
LOGFILE=/home/oracle/logs/sys_gather_`date +"%m%d%y_%H%M"`.log
echo $LOGFILE
sqlplus -s "/as sysdba" << EOF> $LOGFILE
@/u01/app/oracle/scripts/SysGather.sql
exit
EOF


sql script

[oracle@rac02 scripts]$ cat SysGather.sql
spool Gather.spool
alter session set nls_date_format='dd-MON-YYYY hh24:mi';


set time on timing on feedback on
select sysdate from dual;
set echo on feedback on time on timing on verify on

-- show con_name;
set pages 300 lines 350
col open_mode for a10
col host_name for a10
col database_role for a15
col db_unique_name for a10
col controlfile_type for a10
col database_role for a17
col host_name for a35
col open_mode for a20
select name,db_unique_name,instance_name,host_name,open_mode,log_mode,database_status,database_role,switchover_status,protection_mode,protection_level,controlfile_type,status,logins,force_logging,
startup_time "db up time" from v$database,gv$instance;


SET VERIFY OFF

select sysdate from dual;


prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()
prompt ========================================

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();


prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')
prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM', Degree => 8);


prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS' , Degree => 8)
prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', Degree => 8);

prompt ========================================
prompt DBMS_STATS.GATHER_DICTIONARY_STATS
prompt ========================================

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_OPR');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY');
exec dbms_stats.lock_table_stats('SYS', 'X$UNIFIED_AUDIT_TRAIL');

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS(Degree => 8);

exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_OPR');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY');
exec dbms_stats.unlock_table_stats('SYS', 'X$UNIFIED_AUDIT_TRAIL');

select sysdate from dual;

spool off


======



BEGIN
DBMS_SCHEDULER.create_program (
    program_name        => 'GATHER_SYS',
    program_type        => 'EXECUTABLE',
    program_action      => '/u01/app/oracle/scripts/SysGather.sh',
    number_of_arguments => 0,
    enabled             => TRUE,
    comments            => 'Program to Sys gather statistics us a shell script'
                            );

end ;


select enabled from dba_scheduler_programs where program_name = 'GATHER_SYS';


BEGIN
  DBMS_SCHEDULER.create_schedule (
 schedule_name   => 'GATHER_SYS_SCHEDULE',
 start_date   => SYSTIMESTAMP,
 repeat_interval => 'freq=hourly; byminute=0',
 end_date     => NULL,
 comments     => 'Repeats hourly, on the hour, for ever.');
END;
/


col START_DATE for a35
col REPEAT_INTERVAL for a35
col END_DATE for a30
select start_date, repeat_interval, end_date from dba_scheduler_schedules where schedule_name = 'GATHER_SYS_SCHEDULE';



begin
dbms_scheduler.create_job (
    job_name      => 'GATHER_JOB',
    program_name  => 'GATHER_SYS',
    schedule_name => 'GATHER_SYS_SCHEDULE',
    enabled       =>  true,
    comments      => 'Job, combining GATHER_SYS_SCHEDULE with GATHER_SYS'
);
end;


select log_id, log_date, status from DBA_scheduler_job_log where job_name = 'GATHER_JOB' ;



set linesize 300
col JOB_NAME for a20
col REQ_START_DATE   for a35                                                          
col ACTUAL_START_DATE  for a35   
col RUN_DURATION for a18
col STATUS for a14
select job_name,status,req_start_date,actual_start_date,run_duration 
from  DBA_SCHEDULER_JOB_RUN_DETAILS 
where  job_name like 'GATHER_JOB%' 
and req_start_date> sysdate-3 
order by req_start_date;



#Enable a job.

BEGIN
    DBMS_SCHEDULER.ENABLE('GATHER_JOB');
END;


#Disable a job.

BEGIN
    DBMS_SCHEDULER.DISABLE('GATHER_JOB');
END;

#Stop a job.

BEGIN
    DBMS_SCHEDULER.STOP_JOB(
        job_name => 'GATHER_JOB',
        force => true --optional
        );
END;

# Drop a job.

BEGIN
    DBMS_SCHEDULER.DROP_JOB (
        job_name => 'GATHER_JOB',
        force => true --optional
        );
END;

ORA-00845: MEMORY_TARGET not supported on this system

 

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 968M
memory_target                        big integer 968M
shared_memory_address                integer     0
SQL>





alter system set memory_max_target=1200m scope=spfile;


SQL> alter system set memory_max_target=1200m scope=spfile;

System altered.



SQL> alter system set memory_target=1200m scope=spfile;

System altered.




SQL> startup force;
ORA-00845: MEMORY_TARGET not supported on this system


SQL> !df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       21G   17G  2.9G  86% /
tmpfs                1000M  420K 1000M   1% /dev/shm   <<<<------------------ increase this value
/dev/sda1             485M   41M  419M   9% /boot




from root

[root@localhost ~]# mount -t tmpfs shmfs -o size=2048m /dev/shm



 /etc/fstab


/dev/mapper/VolGroup-lv_root /                       ext4    defaults        1 1
UUID=9a9b0c22-cacf-491c-ad27-b636c69da3cf /boot                   ext4    defaults        1 2
/dev/mapper/VolGroup-lv_swap swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
shmfs                   /dev/shm                tmpfs   size=2048m      0 0   <<<<<<<<<---- add following line 


then init 6 <<<<<--- reboot 



SQL> startup ;
ORACLE instance started.

Total System Global Area 1255473152 bytes
Fixed Size                  1344652 bytes
Variable Size             754977652 bytes
Database Buffers          486539264 bytes
Redo Buffers               12611584 bytes
Database mounted.


============================






SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 1200M
memory_target                        big integer 1200M
shared_memory_address                integer     0

Oracle database all file info

@all_file.sql
@dba_all_file.sql
All file in database

 

 

SET LINESIZE 147
SET PAGESIZE 9999
SET VERIFY OFF

COLUMN tablespace FORMAT a29 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a64 HEADING 'Filename'
COLUMN filesize FORMAT 99,999,999,999 HEADING 'File Size'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 99,999,999,999 HEADING 'Next'
COLUMN maxbytes FORMAT 99,999,999,999 HEADING 'Max'

BREAK ON report
COMPUTE SUM OF filesize ON report

SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace 
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_temp_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
UNION
SELECT '[ ONLINE REDO LOG ]' , a.member , b.bytes , null , TO_NUMBER(null), TO_NUMBER(null)
FROM v$logfile a , v$log b
WHERE a.group# = b.group#
UNION
SELECT '[ CONTROL FILE ]' , a.name , TO_NUMBER(null) , null , TO_NUMBER(null), TO_NUMBER(null)
FROM v$controlfile a ORDER BY 1,2
/

SQL> /

Tablespace Name / File Class  Filename                                                               File Size Auto            Next             Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
SYSAUX                        +DATA/vihaan/datafile/sysaux.257.776974139                           241,172,480 YES       10,485,760  34,359,721,984
SYSTEM                        +DATA/vihaan/datafile/system.256.776974135                           503,316,480 YES       10,485,760  34,359,721,984
TEMP                          +DATA/vihaan/tempfile/temp.264.776974399                              20,971,520 YES          655,360  34,359,721,984
UNDOTBS1                      +DATA/vihaan/datafile/undotbs1.258.776974143                          26,214,400 YES        5,242,880  34,359,721,984
USERS                         +DATA/vihaan/datafile/users.259.776974145                              5,242,880 YES        1,310,720  34,359,721,984
[ CONTROL FILE ]              +DATA/vihaan/controlfile/current.260.776974343
[ ONLINE REDO LOG ]           +DATA/vihaan/onlinelog/group_1.261.776974351                          52,428,800
[ ONLINE REDO LOG ]           +DATA/vihaan/onlinelog/group_2.262.776974357                          52,428,800
[ ONLINE REDO LOG ]           +DATA/vihaan/onlinelog/group_3.263.776974361                          52,428,800
                                                                                               ---------------
sum                                                                                                954,204,160

9 rows selected.