两键清理垃圾redo-凯发app官方网站

set lin 200
col name for a60
col member for a50
set pages 200
select name from v$controlfile union all
select name from v$dbfile union all
select member from v$logfile;
set serverout on
declare
-- 1 声明游标
cursor cur_redo1 is
select distinct a.group# from v$logfile a where a.type='standby';
v_log_info cur_redo1%rowtype;
v1 varchar2(100);
begin
-- 2 开启游标
open cur_redo1;
--dbms_output.put_line('--------------');
loop
-- 3 获取数据（一次获取一行）
fetch cur_redo1
into v_log_info;
exit when cur_redo1%notfound;
for x in ( select member from v$logfile where group#=v_log_info.group#) loop
dbms_output.put_line('rm -f '||x.member);
end loop;
execute immediate 'alter database drop standby logfile group '||v_log_info.group#;
end loop;
-- 4 关闭游标
close cur_redo1;
end;
/
set serverout on
declare
-- 1 声明游标
cursor cur_redo is
-- set lin 200
-- col member for a50
select a.group#,a.status, a.thread# from v$log a where a.status not in ('active','current') order by group# desc;
v_log_info cur_redo%rowtype;
begin
-- 2 开启游标
open cur_redo;
dbms_output.put_line('--');
loop
-- 3 获取数据（一次获取一行）
fetch cur_redo
into v_log_info;
exit when cur_redo%notfound;
for x in ( select member from v$logfile where group#=v_log_info.group#) loop
dbms_output.put_line('rm -f '||x.member);
end loop;
--dbms_output.put_line(v_log_info.group# || ' : rm -f ' || v_log_info.member);
execute immediate 'alter database drop logfile group '||v_log_info.group#;
end loop;
-- 4 关闭游标
close cur_redo;
end;
/
select 'alter tablespace '||t.name||' drop tempfile '''||c.name||''';'
from v$tempfile c, v$tablespace t where c.ts#=t.ts# and c.name not like '%t11_%';
select 'rm -f '||substr(name,1,instr(name,'/',-1) )||'archi*/*.dbf' c from v$dbfile where rownum<2;
select 'create pfile='''||substr(name,1,instr(name,'/',-1) )||'init'||(select instance_name from v$instance)||'.ora'' from spfile;' c from v$dbfile where rownum<2;

舒爽了。

阅读(786) | 评论(0) | 转发(0) |

下一篇：禁止数据库随crs启动

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们