a command to show table file-size?

[Geoff hendrey <ge...@yahoo.com>]

Is there a utility or command that I can use to
identify the disk space (bytes) occupied by a
particular table?

Re: a command to show table file-size?

[tdempewolf <td...@gmail.com>]

Here is one which is very handy:

select v.*
from SYS.SYSSCHEMAS s,
SYS.SYSTABLES t,
new org.apache.derby.diag.SpaceTable(SCHEMANAME,TABLENAME) v
where s.SCHEMAID = t.SCHEMAID;

http://www.docjar.com/docs/api/org/apache/derby/diag/SpaceTable.html



--
View this message in context: http://apache-database.10148.n7.nabble.com/a-command-to-show-table-file-size-tp101717p143236.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: a command to show table file-size?

[Fabian Merki <fa...@merkisoft.ch>]

why do you need a programm for this???

select tablename,
(select sum(numallocatedpages*pagesize) from new
org.apache.derby.diag.SpaceTable('APP',t.tablename) x),
 (select sum(estimspacesaving) from new
org.apache.derby.diag.SpaceTable('APP',t.tablename) x) from SYS.SYSTABLES t

could be improved but works and is very simple!

cheers
fabian


----- Original Message ----- 
From: "Kristian Waagan" <Kr...@Sun.COM>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Wednesday, June 13, 2007 11:18 AM
Subject: Re: a command to show table file-size?


> Stanley Bradbury wrote:
> > Geoff hendrey wrote:
> >> Is there a utility or command that I can use to
> >> identify the disk space (bytes) occupied by a
> >> particular table?
> >>
> >>
> >>
> >>
> > The query listed at the link below can be used to determine the filename
> > for a table or tables in a database.  You can then check the size
> > directly or feed the results into a routine that reports the size.
>
> Another option is to use DerbyDiskSpaceDiag.java attached to the
> following Jira:
> https://issues.apache.org/jira/browse/DERBY-2549
>
> It is not polished, but shows you the space of tables and indexes.
>
>
> hth,
> -- 
> Kristian
>
> >
> >
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0408bradbury/#cs_map_filename
> >
> >
> > Note that this query requires the user function bigintToHexString be
> > created.  This is documented in the section just above the one pointed
> > to by the URL provided.
> >
> > Hope this helps.
> >
> >
>

Re: a command to show table file-size?

[Kristian Waagan <Kr...@Sun.COM>]

Stanley Bradbury wrote:
> Geoff hendrey wrote:
>> Is there a utility or command that I can use to
>> identify the disk space (bytes) occupied by a
>> particular table?
>>
>>
>>
>>   
> The query listed at the link below can be used to determine the filename 
> for a table or tables in a database.  You can then check the size 
> directly or feed the results into a routine that reports the size.

Another option is to use DerbyDiskSpaceDiag.java attached to the 
following Jira:
https://issues.apache.org/jira/browse/DERBY-2549

It is not polished, but shows you the space of tables and indexes.


hth,
-- 
Kristian

> 
> http://www.ibm.com/developerworks/db2/library/techarticle/dm-0408bradbury/#cs_map_filename 
> 
> 
> Note that this query requires the user function bigintToHexString be 
> created.  This is documented in the section just above the one pointed 
> to by the URL provided.
> 
> Hope this helps.
> 
> 

Re: a command to show table file-size?

[Stanley Bradbury <St...@gmail.com>]

Geoff hendrey wrote:
> Is there a utility or command that I can use to
> identify the disk space (bytes) occupied by a
> particular table?
>
>
>
>   
The query listed at the link below can be used to determine the filename 
for a table or tables in a database.  You can then check the size 
directly or feed the results into a routine that reports the size.

 http://www.ibm.com/developerworks/db2/library/techarticle/dm-0408bradbury/#cs_map_filename

Note that this query requires the user function bigintToHexString be 
created.  This is documented in the section just above the one pointed 
to by the URL provided.

Hope this helps.

Re: a command to show table file-size?

[Knut Anders Hatlen <Kn...@Sun.COM>]

Geoff hendrey <ge...@yahoo.com> writes:

> Is there a utility or command that I can use to
> identify the disk space (bytes) occupied by a
> particular table?

This query will give you information about the number of pages and the
size of the pages in the table T in schema APP:

select * from new org.apache.derby.diag.SpaceTable('APP', 'T') as space_table;

-- 
Knut Anders