| Inserting large data locks the destination table in RAC [message #479553] |
Mon, 18 October 2010 05:33  |
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 #479834 is a reply to message #479782] |
Tue, 19 October 2010 11:36  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
