expertDBA.com - A Database Administration Blog » 2009

February 2009
M	T	W	T	F	S	S
		Jul »
	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

Here is the source code with steps how to configure:

1) First create the table as below:

CREATE TABLE USER1.DBGROWTH (DATABASE_NAME VARCHAR2(10),
TOTAL_BYTES NUMBER, COLLECTION_DATE DATE);

ALTER TABLE USER1.DBGROWTH MODIFY (COLLECTION_DATE DEFAULT to_char(sysdate,'DD-MON-YY'));

CREATE UNIQUE INDEX USER1.DB_GROWTH_P ON DBGROWTH (DATABASE_NAME,COLLECTION_DATE);

ALTER TABLE USER1.DBGROWTH ADD CONSTRAINT USER1.DB_GROWTH_P PRIMARY KEY (DATABASE_NAME, COLLECTION_DATE) USING INDEX

2) Collect the daily growth of the database using below script.

ORACLE_SID=TEST
ORACLE_HOME=/opt/oracle
export ORACLE_HOME
export ORACLE_SID
echo "Run date:" `date`
SID="PROD1,PROD2" ||->> It will treat as alias name also so for you need to configure the tnsnames to access the production databases.
for i in `echo $SID|tr -s ',' ' '`
do
$ORACLE_HOME/bin/sqlplus -s USER1/USER1< ${ReportFile}
> ${LogFile}
> ${htmlFile}

${ORACLE_HOME}/bin/sqlplus -s "USER1/USER1" < /dev/null
set heading off verify off echo off feedback off linesize 120 pagesize 1000
spool ${LogFile}
select substr(a1.database_name,1,15) || '|' ||
to_char(a1.collection_date,'mm/dd/yyyy') || '|' || round(a1.DB_Size_MB,0) || '|' ||
to_char(a2.collection_date,'mm/dd/yyyy') || '|' || round(a2.DB_Size_MB,0) || '|' ||
(a2.collection_date - a1.collection_date + 1) || '|' ||
round((a2.DB_Size_MB - a1.DB_Size_MB),0) || '|' ||
greatest(round(100*(a2.DB_Size_MB - a1.DB_Size_MB)/a1.DB_Size_MB,2),0) || '|' ||
round(greatest(30 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0) || '|' ||
round(greatest(90 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0) || '|' ||
round(greatest(180 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0)
from
(select database_name, total_bytes/1024/1024 DB_Size_MB, collection_date
from USER1.dbgrowth a
where to_char(collection_date,'mm/dd/yyyy') = (select to_char(min(collection_date),'mm/dd/yyyy')
from USER1.dbgrowth b
where b.database_name = a.database_name
and b.collection_date between (sysdate - 10) and sysdate)) a1,
(select database_name, total_bytes/1024/1024 DB_Size_MB, collection_date
from USER1.dbgrowth a
where to_char(collection_date,'mm/dd/yyyy') = (select to_char(max(collection_date),'mm/dd/yyyy')
from USER1.dbgrowth b
where b.database_name = a.database_name
and b.collection_date <= sysdate)) a2
where a1.database_name = a2.database_name;
spool off
EndSQL
echo "" > ${ReportFile}
echo "" >> ${ReportFile}
echo "" >> ${ReportFile}
echo '' >> ${ReportFile}
echo '' >> ${ReportFile}
echo "Report run date: `date`" >> ${ReportFile}
echo "" >> ${ReportFile}
echo '
' >> ${ReportFile}
echo '
' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

c3tot=0
c5tot=0
c7tot=0
c9tot=0
c10tot=0
c11tot=0

for rows in `cat ${LogFile}`
do
c1=`echo ${rows} | cut -f1 -d"|"`
c2=`echo ${rows} | cut -f2 -d"|"`
c3=`echo ${rows} | cut -f3 -d"|"`
c4=`echo ${rows} | cut -f4 -d"|"`
c5=`echo ${rows} | cut -f5 -d"|"`
c6=`echo ${rows} | cut -f6 -d"|"`
c7=`echo ${rows} | cut -f7 -d"|"`
c8=`echo ${rows} | cut -f8 -d"|"`
c9=`echo ${rows} | cut -f9 -d"|"`
c10=`echo ${rows} | cut -f10 -d"|"`
c11=`echo ${rows} | cut -f11 -d"|"`

c3tot=$(( c3tot + c3 ))
c5tot=$(( c5tot + c5 ))
c7tot=$(( c7tot + c7 ))
c9tot=$(( c9tot + c9 ))
c10tot=$(( c10tot + c10 ))
c11tot=$(( c11tot + c11 ))

echo "
" >> ${ReportFile}echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}
done

echo "
" >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}

c3tot=$(( c3tot / 1024 ))
c5tot=$(( c5tot / 1024 ))
c7tot=$(( c7tot / 1024 ))
c9tot=$(( c9tot / 1024 ))
c10tot=$(( c10tot / 1024 ))
c11tot=$(( c11tot / 1024 ))

echo "
" >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}

echo "
Database First Date DB Size (MB)on First Date Second Date DB Size (MB)on Second Date TotalDays DB Growth(MB) PercentGrowth Projected Growth inNext 30 days (MB) Projected Growth inNext 90 days (MB) Projected Growth inNext 180 days (MB)
' >> ${ReportFile} echo " ${c1}" >> ${ReportFile} echo ' ' >> ${ReportFile} echo ${c2} >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c3}  &nbsp" >> ${ReportFile} echo ' ' >> ${ReportFile} echo ${c4} >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c5}  &nbsp" >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c6}  &nbsp" >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c7}  &nbsp" >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c8} %  " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c9}       " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c10}       " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c11}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "  TOTAL (MB)" >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c3tot}   " >> ${ReportFile} echo ' ' >> ${ReportFile} echo " " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c5tot}   " >> ${ReportFile} echo ' ' >> ${ReportFile} echo " " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c7tot}   " >> ${ReportFile} echo ' ' >> ${ReportFile} echo " " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c9tot}       " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c10tot}       " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c11tot}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "  TOTAL (GB)" >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c3tot}   " >> ${ReportFile} echo ' ' >> ${ReportFile} echo " " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c5tot}   " >> ${ReportFile} echo ' ' >> ${ReportFile} echo " " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c7tot}   " >> ${ReportFile} echo ' ' >> ${ReportFile} echo " " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c9tot}       " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c10tot}       " >> ${ReportFile} echo ' ' >> ${ReportFile} echo "${c11tot}       " >> ${ReportFile} echo '


" >> ${ReportFile}
echo "

" >> ${ReportFile}
echo "" >> ${ReportFile}
echo "" >> ${ReportFile}

#cat ${ReportFile}
uuencode ${ReportFile} ${htmlFile} | mailx -s "Database Growth Projection Report" ${MailList}

rm -rf ${TempDir}
rm -rf ${htmlFile}

exit 0

4) Schedule it in crontab as per requirement to collect the data and generate the report.

#DV db growth report script - Anuj
30 23 * * * /home/oracle/scripts/check_ts_growth.ksh >> /home/oracle/scripts/check_ts_growth.log 2>&1
00 02 * * * /home/oracle/scripts/dbgrowth.ksh > /home/oracle/scripts/dbgrowth.log 2>&1

I will be glad to see your blogs on this as well if any problem during configuring just let me know the error and problem by blog.

DBA_TABLESPACE_USAGE_METRICS
The DBA_TABLESPACE_USAGE_METRICS view is not really documented, but it’s a handy little view.
Here’s a quick description of it.

Let’s do a query that shows which tablespaces are 80% filled up:

SQL> select * from dba_tablespace_usage_metrics
2 where used_percent >= 80 order by used_percent desc;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
—————————— ———- ————— ————
ANUJTS1 38140 38400 99.3229167
ANUJTS2 2248 2500 89.92

The most important columns are TABLESPACE_NAME and USED_PERCENT.

This easy space usage query brings together PERMANENT, TEMPORARY, and UNDO tablespace information into one simple view.

The columns of DBA_TABLESPACE_USAGE_METRICS are:

Column Name Unit Description

TABLESPACE_NAME String Name of the PERMANENT, TEMPORARY, or UNDO tablespace
USED_SPACE Blocks Used space, in blocks
TABLESPACE_SIZE Blocks Total data file space, in blocks
USED_PERCENT Percentage USED_SPACE / TABLESPACE_SIZE * 100

If the tablespace contains autoextensible data files, then TABLESPACE_SIZE will show:

•For Bigfile tablespaces: 4 GB number of blocks (4294967293)

•For ordinary tablespaces: Sum of the number of blocks in each data file, with each autoextensible data file contributing ~4 MB number of blocks (4194302)
You can join against DBA_TABLESPACES and do USED_SPACE * BLOCK_SIZE and TABLESPACE_SIZE * BLOCK_SIZE to get the absolute space amounts in bytes.

You should have patch levels 10.1.0.5 or 10.2.0.2 installed in order to avoid some bugs with DBA_TABLESPACE_USAGE_METRICS.

Keep in mind that this view is undocumented, and it could change without notice in a future release.

Database	First Date	DB Size (MB)on First Date	Second Date	DB Size (MB)on Second Date	TotalDays	DB Growth(MB)	PercentGrowth	Projected Growth inNext 30 days (MB)	Projected Growth inNext 90 days (MB)	Projected Growth inNext 180 days (MB)
' >> ${ReportFile} echo " ${c1}" >> ${ReportFile} echo '	' >> ${ReportFile} echo ${c2} >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c3} &nbsp" >> ${ReportFile} echo '	' >> ${ReportFile} echo ${c4} >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c5} &nbsp" >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c6} &nbsp" >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c7} &nbsp" >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c8} % " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c9} " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c10} " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c11} " >> ${ReportFile} echo '
' >> ${ReportFile} echo " TOTAL (MB)" >> ${ReportFile} echo '		' >> ${ReportFile} echo "${c3tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo " " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c5tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo " " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c7tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo " " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c9tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c10tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c11tot} " >> ${ReportFile} echo '
' >> ${ReportFile} echo " TOTAL (GB)" >> ${ReportFile} echo '		' >> ${ReportFile} echo "${c3tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo " " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c5tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo " " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c7tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo " " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c9tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c10tot} " >> ${ReportFile} echo '	' >> ${ReportFile} echo "${c11tot} " >> ${ReportFile} echo '

A Database Administration Blog

Database Growth Projection Report

SQL statement to check used % space in Tablespace