Out Of Order


"If you want sense, you'll have to make it yourself."
- Norton Juster, The Phantom Tollbooth

An interesting issue reared its head back in version 9.2 that involves inserting data into multiple tables with a single statement when a foreign key is declared. Regardless of the table order in the insert statement the following error can be thrown:


ORA-02291: integrity constraint (SCOTT.FK1) violated - parent key not found 

The error is generated (more...)

Unexpected Behavior


"Expect everything, I always say, and the unexpected never happens."
― Norton Juster, The Phantom Tollbooth

DBMS_LOGMNR is a great tool for finding information about what’s going on in an Oracle database. If it’s written to the redo logs DBMS_LOGMNR can find it. But sometimes it can find too much information, especially when assumptions are made regarding table ownership. Application code is often tested with ‘pristine’ database configurations, where ‘one table, one owner’ is (more...)

It’s A Date?


"You often learn more by being wrong for the right reasons
than right for the wrong reasons." 
― Norton Juster, The Phantom Tollbooth

Dates. The term can conjur fear in the strongest hearts, mainly due to problems that arise when date formats are taken for granted. Not every Oracle database is configured with the ‘default’ Oracle-supplied date format, DD-MON-RR. Since this setting can be changed at both the database level and the session level (more...)

“Just One More…”


"So each one of you agrees to disagree with whatever the other
one agrees with, but if you both disagree with the same thing,
aren't you really in agreement?" 
― Norton Juster, The Phantom Tollbooth

Partitions can make DBA life easier; they allow faster access to data through partition pruning and they allow for easier archiving and removal of old data. There’s not much that can go wrong with partitions [ominous silence]… until you (more...)

“Hear ye, hear ye!”


"Have you ever heard the wonderful silence just before the dawn? Or the quiet
and calm just as a storm ends? Or perhaps you know the silence when you haven't
the answer to a question you've been asked, or the hush of a country road at
night, or the expectant pause of a room full of people when someone is just
about to speak, or, most beautiful of all, the moment after the door closes
 (more...)

Union Station


"Since you got here by not thinking, it seems reasonable to expect that,
in order to get out, you must start thinking."
-- Norton Juster, The Phantom Tollbooth

Combining two data sets can go in several directions and, in the absence of common columns, conventional wisdom would point to using UNION to generate such sets and for many applications this is a suitable solution. If, however, these unions are the ‘table’ for another query, (more...)

Alphabet Soup


"Ali zar u buci nema i dobrih zvukova?" 
-- Norton Juster, The Phantom Tollbooth

Disaster recovery is on the mind of every Oracle DBA, the goal being that there is never a need to use it. Setting optimism aside, many enerprises run regular DR tests to ensure that business can continue as usual should a catastrophic event affect the data center. Before the advent of Oracle Managed Files a ‘proper’ DR onfiguration included generating (more...)

Threadbare


"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

Oracle has, since version 11.2, required standby redo logs to be configured for a Data Guard installation, even though in 11.2 the default log apply process relied on archived redo logs, not the standby logs. That changed in 12.1 and the default became what Oracle labelled “Real Time Apply”; the choice to use archived redo (more...)

Making A List


"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

In-lists can be a convenient way to search for multiple values in a single select statement. They can be hard-coded or can operate on the results of a subquery, and in the first case the reported limitation is 1000 elements, and that is correct for single-valued elements. If the elements are multi-valued that limit changes, which may be (more...)

Recovery Room


"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth

The common wisdom is that any operation declared NOLOGGING won’t contribute to the redo stream, and that includes operations on tables created NOLOGGING. [It cannot be stressed enough that NOLOGGING operations in production databases should NOT be executed if for no other reason than recoverability.] Yet there are operations that will defy the NOLOGGING directive, generating the usual amount of (more...)