Re: Determining the size of a database
--simple boundary
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Hi V?,
I have attached 3 scripts that I use.
stats.ksh uses oncheck -pt to create a database dba_stats which has a
list of all tables with space information in a table called stats.
statdb reports from dba_stats:stats and gives a listing of space used
for each database.
stattab -d DBNAME does same by table.
Regards
David Ashby
______________________________ Reply Separator _________________________________
Hello, everyone,
I have recently inherited a system where a number of different
databases were all created on one dbspace. I would like to, at least
initially, allocate one dbspace per database. I would like to make the
dbspace roughly the size of the database, if possible. Does anyone have
any suggestions as to how I could easily determine the actual size of a
database's data and indexes? I would greatly appreciate any input.
I am running Dynamic Server 7.23 in Sun solaris 2.4
--simple boundary
Content-Type: text/plain; charset=US-ASCII; name="stattab"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="stattab"
#!/bin/ksh
typeset -i EXTENTS=0
typeset DB='No DB'
while getopts d:e: arg
do case ${arg} in
d) DB=$OPTARG ;;
e) EXTENTS=$OPTARG ;;
*) echo "\\nUsage: $0 -d <database name> -e <extents low threshold>\\n\\n"
exit 2 ;;
esac
done
if [[ $DB = 'No DB' ]]
then echo "\\nERROR: Specify Database Name by '-d dbname'\\n\\n"
exit 2
fi
isql dba_stats << EOS
select tabname,
username,
extents,
npalloc,
npused
from stats
where dbname = '${DB}'
and tabname not like 'sys%'
and extents > $EXTENTS
order by username, tabname
EOS
--simple boundary
Content-Type: text/plain; charset=US-ASCII; name="stats.ksh"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="stats.ksh"
#!/bin/ksh
trap "" 1 2 3 4 5 9 15
rm -rf /tmp/dba_stat.out*
clear
echo "\\n\\n\\t\\t[7m Refresh Database Statistics for Instance [0m"
current_instance | tail +4
isql sysmaster <<EOS | grep -v ^$ | tail +2 > /tmp/dba_stat.out1
select name
from sysdatabases
EOS
echo " Collecting for Databases :-\\n"
pr -to 5 -w80 -3 -aFl 15 /tmp/dba_stat.out1
echo "\\n Making ONCHECK script ...\\n"
sed '
s/^/oncheck -pt /
s?$? >> /tmp/dba_stat.out3?
' /tmp/dba_stat.out1 > /tmp/dba_stat.out2
chmod 700 /tmp/dba_stat.out2
echo " Generating ONCHECK output ...\\n"
/tmp/dba_stat.out2
echo " Filtering ONCHECK output ...\\n"
awk '
$0 ~ /TBLspace Report/ {
split($4, name, ":");
DBNAME = name[1];
split(name[2], tname, ".");
TABNAME = tname[2];
USERNAME = tname[1];
}
$0 ~ /Number of extents/ {
EXTENTS = $4;
}
$0 ~ /First extent size/ {
FEXTSIZE = $4;
}
$0 ~ /Next extent size/ {
NEXTSIZE = $4;
}
$0 ~ /Number of pages allocated/ {
NPALLOC = $5;
}
$0 ~ /Number of pages used/ {
NPUSED = $5;
}
$0 ~ /Number of rows/ {
NROWS = $4;
print DBNAME "|" USERNAME "|" TABNAME "|" EXTENTS "|" FEXTSIZE "|" \\
NEXTSIZE "|" NPALLOC "|" NPUSED "|" NROWS "|"
}
' /tmp/dba_stat.out3 > /tmp/dba_stat.out4
echo "[5;7m Recreating STATISTICS Database ... [0m"
isql sysmaster << EOS1
drop database dba_stats;
create database dba_stats;EOS1
echo "[5;7m Reloading Database Tables ... [0m\\n"
isql dba_stats << EOS2
create table stats(
dbname varchar(18),
username varchar(18),
tabname varchar(18),
extents integer,
fextsize integer,
nextsize integer,
npalloc integer,
npused integer,
nrows integer);
create index stats_d1 on stats (dbname, username, tabname);
load from /tmp/dba_stat.out4
insert into stats;
grant connect to public;
grant select on stats to public;EOS2
rm -rf /tmp/dba_stat.out*
--simple boundary
Content-Type: text/plain; charset=US-ASCII; name="statdb"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="statdb"
#!/usr/bin/ksh
clear
echo "\\n\\n\\t\\t[7m Size of Databases for all Instance [0m"
current_instance | tail +4
isql dba_stats <<EOI
select dbname, sum(npalloc) Allotted,
sum(npused) Consumed,
sum(nrows) All_Rows
from stats
group by dbname
EOI
--simple boundary--