October 9, 2010 at 3:31 am
hi
I have a select statement in sql 2000, with the select on a linked server (oracle) table.
For example
select * from linkserver..dbname.my_table
in SQL 2000 reportedly caused locking issues in oracle server where the actual table is located. I guess this is probably imposed by the linkedserver driver used.
Thought this can be solved by having a view in oracle for the select and calling the view in SQL. While that works, I need to verify if calling the view has indeed prevented the lock from being imposed on the table. I mean, like knowing if lock has been acquired on a table? Any solution ?
Also, how can i verify that the initial lock on a select on oracle table was imposed by the driver?
Pls help
Thanks
/* I have posted this in another sub forum too but i am surprised no has replied yet. */
October 11, 2010 at 8:52 am
qaz123 90964 (10/9/2010)
I have a select statement in sql 2000, with the select on a linked server (oracle) table.For example
select * from linkserver..dbname.my_table
in SQL 2000 reportedly caused locking issues in oracle server where the actual table is located. I guess this is probably imposed by the linkedserver driver used.
Thought this can be solved by having a view in oracle for the select and calling the view in SQL. While that works, I need to verify if calling the view has indeed prevented the lock from being imposed on the table. I mean, like knowing if lock has been acquired on a table? Any solution ?
Also, how can i verify that the initial lock on a select on oracle table was imposed by the driver?
Oracle does not locks tables when doing a "select" statement. The only way this could happen if statement is coded as "select for update".
I would ask Oracle DBA to identify and trace this query and look at how is actually coded.
To check locks on specific table you can do...
select nvl(owner,'SYS') owner,
session_id,
name,
mode_held,
mode_requested
from sys.dba_dml_locks
where name in('YOUR_TABLE_NAME')
order by 2
;
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply