Differences

This shows you the differences between the selected revision and the current version of the page.

wiki:sysmaster 2014/04/30 20:59 wiki:sysmaster 2021/04/22 13:28 current
Line 353: Line 353:
            group by  dbsname, tabname              group by  dbsname, tabname 
                       
 +
=====  List all the tables in the temporary dbspaces ===== =====  List all the tables in the temporary dbspaces =====
    select dbsname,tabname     select dbsname,tabname
Line 360: Line 361:
            order by  1             order by  1
                       
 +
 +
 +=====  List all the tables in the temporary dbspaces by session =====
 +
 +    SELECT q1.*, (100 * size_kb / dbs_size) :: DECIMAL(5,2) AS percent
 +      FROM
 +      (
 +        SELECT
 +            t2.owner [1,8],
 +            t2.dbsname [1,18] AS database,
 +            t2.tabname [1,22] AS table,
 +            t3.name [1,10] AS dbspace,
 +            (CURRENT - DBINFO('utc_to_datetime', ti_created)) :: INTERVAL DAY(4) TO SECOND AS life_time,
 +            (ti_nptotal * ti_pagesize/1024) :: INT AS size_kb
 +          FROM
 +            systabinfo AS t1,
 +            systabnames AS t2,
 +            sysdbspaces AS t3
 +          WHERE t2.partnum = ti_partnum
 +            AND t3.dbsnum = TRUNC(t2.partnum/1024/1024)
 +            AND TRUNC(MOD(ti_flags,256)/16) > 0
 +    ) AS q1,
 +    (
 +      SELECT name AS dbspace,
 +          SUM(chksize * d1.pagesize/1024) AS dbs_size
 +        FROM
 +            syschunks AS d1,
 +            sysdbspaces AS d2
 +        WHERE d1.dbsnum = d2.dbsnum
 +        GROUP BY 1
 +    ) AS q2
 +    WHERE q1.dbspace = q2.dbspace
 +    ORDER BY 6 DESC, 5 DESC;
 +
 +
=====  How big is a table ===== =====  How big is a table =====

Personal Tools