Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    121

    Unanswered: is there anyway to find table/index size within a tablespace

    We need to figure out let us say in userspace1,if it is full what tables and indexes occupies all the spaces ..

    Any help will be appreciated...

  2. #2
    Join Date
    Mar 2004
    Posts
    46
    To find the size of a table use the following query -

    SELECT T1.TABLENAME,
    T1.FPAGES*T2.PAGESIZE TABLESIZE_BYTES
    FROM SYSCAT.TABLES T1,
    SYSCAT.TABLESPACES T2
    WHERE T1.TBSPACE = T2.TBSPACE
    AND T1.TABNAME 'YOUR_TABLE_NAME' ;

    To list all tables in a tablespace (largest table first), use the following query

    SELECT T1.TABLENAME,
    T1.FPAGES*T2.PAGESIZE TABLESIZE_BYTES
    FROM SYSCAT.TABLES T1,
    SYSCAT.TABLESPACES T2
    WHERE T1.TBSPACE = T2.TBSPACE
    AND T2.TBSPACE = 'YOUR_TABLESPACE_NAME'
    ORDER BY 2 DESC ;

    Note that there some people might insist on using "NPAGES" instead of "FPAGES" in the above queries, but I leave the choice upto you !

    There is no easy way to get the "size" of an index. However, you can do relative sizing by counting the "leaf pages" as follows -

    SELECT T1.INDEXNAME,
    T1.NLEAF*T3.PAGESIZE INDEX_LEAF_SUM_BYTES
    FROM SYSCAT.INDEXES T1,
    SYSCAT.TABLES T2,
    SYSCAT.TABLESPACES T3
    WHERE T1.TABNAME = T2.TABNAME
    AND T1.TABSCHEMA = T2.TABSCHEMA
    AND T2.INDEX_TBSPACE = T3.TABSPACE
    AND T3.TABSPACE = 'YOUR_TABLESPACE_NAME'
    ORDER BY 2 DESC ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •