Restrict the other session for tables [message #665624] |
Wed, 13 September 2017 04:57 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
I have one requirement is as follows.
One procedure is using 4 tables and when I hit this procedure no user can access these tables by using any of the db objects etc. how to restrict this. Once procedure execution completes then any one can access those tables. Please advice.
|
|
|
|
|
Re: Restrict the other session for tables [message #665627 is a reply to message #665626] |
Wed, 13 September 2017 06:52 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
For example,
If application user A is accessing the proc proc1 using 4 tables.
Second application user B doing different activity and is accessing different procedure proc2 using 2 tables out of 4 tables.
Now proc A is running and at the same time user B is accessing proc2, while accessing proc2, user B should get the message " Table is used by proc A and try after some time".
|
|
|
|
|
|
Re: Restrict the other session for tables [message #665631 is a reply to message #665630] |
Wed, 13 September 2017 07:25 |
Solomon Yakobson
Messages: 3284 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Well, this is indeed bizarre requirement and is, as BlackSwan stated, most likely flawed. What if some user is selecting already when you need to run that procedure? Anyway, you could create a global context, policy function which checks context variable value and raise error if it is set to "busy" + access originated not from stored procedure and create policy on the 4 tables using the function. Then set context to"busy" as very first step in stored procedure.
SY.
|
|
|
Re: Restrict the other session for tables [message #665633 is a reply to message #665630] |
Wed, 13 September 2017 07:59 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
What a silly requirement, so how about this silly solution?
Why not rename all the tables involved and have the procedure operate on those tables, then rename the tables at the end? Equally as dumb, but based on your description, sounds like it meets your requirements.
|
|
|
|
|
|
|
Re: Restrict the other session for tables [message #665638 is a reply to message #665624] |
Wed, 13 September 2017 09:42 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You could adjust your procedures such that the first lines in each areselect * from <your table> skip locked;
lock table <your table> in exclusive mode; for each table. I think that would mean that if no-one is running any of the procedures then you can run one and gain exclusive access; if any other session runs a procedure, it will hang until the first procedure commits or rolls back. This will not prevent anyone from querying the table without using FOR UPDATE.
|
|
|
|
Re: Restrict the other session for tables [message #665661 is a reply to message #665649] |
Thu, 14 September 2017 06:07 |
Solomon Yakobson
Messages: 3284 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
This will not prevent other sessions selecting from these sessions. And lock table <your table> in exclusive mode; superseeds select * from <your table> skip locked;, so there is no need to select * from <your table> skip locked;
SY.
|
|
|
Re: Restrict the other session for tables [message #665663 is a reply to message #665661] |
Thu, 14 September 2017 07:32 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
If you are using these tables as work areas for the procedure then no locking is needed. Simply change the table to GLOBAL TEMPORARY TABLES (GTT) and no locking is needed and there can't be any contention.
|
|
|
Re: Restrict the other session for tables [message #665671 is a reply to message #665661] |
Thu, 14 September 2017 11:47 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:This will not prevent other sessions selecting from these sessions. And lock table <your table> in exclusive mode; superseeds select * from <your table> skip locked;, so there is no need to select * from <your table> skip locked; I think my solution is good. The use of SKIP LOCKED does not prevent other sessions from selecting from the table, but it does mean that they will see no rows if the table is already in use. The LOCK prevents them inserting any new rows that they could see. So overall, I think that achieves what OP wants: no-one can do or see anything until the first user is finished. They only problem is that it relies on users only ever hitting the table through procedures that include these statements.
|
|
|
Re: Restrict the other session for tables [message #665675 is a reply to message #665671] |
Thu, 14 September 2017 23:36 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
We have tried this way.
1. created the table table_status with proc test1 status as FALSE. When we start Proc test1 then changing the status to TRUE and changing the status again to FALSE at the end of the proc test1.
2. In the remaining procedures, taking the status from the above table, if it is TRUE then sending the error or else we are proceeding.
For example, create table table_status (proc_name varchar2(100),running_status varchar2(10), createddate date);
insert into table_Status('TEST1', 'FALSE', sysdate);
|
|
|
|
Re: Restrict the other session for tables [message #665693 is a reply to message #665675] |
Fri, 15 September 2017 08:52 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
If you are going to try doing this programmatically, you might be better off using DBMS_LOCK to define your own lock, which your procedures would take and release. I don't know a lot about DBMS_LOCK, but I am sure that it would be more robust than anything you write yourself.
|
|
|
|
Re: Restrict the other session for tables [message #665892 is a reply to message #665700] |
Thu, 28 September 2017 23:20 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Yes. You all are right.
Any way i kept the status in the table for not to run other procedures as I mentioned. After running this procedure then we are changing the status to FALSE.
Now we are not getting any issues. We will see if we have any issues.
Thank you very much all for spending time on this.
|
|
|