鲁达 问题：最近几周经常手动删除归档日志，归档日志报空间不足（预留800G空间已用完），基于此查询归档日志情况（近期业务有所上涨）。
-----------------------------此为回溯操作流程，截图非实际环境-----------------------
1、查看归档日志大小---实际查到14日单日归档高于200G，
select aa.ArchiveDate, aa.ArchiveSize from (select trunc(first_time) as ArchiveDate, sum(block_size * blocks) / 1024 / 1024 / 1024 as ArchiveSize from v$archived_log group by trunc(first_time)) aa order by aa.ArchiveDate desc;2、查看最近几天，每小时归档日志产生数量----显示14日早8点到10点日志切换较多
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, COUNT(*) TOTAL FROM v$log_history a WHERE first_time>=to_char(sysdate-10) GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;3、查询此时间段的归档日志名称等情况---实际查到+LOG/tzcdb/archivelog/2022_06_15；+LOG/tzcdb/archivelog/2022_06_15；
select resetlogs_time, FIRST_TIME, NEXT_TIME, BLOCKS / 1024 / 1024 mbblock, block_size, block_size * blocks / 1024 / 1024 sjsizemb, status, COMPLETION_TIME from v$archived_log where first_time < to_date('2022-06-16 12:00:00', 'yyyy-mm-dd hh24:mi:ss') and first_time > to_date('2022-06-12 16:05:00', 'yyyy-mm-dd hh24:mi:ss') order by 14、找到对应相关的日志后，就开始进入日志挖掘截断了
Logminer 基本使用步骤 1、 Specify a LogMiner dictionary. 指定Logminer字典 2、 Specify a list of redo log files for analysis. 指定需要挖掘的redo或者archivelog日志文件 3、 Start LogMiner. 开始日志挖掘 4、 Request the redo data of interest. 查询V$LOGMNR_CONTENTS获取挖掘的结果 5、End the LogMiner session. 结束日志挖掘4-1.开启补全日志：（一定要开启，否则后面执行会报错）
#sqlplus / as sysdba SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;4-2.找出需要挖掘的归档日志文件路径(第3步已找到)
4-3.使用此项指定Logminer字典（不用重启数据库）---.Extracting a LogMiner Dictionary to the Redo Log Files 抽取字典到redo日志文件中
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);4-4.Logminer中添加归档日志文件（注意添加只能先添加一个）
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+LOG/tzcdb/archivelog/2022_06_15', OPTIONS => DBMS_LOGMNR.NEW);4-5.继续添加日志：（注意此处的OPTIONS=> DBMS_LOGMNR.ADDFILE）
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+LOG/tzcdb/archivelog/2022_06_15', OPTIONS => DBMS_LOGMNR.ADDFILE);4-6.开始日志挖掘
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS );如果执行不成功，可以手动执行次存储过程
begin (); end; /4-7.查看挖掘效果，并把挖取结果保存到指定表格中：
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE'); create table logtab as select * from v$logmnr_contents;4-8.关闭日志挖掘
begin (); end; /5、查询挖掘出的数据结果（基于数据量较大，不建议直接查询，可仅查询前10行，再收集需要的记录）
select * from logtab where rownum<11; SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('PDBZ, 'JW');-------------------------------------------------------------------------------------------------------------------------关于挖掘出来的日志如何查看，下一次更新续上！