How to Find Bind Variable Values for SQL Currently Executed by Oracle Server, by Zahar Hilkevich

Finding SQL that is currently being executed is fairly trivial – all we need to know is SID of the current session which can be found in v$session dynamic performance view. However, we also need to lookup the values for the bind variables used in the retrieved SQL.

Let’s get the SQL string first:

SELECT b.*
FROM v$session a JOIN v$sql b ON a.sql_id=b.sql_id
WHERE a.sid=[#sid#]

If the actual SQL text is long, we may want to join v$sqltext view:

SELECT b.sql_text
FROM v$session a JOIN v$sqltext b ON a.sql_id=b.sql_id
WHERE a.sid=[#sid#]
ORDER BY b.piece

So far so good, the only thing that may concern you at this moment is bind variables shown in the SQL.
You may need the actual values and not the variable names.
Here is the solution – we need to use v$sql_bind_capture view:

SELECT b.*
FROM v$session a JOIN v$sql_bind_capture b ON a.sql_id=b.sql_id
WHERE a.sid=[#sid#]
ORDER BY child_number, position

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

How to safely grant ALTER SYSTEM KILL SESSION “privilege” to non-DBA users

The title of the post can be a bit misleading as there is no such Oracle privilege “ALTER SYSTEM KILL SESSION”, and there is just “ALTER SESSION” privilege. Nevertheless, it would be nice to have something similar.

Very often developers want to have ability to kill their own sessions, i.e. sessions started by their personal database users. Apparently it is not safe to let a non-DBA user to have a right to execute the “ALTER SYSTEM” command, so what can be done?

A sys user may create a stored procedure (sp_kill_dev_session) that will only allow killing sessions started by a given user or a set of given users. For example, we can check that the session was started by SCOTT and only in that case allow it to be killed.

CREATE OR REPLACE PROCEDURE sys.sp_kill_dev_session(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user IN ('SCOTT') THEN --the list can be extended
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_dev_session;
/

Procedure created.

Now, all we need to do is to grant EXECUTE privilege to SCOTT:

SQL> GRANT EXECUTE ON sp_kill_api_session TO scott;

Grant succeeded.

Here is how scott can call the procedure from SQL*PLUS:

SQL> exec sys.sp_kill_dev_session(14, 26043)
BEGIN sys.sp_kill_dev_session(14, 26043); END;

*
ERROR at line 1:
ORA-20001: Session has Expired or Invalid sid/serial Arguments Passed
ORA-06512: at "SYS.SP_KILL_DEV_SESSION", line 14
ORA-06512: at line 1

SQL> exec sys.sp_kill_dev_session(14, 26043)

PL/SQL procedure successfully completed.

SQL> exec sys.sp_kill_dev_session(18,19218)
BEGIN sys.sp_kill_dev_session(18,19218); END;

*
ERROR at line 1:
ORA-20002: Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.
ORA-06512: at "SYS.SP_KILL_DEV_SESSION", line 16
ORA-06512: at line 1

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Nuances when dropping an Oracle user, by Zahar Hilkevich

When you need to drop a user who is not currently connected to the database, the task is trivial (please do not try it if you don’t really need to drop your user):

DROP USER SCOTT CASCADE;

With actively used database user schemas,  the above command may fail due to active Oracle sessions opened by that user (ORA-01940: cannot drop a user that is currently connected). So you may want to drop all those sessions first before dropping the user:

DECLARE
    CURSOR c IS
    SELECT sid, serial#
    FROM v$session 
    WHERE username='SCOTT';

    v_sql  VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE';
    v_kill VARCHAR2(200);
BEGIN
    FOR v IN c LOOP
        v_kill:=REPLACE(v_sql,'{sid}',v.SID);
        v_kill:=REPLACE(v_kill,'{serial#}',v.serial#);
        EXECUTE IMMEDIATE v_kill;
    END LOOP;
END;
/

Seems like we are ready now to drop the user? Not so quick! While we were dropping existing sessions, the user might have opened more new sessions, so dropping a user may still fail with the same error:

ORA-01940: cannot drop a user that is currently connected

To work around this issue, we need to apply a simple trick: revoke a privilege to create a session before killing existing sessions! Here is a complete script (to be executed as SYS or SYSTEM):

REVOKE CONNECT FROM scott -- so new sessions will be blocked
/
--If CREATE SESSION privilege was granted to scott, we may want to revoke it as well
--Kill existing sessions:
DECLARE
    CURSOR c IS
    SELECT sid, serial#
    FROM v$session 
    where username='SCOTT';

    v_sql  VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE';
    v_kill VARCHAR2(200);
BEGIN
    FOR v IN c LOOP
        v_kill:=REPLACE(v_sql,'{sid}',v.SID);
        v_kill:=REPLACE(v_kill,'{serial#}',v.serial#);
        EXECUTE IMMEDIATE v_kill;
    END LOOP;
END;
/
--Drop the user
DROP USER SCOTT CASCADE
/