SQL> set pages 10
SQL> set line 200
SQL> select 'alter diskgroup '||gname|| ' drop file ||
full_alias_path ||'||' ;' as "drop sql"
from
(select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
gname
from (select g.name gname, a.parent_index pindex,a.name aname,
a.reference_index rindex from v$asm_alias a, v$asm_diskgroup g
where a.group_number = g.group_number
order by 1)
start with (mod(pindex, power(2, 24))) = 0
connect by prior rindex = pindex)
where full_alias_path like '%ARCHIVELOG%'
and full_alias_path like '+%/%/%/%/%';
ALTER DISKGROUP DG1 DROP FILE '+DG1/arch/1_2222.arc'; ・・・・・ASM場からアーカイブログを削除するコマンド
|