In version 10.3 the SYSCS_DIAG diagnostic tables and functions were introduced. For versions 10.3 and above use SYSCS_DIAG.SPACE_TABLE.

But for people concerned with the earlier versions of Derby the SpaceTable VTI can be used to examine the space usage of a particular table and its indexes. This VTI is not documented because it did not meet the standards based requirement to be part of Derby. Regardless it is very useful though it is unsupported and all that that implies.

The example query below shows how to list the space table information for the tables in a database. To include the information on indexes remove the where-clause.

ij>
SELECT T2.* FROM SYS.SYSTABLES systabs,
new org.apache.derby.diag.SpaceTable(systabs.tablename) t2
where t2.isindex = 0;

CONGLOMERATE      |ISIND& |NUMALLOCATED |NUM     |NUMUNFILLED  |PAGESIZE  |ESTIMSPACE
NAME                       PAGES       FREEPAGES  PAGES                   SAVING
----------------------------------------------------   -----------------------------------
AIRLINES           |0     |1            |0        |1             |4096    |0
CITIES             |0     |2            |0        |0             |4096    |0
COUNTRIES          |0     |3            |0        |1             |4096    |0
FLIGHTAVAILABILITY |0     |8            |0        |0             |4096    |0
FLIGHTS            |0     |15           |0        |0             |4096    |0
FLIGHTS_HISTORY    |0     |1            |0        |1             |4096    |0
LOB1               |0     |672          |0        |1             |32768   |0
MAPS               |0     |10           |0        |1             |32768   |0
TEST               |0     |1            |0        |1             |4096    |0
TESTT              |0     |1            |0        |1             |4096    |0
TSTTM              |0     |1            |0        |1             |4096    |0
T_LOB1_LOG         |0     |1            |0        |1             |4096    |0

The following query is a slightly modified version of the one above as it calculates the space used by each conglomerate.

SELECT v.*,
       ((NUMALLOCATEDPAGES + NUMFREEPAGES) * PAGESIZE)  as USEDSPACE
FROM   SYS.SYSSCHEMAS s, SYS.SYSTABLES t,
       new org.apache.derby.diag.SpaceTable(SCHEMANAME,TABLENAME) v
WHERE  s.SCHEMAID = t.SCHEMAID;

OldSpaceTableVti (last edited 2013-05-09 22:35:11 by MikeMatrigali)