For anyone in the area of Raleigh, North Carolina this week I will be presenting at the North Carolina Oracle User Group’s fall meeting. I will be giving a presentation on Oracle Shell Scripting which includes several tips and scripts from my book by the same title.

Don Burleson will also be presenting on Oracle 11g new features. The meeting runs from 12:30 to 4:30 this Friday, October 5th. More details are available on the NCOUG site.

oracle, dba, database, database administrator, 11g, unix, linux

I’m always amazed how much traffic some of my articles on the Oracle date datatype get, but dealing with dates in SQL can be as daunting for beginners as it is tedious for the experts.

Well, here’s another resource to help you sort out those pesky dates. This one focuses on the to_char function and how it can be used to get dates to output in the format you want.

This article from Don Burleson offers a couple handy examples of the to_char function, but more importantly has a table listing the options for output of the date format.

For more reading on the Oracle date format check out my other Oracle stories.

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

On my story about using the minus SQL operator to compare tables, Blake asked what other more efficient methods could be used for tracking changes between tables.

Here are a couple options you could consider to compare table contents instead of the ‘minus’ operator:

Oracle change tables are probably the best for tracking changes internal to the database. You can see an example of them here.

This is what most Oracle data warehouse ETL is built with.

Custom triggers on insert, update and delete can enable similar functionality to change tables (change tables actually work with internal triggers.) You can have the triggers insert your data into another table or just keep some metadata in a separate table to indicate which rows have changed.

Simply adding an activity date column to your tables, populated with a trigger may be enough to track changes and keep databases in sync. If you know, for example, that the database was cloned at midnight on the 1st of the month, then to see what has changed since then you just need to examine all rows with an activity date after that. The activity date can be useful for several other things as well making this a fairly desirable approach.

Blake has an intriguingly eclectic blog himself which is well worth a visit. Thanks for the question Blake!

oracle, database, dba, database development, database administration

The webcomic xkcd is so good I have to resist the urge to blog just about every strip, but the latest one is just too good not to share.

Top excuse for slacking programmers


This of course applies not only to programmers, but sys-admins and DBAs as well. I mean really, some of those Oracle installs take a long time!

slacker, technology, compile, programming, code, excuse

Here’s a quick and dirty method to compare the contents of a couple tables:

See all the rows in hr.employees which do not appear in oe.employees

SQL> select * from hr.employees
2 minus
3 select * from oe.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
210 Groucho Marx
gm@lifeaftercoffee.com 26-JUL-07 ST_MAN

And the converse can be done by switching the order of the tables:

See all the rows in oe.employees which do not appear in hr.employees

SQL> select * from oe.employees
2 minus
3 select * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
207 Jon Emmons
jon@lifeaftercoffee.com 26-JUL-07 AD_VP

208 Gwen Emmons
gwen@lifeaftercoffee.com 26-JUL-07 AD_VP

This can be very useful if you need to, say, determine what has changed between two different databases. I have been using this over a database link recently to compare tables in a development instance to the tables in a production instance.

The caveats: The two tables must have the exact same column layout. If they are close but not exact you should still be able to make this work by specifying all the fields which are the same instead of ‘*’.

If you want to exclude a field (like an activity date) you will need to specify all the fields you want to include from both tables. The types and order of the fields must be identical between the two select statements.

I describe this as “quick and dirty” because it is. You should not use a query like this in a production database without first looking at how it will affect your database. This will cause full table scans and could kill performance.

oracle, database, dba, database administration, sql

« Previous PageNext Page »