57 lines
2.2 KiB
MySQL
57 lines
2.2 KiB
MySQL
-- +----------------------------------------------------------------------------+
|
|
-- | Jeffrey M. Hunter |
|
|
-- | jhunter@idevelopment.info |
|
|
-- | www.idevelopment.info |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | DATABASE : Oracle |
|
|
-- | FILE : asm_drop_files.sql |
|
|
-- | CLASS : Automatic Storage Management |
|
|
-- | PURPOSE : Used to create a SQL script that removes all ASM files |
|
|
-- | contained within all diskgroups. |
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
|
-- | environment before attempting to run it in production. |
|
|
-- +----------------------------------------------------------------------------+
|
|
|
|
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
|