ORA-23375 when adding new master site in multi-master environment

If your database global name contains a word that belongs to the list of Oracle Database Reserved Words and you are planning to add this database as a new master site in multi-master environment, you may want to consider changing the database global name so you can avoid ORA-23375 when (more...)

Importing Payments and Paid Invoices into Oracle – Oracle needs to provide an API

| Aug 31, 2008
How many of us had a requirement of importing paid invoices and their corressponding payment details to Oracle? I would say many. But strangely till date there in no straight forward method to import payments into Oracle. I feel its time that Oracle comes up with a standard process like an API to handle this business need.
There are a few workarounds to this scenario. A direct update of Oracle base table is one but this comes with the risk of losing support from Oracle.
The other, more robust way would be to use the manual payment feature of Oracle (more...)

APEX 3.1.2 Is Available

In case you were not aware, Oracle has released a new version of Application Express. From the APEX site on OTN:

Aug 28

I just finished upgrading my local apex instance. I chose to apply the patch instead of a full install and it worked fantastic. Took less than 5 minutes to patch on my dell 620 laptop  running Ubuntu (more...)

It’s HARD to Break the Marketing Mold

Yesterday a friend who is starting an Oracle blog emailed me her first post. I won’t share it here, but it a was well-written piece of marketing copy about an industry analyst report. Here’s my reply to her:

It's good, of course, but I think it's off the mark.

I (more...)

Concurrent Request Phase and Status

| Aug 26, 2008

Slow drop user

For past 3 weeks i was covering for a project dedicated DBA who had gone on leave for 3 weeks (some people are just lucky). Its an Oracle Peoplesoft General Ledger application on Oracle database. Lot of copies of application are hosted in same database as multiple copies for different stages of software lifecyle.

I was asked by application team to refresh 6 schemas from 2 other schemas over the weekend. How hard could it be I thought? Seemed like a simple request. Just take a datapump export of source schema, drop the destination schema and (more...)

My first post

Finally i managed to start my own blog. I had been thinking about starting my own blog on my website for some time. But somehow it never took off. I am pleased that its up and running….

Watch this space for some interesting oracle reading.

Partition-wise dependencies in 10g release 2

| Aug 20, 2008
Modify partitions without invalidating dependant objects. October 2007 (updated August 2008)

AWR Usage Poll

A number of recent threads in the Oracle-L list have made it pretty clear that Automated Workload Repository (AWR) is a tool that you are expected to use when troubleshooting a database problem.

Never mind the fact that AWR is still a product that is licensed separately from the database, and that a large segment of the Oracle DBA population doesn't seem to realize that. Or that Active Session History (ASH) is part of AWR, and falls under the same license restrictions.

So I conducted a poll regarding the use of AWR. AWR Usage Poll. If you haven't in the (more...)

A quick gripe

I really did not want the Oracle section of this blog to be my whining about all the nasty things Oracle does to me. It is just hard to find the time and motivation to write up anything positive when I run into three new (to me at least) bugs in three days. So, I’m going to gripe:

I hit the bug in metalink note 469587.1 where “Error PLS-00167 : keyword BULK is used in a wrong context” is raised with an incorrect interval literal. Of course, I had to come up with a unique twist of my own. (more...)

Oracle E-Business Tax: Regime to Rate Flow in Oracle R12

| Aug 11, 2008

In Oracle Release12, there has been some very significant changes, one among them being how we define tax. E-Business Tax is now a single point where we define our taxes for all the sub-ledger modules. In this article I will try to do a sample Tax setup using the Regime to Rate Flow of Oracle E-Business Tax in the Tax Manager responsibility.

Creating a Tax Regime

In Oracle E-Business Tax, a Tax Regime is the system of regulations for the determination and administration of one or more taxes.

  1. Navigate to the Create Tax Regime:

• Navigation: Tax Configuration àTax (more...)

Fast-Start Failover – It is reliable

Last Friday there was one post on OTN forums that brought my attention. The OP was wondering “Is Data Guard Buggy” with attention to Fast-Start Failover (FSFO) feature that provides an ability of automatic failover to the standby database if the primary database is not available for certain time.

He (more...)

SCJP 5 and 6

by Fábio Souza OK, I know that is a little late to talk about SCJP 5 but I will give my feedback about the exam. This will help people that feels unprepared to take SCJP 6 (that was my case) and the...

This is a summary only. Please, visit the (more...)

Default precision fractional for timestamp datatype is 6. Huh?

I’ve always read that the default precision for timestamps was 6.

From “Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-03


The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values. Specify the TIMESTAMP datatype as follows:

TIMESTAMP [(fractional_seconds_precision)]

where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value (more...)

Index block split bug in 9i

In his famous index internals presentation Richard Foote mentions a bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.

While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the space issue. The related bug is 3196414 and it is fixed in 10G.

Here is the test case Richard presents in his paper.

SQL> create table t(id (more...)

An unusual cause of ORA-12154

The ORA-12154 (and its cousin ORA-12514) have been the bane of many a novice DBA.

This error is essentially telling you that you have messed up your database naming configuration, whether it be tnsnames, oracle names or OID. The fix is normally quite simple - just correct the naming.

This is usually quite easily done with netmgr, a tool that is fairly good at its job. The syntax for setting up a tnsname is a little convoluted, and I've fallen back on netmgr a number of times when I can't seem to get it just right by typing the entries (more...)

TGI g_friday

Found in a package body:

g_friday CONSTANT VARCHAR2(6) := 'Friday';

...then a couple of hundred lines later:

if to_char(business_date,'fmDay') = g_friday then
    ...end-of-week processing...
    ...regular processing...
end if;

Now that's flexible. If end-of-week processing is ever moved to the weekend, all you have to do is set g_friday := 'Saturday'.

OTN members, don’t change your e-mail account!

I am regular user of OTN and its forums. Last week I was trying to login to OTN from a public computer and I got the "invalid login" error everytime I tried. I was sure I was typing my password correct but I could not get in anyway. So, I tried to get my password reset and sent to my e-mail address. Then I remembered that the e-mail address I used to register for OTN was from my previous employer meaning I did not have access to it anymore. As OTN does not allow changing the registration e-mail address I (more...)