-
Ora-12840
Getting this error message when trying to access a table via Oracle pipeline functions:
ERROR at line 44:
ORA-20004: ORA-20001: ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "OPO.OPO_PKG_OUTBOUND_INTERFACE", line 3351
ORA-06512: at line 1
As fas as I can tell, this seems to be caused some sort of uncommitted transaction in a parallel DML operation. What I don't know is how to find out what the transaction is or how to commit it or rollback. This is an urgent production problem, so any help would be appreciated.
-
you have many options
if you have access to the remote database then check the the remote sessions using v$dblink, v$session
if you wanna check from local database then check v$transaction
what I dont know if you do the insert remotely?
-
Originally Posted by Scorby
Getting this error message when trying to access a table via Oracle pipeline functions:
ERROR at line 44:
ORA-20004: ORA-20001: ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "OPO.OPO_PKG_OUTBOUND_INTERFACE", line 3351
ORA-06512: at line 1
As fas as I can tell, this seems to be caused some sort of uncommitted transaction in a parallel DML operation. What I don't know is how to find out what the transaction is or how to commit it or rollback. This is an urgent production problem, so any help would be appreciated.
Yes, You are right. After Parallel DML operation, you need to commit the transaction immediatley, otherwise you will get this error.
Tamil
-
ORA-12840: cannot access a remote table after parallel/insert direct load txn
Dear All,
while executing this procedure , I am getting this error.
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "DWHCO.GET_UNID_FROM_TABLE", line 17
ORA-06512: at line 1
ORA-06512: at "DWHCO.MIG_TEST", line 7
ORA-06512: at line 2
plz find the procedure
CREATE OR REPLACE PROCEDURE MIG_TEST AS
begin
commit;
Insert /*+ APPEND */ into TEST_V24
(
TEST_TXT_1,
TEST_TXT_2
)
select
get_unid_from_table('TB0_CURRENCY@dwhold','UNID','CURRENCY_CODE', 'TEST', NULL, NULL),
'jojan'
from TEST_V241;
commit;
end;
/
In this procedure I am calling a function. in that function I am taking records from another instance using DBLINK.
Plz find the function..
CREATE OR REPLACE function get_unid_from_table (p_table varchar2,p_unid varchar2, p_field1 varchar2, p_value1 varchar2, p_field2 varchar2, p_value2 varchar2)
return number
as
v_SQL varchar2(2000);
v_unid number;
begin
v_sql := 'select ' || p_unid || ' from ' || p_table || ' where ' || p_field1 || ' = ''' || trim(p_value1) || '''';
if p_field2 is not null and p_value2 is not null then
v_sql := v_sql || ' and ' || p_field2 || ' = ''' || trim(p_value2) || '''';
end if;
v_sql := v_sql || ' and rownum = 1';
begin
execute immediate v_sql into v_unid;
return v_unid;
--exception
--when others then
--return 0;
end;
If I remove this command /*+ APPEND */ from the procedure , then it works perfectly. using this I am getting this error message mentioned above. I Tried a lot to solve this issue but couldnot do the same. If we run the same procedure in the same instance itself(ie without using dblink)it will also work.
kindly provide a solution for the same..
jojan T A
-
Insert /*+ APPEND */ into TEST_V24
causes the problem
You can't do that.
Do it without the APPEND hint and it should work.
This is a direct load-insert limitation as the previous post said
Regards
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|