Re: Any way to get the last SQL statement?

From: Joe Maloney <jrpm_at_my-deja.com>
Date: Thu, 17 Aug 2000 15:32:32 GMT
Message-ID: <8nh0if$170$1@nnrp1.deja.com>

In a multi_user system, consider

select sql_text,users_executing from v$sql where users_executing >0

This will show you what is running now.

In a single user system, any sql you use to get the sql text will be the last.

If you know the userid, the original of the following has been around a while. I lost the reference, this has some of my mods:

Rem
rem FUNCTION: Generate a report of SQL Area Memory Usage rem showing SQL Text and memory catagories rem
rem sqlmem.sql
rem

column sql_text      format a80   heading Text
column sharable_mem               heading Shared|Bytes
column persistent_mem             heading Persistent|Bytes
column runtime_mem                heading Runtime|Bytes
column users         format a15   heading "User"

rem start title132 "Users SQL Area Memory Use" rem spool rep_out\&db\sqlmem
spool d:\reports\sqlmemp.rpt
set long 1000 pages 59 lines 132
break on users
compute sum of sharable_mem on users
compute sum of persistent_mem on users
compute sum of runtime_mem on users
select username users, sql_text, sharable_mem, persistent_mem, runtime_mem
from sys.v_$sqlarea a, sys.dba_users b
where a.parsing_user_id = b.user_id
and b.username like upper('%&user_name%') order by 1;
spool off

This will give you the statements in the SGA for a user. Take out the 'where' and it will dump the SGA.

With some experimentation, you might extend this SGA issue by linking the V$session status='ACTIVE' with minimum LAST_caLl_ET and userid.

In article <MpSm5.4817$XUq6.36307407_at_news.randori.com>,   "Rognvald Bjarne" <wear_u_out_at_nospam.hotmail.com> wrote:
> Closest I can get is to see which ones are "active" by linking
 v$sqltext or
> v$sqlarea with v$session; this'll be interesting to see if someone
 finds
> what you're looking for though.
>
> "Kit" <pashwawa_at_hotmail.com> wrote in message
> news:8nft9v$o3f29_at_imsp212.netvigator.com...
> > I need to get the last SQL statement performed by Oracle. Can it be
> > retrieved from the system tables?
> >
> > Thanks.
> >
> >
>
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Aug 17 2000 - 10:32:32 CDT