Performing Math on Oracle Dates

Here are some quick and dirty examples for adding and subtracting days and months and finding the difference between dates in Oracle.

These examples select the result from the ‘dual’ table. Dual is a dummy table that exists in all Oracle databases. I am also using sysdate heavily. Sysdate simply returns the current date and time.

Adjusting Days, Weeks, Hours and Minutes

To add and subtract days from a date we simply use + or -. Here are some examples:

SQL> SELECT sysdate + 7 FROM dual;


SQL> SELECT sysdate - 30 FROM dual;


SQL> SELECT to_char(sysdate - 14, 'MM/DD/YYYY HH:MI AM') FROM dual;

11/15/2005 09:41 AM

In the first example we see that the query returns 7 days from today. The second one shows 30 days before today. In the third query I have added the to_char function so we see hour and minute. This shows us that while the date has changed, the time has not.

The first and third examples above also show how adding days would be used to add weeks. If you wanted to simplify this you could add the logic into the query like this:

SQL> SELECT sysdate + (7 * 2) FROM dual;


Likewise to work with hours you would use fractional days. The simplest way to show this is by dividing the hours by 24 right in the query. The parentheses may not be necessary, but I feel they make the query a little clearer.

Here I show the current time, then the current time plus two hours:

SQL> SELECT to_char(sysdate, 'HH:MI AM') FROM dual;

09:48 AM

SQL> SELECT to_char(sysdate + (2/24), 'HH:MI AM') FROM dual;

11:48 AM

An alternative to this method is to use the numtodsinterval function. The example above can instead be written like this:

to_char(sysdate + numtodsinterval(2, 'HOUR'), 'HH:MI AM')
FROM dual;

11:57 AM

Here the numtodsinterval function is doing the work of dividing 2/24 for hours. Valid options for the numtodsinterval are ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’. Here is an example using ‘MINUTE’. When working with minutes the numtodsinterval function is much more readable.

to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'HH:MI AM')
FROM dual;

10:47 AM

Adjusting Months and Years

To work with months and years (either of which may have a varying number of days) Oracle has provided the function numtoyminterval. This works much like the numtodsinterval function mentioned above by taking a number and a string. Valid options for the string are ‘YEAR’ or ‘MONTH’.

to_char(sysdate + numtoyminterval(5, 'MONTH'), 'MM/DD/YYYY')
FROM dual;


to_char(sysdate + numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY')
FROM dual;


Comparing dates

Let’s say we want to compare some dates and find out how many days or weeks between them. To simply see the result in days we can use the minus operator like this:

TO_DATE('12/25/2005', 'MM/DD/YYYY') - sysdate
FROM dual;


The result is expressed in days including fractional hours. Of course if we wanted weeks we could just divide by 7. Similarly if we’re looking for hours we could multiply by 24, but if we want months we need to use the months_between function.

months_between(sysdate, to_date('01/01/2006', 'MM/DD/YYYY'))
FROM dual;


Finding the Latest or Earliest Date in a Set

The Oracle functions greatest and least can be used on dates to return the latest or earliest date.

greatest(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
FROM dual;


least(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
FROM dual;


What’s the Last Day in a Given Month

The last_day function can be used to return the last day in a given month.

SQL> select last_day(sysdate) from dual;


Next Day of the Week After a Date

The next_day function gives the date of the next occurrence of a day of the week (‘Monday’, ‘Tuesday’, etc.) after a given date. Here we see the date of the next Sunday after today:

SQL> select next_day(sysdate,'Sunday') from dual;


Still haven’t found what you’re looking for?

You may also want to look at these other articles:

Converting Time Zones in Oracle
Oracle, SQL, Dates and Timestamps
UNIX timestamp to Oracle Date Conversion

oracle, sql, dba, database administration, database development

Changes to OFA for Oracle 10g

With Oracle 10g there has been a small but useful change made to Oracle’s Optimal Flexible Architecture.

The Optimal Flexible Architecture standards are a guideline for setting up Oracle databases to minimize downtime and maximize scalability. For more information, check out my article on OFA.

In 10g, Oracle has added one level to the ORACLE_HOME path. An Oracle home directory which was formerly /u01/app/oracle/product/9.2.0 is now one more level deeper at /u01/app/oracle/product/10g/db_1.

By adding install type and an install number as the final level of the Oracle home variable it is now possible to have two or more installs of the same version of Oracle in the same Oracle directory.

Oracle offers up the idea of using db_1, db_2 and so-on for full database releases and client_1, client_2 and so-on for client installs I can also see using this method to install Oracle applications servers in the same directory as well.

To read more about OFA, check out my earlier article on the topic and Oracle’s explanation of OFA.

oracle, sql, dba, database administration, database development, database security, database, oracle security

Free Oracle V$ Views Poster!

No Oracle geek should be without one of these.

If you’ve been an Oracle DBA for long you know the horrors of navigating through Oracle’s “data dictionary”, the tables and views which contain all the metadata about your database. While views like dba_objects and dba_tab_privs are their own level of hell, one thing is certain… the v$ views are far worse.

While the names of the v$ views are not terrible in themselves (e.g. v$session, v$datafile, v$tablespace) the true crux of the problem is you never use them. Any time your database is running fine, you would go to the dba_ tables and the like. Only when your database refuses to open after a fairly nasty crash and users are knocking on your door asking when you will have the database up do you finally need the v$ views, and then you can’t remember any of them.

v$viewsEnter I’m not sure how long they have been doing this, but they offer a free poster of the Oracle v$ views. I’ve seen these as far back as Oracle 8.0, and the company has been around since 1988, so clearly they know what they’re doing.

Well, they seem to be a little confused about “free”, but basically they want eight bucks to send you one of these and two bucks for each additional one. I figure they’re not making any money on this, just covering shipping and handling (and not in the same way eBay-ers need $50 to cover shipping and handling so they can sell something for cheap).

Still not convinced? Tusc has also been nice enough to offer the poster in PDF format. Download it, check it out, put it somewhere and forget about it until you have a database misbehaving then go frantically after it.

oracle, sql, dba, database administration, database recovery, database

Tracking Oracle Logons

There are a couple ways of tracking logon and logoff information within an Oracle database, however few offer the simplicity and flexibility of the use of an logon and logoff trigger. Here is how I built a simple set of triggers and accompanying table to track usernames, logon time, logoff time, machine connected from and program used.

I performed these steps as system. If you want to install these as another user you will need to assign the appropriate permissions first.

The first step is to create a table to store the information. This table may grow quickly if this is a very active system so we want to put it somewhere other than the system tablespace. For reference, mine currently has around 3000 records in it and is around 200k in size. Of course mileage may vary.

CREATE TABLE session_audit
user_id VARCHAR2(30),
session_id NUMBER,
host VARCHAR2(30),
program VARCHAR2(60),
logon_time DATE,
logoff_time DATE

This will be the table you query to determine login information. Of course you will probably want to purge data from here occasionally to keep it from getting too big.

Next we create the logon trigger which will populate all but the logoff_time of the session_audit table. This uses the sys_context function to lookup the host and session id (different from the SID) of the session. The program is retrieved by a subquery on the V$SESSION table.

CREATE OR REPLACE TRIGGER tr_session_audit_logon
session_id number;
select sys_context('USERENV','SESSIONID') into session_id from dual;
IF session_id != 0 --ignore internal connections
INSERT INTO session_audit (
(SELECT program FROM v$session

UPDATE: I have found that the old version of this resulted in an ORA-1427 error when someone made an internal connection. The problem also came up when dbms_jobs were run. The code now ignores internal connections (where the session id is 0).

Finally we create the logoff trigger to fill in the logoff_time.

CREATE OR REPLACE TRIGGER tr_session_audit_logoff
UPDATE session_audit
SET logoff_time = sysdate
WHERE sys_context('USERENV','SESSIONID') = session_id;

That’s it. You can now search the session_audit table for logins durring a time window or all logins for a specific user. You could even check who is using a certain application.

I mentioned one possible issue, that you don’t want the session_audit table to fill up your system tablespace, however it is also important to mention that if your logon trigger fails for some reason people will not be able to log in, so watch the space, no matter where you put it.

Donald Burleson (who desperately needs a new portrait for his website) of Burleson Consulting offers these instructions for a similar trigger, however he is grabbing more information and fills most of it in at the end.

oracle, sql, dba, database administration, database development, database security, database, oracle security

What the heck do I do with a .cpio file?

For some unknown reason, Oracle considers it necessary to distribute their UNIX software in .cpio files. Since this is the only time I ever use cpio, I can never remember the command and I always end up looking it up.

Well, for future reference, here is how you extract a .cpio file to the current directory on most platforms:

cpio -idmv < filename_to_extract.cpio

Some platforms, like AIX, may give errors like this with these options:

cpio: 0511-903 Out of phase!
cpio attempting to continue...

cpio: 0511-904 skipping 732944 bytes to get back in phase!
One or more files lost and the previous file is possibly corrupt!

cpio: 0511-027 The file name length does not match the expected value.

If you run into these you need to add the c option as the headers are stored in ASCII. The command should now look like this:

cpio -idcmv < filename_to_extract.cpio

For more information refer to the man page for cpio, but this is all I ever do with cpio. For a better UNIX archiving utility, consider tar.