ORA-12840 1

[Dagon]

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.

 

[sem]

Most probably you may find locked rows in this table. I have no script, but the idea is to investigate v$session, dba_objects and v$locked_object.

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Something like

SELECT s.SID, s.SERIAL#, s.MACHINE, s.USERNAME
  FROM v$locked_object l, dba_objects t, v$session s
 WHERE t.OBJECT_ID = l.OBJECT_ID
   AND s.SID = l.SESSION_ID
   AND t.OBJECT_TYPE = 'TABLE'
   AND t.OBJECT_NAME = '<table>'
   AND t.OWNER = '<owner>'

With s.SID, s.SERIAL# you may kill session (don't do it), with s.MACHINE, s.USERNAME you may find a guy who locked that table and ask him to commit

Regards, Dima

http://seminihin.narod.ru

 

[Dagon]

There aren't any visible in doubt transactions. The problem seems to be some sort of bug in Oracle. The SQL has the form:

WITH tmp_sec As (SELECT
  				b.external_system_id,
  				b.external_system_name,
  				b.data_access_rule_desc,
  				b.data_class_name,
  				b.ps_gl_comp_id,
  				b.book_global_id,
				b.err_msg
	 		      FROM
	 		  	table(OPO_PKG_OUTBOUND_SECURITY.OPO_F_TMP_SECURITY(911,
                                              to_date('28/09/2005', 'DD/MM/YYYY' ),
				                                                   'SWAP',
										   entity_list('320'), 
										   NULL,
										   NULL, 
										   null ) ) b
			  )
select
...lots more stuff
AND EXISTS 
(SELECT DISTINCT ps_gl_comp_id FROM tmp_sec
	               WHERE ps_gl_comp_id = 
(OPO_PKG_OUTBOUND_COMMON.OPO_F_ENTITY(pb.book_global_id,pb.src_sys_feed_id, pb.business_date))  and err_msg is Null)

Having the exists clause linking back to the "with" statement causes the error. If I remove the "exists", the error goes away.

 

[sem]

What do you mean by "in doubt"? That should be an ordinary transaction holding a (probably row exclusive) lock on table row. If you don't see any transactions loking tables involved, try to check also OPO_PKG_OUTBOUND_COMMON.OPO_F_ENTITY dependencies.

Regards, Dima

http://seminihin.narod.ru