Home » SQL & PL/SQL » SQL & PL/SQL » Restrict the other session for tables (11.2.0.4)
Restrict the other session for tables [message #665624] Wed, 13 September 2017 04:57 Go to next message
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 #665625 is a reply to message #665624] Wed, 13 September 2017 05:10 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'd lock those tables, I presume. Though, that's not usual.
Re: Restrict the other session for tables [message #665626 is a reply to message #665624] Wed, 13 September 2017 06:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You need to clarify "no user can access these tables". Locking table, like Littlefoot suggested will not prevent users selecting from locked tables.

SY.
Re: Restrict the other session for tables [message #665627 is a reply to message #665626] Wed, 13 September 2017 06:52 Go to previous messageGo to next message
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 #665628 is a reply to message #665627] Wed, 13 September 2017 07:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Again, clarify activity. Does it include selecting data from these tables or just modifying data in these tables?

SY.
Re: Restrict the other session for tables [message #665629 is a reply to message #665624] Wed, 13 September 2017 07:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
grpatwari wrote on Wed, 13 September 2017 02:57
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.
This "design" is SERIOUSLY flawed.
If implemented as stated it will totally serialize the whole application.
For an multi-user OLTP application this will result in a performance nightmare.
Somebody could literally go out to lunch & freeze the whole application for every & all users & prevent any additional processing.

Do NOT do this!
Re: Restrict the other session for tables [message #665630 is a reply to message #665628] Wed, 13 September 2017 07:08 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Selecting and modifying the data in both the procedures. Once proc test1 started running no other DB object should access by any other user and get the error message.
Re: Restrict the other session for tables [message #665631 is a reply to message #665630] Wed, 13 September 2017 07:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
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 #665634 is a reply to message #665633] Wed, 13 September 2017 08:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Yes, this is possible but stored proc would have to be rewritten to use dynamic SQL and to recompile all objects invalidated by rename. And only if tables are not in use. That's why I asked "What if some user is selecting already when you need to run that procedure?"

SY.

[Updated on: Wed, 13 September 2017 08:24]

Report message to a moderator

Re: Restrict the other session for tables [message #665635 is a reply to message #665634] Wed, 13 September 2017 08:22 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Solomon Yakobson wrote on Wed, 13 September 2017 14:21
Yes, this is possible but stored proc would have to be rewritten to use dynamic SQL and to recompile all objects invalidated by rename.

SY.

I feel like that would present the least of the problems here Laughing
Re: Restrict the other session for tables [message #665636 is a reply to message #665635] Wed, 13 September 2017 08:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Roachcoach wrote on Wed, 13 September 2017 09:22

I feel like that would present the least of the problems here Laughing
It could present more issues - table which is currently in use can't be renamed - we'll get ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. So we'll have to loop till each table is released which can be "forever".

SY.
Re: Restrict the other session for tables [message #665637 is a reply to message #665636] Wed, 13 September 2017 08:31 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Solomon Yakobson wrote on Wed, 13 September 2017 14:27
Roachcoach wrote on Wed, 13 September 2017 09:22

I feel like that would present the least of the problems here Laughing
It could present more issues - table which is currently in use can't be renamed - we'll get ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. So we'll have to loop till each table is released which can be "forever".

SY.
OP wants to lock everyone and anyone from so much as reading a table when "thing" runs, technicalities around the implementation of such an insane requirement are definitely the least of the problems Wink
Re: Restrict the other session for tables [message #665638 is a reply to message #665624] Wed, 13 September 2017 09:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You could adjust your procedures such that the first lines in each are
select * 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 #665649 is a reply to message #665638] Thu, 14 September 2017 00:23 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you. I will try this option.
Re: Restrict the other session for tables [message #665661 is a reply to message #665649] Thu, 14 September 2017 06:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 #665687 is a reply to message #665675] Fri, 15 September 2017 05:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And what prevents users from not checking status table before issuing each statement?? And what about SQL that is already running when you decide to set status to TRUE?

SY.
Re: Restrict the other session for tables [message #665693 is a reply to message #665675] Fri, 15 September 2017 08:52 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #665700 is a reply to message #665624] Fri, 15 September 2017 12:35 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I just really think this is not a requirement, but some bad guess at a business rule. This is just plain not practical in a multi-user environment.
Re: Restrict the other session for tables [message #665892 is a reply to message #665700] Thu, 28 September 2017 23:20 Go to previous message
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.
Previous Topic: regexp_replace append 0 to single digit date,month,hour,minute,second
Next Topic: Converting SYS_REFCURSOR to xml
Goto Forum:
  


Current Time: Thu Mar 28 16:59:42 CDT 2024