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.
Oracle error in parallel job
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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?
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
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?
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
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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.
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.
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
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.
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.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn