oracle查询归档日志使用量

1.统计最近30天的数据
SELECT TRUNC(first_time, 'DD') "日期", 
       SUM(blocks * block_size) / 1024 / 1024 / 1024 "大小(GB)"
FROM v$archived_log
WHERE first_time >= SYSDATE - 30  -- 统计最近30天的数据
GROUP BY TRUNC(first_time, 'DD')
ORDER BY 1 DESC;
2--通过以下命令检查日志文件大小：
SELECT group#, 
       bytes / 1024 / 1024 "大小(MB)"
FROM v$log;

 

-- 3查询3月24日的高负载会话历史
SELECT sql_id, program, module, action, COUNT(*) AS log_switches
FROM v$active_session_history
WHERE sample_time BETWEEN TO_DATE('2025-03-24', 'YYYY-MM-DD') AND TO_DATE('2025-03-25', 'YYYY-MM-DD')
GROUP BY sql_id, program, module, action
ORDER BY log_switches DESC;

--4查询对应的sql通过sql_id
SELECT sql_fulltext 
FROM v$sql 
WHERE sql_id = 'cbg50043x46k2';


SELECT sql_fulltext 
FROM v$sql 
WHERE sql_id = '1hvn8napwu2mj';

--查询跨库sql
SELECT TEXT 
FROM ALL_SOURCE@ZYMES_JH_LINK 
WHERE NAME = 'AUTO_ENGINE_ENVIROMENTAL' 
  AND TYPE = 'PROCEDURE' 
ORDER BY LINE;