記錄資料庫所有ddl操作

xychong123發表於2017-01-10
資料庫
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging     boolean FALSE



透過ash查詢歷史執行sql：
V$ACTIVE_SESSION_HISTORY
DBA_HIST_SQLTEXT

select s.*,b.SQL_TEXT from (select a.SAMPLE_TIME,a.sql_id  from V$ACTIVE_SESSION_HISTORY a where a.sql_id<>'NULL') s ,DBA_HIST_SQLTEXT b where s.sql_id=b.sql_id ;

select a.SAMPLE_TIME,a.sql_id,b.SQL_TEXT FROM  V$ACTIVE_SESSION_HISTORY a,DBA_HIST_SQLTEXT b   WHERE a.sql_id<>'NULL' AND  a.sql_id=b.sql_id
select a.SAMPLE_TIME,a.sql_id,b.SQL_TEXT FROM  V$ACTIVE_SESSION_HISTORY a,DBA_HIST_SQLTEXT b   WHERE a.sql_id<>'NULL' AND  a.sql_id=b.sql_id AND  a.SAMPLE_TIME<sysdate-1 24;=""



select a.SAMPLE_TIME,a.sql_id,b.SQL_TEXT FROM  V$ACTIVE_SESSION_HISTORY a,DBA_HIST_SQLTEXT b   WHERE a.sql_id<>'NULL' AND  a.sql_id=b.sql_id
AND  a.SAMPLE_TIME between  TO_DATE ('2017-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss') and  TO_DATE ('2017-01-16 12:00:00','yyyy-mm-dd hh24:mi:ss')








SELECT  sql_id,count(*)
    FROM DBA_HIST_ACTIVE_SESS_HISTORY a
   WHERE a.SAMPLE_TIME between  TO_DATE ('2017-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss') and  TO_DATE ('2017-01-16 01:00:00','yyyy-mm-dd hh24:mi:ss') group by sql_id










SELECT  sql_id,count(*)
    FROM DBA_HIST_ACTIVE_SESS_HISTORY a
   WHERE a.SAMPLE_TIME between  TO_DATE ('2017-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss') and  TO_DATE ('2017-01-16 01:00:00','yyyy-mm-dd hh24:mi:ss') group by sql_id




select a.SAMPLE_TIME,a.sql_id,b.SQL_TEXT FROM  DBA_HIST_ACTIVE_SESS_HISTORY a,DBA_HIST_SQLTEXT b   WHERE a.sql_id<>'NULL' AND  a.sql_id=b.sql_id
AND  a.SAMPLE_TIME between  TO_DATE ('2017-01-16 00:20:00','yyyy-mm-dd hh24:mi:ss') and  TO_DATE ('2017-01-16 00:40:00','yyyy-mm-dd hh24:mi:ss')

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20747382/viewspace-2132160/，如需轉載，請註明出處，否則將追究法律責任。

相關文章