Page 1 of 1
Oracle error in parallel job
Posted: Wed Mar 29, 2006 4:32 pm
by seanc217
I'm getting a strange error in my job. The weird part is that it does not happen all the time.
The error is:
ciratan_6_to_Oracle: Oracle call failed: sqlcode = -38301
Message: ORA-38301: can not perform DDL/DML over objects in Recycle Bin
ciratan_6_to_Oracle: ExecuteImmediate failed for:
alter table DW_D3000_S1.DW_D3000_S1_CIRATAN_6 enable constraint DW_D3000_S1_CIRATAN_6_PK
Thanks for any insight.
Posted: Wed Mar 29, 2006 4:58 pm
by vmcburney
Looks like it's trying to disable the constraint when the job starts, jam the data in there and enable it when the job finishes. Do you see any commands in the before-SQL and after-SQL tabs?
Posted: Wed Mar 29, 2006 8:51 pm
by kumar_s
Is it oracle 10g.
Are you trying to access or drop a table which is already droped.
What you get if you issue
from command prompt?
Posted: Wed Mar 29, 2006 9:32 pm
by rasi
Are you trying do any FLASHBACK option in Oracle 10G. What is that you are trying to do. Post your SQL to understand the problem in detail...
How frequently it happens. Oracle 10G has options like
purge dba_recyclebin
purge recyclebin
purge user_recyclebin
But I would suggest you to discuss with you DBA before doing any purge.
Posted: Thu Mar 30, 2006 8:49 am
by seanc217
Thanks for all the replies.
This job is simple. It loads a datafile into a table. The table does have a primary key. I specify the disable constraint option in the Oracle Stage so that the constraint is disabled and then re-enabled after the load. That's it. I do not execute any custom sql in this stage. I have dropped and re-created the tables several times in the database. I am wondering if DataStage is getting confused somehow with the objects in the recycle bin. I have a case open with IBM I will keep everyone posted.
Posted: Thu Mar 30, 2006 4:55 pm
by vmcburney
On a parallel stage is there any chance the before-SQL and after-SQL could be executed on each node? This would result in duplicate drop and create commands.
Posted: Fri Mar 31, 2006 2:39 pm
by seanc217
I still have not heard anything from support yet. As soon as I do I will post an update if any.