How to find last SQL running on Database

SQL> spool D:\lastsql.lst

Enter value for filename:

SQL> set verify off lines 132 head on

SQL>

SQL>

SQL> col UNAM format a20  word  heading 'User'

SQL> col STMT format a56  word  heading 'Statement'

SQL> col RUNT format a08  word  heading 'Run Time'

SQL> col ltim format a20 word heading 'Logon Time'

SQL> col etim format a20 word heading 'Connect Time'

SQL> col PROG format a30 word heading 'Program|Client Terminal Details'

SQL> col SID  format a10 word heading 'SID/|Serial#'

SQL> col DR   format 999999999 heading 'Disk Reads'

SQL> col BG   format 999999999 heading 'Buffer Gets'

SQL> col thread   format 99999 heading 'ThreadID

string "'ThreadID" missing terminating quote (').

SQL> col sqltext format A64 wrap heading 'Last SQL'

SQL>

SQL> break on unam on sid on status

SQL>

SQL> select S.USERNAME||'('||s.sid||')-'||s.osuser     UNAM

  2  --      ,s.program||'-'||s.terminal||'('||s.machine||')' PROG

  3        ,s.sid||'/'||s.serial# sid

  4       ,s.status "Status",p.spid

  5       ,sql_text sqltext

  6  from v$sqltext_with_newlines t,V$SESSION s , v$process p

  7  where t.address =s.sql_address

  8  and p.addr=s.paddr(+)

  9  and t.hash_value = s.sql_hash_value

 10  order by s.sid,t.piece

 11  /

* Please test this script before use. Author will not be responsible for any damage that may be cause by this script.

Host and IP address Information

SYS_CONTEXT

The SYS_CONTEXT function is able to return the following host and IP address information for the current session:

* TERMINAL - An operating system identifier for the current session. This is often the client machine name.

* HOST - The host name of the client machine.

* IP_ADDRESS - The IP address of the client machine.

* SERVER_HOST - The host name of the server running the database instance.

The following examples show the typical output for each variant.

SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

SYS_CONTEXT('USERENV','TERMINAL')
--------------------------------------------------------------------
oraplanet

1 row selected.

SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;

SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------
oraplanet

1 row selected.

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------
192.168.0.1

1 row selected.

SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

SYS_CONTEXT('USERENV','SERVER_HOST')
--------------------------------------------------------------------
Ziya

1 row selected.

SQL>