Oracle 数据清理

数据清理

删除用户

删除用户前需要先删除schema下的对象，否则会出现如下报错

SQL> drop user test01;
drop user test01
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'TEST01'

删除schema下的对象

拼接删除指定schema下对象的SQL

select owner,
       object_name,
       object_type,
       case when object_type = 'TABLE' then 'drop TABLE ' || owner || '.' || object_name || ' CASCADE CONSTRAINTS;'
            when object_type = 'SYNONYM' then 'drop SYNONYM ' || OWNER || '.' || object_name || ' ;'
            when object_type = 'VIEW' then 'drop VIEW ' || owner || '.' || object_name || ' ;'
            when object_type = 'MATERIALIZED VIEW' then 'drop MATERIALIZED VIEW ' || owner || '.' || object_name || ' ;'
            when object_type = 'SEQUENCE' then 'drop SEQUENCE ' || OWNER || '.' || object_name || ' ;'
            when object_type = 'FUNCTION' then 'drop FUNCTION ' || OWNER || '.' || object_name || ';'
            when object_type = 'PROCEDURE' then 'drop PROCEDURE ' || OWNER || '.' || object_name || ';'
            when object_type = 'PACKAGE' then 'drop PACKAGE ' || OWNER || '.' || object_name || ';'
            when object_type = 'TRIGGER' then 'DROP TRIGGER ' || OWNER || '.' || object_name || ';'
            when object_type = 'TYPE' then 'DROP TYPE ' || OWNER || '.' || object_name || ';' end script
from dba_objects 
where owner in ('HR') 
and object_type in 
('TABLE','SYNONYM','VIEW','MATERIALIZED VIEW','SEQUENCE','FUNCTION','PROCEDURE','PACKAGE','TYPE','TRIGGER');

删除表空间

-- 删除空的表空间，但是不包含物理文件
drop tablespace ts01;
-- 删除非空表空间，但是不包含物理文件
drop tablespace ts01 INCLUDING CONTENTS;
-- 删除空表空间，包含物理文件（会从文件系统中删除数据文件）
drop tablespace ts01 including datafiles;
-- 删除非空表空间，包含物理文件（会从文件系统中删除数据文件）
drop tablespace ts01 including contents and datafiles;
-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上 CASCADE CONSTRAINTS
drop tablespace ts01 including contents and datafiles CASCADE CONSTRAINTS;

删除redo日志

删除日志组成员

-- 删除日志文件组的成员的时候该组的状态不能是current
alter database drop logfile member '/oradata/tmp/redo011.log';
-- 如果该组状态是current，多次手动切换日志，直到非current状态
alter system switch logfile;

删除日志组

删除日志文件组的前提条件是该组只有一个成员

alter database drop logfile group 1;

注意事项:

• Oracle规定一个实例里面至少有两组日志文件组
• 不能删除组内的唯一个成员，可以直接删除整个日志组
• 不能删除处于active和current状态组内的成员
• 删除处于active和current状态组内的成员,应使用日志切换使其处于INACTIVE状态后再删除
• 对于组内如果一个成员为NULL值,一个为INVALID,且组处入INACTIVE ,仅能删除INVALID状态
• 删除日志成员,物理文件并没有真正删除,需要手动删除，如果是ASM管理的话会自动删除。
• 删除日志文件后,控制文件被更新
• 对于处于归档模式下的数据库,删除成员时确保日志已被归档,查看v$loq视图获得归档信息

删除归档日志

统计归档信息

-- 按天统计过去7天每天生成的归档数量
select to_char(COMPLETION_TIME, 'yyyymmdd'), count(*)
  from v$archived_log t
 where COMPLETION_TIME > sysdate - 7
 group by to_char(COMPLETION_TIME, 'yyyymmdd')
 order by to_char(COMPLETION_TIME, 'yyyymmdd');
 
 
-- 按小时统计当日生成的归档数量
select to_char(FIRST_TIME, 'yyyymmddhh24'), count(*)
  from sys.v_$archived_log t
 where t.FIRST_TIME > trunc(sysdate)
 group by to_char(FIRST_TIME, 'yyyymmddhh24')
 order by to_char(FIRST_TIME, 'yyyymmddhh24');


-- 按天和小时综合统计：用于统计每天每个小时的日志数量，并按照日期倒序排序
SELECT    TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999') "00",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999') "01",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999') "02",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999') "03",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999') "04",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999') "05",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999') "06",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999') "07",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999') "08",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999') "09",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999') "10",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999') "11",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999') "12",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999') "13",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999') "14",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999') "15",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999') "16",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999') "17",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999') "18",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999') "19",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999') "20",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999') "21",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999') "22",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999') "23"
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD') 
ORDER BY 1 DESC;

删除归档日志

-- 列出所有归档信息
RMAN> list archivelog all;
-- 检查归档信息
RMAN> crosscheck archivelog all;
-- 列出过期的归档日志
RMAN> list expired archivelog all; 
-- 删除过期的归档日志
RMAN> delete expired archivelog all;

-- 删除指定日期归档日志
RMAN> delete archivelog all completed before 'sysdate - n';
-- 删除三天以前的日志
RMAN> delete archivelog all completed before 'sysdate - 3';
-- 删除一小时以前的日志
RMAN> delete archivelog all completed before 'sysdate-1/24';

RMAN> delete archivelog until time 'sysdate - 7';
RMAN> delete archivelog until time "to_date('2023-10-04 23:43:44','yyyy-mm-dd hh24:mi:ss')";
RMAN> delete archivelog until sequence 16;
RMAN> list copy of database archivelog all;

-- 删除所有的归档日志
RMAN> delete archivelog all;

规范清理v$archived_log记录

使用rman删除的归档日志后v$archived_log里面还会有记录，只是DELETED字段标识为YES

要想删除v$archived_log里面的日志记录使用以下存储过程

-- 1、清空v$archived_log
execute sys.dbms_backup_restore.resetCfileSection(11);

-- 2、根据条件清理
DECLARE
  retention_period NUMBER := 7; -- 保留天数
BEGIN
  DELETE FROM V$ARCHIVED_LOG
  WHERE COMPLETION_TIME < SYSDATE - retention_period;
  COMMIT;
END;
/

定时删除脚本

#!/bin/bash
export ORACLE_HOME=/oracle/product/11.2.0/dbhome_1

$ORACLE_HOME/bin/rman target / nocatalog log /oracle/del_arch_log.log <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-7';
crosscheck archivelog all;
exit;
EOF