Dodgy causes of ORA-12549

etloracleoracle-11g-r2

I am dealing with a rather massive ETL processing which keeps throwing me the ORA-12549 error.

Have been looking for problems in the database so far and nothing. The DBA says the database is "fine" – has resources, hasn't reached maximum files to open or processes and have space on all tablespaces.

I start to wonder if this error could have been thrown by client. However it is a Java application using JDBC to connect and the heap size is of 16gb, also not nearly filled up according to the GC logs.

The process itself is rather simple: make a massive select into Oracle, apply some logic over the data and commit is periodically to another table. I have used commits from 5million lines to mere 100k (divided in 100 batches of 1k lines). The error is always about the same point.

Can someone give me some insight on what is happening? Where should I look for clues?

Client-side, ulimit is 1024 (on Oracle's server is is 65k), could it be related?

Best Answer

This is a resource issue.

The DB server cannot satisfy your queries due to the client host being configured incorrectly as-per the pre-requisites for an Oracle database client installation.

Ask your system administrators to verify that they have set the required number of open files, semaphores, shmax etc etc. Link to the Oracle documentation - I assume Linux, but Solaris docs are a link away from that.

Related Solutions

Mysql – Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

For Oracle, this seems like a good sneaky way of catching COMMITs:

https://stackoverflow.com/a/6463800/790702

What he doesn't mention is that you should be able to catch the constraint violation in your code too, to stop the 2nd situation occurring.

Why ORA-00932: Inconsistent Types error

When you are casting a varchar to a number you have to make sure this is possible for ALL values in this table. So in your case also for the values you think are filtered by "WHERE UPPER(NAME)!='XXX'". The reason is the optimizer does not have to follow a specific order for the logic in your query. So it could do the casting and joining the tables frist and only after do the filtering specified in the inner query. Changing the * to a specific field can change the execution plan the optimizer chooses, hence you could have the problem with one version and not the other.

You could use your own function to avoid this error:

create or replace function tonumberorzero(txt in varchar2) return number
 is
   retval number;
 begin
    return to_number(txt);
 exception
    when invalid_number then return 0;
 end;

Best Answer

Related Solutions

Mysql – Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Why ORA-00932: Inconsistent Types error

Related Question