ASM, Files - TBS|DTFs|DBFiles

Shared Diskgroup for Several Databases: How much each Database is really using?

“Holy space usage, DBAtman!”

OK, So now you have a lot of databases on same DG, and don’t know how much of DG is being used by each database?

Here is something that may help you:

@asm_usage.sql

-- aka asm-db.sql
set pages 350 timing on
col gname form a10
col dbname form a10
col file_type form a16
break on gname skip 2 on dbname skip 1
compute sum label total_db of gb on dbname
compute sum label total_diskg of gb on gname  

SELECT
    gname,
    dbname,
    file_type,
    round(SUM(space)/1024/1024) mb,
    round(SUM(space)/1024/1024/1024) gb,
    COUNT(*) "#FILES"
FROM
    (
        SELECT
            gname,
            regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
            file_type,
            space,
            aname,
            system_created,
            alias_directory
        FROM
            (
                SELECT
                    concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                    system_created,
                    alias_directory,
                    file_type,
                    space,
                    level,
                    gname,
                    aname
                FROM
                    (
                        SELECT
                            b.name            gname,
                            a.parent_index    pindex,
                            a.name            aname,
                            a.reference_index rindex ,
                            a.system_created,
                            a.alias_directory,
                            c.type file_type,
                            c.space
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b,
                            v$asm_file c
                        WHERE
                            a.group_number = b.group_number
                        AND a.group_number = c.group_number(+)
                        AND a.file_number = c.file_number(+)
                        AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                AND rindex IN
                    (
                        SELECT
                            a.reference_index
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b
                        WHERE
                            a.group_number = b.group_number
                        AND (
                                mod(a.parent_index, power(2, 24))) = 0
                    ) CONNECT BY prior rindex = pindex )
        WHERE
            NOT file_type IS NULL
            and system_created = 'Y' )
GROUP BY
    gname,
    dbname,
    file_type
ORDER BY
    gname,
    dbname,
    file_type
/

And example of the output:

SQL> @asm_usage.sql

GNAME	   DBNAME     FILE_TYPE 	       MB	  GB	 #FILES
---------- ---------- ---------------- ---------- ---------- ----------
DATA_EXE1  ASM	      PARAMETERFILE		8	   0	      1
	   **********				  ----------
	   total_db					   0

	   DBA	      CHANGETRACKING	       24	   0	      1
		      CONTROLFILE	      600	   1	      2
		      DATAFILE		   198300	 194	      8
		      ONLINELOG 	     8352	   8	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		     5452	   5	      1
	   **********				  ----------
	   total_db					 208

	   DBB	      CHANGETRACKING	       24	   0	      1
		      CONTROLFILE	      984	   1	      2
		      ONLINELOG 	     8352	   8	      8
		      PARAMETERFILE		8	   0	      1
	   **********				  ----------
	   total_db					   9

	   DBC       CHANGETRACKING	       24	   0	      1
		      CONTROLFILE	       96	   0	      1
		      DATAFILE		    26564	  26	      9
		      ONLINELOG 	    32928	  32	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		       72	   0	      1
	   **********				  ----------
	   total_db					  58

	   DBD       CHANGETRACKING	       24	   0	      1
		      CONTROLFILE	      192	   0	      1
		      DATAFILE		    12800	  13	      9
		      ONLINELOG 	    32928	  32	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		       72	   0	      1
	   **********				  ----------
	   total_db					  45

	   DBE	      CHANGETRACKING	       24	   0	      1
		      CONTROLFILE	      492	   0	      1
		      DATAFILE		   133220	 130	      5
		      ONLINELOG 	    65696	  64	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		    65548	  64	      1
	   **********				  ----------
	   total_db					 258

	   DB_UNKNOWN PARAMETERFILE	       24	   0	      3
	   **********				  ----------
	   total_db					   0

	   DBX_EX     CHANGETRACKING	      104	   0	      1
		      CONTROLFILE	      192	   0	      1
		      ONLINELOG 	    32928	  32	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		    65548	  64	      1
	   **********				  ----------
	   total_db					  96

	   DWMSP      CHANGETRACKING	       88	   0	      1
		      CONTROLFILE	      192	   0	      1
		      DATAFILE		   931752	 910	     12
		      DATAGUARDCONFIG	       16	   0	      2
		      ONLINELOG 	    65856	  64	     16
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		   131080	 128	      2
	   **********				  ----------
	   total_db					1102

	   DWMSQ      CHANGETRACKING	      128	   0	      1
		      CONTROLFILE	      192	   0	      2
		      ONLINELOG 	    32928	  32	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		    65548	  64	      1
	   **********				  ----------
	   total_db					  96

	   DBAB      CHANGETRACKING	       24	   0	      1
		      CONTROLFILE	       96	   0	      1
		      ONLINELOG 	    32928	  32	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		       72	   0	      1
	   **********				  ----------
	   total_db					  32

	   DBXA      CHANGETRACKING	      540	   1	      1
		      CONTROLFILE	      192	   0	      2
		      DATAFILE		  1229068	1200	      1
		      ONLINELOG 	    32928	  32	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		   131080	 128	      2
	   **********				  ----------
	   total_db					1361

	   EXMP       CHANGETRACKING	      104	   0	      1
		      CONTROLFILE	       96	   0	      1
		      DATAFILE		  1123928	1098	      9
		      ONLINELOG 	    32928	  32	      8
		      PARAMETERFILE		8	   0	      1
		      TEMPFILE		    20500	  20	      1
	   **********				  ----------
	   total_db					1150
[....]

**********					  ----------
total_disk					       41362

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s