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 ===== |