v$active_session_history 可以嘛

这个问题百度上很多的

select PARSING_SCHEMA_NAME,COUNT(DISTINCT T.SQL_TEXT) from v$sqlarea t
WHERE T.LAST_ACTIVE_TIME >= TRUNC(SYSDATE)
GROUP BY PARSING_SCHEMA_NAME

可以创建一个登录触发器，如果判断是这个用户，就开启10046trace

CREATE OR REPLACE TRIGGER trace_user_steven AFTER LOGON ON DATABASE
BEGIN
  IF USER := 'STEVEN' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=true';
    EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size=unlimited';
    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12'' ';
  END IF;
END;
/

（1）

exec dbms_support.start_trace_in_session(SID,SERIAL#); --跟踪OTHER SESSION
exec dbms_support.stop_trace_in_session(SID,SERIAL#); --停止跟踪OTHER SESSION

（2）

exec dbms_system.set_ev(SID, SERIAL#, 10046, 12, '');
exec dbms_system.set_ev(SID, SERIAL#, 10046, 0, '');

（3）

begin

dbms_monitor.session_trace_enable(session_id =>,

                                  serial_num =>,

                                  binds =>TRUE,

                                  waits =>TRUE,

                                  plan_stat =>'all_executions');

（4）dbms_system.SET_BOOL_PARAM_IN_SESSION 无bind 无wait信息

（5）dbms_system.set_sql_trace_in_session 无bind无wait信息

详情测试方案：http://albertdba.com/?p=626

show parameter user_dump_dest; trace 文件很多怎么查看这个用户的TRACE文件呢

有报错

Skipped error 4098 during the execution of SYS.TRACE_USER_STEVEN 

*** 2019-08-06 09:27:53.946

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)

----- Error Stack Dump -----

ORA-04098: trigger 'SYS.TRACE_USER_STEVEN' is invalid and failed re-validation

*** 2019-08-06 09:28:13.954

*** SESSION ID:(1388.42735) 2019-08-06 09:28:13.954

*** SERVICE NAME:(SYS$USERS) 2019-08-06 09:28:13.954

Skipped error 4098 during the execution of SYS.TRACE_USER_STEVEN 

*** 2019-08-06 09:28:13.954

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)

----- Error Stack Dump -----

ORA-04098: trigger 'SYS.TRACE_USER_STEVEN' is invalid and failed re-validation

oradebug setmypid 

oradebug tracefile_name   --回显为TRACE文件

执行.....

触发器有问题？

Redo thread mounted by this instance: 1

Oracle process number: 84

Unix process pid: 27399, image: oracle@YL-XMK3DB2-DEV (J000)

*** 2019-08-06 11:33:16.316

*** SESSION ID:(564.16911) 2019-08-06 11:33:16.316

*** CLIENT ID:() 2019-08-06 11:33:16.316

*** SERVICE NAME:(SYS$USERS) 2019-08-06 11:33:16.316

*** MODULE NAME:() 2019-08-06 11:33:16.316

*** ACTION NAME:() 2019-08-06 11:33:16.316

Skipped error 4098 during the execution of SYS.TRACE_USER_STEVEN 

*** 2019-08-06 11:33:16.316

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)

----- Error Stack Dump -----

ORA-04098: trigger 'SYS.TRACE_USER_STEVEN' is invalid and failed re-validation