Fake space consumption by ASM database

Almost all of the 10g and above databases usually use ASM. One of the most common requests of development systems is to add another lun to add more space to ASM. This is one of the great features of ASM, adding or removing disk without affecting the current databases. Before adding a lun, however, I always check to see if space is really needed. Sometimes, there will be a database which has not been backed up in weeks and it will have literally thousands of archive logs that are filling up ASM. That remedy is easy, either back up the database and archives or delete the archive logs and run a new full backup.

Then there are the vampire databases, those that suck up disk space, but are not active and not being used. Often, they are just forgotten databases by developers or DBAs that are shut down, removed from oratab and backups and generally forgotten about since no one notices the space they take up in ASM. In dealing with space issues, I needed a quick way to determine what databases were in ASM, whether or not they were actually running. Here is what I came up with:

REM asm_db_size.sql
REM Author - Jay Caviness - Grumpy-dba.com
REM 5 March 2009
REM ------------------------------------------------------------
set pages 999
set heading on
set feedback off
set lines 80
col "Size in MB" for 999,999,999
col "Database" for a25

select database_name "Database",
         sum(space)/1024/1024 "Size in MB" FROM (
  SELECT
      CONNECT_BY_ROOT db_name as database_name, space
  FROM
      ( SELECT
            a.parent_index       pindex
          , a.name               db_name
          , a.reference_index    rindex
          , f.bytes              bytes
          , f.space              space
          , f.type               type
        FROM
            v$asm_file f RIGHT OUTER JOIN v$asm_alias a
                         USING (group_number, file_number)
      )
  WHERE type IS NOT NULL
  START WITH (MOD(pindex, POWER(2, 24))) = 0
      CONNECT BY PRIOR rindex = pindex)
group by database_name
order by database_name
/

Which, when run gives the output:

Database                    Size in MB
------------------------- ------------
DB_UNKNOWN                          10
QA                              29,667
QACA20A                          9,011
QARA20A                         13,258
QCONFIG                         17,653
QHC1011                          8,874
QHR1011                         12,068
QICA11A                         17,247
QIRA11A                         27,041
TESTC02                          8,908
TESTR02                         11,791

In this case I know that TESTC02 and TESTR02 are not running and upon checking with the owners of this system, received permission to drive a stake through the heart of. Cheers !remove these databases saving 20G of space.