Extent Upper Limit - The Ultimate Query!

All: I've been searching in the Group and the web for a SQL query to the SMI (sysmaster database) that gives me the extent information needed to monitor and prevent reaching the extent upper limit by fragment/ table/database. The following is a query that I have come up with based on all that I have seen out there. It works in both 7.3x and 9.30 and above (although I have not tested on 10). Please feel free to improve it. I hope this helps. Ramón. -- I would recommend to unload this and then load it to a "temp" -- table with indexes on dbsname and tab name, then get all the reports -- of off the "temp" table. select {+ ordered, index(a, syspaghdridx) } -- necessary c.dbsname, -- the database c.tabname, -- the table or index b.name, -- the dbspace c.partnum, -- necesary to get the count and sum right trunc(a.pg_frcnt / 8) frext, -- extents left in the fragment until ceiling count(*) num_of_extents, -- num of extents allocated in this fragment sum( pe_size ) total_size -- total size of the extents in the fragment from sysmaster:sysdbspaces b, sysmaster:syspaghdr a, sysmaster:systabnames c, sysmaster:sysptnext d where a.pg_partnum = sysmaster:partaddr(b.dbsnum, 1) and sysmaster:bitval(a.pg_flags, 2) = 1 and a.pg_nslots = 5 and c.partnum = sysmaster:partaddr(b.dbsnum, a.pg_pagenum) and c.partnum = d.pe_partnum --and c.dbsname = "your_database" -- use these 2 in case you want to --and c.tabname = "your_table" -- filter them, but would not recommend group by 1,2,3,4,5 order by 4