Unanswered: SQL Loader (sqlldr) problem in Oracle 10g
We recently upgraded to Oracle 10.2.0.2 from Oracle 9.2.
Our OS is: Unix AIX
After the upgrade we are facing a strange problem with our sql loader. We load several flat files everyday one after the other into our flat tables using sql loader. After the upgrade, the sql loader hangs completely on a file (at random) and sits there for ever. No error in the logs or anywhere else. No code changes were made after the upgrade.
Here is the syntax we are using from the Unix script to invoke the sql loader.
Same syntax is used repeatedly from a Unix script for all the files and all the tables. But it works fine one day for all the files/tables and fails on other day for a file (random, could be any).
No errors found anywhere. Unix command 'ps -ef' says sqlldr is executing. When I kill using 'kill -9' command, the sqlldr continues with next file without any problem. I tested loading the same file on our test system (10g, Unix AIX) and it works just fine.
Did anyone come across the same problem with Oracle 10g ? Any inputs are greatly appreciated.
But it works fine one day for all the files/tables and fails on other day for a file (random, could be any).
Do you have a "TRUNCATE TABLE" in your control file? If so this this could be related to locks beeing held on the table. If any other process is accessing the table, then SQL*Loader cannot truncate the table.
Next time this happens I would check Oracle sessions and locks rather than OS processes.