Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    India
    Posts
    12

    Unanswered: Locking in Oracle 9i

    Hi All,
    I am currently working in Oracle 9i.I have a front end application in Java which querries and inserts address data into the database.There is tables which serves to store the newly inserted address on that day.At the end of the day i refresh this data into a snapshot and then i have to delete these records from the base table.But the problem is i have to carry out this delete operation when the users will be querring data from this table.Is there any way by which i can lock this table when i start the delete operation and no users will be able to access this table when this delete process is going on.And when the delete process is complete the lock will be released.

    Thanks in advance
    sbdash

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    You can use LOCK TABLE table_name IN EXCLUSIVE MODE statement and perform your delete operation and after commit, lock will autometically be released.

    You can make a small procedure for this.

    CREATE OR REPLACE PROCEDURE delete_all AS
    BEGIN
    LOCK TABLE table_name IN EXCLUSIVE MODE;
    DELETE table_name;
    COMMIT;
    END;
    /

    execute this whenever you want after refreshing the snapshot.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Dec 2003
    Location
    India
    Posts
    12
    Hi,
    Thanks for the quick reply.I am able to lock the table in exclusive mode but still then while running the delete process i find i can querry from this table ?

    Thanks
    sbdash

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    Originally posted by sbdash
    Hi,
    Thanks for the quick reply.I am able to lock the table in exclusive mode but still then while running the delete process i find i can querry from this table ?

    Thanks
    sbdash
    that's how Oracle works. They allow you always to read and they give you the before image from the rows contents

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •