script for oracle database usage for index used space,table used space and overall size of DB

The script in this document help determine the following information,

•        The overall size of the database
•          The amount of storage consumed by the Application tables in the database
•          The amount of storage consumed by the Application indexes in the database 

Script : script_for_database_usage.sql 

script contents to create script:

=================================

spool '&spoolfilename'

set wrap off;

set linesize 500;

rem ==OVERALL SIZE OF THE DATABASE=====    

    

    SELECT e.Allocated_MB "DB_SIZE_IN_MB",a.data_size + b.temp_size + c.redo_size + d.controlfile_size

   "DB_CAPACITY_IN_MB"

    FROM (SELECT SUM (bytes) /1024/1024 data_size FROM dba_data_files) a,

    (SELECT NVL (SUM (bytes), 0) /1024/1024 temp_size

    FROM dba_temp_files) b,

    (SELECT SUM (bytes)/1024/1024 redo_size FROM sys.v_$log) c,

    (SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) /1024/1024

    controlfile_size

    FROM v$controlfile) d,(select sum(bytes/1024/1024) Allocated_MB from dba_segments)e;

    

rem ==SIZE OF APPLICATION TABLES=====

col NUM_ROWS format 999999;

col Used_MB format 9.99999999;

col segment_name format a20;

select OWNER,TABLE_NAME,tablespace_name,NUM_ROWS,BLOCKS*(bs.block_size_in_bytes)/1024/1024 "Used_MB" from dba_tables,(select distinct bytes/blocks block_size_in_bytes from dba_segments)bs where owner='&Application_DB_User';

rem ==SIZE OF DATABASE INDEXES=====

SELECT idx.TABLE_NAME,idx.index_name,SUM(bytes)/1024/1024 "INDX_USED_IN_MB"
  FROM dba_segments seg,dba_indexes  idx
 WHERE idx.owner=seg.owner AND idx.index_name=seg.segment_name and seg.owner='&Application_DB_USER'
 GROUP BY idx.TABLE_NAME,idx.index_name;

spool off

exit

======================================

Execution Steps:

·         Open sqlplus using command prompt (in Windows) or using Shell terminal (in Linux) and connect to your database.

·         Execute the attached script_for_database_usage.sql file using the following syntax,

SQL> @ /u01/app/oracle/product/11.2.0/dbhome_1/bin/script_for_database_usage.sql

It will prompt for output file,

Entevalueforspoolfilename: /u01/app/oracle/product/11.2.0/dbhome_1/bin/script_for_database_usage.log

Where /u01/app/oracle/product/11.2.0/dbhome_1/bin/script_for_database_usage.sql is the SQL file path and /u01/app/oracle/product/11.2.0/dbhome_1/bin/script_for_database_usage.log is the output file.

As shown in below screenshot,

          

Overall Size of the Database

The first section of the output file contains information on DB size in Megabytes (MB). The total used size of the database is the value under the column header DB_SIZE_IN_MB.

Sample Output:

                                                                                                                                                                                                                                                                                                                                                                           

Size of Application Tables

The second section of the output file contains information on space consumed by all tables within the database where blocks are taken from dba_tables and blocks calculated we run dbms_stats. To find the total space used or by a set of tables you may need to copy the results onto an Excel document and calculate the total sum of the values under column header Used_MB.
Note:  Indba_tables, the "blocks" are calculated when you run dbms_stats, while indba_segments, "blocks" is the actual number of blocks used by the object on disk.

Sample Output:

  

Size of Database Indexes

The third section of the output file indicates the index space used by the database tables in Megabytes (MB). To find the total space used by all the indexes you may need to copy the results onto an Excel document and find the sum of values under the column header INDX_USED_IN_MB.

Sample Output: