Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Locking in oracle Expand / Collapse
Author
Message
Posted Saturday, October 9, 2010 3:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 9, 2010 3:36 AM
Points: 5, Visits: 4
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. */
Post #1001696
Posted Monday, October 11, 2010 8:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1002228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse