Into The Stream

Uncategorized
| Dec 24, 2019

"Let me try once more," Milo said in an effort to explain. "In other words--"
"You mean you have other words?" cried the bird happily. "Well, by all means,
use them. You're certainly not doing very well with the ones you have now."
- Norton Juster, The Phantom Tollbooth

Sometimes conventional wisdom isn’t so conventional, or common. As a case in point DBAs may believe that the STREAMS pool is reserved strictly for (more...)

Out Of Order

Uncategorized
| Nov 1, 2019

"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

Uncategorized
| Sep 14, 2019

"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?

Uncategorized
| Aug 29, 2019

"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…”

Uncategorized
| Jun 20, 2019

"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!”

Uncategorized
| Jun 4, 2019

"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

Uncategorized
| May 11, 2019

"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

Uncategorized
| May 7, 2019

"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

Uncategorized
| Apr 17, 2019

"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

Uncategorized
| Apr 9, 2019

"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...)