last SQL statement?

[Sweetie]

Hi all

How do I check the last and the current SQL statement a user issued?
Also I tried to sql_text from v$open_cursor for the current statement (am I right) but the sql_text column is always truncated, is there anyway to see it fully with SQL*PLUS?

Thanks

[aracnid]

you have to check V_$SQLTEXT_WITH_NEWLINES in join with v$session by address

[sthiriveed]

which address in v$session..
paddr, saddr or sql_addr

[Sweetie]

I think sql_address

anyway I see sql_text truncated though, is there anyway to see them full?

[Sweetie]

I did this

select username, sql_text
from v$session, v$sqltext_with_newlines
where v$session.sql_address=v$sqltext_with_newlines.address
and username is not null;

I got this back:

USERNAME SQL_TEXT
---------- ----------------------------------------------------------------
LSC d username is not null


LSC select username, sql_text
from v$session, v$sqltext_with_newline

LSC s
where v$session.sql_address=v$sqltext_with_newlines.address
an


which is one query, how can i order it?

[aracnid]

try this:
prompt USER VAR
set heading off
col ute for a10
col time for a30
col text format a500
select v.sql_text text, d.username ute, to_char(d.logon_time, 'dd-mon-yyyy:hh:mi') time
from V_$SQLTEXT_WITH_NEWLINES v, v$session d
where d.username='&USER
and
v.address=d.SQL_ADDRESS order by logon_time
/
.... so you've to put the user you want to get

[Sweetie]

That looked horrible, cant read anything at all :(

[sanrak]

Hi

Change the order by column to piece instead of logon_time

_____________
Santosh
OCP - Dba 8i

[Tatyana]

Sweetie, i have tested this now and got the current SQL statement

select username,
sql_text
from v$session, v$sql
where v$session.sql_address =
v$sql.address;
i have O7.2.3