Getting a specific time of day in Oracle

OracleHere’s a way to force an Oracle date calculation to find a specific time of day every time. This is useful when using the DBMS_JOB package for job scheduling jobs as it gives you full control over the time of day a job runs.

The Oracle date data type is accurate down to the second, but if you want to ignore this precision and get to a certain time you can use the TRUNC function to truncate the date down to just the day, then add as many hours or minutes as you want.

By default the TRUNC command will truncate a date and time to midnight on the start of that date, so to consistently get, for example, 2:00 pm, we can truncate the time and add 14 hours.

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

TO_CHAR(TRUNC(SYSDA
-------------------
02/13/2006 02:00 PM

Here we truncate to midnight and add 14/24 of a day (14 hours). This works on the idea that the default increment for math with oracle dates is 1 day, so if we add 14/24 days we’re adding 14 hours. You could also use the NUMTODSINTERVAL function as described here, but it makes the command longer.

Applying this to scheduled jobs

When scheduling jobs with the DBMS_JOB package you specify the interval with a string which evaluates to an Oracle date. The interval is evaluated every time a job runs to calculate the next time the job should run. Typically the interval is something like 'SYSDATE + 1' if you want the job to run once a day.

The interval is evaluated each time the job starts. If the job runs at 11:00 PM, the interval would evaluate to 11:00 pm the following day; however, if you force the job to run with the DBMS_JOB.RUN at 2:30 pm, the interval would be evaluated to 2:30 pm the next day and you have changed your job to run at 2:30 every day thereafter.

To make sure things always run at the same time we can apply the method above to the interval when we submit the job.

SQL> BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => 'TRUNC(SYSDATE + 1) + 23/24';
END;
/

This would update job number 21 to use the string 'TRUNC(SYSDATE + 1) + 23/24' to calculate the next time this job should run. In this case, we take SYSDATE, plus 1 day, truncate it to midnight of that day, and add 23 hours. The end result is, no matter what time the job is run the interval will evaluate to 11:00 pm the next day.

oracle, database, dba, database programming, database administration, sql, plsql, pl/sql

SQL: Find the Week Number in a Month

OracleAjama commented on my article Oracle Question and Answer Site asking how to write a SQL function to return the calendar week in a month.

Basically if your week starts on a Sunday, anything leading up to the first Sunday would be week 1, then the first complete week, Sunday through Saturday would be week 2, then week 3, etc. If the month happens to start on a Sunday week 1 would be a complete week.

If you are unfamiliar with manipulating dates in Oracle, you may want to check out my other articles on Oracle, SQL, Dates and Timestamps and Performing Math on Oracle Dates.

Starting simple I worked out how to find the first day of the month. The TRUNC function will allow you to truncate a date to a certain precision, so I started by truncating the date to the month. Today is February 1, so since we’re using sysdate for some testing we coincidentally get back February 1.

SQL> select trunc(sysdate, 'MM') from dual;

TRUNC(SYS
---------
01-FEB-06

Now we have a starting point. From here I used the NEXT_DAY function to find the beginning of the first week of the month. This is found by looking at the first of the month and the 6 days leading up to it. Since next_day excludes the day it is calculating from we subtract 7 for the start date.

SQL > select next_day(trunc(sysdate, 'MM') - 7, 'Sunday') from dual;

NEXT_DAY(
---------
29-JAN-06

Now we have established the beginning of the first week in the month. The first week starts at the end of the previous month as it is only partially in February. With this date in mind we can now calculate how many days we are from then:

SQL> select sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday') from dual;

SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'MM')-7,'SUNDAY')
------------------------------------------------
3.55945602

This tells us we are 3.559 days from the beginning of the first week in the month. To express that in weeks we simply divide by 7.

SQL> select (sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday'))/7 from dual;

(SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'MM')-7,'SUNDAY'))/7
----------------------------------------------------
.508627646

This tells us that we are about .5 weeks from that first Sunday. We don’t care about the fractional part so we can use the other form of the TRUNC function to to truncate this to a whole number. That would put us in the 0th week of the month, but since we want to start counting at 1 we add 1 to the number.

SQL> select trunc(((sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday'))/7),0) + 1
from dual;

TRUNC(((SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'MM')-7,'SUNDAY'))/7),0)+1
-----------------------------------------------------------------
1

This tells us accurately that right now we’re in the first week of the month. Now we want to try this logic out with some other dates. It’s easiest to do that if we just create a function for it.

CREATE OR REPLACE FUNCTION week_in_month
(check_date DATE DEFAULT sysdate, week_start CHAR DEFAULT 'Sunday')
RETURN number
IS
week_number NUMBER;
BEGIN
select trunc(((check_date - next_day(trunc(check_date, 'MM') - 7, week_start))/7),0) + 1 into week_number from dual;
RETURN week_number;
END;
/

Here I’ve created a function week_in_month to execute this SQL with two parameters. The first, check_date can be any Oracle date. The second parameter, week_start is the day that your week starts. This must be spelled out, like ‘Sunday’, ‘Monday’, etc. The function will return the week number from start of month.

If the check_date is not specified, the current date and time will be used. If the week_start is not specified we’ll default to Sunday.

Now let’s try our function without parameters.

SQL> select week_in_month from dual;

WEEK_IN_MONTH
-------------
1

The function correctly identifies that we’re in week 1 of the month by our original definition. Now we’ll look at a few other examples. Remember, where we have not specified a starting day our function will assume Sunday.

SQL> select week_in_month(to_date('01/29/06','MM/DD/YY')) from dual;

WEEK_IN_MONTH(TO_DATE('01/29/06','MM/DD/YY'))
---------------------------------------------
5

Here we see that 1/29/06 was in the 5th week of January.

SQL> select week_in_month(to_date('02/06/06','MM/DD/YY')) from dual;

WEEK_IN_MONTH(TO_DATE('02/06/06','MM/DD/YY'))
---------------------------------------------
2

This shows that February 6 will be in the second week in February.

SQL> select week_in_month(to_date('02/06/06','MM/DD/YY'), 'Wednesday') from dual;

WEEK_IN_MONTH(TO_DATE('02/06/06','MM/DD/YY'),'WEDNESDAY')
---------------------------------------------------------
1

Here we have specified that our week starts on Wednesday, and based on that February 6th will be in the first week in February.

SQL> select week_in_month(to_date('04/30/06','MM/DD/YY')) from dual;

WEEK_IN_MONTH(TO_DATE('04/30/06','MM/DD/YY'))
---------------------------------------------
6

This is a somewhat rare instance, and it may look like there’s a problem with the logic, but because of how April of 2006 falls, the 30th would actually be in the 6th week of the month based on our criteria.

oracle, sql, dba, database administration, database development

SQL – Finding the Last Weekday in a Month

OracleSidney V. commented on my post Performing Math on Oracle Dates that he was looking for a way to calculate the last weekday of a month. After some research I concluded that Oracle does not provide a function for this, so I wrote one.

I started with the last_day Oracle function to find the last day of the month. I’m using December 2005 as an example as the last day of the month was a Saturday.

SELECT last_day(to_date('12/2005','MM/YYYY')) FROM dual;

LAST_DAY(
---------
31-DEC-05

This gives us the last day of the month regardless of the day of the week. Now we rewind from that 7 days by subtracting 7 from the date so we can look at the last week in detail.

SELECT last_day(to_date('12/2005','MM/YYYY')) - 7 FROM dual;

LAST_DAY(
---------
24-DEC-05

This lets us start 7 days before the end of the month. Now we can look at this last week in detail. To see the last Monday of the month we use the next_day function to go forward from the last_day() - 7.

SELECT
next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day
FROM dual;

DAY
---------
26-DEC-05

Using this we could find the last Monday, Tuesday, etc. of the month. In this case we want to find the last weekday (Monday through Friday) of the month. We can accomplish this with a UNION of several of these statements.

SELECT day
FROM (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual);

DAY
---------
26-DEC-05
27-DEC-05
28-DEC-05
29-DEC-05
30-DEC-05

So we know when the last Monday, Tuesday, Wednesday, Thursday, and Friday of this month was, but which was latest? For that we’ll apply the SQL MAX function to the results of the above union. To do this we put the union above in the FROM part of the query.

SELECT MAX (day)
FROM ((SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual));

MAX(DAY)
---------
30-DEC-05

Now we have the date of the last weekday of the month. The Oracle to_char function can be applied to this result to get the date in a different format.

SELECT to_char(MAX (day), 'DAY, MM/DD')
FROM ((SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual))
;

TO_CHAR(MAX(DAY)
----------------
FRIDAY , 12/30

Now let’s try this logic on another date to make sure it’s sound. We’ll try today (Jan, 6 2006).

SELECT to_char(MAX (day), 'DAY, MM/DD')
FROM (
(SELECT next_day((last_day(sysdate) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Friday') AS day FROM dual)
)
;

TO_CHAR(MAX(DAY)
----------------
TUESDAY , 01/31

So the logic works, but this would be a lot of typing if you wanted to use it, so let’s make it into a function so we can use it anywhere in the database.

CREATE OR REPLACE FUNCTION last_weekday(month_check DATE DEFAULT sysdate)
RETURN date
IS
last_weekday_date DATE;
BEGIN
SELECT MAX(day) INTO last_weekday_date
FROM ((SELECT next_day((last_day(month_check) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Friday') AS day FROM dual));
RETURN last_weekday_date;
END;
/

Note: If you want other users to be able to use this function you have to grant them execute on it. Creating a synonym for it may also be usefull.

Now let's test drive the function we created. We assigned a default of sysdate so if we call it without any parameters it should return the last weekday of the current month.

SELECT last_weekday FROM dual;

LAST_WEEKDAY_DATE
---------
31-JAN-06

If we pass this function a date as a parameter it will return the last weekday of that month.

SELECT last_weekday(to_date('12/2005','MM/YYYY')) FROM dual;

LAST_WEEK
---------
30-DEC-05

Thanks Sidney for the challenge!

UPDATE: Oraboy writes with this algorithm. His code is much shorter than mine and likely more efficient. Thanks Oraboy!

With date_v as
(select last_day(to_date('&MM-&DD-&yyyyy','MM-DD-YYYY')) mydate from dual)
select max(date_v.mydate-(i-1)) last_weekday
from date_v,(select level i from dual connect by level < =3) where to_char(date_v.mydate-(i-1),'Dy') not in ('Sat','Sun')

oracle, sql, dba, database administration, database development

Oracle Optimal Flexible Architecture Explained

OracleI have posted about the Oracle OFA (Optimal Flexible Architecture) and recent changes to OFA for Oracle Database 10g, but I wanted to go into a more practical application of these rules to act as a quick reference.

This is not intended to be a complete explanation of the OFA standard. For more complete information refer to the OFA whitepaper. The OFA Standard Recommendations below are taken directly from the OFA whitepaper.

OFA recommendations 9-11 pertain to very specific installation types I have chosen to exclude them. The examples below are based on a UNIX environment.

OFA Standard Recommendation 1: Name all mount points that will hold site specific data to match the pattern /pm where p is a string constant chosen not to misrepresent the contents of any mount point, and m is a unique fixed-length key that distinguishes one mount point from another.

Typical Application: Name operating system mount points with the convention /u01, /u02, /u03 etc.

Options: Anything can be used instead of the leading ‘u’, however be careful not to use something which could eventually misrepresent the future contents of the directory.

OFA Standard Recommendation 2:Name home directories matching the pattern /pm/h/u, where pm is a mount point name, h is selected from a small set of standard directory names, and u is the name of the owner of the directory.

Typical Application: The original OFA whitepaper states the oracle user home directory be placed in a directory like /u01/app/oracle.

Options: This rule is typically overlooked. The oracle user is usually given a home directory which matches other UNIX users, such as /export/home/oracle/ or /home/oracle, however the directory /u01/app/oracle (or similar) should be created for software installation (see recommendation 4). The recommended home directory (/u01/app/oracle) is typically referred to as $ORACLE_BASE.

OFA Standard Recommendation 3:Refer to explicit path names only in files designed specifically to store them, such as the UNIX /etc/passwd file and the Oracle oratab file; refer to group memberships only in /etc/group.

Typical Application: Whenever possible, refer to the oracle user’s home directory as ~oracle and use other environment variables such as $ORACLE_HOME and $ORACLE_BASE instead of full paths. As an example, you would typically use $ORACLE_BASE/admin instead of /u01/app/oracle/admin.

Options: ~oracle, $ORACLE_BASE and $ORACLE_HOME are typically used as environment variables. Other variables such as $ORACLE_ADMIN can be set and used in a similar fashion.

OFA Standard Recommendation 4: Store each version of Oracle Server distribution software in a directory matching the pattern h/product/v, where h is the login home directory of the Oracle software owner, and v represents the version of the software.

Typical Application: Oracle server software should be stored in a directory below the home ($ORACLE_BASE) in the format $ORACLE_BASE/product/9.2. 9.2 should be replaced with the version of Oracle software installed.

Options: This could be on any of the /u01 sequence of partitions.

NOTE: For 10g Oracle has added another level to allow multiple installs of the same version of software. The new recommendation is to install software in a directory in the format of $ORACLE_BASE/product/10.1/db_1.

OFA Standard Recommendation 5:For each database with db_name=d, store database administration files in the following
subdirectories of h/admin/d:
• adhoc — ad hoc SQL scripts for a given database
• adump — audit trail trace files
• arch — archived redo log files
• bdump — background process trace files
• cdump — core dump files
• create — programs used to create the database
• exp – database export files
• logbook — files recording the status and history of the database
• pfile — instance parameter files
• udump — user SQL trace files
where h is the Oracle software owner’s login home directory.

Typical Application: Again the $ORACLE_BASE directory should be used for the home directory, resulting in a path of $ORACLE_BASE/admin/adump

Options: Not all these directories are always used; however, if there are multiple DBAs I highly recommend the adhoc and logbook directories.

OFA Standard Recommendation 6:Name Oracle database files using the following patterns:
• /pm/q/d/control.ctl — control files
• /pm/q/d/redon.log — redo log files
• /pm/q/d/tn.dbf — data files
where pm is a mount point name, q is a string denoting the separation of Oracle data from all other files, d is the db_name OFA Standard • 21 Oracle System Performance Group Technical Paper, September 24, 1995 of the database, n is a distinguishing key that is fixed-length for a given file type, and t is an Oracle tablespace name. Never store any file other than a control, redo log, or data file associated with database d in /pm/q/d.

Typical Application: Control, redo, and data files are typically stored in one or more of the /u01 series of partitions in a subdirectory called oradata then in a folder matching the database name. The resulting path should resemble /u01/oradata/orcl.

Only control, redo, and data files should reside in this path and they should have the appropriate extensions (.ctl, .log or .dbf respectively).

Control and redo files should have a fixed length number to identify them, such as control01.ctl or redo04.log.

Data files should contain the tablespace name and a fixed length number resulting in the format system01.dbf.

Options: Many place the database name in these files. This is redundant since the database name is already in the path to these files and should be avoided as it complicates changing the database name.

OFA Standard Recommendation 7: Separate groups of segments with different lifespans, I/O request demands, and backup frequencies among different tablespaces. For each Oracle database, create the following special tablespaces in addition to those needed for applications segments:
• SYSTEM — data dictionary segments only
• TEMP — temporary segments only
• RBS — rollback segments only
• TOOLS — general-purpose tools only
• USERS — miscellaneous user segments

Typical Application: Here a segment is a piece of data in the database associated with a table, index, or other database object. Basically you should represent the tablespaces listed above and any needed for the appropriate application. Do not put application data in any of these tablespaces, but instead create one or more tablespaces for each application.

Options: TOOLS and USERS tablespaces may be omitted, however it is likely you will want them in the long run.

NOTE: For database 10g Oracle has added a new tablespace called SYSAUX which contains all non-essential system components.

OFA Standard Recommendation 8: Name tablespaces connotatively with eight or fewer characters.

Typical Application: The eight character limit is not necessary; however, tablespace names should be kept fairly short. More importantly tablespace names should be indicative of their contents. WEBCT_DATA or BANNER_INDEX_SMALL are acceptable tablespace names, but DATA or INDEX would not be.

Options: There is a lot of flexibility here. Using a consistent convention at your site is key.

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

Compressing Extents in Oracle

While Oracle has been moving in the direction of locally managed extents in tablespaces several of us are still working with dictionary managed tablespaces. Here’s a good trick for adjusting table storage clauses in dictionary managed tablespaces.

Note: “compressing” extents is not a way to make data take up less space, but rather a way to take a fragmented (for lack of a better word) table and re-ogranize it into a more contiguous area of storage.

In the older dictionary managed method a storage clause in the table creation command controls how much space was initially allocated to that table (the initial extent) and how much would additionally be allocated if it filled the initial space (the next extent). If no storage clause was specified an often absurd default would be used.

This leaves us with two problems: First, we may have a very low value for the next extent. This can leave us creating extents of, for instance, 32KB even though each row may be 50KB.

Second, we may have tables which already contain a ridiculous number of extents and possibly chained rows. (Chained rows are a topic for another day.)

For my example I will use a table owned by system called session_audit.

To view the storage parameters on a table we can query the dba_tables view:

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
131072 65536

These numbers represent size in bytes, so dividing by 1024 we see that this table is configured to have an initial extent of 128KB and a next extent of 64KB.

We can also count the current number of extents whch make up this table with the following SQL command:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
77

It may seem the right method is to change the initial and next values with an ALTER TABLE command like this:

SQL> ALTER TABLE system.session_audit
STORAGE (INITIAL 10M NEXT 5M);
ALTER TABLE SYSTEM.SESSION_AUDIT
*
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

If we dropped the change to the initial extent we could use this command to change the next extent. If we want to update the initial extent we will need to get sneaky. We could create a new table with the desired storage settings, move the data, then rename the new table to the old name, but this would cause the table to be temporarily unavailable and have the unfortunate side-effect of invalidating any of this tables dependencies. Alternately we could export and import the table but this would lead to the same problem.

Instead, using the ‘ALTER TABLE MOVE’ command, we can tell Oracle to move this table into a different tablespace while at the same time sneaking a new storage clause in.

SQL> ALTER TABLE system.session_audit
MOVE TABLESPACE system
STORAGE (INITIAL 10M NEXT 5M);

Table altered.

We now have our table in the new tablespace with the desired storage clause. This also creates an initial extent of the specified size instead of the smaller extents we had previously. If we want to move the table back to it’s original tablespace we can just issue the same command with the original tablespace.

NOTE: I was also able to tell Oracle to “move” the table to the same tablespace it was originally in. This had the same affect of changing the storage clause and compressing the extents without needing a foster tablespace.

We can now re-check the number of extents and storage clause for the table:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
1

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
10485760 5242880

We see that the number of extents currently being used by the table has been reduced to 1 and our next extent will be 5MB in size.

oracle, database, database administrator, oracle database, dba, sql, database tuning