Oracle ASM SQL scripts

Oracle ASM SQL scripts

The following scripts are outlined to assist the use in managing ASM files; i.e., listing or deleting.

Note:  Oracle recommends using 10g EM Grid Control or Database Control for performing these functions.

1. This SQL statement generates a script to list the ASM files for a given database.
COL full_path FORMAT a120
COL dir FORMAT a3
COL sys FORMAT a3
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 39
SET LINESIZE 130
–get database name
ACCEPT database PROMPT ‘Enter the database name: ‘
–spool file
spool lis_&database..lst
–generates list
SELECT full_path, dir, sys FROM
(SELECT CONCAT(‘+’||gname, SYS_CONNECT_BY_PATH(aname,’/’)) full_path, dir,
sys FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir, a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc)
WHERE full_path LIKE UPPER(‘%/&database%’);
–end spool
spool off
— resetting format
COL full_path CLEAR
COL dir CLEAR
COL sys CLEAR
SET VERIFY ON
SET HEADING ON
SET FEEDBACK ON

 

2.  This SQL statement will generate a script that will delete files in ASM for a given database.
COL gsql FORMAT a300
SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 600
SET TRIMSPOOL ON
–get database name
ACCEPT database PROMPT ‘Enter the database name: ‘
–spool file
spool del_&database..sql
–generates file list
SELECT ‘ALTER DISKGROUP ‘||gname||’ DROP FILE ”’||full_path||”’;’ gsql
FROM
(SELECT CONCAT(‘+’||gname, SYS_CONNECT_BY_PATH(aname,’/’)) full_path, gname
FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY adir
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
WHERE adir=’N’
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
WHERE full_path LIKE UPPER(‘%/&database/%’);

 

3. This SQL statement will generate a script to drop ASM directories.
SELECT ‘ALTER DISKGROUP ‘||gname||’ DROP DIRECTORY ”’||full_path||”’;’
gsql FROM
(SELECT CONCAT(‘+’||gname, SYS_CONNECT_BY_PATH(aname,’/’)) full_path, gname
FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY adir
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
WHERE adir=’Y’
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY full_path desc)
WHERE full_path LIKE UPPER(‘%/&&database%’);
–end spool
spool off
— resetting format
COL gsql CLEAR
SET VERIFY ON
SET HEADING ON
SET FEEDBACK ON
SET PAGESIZE 30
SET LINESIZE 600
SET TRIMSPOOL OFF

Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."

Latest posts by Nagulu Polagani (see all)