| SQL Loader problem in Oracle 10g [message #256783] |
Mon, 06 August 2007 11:30  |
bheemsen
Messages: 13
Registered: February 2006
|
Junior Member |
|
|
Hi,
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.
sqlldr PARFILE=<file with userid=user/password@dbname> data=data_file.dat control=control_file.ctl log=log_file.log bad=bad_file.bad discard=discard_file.dsc errors=999 direct=true
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.
Thanks
-Bheem
|
|
|
|
|
|
|
|
|
|
| Re: SQL Loader problem in Oracle 10g [message #256820 is a reply to message #256783] |
Mon, 06 August 2007 15:33  |
bheemsen
Messages: 13
Registered: February 2006
|
Junior Member |
|
|
Thanks ebrian for your reply.
This is our production batch script that is scheduled to run everyday from cron. I do not want to change a script that is in production. Do you know how to run a truss command on a running script ? Please reply. Also, this sql loader problem is happening at randmon on somedays. I can run the truss command when next time I see the problem.
Thanks
-Bheem
|
|
|
|
|
|
| Re: SQL Loader problem in Oracle 10g [message #257189 is a reply to message #256783] |
Tue, 07 August 2007 13:11 |
bheemsen
Messages: 13
Registered: February 2006
|
Junior Member |
|
|
Yes, the upgrade was performed without any errors.
No invalid objects.
After the upgrade we are using the same hardware and file system.
Unfortnately it is difficult to reproduce the same problem, as it is happening on any file at random. All our systems are on 10.2.0.2. On test system the files loaded successfully.
The day I had posted this question, the sql loader hang on a particular file. The same file got loaded successfully without any problem yesterday on the production system. Nothing changed. It is very weird. So the problem is every now and then. Today we had no problems.
We load multilple files one after the other into the same flat tables using one script everyday.
Hope this answers your questions.
Thanks
-Bheem
|
|
|
|
|
|
| Re: SQL Loader problem in Oracle 10g [message #257304 is a reply to message #256783] |
Wed, 08 August 2007 01:23 |
cbruhn2
Messages: 41
Registered: January 2007
|
Member |
|
|
To bheemsen,
you state that there is a sqlldr process (kill -9), then there should also be an oracle shadow proces associated with this (I assume you don't use multithreaded server)
There should also be an associated sid to identify the process in Oracle.
Have you tried to look in the v$session_wait for the process ?
Have you tried to see if there is a locking problm ? There is a nice note on Metalink (Note:245981.1) about locking identifications through v$session.
Also it should be possible to start a trace on the sid with :
execute sys.dbms_system.set_ev(sid,serial,10046,level,'');
Where sid, serial and level are the numbers you supply. (sid,serial) from v$session and level could be 8.
best regards
Carl Bruhn
|
|
|
|
| Re: SQL Loader problem in Oracle 10g [message #406776 is a reply to message #257304] |
Fri, 05 June 2009 15:51 |
jkowtko
Messages: 1
Registered: June 2009
Location: SF Bay Area
|
Junior Member |
|
|
We've run into what looks like the same problem:
- direct load
- a few dozen relatively small files, loaded in succession
- client and server on different machines
- 10.2.0.1 client, 10.2.0.4 server
- batch job was running from cron
At first the problem seemed intermittent, then we could reproduce it on our Production instance. Reproduced 2-3 times on one specific table, and again on a different table. UAT did not see the problem.
I suspected that Direct Loader was getting caught up coordinating the use of free/used space maps from the database (since it formats the data pages directly) that reached a certain corner condition -- so we turned off direct load and that seems to avoid the problem. Runs 3x slower, but problem avoided.
So I suspect direct load has some quirks in it. If anyone knows what this could be (i.e. bug, version compatibility issue, database maintenance issue, or general config issue) please let us know.
Thanks. john
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
