Solved

Using the DBMS_LOCK package: how to lock a user independently from the SQL*Plus session.

Posted on 2008-10-03
24
2,170 Views
Last Modified: 2013-12-18
Good evening,

To lock a user, I use the procedures getlockuser e releaselockuser (see code), as follows:

SQL> edit
Registrato file afiedt.buf

  1  DECLARE
  2    VPUSER VARCHAR2(200);
  3    IRETCODE NUMBER;
  4  BEGIN
  5    VPUSER := NULL;
  6    IRETCODE := NULL;
  7    MASTER.RACPACCESSI_UNICA.GETLOCKUSER ( 'USERIWANTTOLOCK', IRETCODE );
  8    COMMIT;
  9    DBMS_OUTPUT.PUT_LINE ('IRETCODE = '||IRETCODE);
 10* END;
SQL> /
lockhandle   10738250491073825049200
IRETCODE = 0

Procedura PL/SQL completata correttamente.

SQL>

If I open another PL*SQL session, I obtain the following error message when locking the same user:

SQL> edit
Registrato file afiedt.buf

  1   DECLARE
  2     VPUSER VARCHAR2(200);
  3     IRETCODE NUMBER;
  4   BEGIN
  5     VPUSER := NULL;
  6     IRETCODE := NULL;
  7     MASTER.RACPACCESSI_UNICA.GETLOCKUSER ( 'USERIWANTTOLOCK', IRETCODE );
  8     COMMIT;
  9     DBMS_OUTPUT.PUT_LINE ('IRETCODE = '||IRETCODE);
 10*  END;
SQL> /
lockhandle   10738250491073825049200
IRETCODE = -5

Procedura PL/SQL completata correttamente.

SQL>

But if I close the FIRST SQL*Plus session, in the SECOND session already open is it now possibile to lock that user:

SQL> /
lockhandle   10738250491073825049200
IRETCODE = 0

Procedura PL/SQL completata correttamente.

SQL>

Why Oracle "forgets" user locks when I close SQL*Plus session?  Is DBMS_LOCK valid only in the same SQL*Plus session?  How can I lock a user independently from the Oracle session used?
PROCEDURE getlockuser (vpuser IN VARCHAR2, iretcode OUT NUMBER)

   IS

      v_lockhandle   VARCHAR2 (200);

      ireturn        INTEGER;

   BEGIN

      DBMS_LOCK.allocate_unique (UPPER (vpuser), v_lockhandle);

      ireturn := DBMS_LOCK.request (v_lockhandle, 6, 5);
 

      CASE

         WHEN ireturn = 0

         THEN

            iretcode := 0;                                -- 'Lock allocated'

         WHEN ireturn = 1

         THEN

            iretcode := -5;                                       --'Timeout'

         WHEN ireturn = 2

         THEN

            iretcode := -15;                                     --'Deadlock'

         WHEN ireturn = 3

         THEN

            iretcode := -16;                             -- 'Parameter Error'

         WHEN ireturn = 4

         THEN

            iretcode := -5;                              --'Utente  connesso'

         WHEN ireturn = 5

         THEN

            iretcode := -18;                          --'Illegal Lock Handle'

      END CASE;
 

      DBMS_OUTPUT.put_line ('lockhandle   ' || v_lockhandle);

   END getlockuser;
 

   PROCEDURE releaselockuser (vpuser IN VARCHAR2, iretcode OUT NUMBER)

   IS

      v_lockhandle   VARCHAR2 (200);

      ireturn        INTEGER;

   BEGIN

      DBMS_LOCK.allocate_unique (UPPER (vpuser), v_lockhandle);

      ireturn := DBMS_LOCK.release (v_lockhandle);
 

      CASE

         WHEN ireturn = 0

         THEN

            iretcode := 0;                               --'Lock Deallocated'

         WHEN ireturn = 3

         THEN

            iretcode := -16;                              --'Parameter Error'

         WHEN ireturn = 4

         THEN

            iretcode := -20;                                    --'Not owned'

         WHEN ireturn = 5

         THEN

            iretcode := -18;                          --'Illegal Lock Handle'

      END CASE;

   END releaselockuser;

Open in new window

0
Comment
Question by:CRISTIANO_CORRADI
  • 11
  • 5
  • 5
  • +1
24 Comments
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 125 total points
ID: 22633649
call ALLOCATE_UNIQUE only once for any given lockname per session. This is why the function stashes the lockhandle in the global variable, printer_lockhandle, and calls ALLOCATE_UNIQUE only if this global has not been initialized. There are two reasons for using this technique: efficiency and avoidance of extra COMMITs. Remember that ALLOCATE_UNIQUE will always return the same handle for a given lockname and that it always performs a COMMIT. Thus, best practice for using DBMS_LOCK includes calling ALLOCATE_UNIQUE only once per named lock.
 
DECLARE
       VPUSER VARCHAR2(200);
      IRETCODE NUMBER;
    BEGIN
       VPUSER := NULL;
       if IRETCODE = NULL then
      MASTER.RACPACCESSI_UNICA.GETLOCKUSER ( 'USERIWANTTOLOCK', IRETCODE );
end if;
  --     COMMIT;  --not required
      DBMS_OUTPUT.PUT_LINE ('IRETCODE = '||IRETCODE);
   END;
SQL> /
 
for more information visit  section 4.1.2.1.2 in the below mentioned url
http://www.unix.com.ua/orelly/oracle/bipack/ch04_01.htm
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 142 total points
ID: 22633693
I am not that familiar with DBMS_LOCK, but from what I read, my guess is that if  you put a commit in your getlockuser procedure it will work the way you want it to.  According to the documentation, ALLOCATE_UNIQUE is supposed to commit, so you should see the lock, but that doesn't appear to be happening.  When you exit out of the first session a implicit commit is executed and that is why the second session works.
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 125 total points
ID: 22633690
see demo section in below url for how the lock works between multiple sessions
http://www.psoug.org/reference/dbms_lock.html
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 233 total points
ID: 22634377
I believe that you are using DBMS_LOCK in a totally different context here.
Purpose of DBMS_LOCK I.e. Named locks is similar to those of semaphores in Operating systems. It is not meant to lock a user. It merely locks a "piece of code" only.

You may use ALLOCATE_UNIQUE multiple times, it is perfectly fine as long as you are able to manage the COMMITS.

Now, When you run your block from the first session it acuires the named lock and waits. It hopes that you may release the lock sometimes later.
In the second session when you request for the lock; it sees that the lock is already aqured by another session and it returns the lock handle.
When you come out of the first session; I.e exits sqlplus, it releases all the locks held. That is the reason you are able to aquire a lock from the second session again.
Hope this explains the behaviour.

Now, What exactly are you trying to use the locking for?
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22634417
@sujith80: I am trying to use locking to avoid that the same Oracle user logins from 2 or 3 or 8000 locations, and runs the same application.  
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22634433
I've found a package called DBLOCK (see http://www.unix.com.ua/orelly/oracle/bipack/ch04_01.htm#ch04-SECT-1.4.1.1 , paragraph 4.1.4.1) but in that hyperlink I can't found the complete script package.

Of course if you have other solutions feel free for different suggestions.
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 233 total points
ID: 22634452
I see.

So you are trying to aquire the lock and running your code if able to get the lock right?
Thats the way to go.
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22634479
Sounds like that!

We have many client locations, and we would avoid that different users with the SAME LOGIN launch the SAME APPLICATIONS, causing errors.  When a user OMICRON runs XYZ application with login ALPHA, another user GAMMA with the same login ALPHA ***CANNOT*** run XYZ application.
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 233 total points
ID: 22634540
So, your XYZ application should have a structure like this.

Get Named Lock;
If able to get the lock
 Do the code for XYZ application;
 Release Named Lock;
end if;

This will prevent multiple logins executing it at the same time.
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22634566
Perfect!  But HOW can I achieve this??
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 233 total points
ID: 22634692
:)
If all your code is in pl/sql code an anonymous block to do it.
(But remember,the duration of the lock is until the end of your session. I mean, if you are switching between things like java, pl/sql, shell,back to java, pl.sql then you need to investigate further.)
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 142 total points
ID: 22634745
What about ignoring locks altogether?  What about a login trigger.

Below is untested, but it could be an sample.
create or replace trigger logon_audit

after logon

on database

declare

  v_prog                varchar2(48);

  v_cnt                 pls_integer;

begin

  select program

    into v_prog

    from v$session

   where audsid = sys_context('USERENV', 'SESSIONID');
 

  select count(1)

    into v_cnt

    from v$session

   where username = sys_context('USERENV', 'SESSION_USER') and

         audsid != sys_context('USERENV', 'SESSIONID') and

         program = v_prog;
 

  if v_cnt > 0 then

    raise_application_error(-20101, 'User ' || sys_context('USERENV', 'SESSION_USER') || ' already logged in with program ' || v_prog);

  end if;

end;

/

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 142 total points
ID: 22634767
That formatted strangely.  Maybe this will be better.
create or replace trigger logon_audit

after logon

on database

declare

  v_prog varchar2(48);

  v_cnt pls_integer;

begin
 

   select program

     into v_prog

     from v$session

   where audsid = sys_context('USERENV', 'SESSIONID');
 

  select count(1)

    into v_cnt

    from v$session

   where username = sys_context('USERENV', 'SESSION_USER') and

         audsid != sys_context('USERENV', 'SESSIONID') and

         program = v_prog;
 

  if v_cnt > 0 then

    raise_application_error(-20101, 'User ' || sys_context('USERENV', 'SESSION_USER') || ' already logged in with program ' || v_prog)

  end if;

end;

/

Open in new window

0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22634887
@johnsone: your solution is not applicable in my applicative context, because users have different login, but access to Oracle with the same login.    In example, users ALPHA, BETA and GAMMA access to the tool using the same Oracle login P300/P300@P300

0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 142 total points
ID: 22634935
This has to work:

create or replace trigger logon_audit
after logon
on database
declare
  v_prog varchar2(48);
  v_cnt pls_integer;
begin
  select program
    into v_prog
    from v$session
   where audsid = sys_context('USERENV', 'SESSIONID');

  select count(1)
    into v_cnt
    from v$session
   where username = sys_context('USERENV', 'SESSION_USER') and
            audsid != sys_context('USERENV', 'SESSIONID') and
            program = v_prog;

  if v_cnt > 0 then
    raise_application_error(-20101, 'User ' || sys_context('USERENV', 'SESSION_USER') || ' already logged in with
program ' || v_prog);
  end if;
end;
/
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22634958
sujith80 wrote:
>If all your code is in pl/sql code an anonymous block to do it.
>(But remember,the duration of the lock is until the end of your session. I mean, if you are switching
>between things like java, pl/sql, shell,back to java, pl.sql then you need to investigate further.)

In our situation, a C# program open a PL/SQL Session using always the same Oracle credentials P300/P300@P300

When the Oracle session is open, some PL/SQL code introduces a "lock" to the HIGH-LEVEL user (not to Oracle P300 user, of course: if so, all users are locked because they can't access to the unique oracle instance P300!).  The lock will be released when the user ends using the application.

0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22634991
It would be useful a global variable containing the list of the HIGH-LEVEL users locked....

But nobody has the complete source of the Oracle package dblock.sql ?!
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 142 total points
ID: 22635076
Then what about a table based approach?

You can have a table with users, applications and flags.  If you do a select for update nowait on the table, then if you get a failure someone else is updating and try again (or assume they have a lock).  If you get a result, check the flag.

The source for the DBMS_LOCK package is wrapped.  You cannot view it.  You can only view the package header.  Reverse engineering the wrapped code would violate your license.
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22635099
I didn't make myself clear: I'm searching for the creation script of the PACKAGE "dblock.sql" contained in the book "Oracle Built-in Packages By Steven Feuerstein, Charles Dye, John Beresniewicz": in this book, the full script is only stored in the floppy/cd-rom attached.......

A table based approach was my first solution, refused from my boss.....

0
 
LVL 27

Accepted Solution

by:
sujith80 earned 233 total points
ID: 22636468
I believe it should not matter who the application users are. The application users connect to the same oracle user. And locking happens at oracle, through the same user.

If the requirement is to prevent multiple application users running the same C# program, the approach I suggested will work.
Provided your code doesnt re-connect within the application.
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22637913
Hey sujith80, what do you think about the following solution I've written??

(see attached code)

I've found this code at site http://www.psoug.org/reference/sys_context.html

CREATE OR REPLACE CONTEXT App_Ctx using My_pkg
ACCESSED GLOBALLY;
CREATE OR REPLACE PACKAGE my_pkg IS
PROCEDURE set_session_id(p_session_id NUMBER);
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
PROCEDURE close_session(p_session_id NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY my_pkg IS
g_session_id NUMBER;
PROCEDURE set_session_id(p_session_id NUMBER) IS
BEGIN
g_session_id := p_session_id;
dbms_session.set_identifier(p_session_id);
end set_session_id;
--
===============================================
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
BEGIN
dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id);
END set_ctx;
--
===============================================
PROCEDURE close_session(p_session_id NUMBER) IS
BEGIN
dbms_session.set_identifier(p_session_id);
dbms_session.clear_identifier;
END close_session;
--
===============================================
END;
/

... and I've integrated id with two stored procedures "USER_LOCK" and "USER_UNLOCK".

I pass in input a VARCHAR2 representing the name of the user I would like to lock VIRTUALLY.

If I use USER_LOCK, this name (= ne_user) will be added to a list stored in a variabled called var1, which is linked to an Oracle context called APP_CTX.  The user will be preceded and followed by an asterisk, so in var1 I could have a long string as follows:

*NAMEOFUSER1**NAMEOFUSER2**NAMEOFUSER3*

When I call USER_UNLOCK with a parameter, the name of the user to VIRTUALLY unlock, a replace function will suppress all occurrences of the name (preceded and followed by an asterisk in order to avoid confusion between names as NE and NECOLL which have a root in common, NE) and will store the result in the same variable var1 linked to the Oracle context APP_CTX.

Opening different SQL*Plus sessions, Oracle will remember the var1 value, only if I give the command:

my_pkg.set_session_id (1234);   (===> of course 1234 is an example, It could be 4321 or 4917).

What do you think about?

What could be wrong?

CREATE OR REPLACE CONTEXT App_Ctx using My_pkg

ACCESSED GLOBALLY;

CREATE OR REPLACE PACKAGE my_pkg IS

PROCEDURE set_session_id(p_session_id NUMBER);

PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);

PROCEDURE close_session(p_session_id NUMBER);

END;

/

CREATE OR REPLACE PACKAGE BODY my_pkg IS

g_session_id NUMBER;

PROCEDURE set_session_id(p_session_id NUMBER) IS

BEGIN

g_session_id := p_session_id;

dbms_session.set_identifier(p_session_id);

end set_session_id;

--

===============================================

PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS

BEGIN

dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id);

END set_ctx;

--

===============================================

PROCEDURE close_session(p_session_id NUMBER) IS

BEGIN

dbms_session.set_identifier(p_session_id);

dbms_session.clear_identifier;

END close_session;

--

===============================================

END;

/
 

CREATE OR REPLACE PROCEDURE NE.user_lock (ne_user IN VARCHAR2)

IS

BEGIN

   DECLARE

      indice                NUMBER;

      appoggio_variabile1   VARCHAR2 (250);

   BEGIN

      -- my_pkg.close_session(1234);

      my_pkg.set_session_id (1234);

      appoggio_variabile1 := SYS_CONTEXT ('app_ctx', 'var1');

      DBMS_OUTPUT.put_line (appoggio_variabile1);

 

      IF INSTR (appoggio_variabile1, ne_user) >= 1

      THEN

         BEGIN

            DBMS_OUTPUT.put_line (   'The user '

                                  || ne_user

                                  || ' is already locked!'

                                 );

         END;

      ELSE

         BEGIN

            my_pkg.set_ctx ('Var1',

                            appoggio_variabile1 || '*' || ne_user || '*'

                           );

            DBMS_OUTPUT.put_line (   'The user '

                                  || ne_user

                                  || ' is locked from now.'

                                 );

         END;

      END IF;

   END;

END user_lock;

/

 

 

DROP PROCEDURE NE.USER_UNLOCK

/

 

CREATE OR REPLACE PROCEDURE NE.user_unlock (ne_user IN VARCHAR2)

IS

BEGIN

   DECLARE

      indice                NUMBER;

      appoggio_variabile1   VARCHAR2 (250);

   BEGIN

      -- my_pkg.close_session(1234);

      my_pkg.set_session_id (1234);

      appoggio_variabile1 := SYS_CONTEXT ('app_ctx', 'var1');

      DBMS_OUTPUT.put_line (appoggio_variabile1);

 

      IF    INSTR (appoggio_variabile1, ne_user) = 0

         OR appoggio_variabile1 IS NULL

      THEN

         BEGIN

            DBMS_OUTPUT.put_line (   'The user '

                                  || ne_user

                                  || ' is already unlocked!'

                                 );

         END;

      ELSE

         BEGIN

            my_pkg.set_ctx ('Var1',

                            REPLACE (appoggio_variabile1,

                                     '*' || ne_user || '*'

                                    )

                           );

            DBMS_OUTPUT.put_line (   'The user '

                                  || ne_user

                                  || ' is unlocked from now.'

                                 );

         END;

      END IF;

   END;

END user_unlock;

/

Open in new window

0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 125 total points
ID: 22639888
Dear Sujith
I don't understand why he want to hold the lock even after user session ends.
once the session is ended that doesn't mean the program is ended?
can u plz guide me?
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22640793
I wish to HOLD the LOCK even after user session ends, because I don't want that other users, accessing to a software tool using different credentials, open a new Oracle connession (Oracle connections are opened with ALWAYS the same login and password on the same SID) launching the same procedure.

All this must be realized without using auxiliary tables.

The only doubt I have is related to the my_pkc.close_session (1234) command: we know that is uses the following command:

dbms_session.set_identifier(p_session_id);

I have to study the dbms_session.set_identifier command to see if that identifier is volatile or permanent: I suppose it is permanent, because opening different SQL*Plus sessions, if I recall my_pkg.set_session_id (1234) , I obtain that the variable instantiation is not changed from the last modification in another session.
0
 

Author Closing Comment

by:CRISTIANO_CORRADI
ID: 31503323
Thanks to all for having helped me to find an acceptable solution!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now