How can I see the last SQL statement executed in O

Question

I am new to oracle database.

Can someone give me an example of the steps for how to see the last statements executed on the Oracle database 11g r2?

Answer 1

Find all sql where sql is like ....

select h.sample_time
, u.username
, h.machine
, s.sql_text
, h.*
from dba_hist_active_sess_history h
inner join v$sql s
on s.sql_id = h.sql_id
left outer join dba_users u
on u.user_id = h.user_id
where s.sql_text like 'DELETE%'
order by h.sample_time desc;

You need to be connected as sysdba user for this sql

Answer 2

select sq.PARSING_SCHEMA_NAME, sq.LAST_LOAD_TIME, sq.ELAPSED_TIME, sq.ROWS_PROCESSED, ltrim(sq.sql_text)
  from v$sql sq, v$session se
 where sq.PARSING_SCHEMA_NAME = 'YOUR_SCHEMA'
 order by sq.LAST_LOAD_TIME desc;

Answer 3

Connect as SYS user and execute the following query

select sql_text from v$sql where first_load_time=(select max(first_load_time) from v$sql) ;

Answer 4

A couple of hints:

• In SQLplus, type a semicolon+ to see, and slash to execute again
• In SQLdeveloper, use F8
• If you mean see other users' statements then it's not possible by default.
  • You can configure AUDIT.
  • You can see some SQL statements in SELECT * FROM V$SQLAREA;

Answer 5

You can use the below query to get the last sql executed based on last sql which was active in database

select ltrim(sq.sql_text)
  from v$sql sq, v$session se, v$open_cursor oc
 where sq.sql_id = oc.sql_id
   and se.saddr = oc.saddr
   and se.sid = oc.sid
   and se.audsid = SYS_CONTEXT('userenv', 'sessionid')
 order by oc.LAST_SQL_ACTIVE_TIME desc;

You can also use the below to find the last query executed in your session.

  SELECT (SELECT t2.sql_fulltext
    FROM   v$sql t2
    WHERE  t1.prev_sql_id = t2.sql_id
           AND t1.prev_child_number = t2.child_number) sql_fulltext
   FROM   v$session t1
   WHERE  t1.audsid = Sys_context('userenv', 'sessionid'); 

Answer 6

You can use the below query:

SELECT program_id, program_line#, sql_text
FROM V$SQL VS , ALL_USERS AU
WHERE (executions >= 1)
AND (parsing_user_id != 0)
AND (AU.user_id(+) = VS.parsing_user_id)
AND UPPER(AU.USERNAME)  IN (UPPER('YourUser'))
ORDER BY last_active_time DESC;

if you need to know the statements of an PL/SQL object were executed then use or join with

select *
    from   dba_objects
    where object_id = program_id