I am looking on one row/query in view "V$SQLAREA" and want to which user/schema was used to log in to the database when that one query was executed. How i can get that schema name?
Should i look this column maybe:
V$SQLAREA
PARSING_SCHEMA_NAME VARCHAR2(30) Schema name that was used to parse this child cursor
( http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2129.htm )
I have Java software that uses different database schemas/users to make connections to my database. And i want to know for one query which schema/user was used to connect to database before running the query.
--
I think i need to join "V$SQLAREA" to view V$SESSION and there the column "USERNAME" tells me the connection user name for the executed query.
V$SESSION
USERNAME VARCHAR2(30) Oracle username
( http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm )
Seems like i fail to join "V$SQLAREA" to view V$SESSION:
select q.SQL_TEXT, q.sql_id, q.PARSING_SCHEMA_NAME, q.ADDRESS, q.last_active_time, s.USERNAME, s.SCHEMANAME from v$sqlarea q, v$session s
where q.last_active_time > sysdate - 1
and q.sql_text like 'DELETE FROM casino.lgngameoperationlog WHERE sessionCode%'
and s.sql_address=q.address
order by last_active_time desc
/*
no rows found
*/
select q.SQL_TEXT, q.sql_id, q.PARSING_SCHEMA_NAME, q.ADDRESS, q.last_active_time from v$sqlarea q
where q.last_active_time > sysdate - 1
and q.sql_text like 'DELETE FROM casino.lgngameoperationlog WHERE sessionCode%'
order by last_active_time desc
/*
'DELETE FROM casino.lgngameoperationlog WHERE sessionCode = :1 and sessionType = :2'; 51thfgbubkkg6; JAVA; 0000000392523658; 28.10.2010 18:09:14;
*/