Home » Server Options » RAC & Failsafe » Inserting large data locks the destination table in RAC (oracle 11.1.7 three node RAC, linux)
Inserting large data locks the destination table in RAC [message #479553] Mon, 18 October 2010 05:33 Go to next message
priza
Messages: 2
Registered: October 2010
Location: UK
Junior Member
Scenario:

Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.

What we are performing is:

Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;


Issues:

my_table_rpt get locked


We have found the workaround by disable locking in destination and subsequent to the insert enable locking

ALTER TABLE my_table_rpt DISABLE TABLE LOCK;

Insert the data to the reports database table

Then

ALTER TABLE my_table_rpt ENABLE TABLE LOCK


Question:

Why does the large destination table (my_table_rpt) get locked in the RAC environment?

Has any of you seen this issues, and what it the work around you used?

please let me know.

Regards,

Pri
Re: Inserting large data locks the destination table in RAC [message #479782 is a reply to message #479553] Tue, 19 October 2010 05:38 Go to previous messageGo to next message
kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA
Junior Member
Hi,
Did you notice any info in alert log?

How about trying MV? MV is ideal for your requirement instead of LOCK and UNLOCK.
Re: Inserting large data locks the destination table in RAC [message #479834 is a reply to message #479782] Tue, 19 October 2010 11:36 Go to previous message
priza
Messages: 2
Registered: October 2010
Location: UK
Junior Member
Thanks for the replay

No noting in alert logs. Get a lock in destination (even though we are selecting form the destination and inserting into it) and large net wait in insert. However if we disable to locking in the destination table the insert works.


Yes may be the MV could be the way forward but it could be a overkill as we don't have extremely large amount of data. I will try and raise this with Oracle.


Thanks.
Previous Topic: Does jdbc thin driver support Oracle TAF
Next Topic: 11GR2 RAC TAF,FCF
Goto Forum:
  


Current Time: Thu Oct 02 04:57:09 CDT 2014

Total time taken to generate the page: 0.11794 seconds