qaz123 90964 (10/9/2010)
I have a select statement in sql 2000, with the select on a linked server (oracle) table.
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,
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.