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.

How to check if an anonymous PL/SQL block will compile without trying to execute it, by Zahar Hilkevich

Sometimes we work on a PL/SQL block that can possibly make some content or even object changes we don’t want to do right away. At the same time you want to make sure that your block will compile just fine. A simple solution might be to turn your block into a stored procedure and try compiling it:

DECLARE
--declarations
BEGIN
--any executable content 
END;

All we need to do is to replace DECLARE with “CREATE OR REPLACE PROCEDURE procedure name AS” as follows:

CREATE OR REPLACE PROCEDURE sp_test 
AS
--declarations
BEGIN
--any executable content 
END;

According to Oracle documentation, “to create a procedure in your own schema, you must have the CREATE PROCEDURE system privilege.”

In some rare cases, you may not have such privilege granted, or, if for example, you work in production database, you are not allowed create any new objects. In such circumstances, you can use a very elegant oracle feature, called Inline Procedure.

DECLARE
  PROCEDURE test
  AS
  BEGIN
    --any executable content 
  END test;
BEGIN
   NULL;
END;

Inline procedures don’t require any privileges and don’t need to be executed inside of PL/SQL block so they can be used for validating the code as well as for code reuse.

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

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_dev_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.