Oracle ASM: Freeing up space

oracleoracle-asmrac

Oracle RAC 11.2 with asm storage, all type = EXTERN redundancy.

I've been cleaning up some tablespaces and shrinking datafiles back down to sizes that they're not likely to grow much larger than (processes no longer used had blown them up).

The sum of the bytes of datafiles from dba_data_files reflects this shrink, however, USED_MB in v$asm_diskgroup has not fallen and it doesn't match to what adding up the datafiles bytes is?

What would account for this difference? We typically use v$asm_diskgroup to monitor if we're running low on space but this doesn't appear to be correct.

  SELECT name group_name ,
  type type ,
  total_mb total_mb ,
  (total_mb - free_mb) used_mb ,
  ROUND(
  (SELECT SUM(bytes)/1024/1024
  FROM dba_data_files
  WHERE file_name LIKE '+DATA02%'
  ),0) AS datafile_used
FROM v$asm_diskgroup w
WHERE name = 'DATA02'


GROUP_NAME  TYPE  TOTAL_MB  USED_MB  DATAFILE_USED
______________________________________
DATA02  EXTERN  542712  516902  506447

Best Answer

dba_data_files does not include:

  • tempfiles
  • controlfiles
  • spfiles
  • redo logs
  • archived logs
  • backups/copies
  • ADVM volumes (ACFS)
  • OCR
  • reserved space for ASM metadata

Include v$asm_alias and v$asm_file in your queries, or just simply list the files in the diskgroup (asmcmd, ls).

Related Question