关闭

批量生产删除ASM物理文件的脚本

100人阅读 评论(0) 收藏 举报
分类:
分享一个SQL语句删除ASM物理文件的方法,可以不必登录ASM直接操作删除物理文件,批量删除指定的归档日志还是挺方便的,生产环境下手需谨慎

SET ECHO        OFF
SET FEEDBACK    OFF
SET HEADING     OFF
SET LINESIZE    256
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN full_alias_path    FORMAT a255       HEAD 'File Name'
COLUMN disk_group_name    NOPRINT

SELECT
    'ALTER DISKGROUP '  ||
        disk_group_name ||
        ' DROP FILE ''' || CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) || ''';' full_alias_path
FROM
    ( SELECT
          g.name               disk_group_name
        , a.parent_index       pindex
        , a.name               alias_name
        , a.reference_index    rindex
        , f.type               type
      FROM
          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)
                                   JOIN v$asm_diskgroup g USING (group_number)
    )
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
    CONNECT BY PRIOR rindex = pindex
/

SET FEEDBACK    6
SET HEAD        ON
下面是执行结果--- ---
ALTER DISKGROUP DATA DROP FILE '+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.929759073';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/DATAFILE/SYSTEM.256.929823027';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/DATAFILE/SYSAUX.257.929823029';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/DATAFILE/UNDOTBS1.258.929823029';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/DATAFILE/USERS.259.929823029';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/DATAFILE/T1.589.953833675';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/CONTROLFILE/Current.261.929823285';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/CONTROLFILE/Current.260.929823287';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/ONLINELOG/group_1.262.929823293';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/ONLINELOG/group_1.263.929823301';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/ONLINELOG/group_2.264.929823309';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/ONLINELOG/group_2.265.929823317';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/ONLINELOG/group_3.266.929823327';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/ONLINELOG/group_3.267.929823335';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/TEMPFILE/TEMP.268.929823353';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/PARAMETERFILE/spfile.269.929823561';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/ARCHIVELOG/2017_08_04/thread_1_seq_410.645.951170415';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/ARCHIVELOG/2017_08_03/thread_1_seq_408.647.951019227';
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/spfileorcl.ora';

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:771396次
    • 积分:8683
    • 等级:
    • 排名:第2454名
    • 原创:137篇
    • 转载:45篇
    • 译文:9篇
    • 评论:43条
    简介
    此博客为笔者的DT工作记录,欢迎一起交流学习工作中的点滴,若博客文章观点存在原理的错误、纰漏,笔者非常欢迎各位不保留地指出
    最新评论