DB size!
A poster asked for a script to find the total size of a database including fragmented tables. Jay Konigsberg suggested extracting it from onstat -d. Stefanie Vario posted SQL that sums nptotal and npused from sysmaster:sysptnhdr for tables and sysfragments into a temp table, multiplying by 2 for 2 KB pages, or by 4 on 4 KB page systems. Answered.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Anyone has a script to find the total size of a database? If a script is available, would it also calculate fragmented tables? Thanks in advance! Sent via Deja.com http://www.deja.com/ Before you buy.
You can extract the information from onstat -d.
<bullmkt123@my-deja.com> wrote in message
news:8qnmlm$h3s$1@nnrp1.deja.com...
> Anyone has a script to find the total size of a database? If a script
> is available, would it also calculate fragmented tables?
>
> Thanks in advance!
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
In article <8qnmlm$h3s$1@nnrp1.deja.com>,
bullmkt123@my-deja.com wrote:
> Anyone has a script to find the total size of a database? If a script
> is available, would it also calculate fragmented tables?
>
> Thanks in advance!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
I use the following SQL statements to get the size of a database (it also
includes fragmented tables.)
create temp table tmp1
(k_alloc integer, k_used integer) with no log;
insert into tmp1
select
sum(sysptnhdr.nptotal*2) k_alloc,
sum(sysptnhdr.npused*2) k_used
from
systables,
sysmaster:sysptnhdr sysptnhdr
where
systables.tabtype = 'T'
and systables.partnum = sysptnhdr.partnum;
insert into tmp1
select
sum(sysptnhdr.nptotal*2) k_alloc,
sum(sysptnhdr.npused*2) k_used
from
sysfragments,
sysmaster:sysptnhdr sysptnhdr
where
sysfragments.partn = sysptnhdr.partnum; select
sum(k_alloc) k_alloc,
sum(k_used) k_used
from
tmp1;
Stefanie Vario
DBA - Roush Industries
svario@roushind.com
Sent via Deja.com http://www.deja.com/
Before you buy.
In article <8qnmlm$h3s$1@nnrp1.deja.com>,
bullmkt123@my-deja.com wrote:
> Anyone has a script to find the total size of a database? If a script
> is available, would it also calculate fragmented tables?
>
> Thanks in advance!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
I use the following SQL statements to get the size of a database
(including fragmented tables):
create temp table tmp1
(k_alloc integer, k_used integer) with no log;
insert into tmp1
select
sum(sysptnhdr.nptotal*2) k_alloc,
sum(sysptnhdr.npused*2) k_used
from
systables,
sysmaster:sysptnhdr sysptnhdr
where
systables.tabtype = 'T'
and systables.partnum = sysptnhdr.partnum;
insert into tmp1
select
sum(sysptnhdr.nptotal*2) k_alloc,
sum(sysptnhdr.npused*2) k_used
from
sysfragments,
sysmaster:sysptnhdr sysptnhdr
where
sysfragments.partn = sysptnhdr.partnum; select
sum(k_alloc) k_alloc,
sum(k_used) k_used
from
tmp1;
In the above formula,you will need to substitute the "2" with a "4" if
you're on a 4k page size system.
Stefanie Vario
DBA - Roush Industries
svario@roushind.com
Sent via Deja.com http://www.deja.com/
Before you buy.