Oracle 歸檔使用情況分析

--------------------------------------------------------------------------------------------

select * from v$flash_recovery_area_usage; --檢視使用率，有時候統計資訊延遲。

select name, space_limit, space_used, space_used/space_limit * 100 from v$recovery_file_dest; --自己計算也可以。

select name, space_limit, space_used from v$recovery_file_dest;

select name, space_limit, space_used, space_used/space_limit * 100 from v$recovery_file_dest;

{#SPACE_USED}/{#SPACE_LIMIT}*100

--------------------------------------------------------------------------------------------

rm -fr 後需要手動校驗刪除失效的歸檔

crosscheck archivelog all;

delete expired archivelog all; --刪除失效的歸檔

--------------------------------------------------------------------------------------------

delete noprompt archivelog all; --刪除所有歸檔

delete archivelog all completed before 'SYSDATE - 3'; 刪除指定日期的歸檔

--------------------------------------------------------------------------------------------

每天日誌量大小：

SELECT TRUNC(FIRST_TIME) "TIME",

SUM(BLOCK_SIZE * BLOCKS) / 1024 / 1024 / 1024 "SIZE(GB)"

FROM V$ARCHIVED_LOG

GROUP BY TRUNC(FIRST_TIME) order by TRUNC(FIRST_TIME);

alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

select trunc(completion_time) as arch_date,

count(*) as count,

round((sum(blocks*block_size)/1024/1024/1024),2) as ARC_GB

from v$archived_log

group by trunc(completion_time)

order by trunc(completion_time);

--------------------------------------------------------------------------------------------

最近20h內生成日誌量最大的前幾個物件

select * from (

SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,

dhsso.object_name,

SUM(db_block_changes_delta)

FROM dba_hist_seg_stat dhss,

dba_hist_seg_stat_obj dhsso,

dba_hist_snapshot dhs

WHERE dhs.snap_id = dhss.snap_id

AND dhs.instance_number = dhss.instance_number

AND dhss.obj# = dhsso.obj#

AND dhss.dataobj# = dhsso.dataobj#

AND begin_interval_time> sysdate - 1200/1440

GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),

dhsso.object_name

order by 3 desc)

where rownum<=5;

找出與具體物件相關的sql：

SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),

dbms_lob.substr(sql_text, 10, 1), --可根據需要擷取1000或者3000

dhss.instance_number,

dhss.sql_id,

executions_delta,

rows_processed_delta

FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst

WHERE UPPER(dhst.sql_text) LIKE '%RPT_ODST_LOTHR_HST%'

AND dhss.snap_id = dhs.snap_id

AND dhss.instance_Number = dhs.instance_number

AND dhss.sql_id = dhst.sql_id;

找出與具體物件相關的當前執行的sql：

select a.username,a.sid,b.sql_text from v$session a,v$sql b

where a.sql_id=b.sql_id and b.sql_text like '%RPT_ODST_LOTHR_HST%';

查詢關聯SQL的session和module和machine

select username,program,module,machine from dba_hist_active_sess_history WHERE sql_id = '51vfyuqucx6tm';

select program,module,machine from dba_hist_active_sess_history WHERE sql_id = '51vfyuqucx6tm';

從下面找出也是可以的

select * from dba_hist_active_sess_history WHERE sql_id = 'sql_id號';

select * from v$active_session_history where sql_Id = 'sql_id號';

--------------------------------------------------------------------------------------------

也可以透過日誌挖掘，找到具體的物件和sql