Oracle 常用指令 & SQL - SQL Monitor 篇 (sql monitor section)

1. query running SQL or history SQL then get SQL_ID

select sql_id, status, sql_text
from   v$sql_monitor
where  username = 'SYS'
  and  sql_text like '%TAA%';
  
-- or 
select x.sid
      ,x.serial#
      ,x.username
      ,x.sql_id
      ,x.sql_child_number
      ,optimizer_mode
      ,hash_value
      ,address
      ,sql_text
from   v$sqlarea sqlarea
      ,v$session x
where  x.sql_hash_value = sqlarea.hash_value
and    x.sql_address    = sqlarea.address
and    x.username       is not null
and    x.program like 'sqlplus%';

2. generate a report using the REPORT_SQL_MONITOR function.

set long 1000000
set longchunksize 1000000
set linesize 1000
set pagesize 0
set trim on
set trimspool on
set echo off
set feedback off

-- >= 12c
-- type : TEXT / HTML / XML
select dbms_sql_monitor.report_sql_monitor(
  sql_id       => 'sql_id',
  type         => 'TEXT',
  report_level => 'ALL') as report
from dual;

Source:

Real-Time SQL Monitoring

3. Query Running SQL by session

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT 
from v$session a, v$sqlarea b 
where a.sql_address = b.address
  and a.sid = 1111;