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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy