Unexpected results from V$SQL and V$SESSION
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Unexpected results from V$SQL and V$SESSION

  1. #1
    Join Date
    Apr 2001
    Posts
    54

    Unexpected results from V$SQL and V$SESSION

    I am trying to capture all of the SQL sent by an application. To test my query, I am attempting to get the SQL sent from a SQL Plus session. My syntax is below:

    col sql_text form a100

    select b.sql_text, b.last_load_time from v$session a, v$sql b
    where a.schemaname = 'SCOTT' and upper(a.program) = 'SQLPLUS.EXE'
    and b.parsing_schema_id = a.schema#
    order by b.last_load_time;


    This seems to be working OK, but I also get some statements not sent directly by my user. After logging in as SCOTT and issuing 2 SQL statements:
    select username from user_users;
    select table_name from user_sdo_geom_metadata;


    This is my result when trying to view SCOTT's activities from a DBA user:

    SQL> select b.sql_text, b.last_load_time from v$session a, v$sql b
    2 where a.schemaname = 'SCOTT' and upper(a.program) = 'SQLPLUS.EXE'
    3 and b.parsing_schema_id = a.schema#
    4 order by b.last_load_time;

    SQL_TEXT
    --------------------------------------------------------------------------------
    LAST_LOAD_TIME
    -------------------
    BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
    2009-04-01/08:27:06

    SELECT DECODE('A','A','1','2') FROM DUAL
    2009-04-01/08:27:06

    SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPE
    R(PRODUCT)) AND ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPE
    R(ATTRIBUTE) = 'ROLES')

    SQL_TEXT
    --------------------------------------------------------------------------------
    LAST_LOAD_TIME
    -------------------
    2009-04-01/08:27:06

    SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
    RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)

    2009-04-01/08:27:06

    SELECT USER FROM DUAL
    2009-04-01/08:27:06

    SQL_TEXT
    --------------------------------------------------------------------------------
    LAST_LOAD_TIME
    -------------------

    BEGIN DBMS_OUTPUT.DISABLE; END;
    2009-04-01/08:27:06

    select username from user_users
    2009-04-01/08:28:24

    select table_name from user_sdo_geom_metadata
    2009-04-01/08:53:12

    SQL_TEXT
    --------------------------------------------------------------------------------
    LAST_LOAD_TIME
    -------------------

    8 rows selected.


    Can anyone tell me why I am seeing these other statements, and how to filter them out of my results?

    Thank you.
    -mcslain

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool Tie-down

    In order to find the sql a session is executing, you need join V$SQL with one or more of these columns:

    SQL_ADDRESS - Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed

    SQL_HASH_VALUE - Used with SQL_ADDRESS to identify the SQL statement that is currently being executed

    PREV_SQL_ADDR - Used with PREV_HASH_VALUE to identify the last SQL statement executed

    PREV_HASH_VALUE - Used with SQL_HASH_VALUE to identify the last SQL statement executed
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Apr 2001
    Posts
    54
    Thanks, but none of those columns change my query when they are used. I still get these values returned:

    BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
    SELECT DECODE('A','A','1','2') FROM DUAL
    SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPER(ATTRIBUTE) = 'ROLES')
    SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)
    SELECT USER FROM DUAL
    BEGIN DBMS_OUTPUT.DISABLE; END;


    When all I want to see is the actual SQL ssubmitted by the user:

    select username from user_users
    select table_name from user_sdo_geom_metadata


    Thanks
    -mcslain

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width