Oracle and Datastage compilation error

rexsmith · Post by **rexsmith** » Tue Jun 05, 2007 8:31 am

I've a job which loads the data in to three oracle tables. It worked fine until i started loading bulk data which is abt half million records. I can insert the data in to two tables but the table gets loaded but the job never gets finished. I did stop the job couple of times and restarted it . It doesnt work. I mean the job doesnt finish . I tried loading 100 records even then the job hangs. Even i tried with one record. After a while when i started running the job it throws me warning " ORA-00054: resource busy and acquire with NOWAIT specified " I truncated and dropped the table it throws the same warning . I created new job to load the same table it throws me same warning. Later i started seeing a compilation error which says "cannot get exclusive access to executable file for job"
I created new job but still i get this warning "ORA-00054: resource busy and acquire with NOWAIT specified "

rexsmith · Post by **rexsmith** » Tue Jun 05, 2007 1:35 pm

I followed steps in this link to unlock my jobs . Once my jobs got released i could run it successfully with no warnings
http://dsxchange.com/viewtopic.php?t=10 ... 5add6ef56b

Though i've the answer for my issue i still wonder what could be the reason for the warnings for loading the data in to oracle table from a new job?

chulett · Post by **chulett** » Tue Jun 05, 2007 2:04 pm

Your previous incarnation of the job you 'stopped' was still running. Or at least the Oracle process associated with it still was. That was what kept the table 'busy'. I've seen that happen - job stops or aborts but the Oracle process lives on.

Then you either need to wait for it to finish or have your Oracle DBA kill the SID.

rexsmith · Post by **rexsmith** » Tue Jun 05, 2007 4:16 pm

I've dropped the table and recreated still i had the same issue.

chulett · Post by **chulett** » Tue Jun 05, 2007 5:12 pm

Then somebody else or some other iteration of your process 'got busy' in your table. I've seen job designs that do things 'out of order' and lock themselves out of being able to truncate tables. Or someone (yourself included) could have been in the table via your favorite tool with an inadverdant transaction that kept it in use until they or it exited / completed. That is how a 'resource busy' error is generated.

Have a chat with your DBA if you think I'm full of hooey.

Next time you run into this problem, have your DBA help determine what process or person is responsible. Hard to do after the fact, after it no longer is an issue but pretty straight-forward while it is happening.