-- Clients
select INSTANCE_NAME, DB_NAME, STATUS from v$asm_client order by DB_NAME,INSTANCE_NAME;
ASM Diskgroups & Space, diskgroup size
col pct_used for a9
set line 200
select name, group_number,state, round((total_mb-free_mb)/total_mb*100)||'%' pct_used,
total_mb/1024 total_gb,round(free_mb/1024) free_gb
from V$ASM_DISKGROUP;
ASM Archivelog Usage by DB
col path for a40
set line 200
SELECT substr(full_path,1,instr(full_path,'/',1,2)) path,
round(sum(f.bytes)/1024/1024) sum_MB,
count(*) qty
FROM (SELECT CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,
group_number, file_number
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.group_number, a.file_number
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) sub,
v$asm_file f
WHERE sub.group_number = f.group_number
AND sub.file_number = f.file_number
AND f.type = 'ARCHIVELOG'
GROUP BY substr(full_path,1,instr(full_path,'/',1,2))
ORDER BY 2;
Disk Usage by file/object name
select substr(al.name, 0, instr(al.name, '.')-1) Friendly_name, round(sum(a.bytes)/1024/1024, 2)
from v$asm_file a, v$asm_alias al
where al.file_number = a.file_number and a.group_number = 2
group by substr(al.name, 0, instr(al.name, '.')-1);
If a db has a restore point and you want to turn off flash back, it was slightly tricky on 10g.
After few tries found that i had to drop the restore point first.
select flashback_on from v$database;
select name, scn, time, guarantee_flashback_database from v$restore_point;
DROP RESTORE POINT FRI_1120_TEST_RW;
alter database flashback off;
Drop files.
$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysdba"
SQL> ALTER DISKGROUP TESTDB_DATA2 DROP FILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837';
