Locking in oracle

  • qaz123 90964

    SSC Journeyman

    Points: 97

    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. */

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply