Oracle error in parallel job

[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.

[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?

[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

Code: Select all

select * from user_recyclebin

from command prompt?

[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.

[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.

[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.

[seanc217]

I still have not heard anything from support yet. As soon as I do I will post an update if any.