Author Archive


This is something a colleague asked about the other day and it is a very interesting Oracle hint. The IGNORE_ROW_ON_DUPKEY_INDEX hint is for single table INSERT statements and, when used, will cause the statement to ignore unique key violations for the specified index. Create a test table — Create a table using mod 2 — [...]

Data Guard ORA-16843 and ORA-16839 Solutions »

It was difficult to find a solution for this issue so I thought I’d write about it. Error: ORA-16843: errors discovered in diagnostic repository and ORA-16839: one or more user data files are missing We had a standby database that showed the ORA-16843 error when running show configuration in Data Guard Manager. Then, while running [...]

Checker run found 1 new persistent data failures »

I saw this in the alert.log file for a standby database. I didn’t find much information on it during a search so I figured I’d put a post together. Checker run found 1 new persistent data failures Here is the full text of what I was seeing in the alert.log. Mon Feb 09 08:39:43 2015 [...]

Write to Trace files and Alert Log with DBMS_LOG »

From time to time it can be handy to write your own custom messages to the alert log. You can also write messages to trace files. I’ve used this approach while performing a massive partitioned table redesign just so I could keep track of what happened during the 5 day process. For example, I may [...]

Rename Oracle User (Schema) »

If you have ever wanted to rename a user in oracle, but didn’t think you could then this is for you. Before I continue I should mention this is not an oracle supported process so continue at your own risk.  I will say this though … I have NEVER experienced any issues with the following [...]

ORA-04068 – What to do about it »

One of the most irritating errors I’ve ever had to deal with is         ORA-04068: existing state of packages has been discarded. If you have ever received this error while incorporating connection pooling here is what you can do about it. The error looks like this in SqlPlus. ERROR at line 1: ORA-04068: existing state [...]

Query the User Environment with SYS_CONTEXT »

Many times I have granted permissions to various V$ views so I (or developers) could query environment information for a user.  That is mostly unnecessary when using the SYS_CONTEXT function. Below are a few examples of what the SYS_CONTEXT function can provide.  I’ve done this in a SQL statement format so you can copy and [...]

Using Advanced Query Rewrite to Fix Vendor SQL »

Have you ever had vendor software that performed poorly and there was no way to tune it by creating indexes – or the vendor restricted you from trying?  If so, here is a way you may be able to help the offending query. Advanced Query Rewrite (DBMS_ADVANCED_REWRITE) gives the DBA a powerful method to influence [...]


The other day my boss sent me an email telling me that we must have fixed object stats (GATHER_FIXED_OBJECT_STATS) in the database. He also included a link to this article explaining why we have to have fixed object stats. I have never run fixed object stats and I thought I had missed something extreme. At [...]

Oracle – Sessions Logged In »

Often it is nice to see who is logged in to a database.  This is a simple script I use. This script is called sid.sql set linesize 132 set pagesize 100 ttitle on ttitle center ‘*****  Connected Users  *****’ skip 2 clear breaks column sid                format 9999         heading ‘SID’ column serial#            format 99999        heading ‘Ser#’ [...]