Use the menus on the left-hand side to select a script - a brief description of each is given when your mouse is over the script name. You must check scripts before use - please read the disclaimer page.


Previous script of the week - show DBMS_SCHEDULER schedule repeat interval next run date/time(s) (week 2006/53)


ShowScheduleNextRun.Sql - Oracle 10g deprecated the DBMS_JOBS package for scheduling database jobs and replaced it with some pretty awesome functionality under the DBMS_SCHEDULER package. Not only can you schedule SQL and PL/SQL but now you can also schedule external programs. The scheduling part goes much further than DBMS_JOBS ever did allowing run windows to be defined and the creation of all manner of complex schedule repeat intervals via the new freq= syntax.

This PL/SQL script uses the Oracle supplied DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING procedure to show the next date and time after the current time the repeat intervals for existing database schedules (for selected owner and scheduler names) will run at and then goes on to show the next "N" run date/times after that. It also shows details of the schedule itself. The trick is to use the next run date/time as the starting point for the procedure after each result is returned in order to get the sequence of date and times that the scheduler interval would result in.

The script also takes into account the start and end dates of the schedule. It will either ignore them (simply printing a "<" symbol for each repeat interval date/time before the start date or a ">" for each after the end date). Alternatively it will still show them but add a "<" or ">" to show that they are outside the run range of the schedule. The "FORCE" parameter is used to force including the dates or not.

It is also important to realise that the procedure shows the date and times in the client session locale - for this reason the script given here also converts the times to UTC (Universal Time Co-ordinates, GMT to us Brits) - seeking to avoid any confusion as to exactly when the schedule would run.

Sample usage of this script might result in the following output:

SQL> start d:\timonionscom\scripts\showschedulenextrun
Enter schedule owner (blank for all): TONIONS
Enter schedule name  (blank for all): EVERY3
Enter iteration count(blank for 1): 6
Show run dates BEYOND schedule start/end dates-Y/N?: Y
NB All times in client session local timezone unless otherwise stated
================================================================================
Schedule TONIONS.EVERY30MINUTES
[repeat interval of: freq=minutely;interval=30;
,schedule starts Thu 28 Dec 2006@08:08:22-05:00-no schedule end date]
Next 6 runs starting Sat 30 Dec 2006@16:30:56-05:00[21:30:56UTC] are ..
..Saturday 30 December 2006 @ 17:00:56-05:00 [22:00:56UTC]
..Saturday 30 December 2006 @ 17:30:56-05:00 [22:30:56UTC]
..Saturday 30 December 2006 @ 18:00:56-05:00 [23:00:56UTC]
..Saturday 30 December 2006 @ 18:30:56-05:00 [23:30:56UTC]
..Saturday 30 December 2006 @ 19:00:56-05:00 [00:00:56UTC]
..Saturday 30 December 2006 @ 19:30:56-05:00 [00:30:56UTC]
================================================================================
Schedule TONIONS.EVERY3HOURS
[repeat interval of: freq=hourly;interval=3;
,schedule starts Thu 28 Dec 2006@08:08:22-05:00-no schedule end date]
Next 6 runs starting Sat 30 Dec 2006@16:30:56-05:00[21:30:56UTC] are ..
..Saturday 30 December 2006 @ 19:30:56-05:00 [00:30:56UTC]
..Saturday 30 December 2006 @ 22:30:56-05:00 [03:30:56UTC]
..Sunday 31 December 2006 @ 01:30:56-05:00 [06:30:56UTC]
..Sunday 31 December 2006 @ 04:30:56-05:00 [09:30:56UTC]
..Sunday 31 December 2006 @ 07:30:56-05:00 [12:30:56UTC]
..Sunday 31 December 2006 @ 10:30:56-05:00 [15:30:56UTC]
================================================================================
Schedule TONIONS.EVERY3TEST
[repeat interval of: freq=daily;interval=1;byminute=0;byhour=0,8,20;bysecond=0;
,schedule starts Sun 31 Dec 2006@6:27:27-05:00
,schedule ends   Mon 01 Jan 2007@16:27:27-05:00]
Next 6 runs starting Sat 30 Dec 2006@16:30:56-05:00[21:30:56UTC] are ..
<<Saturday 30 December 2006 @ 20:00:00-05:00 [01:00:00UTC]
<<Sunday 31 December 2006 @ 00:00:00-05:00 [05:00:00UTC]
<<Sunday 31 December 2006 @ 08:00:00-05:00 [13:00:00UTC]
..Sunday 31 December 2006 @ 20:00:00-05:00 [01:00:00UTC]
..Monday 01 January 2007 @ 00:00:00-05:00 [05:00:00UTC]
..Monday 01 January 2007 @ 08:00:00-05:00 [13:00:00UTC]
================================================================================

SQL> start d:\timonionscom\scripts\showschedulenextrun

Enter schedule owner (blank for all): TONIONS
Enter schedule name  (blank for all): EVERY3
Enter iteration count(blank for 1): 6
Show run dates beyond schedule start/end date range-Y/N?: N
NB All times in client session local timezone unless otherwise stated
================================================================================
Schedule TONIONS.EVERY30MINUTES
[repeat interval of: freq=minutely;interval=30;
,schedule starts Thu 28 Dec 2006@08:08:22-05:00-no schedule end date]
Next 6 runs starting Sat 30 Dec 2006@16:31:09-05:00[21:31:09UTC] are ..
..Saturday 30 December 2006 @ 17:01:09-05:00 [22:01:09UTC]
..Saturday 30 December 2006 @ 17:31:09-05:00 [22:31:09UTC]
..Saturday 30 December 2006 @ 18:01:09-05:00 [23:01:09UTC]
..Saturday 30 December 2006 @ 18:31:09-05:00 [23:31:09UTC]
..Saturday 30 December 2006 @ 19:01:09-05:00 [00:01:09UTC]
..Saturday 30 December 2006 @ 19:31:09-05:00 [00:31:09UTC]
================================================================================
Schedule TONIONS.EVERY3HOURS
[repeat interval of: freq=hourly;interval=3;
,schedule starts Thu 28 Dec 2006@08:08:22-05:00-no schedule end date]
Next 6 runs starting Sat 30 Dec 2006@16:31:09-05:00[21:31:09UTC] are ..
..Saturday 30 December 2006 @ 19:31:09-05:00 [00:31:09UTC]
..Saturday 30 December 2006 @ 22:31:09-05:00 [03:31:09UTC]
..Sunday 31 December 2006 @ 01:31:09-05:00 [06:31:09UTC]
..Sunday 31 December 2006 @ 04:31:09-05:00 [09:31:09UTC]
..Sunday 31 December 2006 @ 07:31:09-05:00 [12:31:09UTC]
..Sunday 31 December 2006 @ 10:31:09-05:00 [15:31:09UTC]
================================================================================
Schedule TONIONS.EVERY3TEST
[repeat interval of: freq=daily;interval=1;byminute=0;byhour=0,8,20;bysecond=0;
,schedule starts Sun 31 Dec 2006@16:27:27-05:00
,schedule ends   Mon 01 Jan 2007@16:27:27-05:00]
Next 6 runs starting Sat 30 Dec 2006@16:31:09-05:00[21:31:09UTC] are ..
<<<
..Sunday 31 December 2006 @ 20:00:00-05:00 [01:00:00 UTC]
..Monday 01 January 2007 @ 00:00:00-05:00 [05:00:00 UTC]
..Monday 01 January 2007 @ 08:00:00-05:00 [13:00:00 UTC]
================================================================================
Go back to the top of the page


Previous script of week - validate DBMS_SCHEDULER freq= string setting (week 2006/52)


ShowFreqNextRun.Sql - Oracle 10g deprecated the DBMS_JOBS package for scheduling database jobs and replaced it with some pretty awesome functionality under the DBMS_SCHEDULER package. Not only can you schedule SQL and PL/SQL but now you can also schedule external programs. The scheduling part goes much further than DBMS_JOBS ever did allowing run windows to be defined and all manner of complex schedules for the freq= syntax when constructing the interval.


The PL/SQL script uses the Oracle supplied DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING procedure to show the next date and time after the current time a supplied freq interval will run at and then goes on to show the next N run date/times after that. The trick is to use the next run date/time as the starting point for the procedure after each result is returned in order to get the sequence of date and times that the scheduler interval would result in.


There are a couple of examples of this kind of thing out on the web but they do not make it clear how this works. It is also important to realise that the procedure shows the date and times in the client session locale - for this reason the script given here also converts the times to UTC (Universal Time Co-ordinates, GMT to us Brits) - seeking to avoid any confusion as to exactly when the schedule would run.


Sample usage of this script might result in the following output:

SQL> start d:\timonionscom\scripts\showfreqnextrun
Enter schedule freq: freq=daily;byday=mon,wed;byhour=10,11;
Enter iteration count: 5
NB All times in client session local timezone unless otherwise stated
Next 5 runs of freq=daily;byday=mon,wed;byhour=10,11;
starting Saturday 23 December 2006 @ 14:20:23+00:00 [14:20:23 UTC] are ...
..Monday 25 December 2006 @ 10:20:24+00:00 [10:20:24 UTC]
..Monday 25 December 2006 @ 11:20:24+00:00 [11:20:24 UTC]
..Wednesday 27 December 2006 @ 10:20:24+00:00 [10:20:24 UTC]
..Wednesday 27 December 2006 @ 11:20:24+00:00 [11:20:24 UTC]
..Monday 01 January 2007 @ 10:20:24+00:00 [10:20:24 UTC]

PL/SQL procedure successfully completed.

SQL> start d:\timonionscom\scripts\showfreqnextrun
Enter schedule freq: freq=monthly;byday=mon;byhour=9;bysecond=0;
Enter iteration count: 5
NB All times in client session local timezone unless otherwise stated
Next 5 runs of freq=monthly;byday=mon;byhour=9;bysecond=0;
starting Saturday 23 December 2006 @ 14:20:57+00:00 [14:20:57 UTC] are ...
..Monday 25 December 2006 @ 09:20:00+00:00 [09:20:00 UTC]
..Monday 01 January 2007 @ 09:20:00+00:00 [09:20:00 UTC]
..Monday 08 January 2007 @ 09:20:00+00:00 [09:20:00 UTC]
..Monday 15 January 2007 @ 09:20:00+00:00 [09:20:00 UTC]
..Monday 22 January 2007 @ 09:20:00+00:00 [09:20:00 UTC]

PL/SQL procedure successfully completed.

SQL> start d:\timonionscom\scripts\showfreqnextrun
Enter schedule freq: freq=monthly;byday=1mon;byhour=9;bysecond=0;
Enter iteration count: 5
NB All times in client session local timezone unless otherwise stated
Next 5 runs of freq=monthly;byday=1mon;byhour=9;bysecond=0;
starting Saturday 23 December 2006 @ 14:21:14+00:00 [14:21:14 UTC] are ...
..Monday 01 January 2007 @ 09:21:00+00:00 [09:21:00 UTC]
..Monday 05 February 2007 @ 09:21:00+00:00 [09:21:00 UTC]
..Monday 05 March 2007 @ 09:21:00+00:00 [09:21:00 UTC]
..Monday 02 April 2007 @ 09:21:00+00:00 [09:21:00 UTC]
..Monday 07 May 2007 @ 09:21:00+00:00 [09:21:00 UTC]

PL/SQL procedure successfully completed.

SQL>
Go back to the top of the page


Previous script of the week - what's active on the database (week 2006/51)


InFlightSQL.Sql - This script is in two parts:

  1. show details of the sessions actively running SQL/DML with how long they have been attached and active
  2. show the SQL that is actively being run
Knowing what is going on with a database right now as well as getting a feel for what is typically active on a database is essential detail for a DBA. This script will show the details (machine, user, login time etc) for all sessions that are active - i.e. running SQL/DML. It then goes on to list text of the SQL that is being run with the length of time the SQL has been active. This is the first thing I run when I log into any database and what I run when somebody reports a database is in trouble. By knowing what normally runs I can tell from the output if something out of the ordinary is happening - either because there are many more session than would be typical (or far fewer) or because there are SQL statements that have been running for longer than I'd normally see.


There is one thing to note about the two parts of the script's output - they are not necessarily consistent. I do not set any transaction consistency so very quick statements may show up in the first part but not in the second (and vice versa) as their total execution duration may be shorter than the total duration of the two parts of the script running.


Sample usage of this script might result in the following output:

SQL> start d:\timonionscom\scripts\inflightsql

List all connected sessions that are currently executing SQL


Fri Dec 15                                                               page    1
                      Users Currently Running Statements

User                 Program                        Logon Time/          Run Time
Details              Client Terminal Details        Last Activy time     (so far)
-------------------- ------------------------------ -------------------- --------
REPORTMX (149)       sqlplus.exe-TIMDB01(TIMO1\TIMD 15-DEC-2006:05:07:49 00:00:50
                     B01) as timdba                 /                            
                                                    15-DEC-2006:05:07:49         
                                                                                 
SYS (55)             RMAN.EXE-TIMDB01(TIMO1\TIMDB01 15-DEC-2006:04:32:50 00:35:27
                     ) as timdba                    /                            
                                                    15-DEC-2006:04:33:12         
                                                                                 
SYS (73)             -() as                         15-DEC-2006:04:32:50 00:35:49
                                                    /                            
                                                    15-DEC-2006:04:32:50         
                                                                                 
TONIONS (110)**      sqlplus.exe-TOLIPS-UK001(FATIL 15-DEC-2006:05:08:08 00:00:00
                     YONIONS\TOHIPS-US101) as       /                            
                     Aligator                       15-DEC-2006:05:08:39         
                                                                                 
                                                                                 
                                                                                 

Fri Dec 15                                                                    page    1
                      Users Currently Running Statements
User                                                               Run Time                                                                                                          
Details         Statement                                         (so far) Addr/Hash
--------------- ------------------------------------------------- -------- ------------
REPORTMX (149)  INSERT INTO bobs_cv (job_id,source) SELECT bob_id 00:00:52 8BDA8F7C
                FROMbobs b1 WHERE user_accord_id=3087 AND EXISTS           /2383818784                                                                                                            
                (SELECT 1 FROM bobs@timbo1.world c1 WHERE 
                c1.bob_id=c1.bob_id AND useraccord_id+0!=3087)
                                                                                                                                                                                                        
RLAYER (97)    SELECT SC.TEXT_PAGE_ID AS PAGE_ID,SC.ENTRY_PINT    00:00:00 8AD62ED8
               FROM PAGE_CONTROL SC,(SELECT PAGE_ID,LEVEL AS L             /3649380386
               FROM PAGES CONNECT BY PRIOR VASE_PAGE_ID=PAGE_ID
               START WITH PAGE_ID = :B2 ) X WHERE SC.PAGE_ID
               =X.PAGE_ID AND SC.PORKFAT_ORDER >= :B4 AND 
               (SC.TURN_CODE =:B3 OR SC.TURN_CODE = -666) 
               ORDER BY X.L,SC.PORKFAT_ORDER

SYS (55)       SELECT TO_CHAR(SYSDATE,'YYYY',                     00:35:29 8F73D1B0
               'NLS_CALENDAR=Gregorian'),                                  /1141676395
               SYSDATE,'MM','NLS_CALENDAR=Gregorian')
               ,TO_CHAR(SYSDATE,'DD','NLS_CALENDAR=Gregorian')
               FROM X$DUAL

SYS (73)       SELECT sysdate edt,ROUND((sysdate-TO_DATE('15-DEC  00:35:51 9A4908E4
               -2006 04:05:00'))*24*60,2) ela FROM DUAL                    /1309375952

SQL>
Go back to the top of the page

Previous script of the week - who's connected (week 2006/50)


WhoDat.Sql - This script first and foremost shows everyone who is connected to the database, although you can supply a filter so that you only see sessions from specific tools/machines if you wish. Not only does it show you who is on but it also shows you how they are connected (eg SQL*Plus, Java.exe etc), where they are connected from (ie machine name), who they connected as (both the Oracle user and the OS user), their status (are they running a command or idle), their SID and serial number (which are useful for further tracing and potentially killing the session), details of when they connected, how long they have been connected for, when they last did anything and if they used dedicated or a shared connection. This information is useful in at least two ways:

  1. use it regularly and get to know who uses the database and how long they stay connected
  2. use it to spot sessions that have been idle or active for a long time, that may well indicate a problem (or just the need to do some tidying up)

Sample usage of this script might result in the following output (note how the first 10 entries are Oracle's own background processes):

SQL> start d:\timonionscom\scripts\whodat

NB Asterixes after username indicates current session (ie your own session)
Enter specific program/terminal/machine name for connections (leave blank for all): %
                    Program-Terminal  SID/             Logon Time/      Time Since
 Oracle User/OSUser (Machine)         Serial# Status   Last Activy time Last Act. D/S
 ------------------ ------------------------- ------   ---------------- --------- ---
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   1,1[0]  ACTIVE   07-12-06:17:47:49 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   2,3[0]  ACTIVE   07-12-06:17:47:49 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   3,1[0]  ACTIVE   07-12-06:17:47:49 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   4,1[0]  ACTIVE   07-12-06:17:47:50 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   5,1[0]  ACTIVE   07-12-06:17:47:50 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   6,1[0]  ACTIVE   07-12-06:17:47:50 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   7,1[0]  ACTIVE   07-12-06:17:47:51 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   8,1[0]  ACTIVE   07-12-06:17:47:51 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01   9,1[0]  ACTIVE   07-12-06:17:47:51 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 ORACLE PROC/SYSTEM ORACLE.EXE-DB01  10,1[0]  ACTIVE   07-12-06:17:47:51 29:30:05 D
                    (DB01)                             07-12-06:17:47:48
 BAMND5DEV/SYSTEM   TPServ.exe-TIMGC 11,988   INACTIVE 08-12-06:23:08:43 00:09:11 D
                    PU(TOCORP\TIMGC) [1939062]         08-12-06:23:08:42
 BAMND5DEV/GJakselt TOAD.exe-GAMESJ( 12,1399  INACTIVE 08-12-06:17:06:08 06:11:44 D
                    TO\GAMESJ)       [1938415]         08-12-06:17:06:09
 TONIONS/           sqlplus.exe-T01(  15,4145 ACTIVE   08-12-06:23:16:18 00:00:00 D
 Administrator**    FATILY\TOHIPS-U1)[1939082]         08-12-06:23:17:53
 
13 rows selected.

SQL>
Go back to the top of the page

Previous script of the week - Show Session Counts (week 2006/49)


SessionCount.Sql - this simple SQL*Plus script shows a count of how many connected sessions there are to your database grouping by the user and client machine. It is useful to determine users with more than one session and connection pooling evironments that naturally make multiple connections. Sessions marked for killing and Oracle's own internal sessions are ignored as is the session that the script is run from.


Sample usage of this script might result in the following output:

SQL> start d:\timonionscom\scripts\sessioncount

PROG                                                      CNT
--------------------------------------------------------- ---
TPMServer.exe-ClientX-XP(domain\oSUser)[OraUser1/SYSTEM]    1
TPMServer.exe-ClientY-XP(domain\oSUser)[OraUser2/SYSTEM]    2
sqlplusw.exe-CL0165(domain\CL0165 )[OraUser3/jbloggs]       1
TOAD.exe-CL0165(domain\CL0165 )[OraUser3/jbloggs]           1
TOAD.exe-CL0169(domain\CL0169)[OraUser5/SSmith]             2
                                                          ---
sum                                                         7

5 rows selected.

SQL>
Go back to the top of the page