-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|