This query might do the trick. It checks whether the page bitmaps are marked for the according page number as table page, extension page or index page. Note: These are just my own test results, DO NOT AT ALL CONSIDER THIS VALID INFORMATION. -- These queries rely on field marked as "For internal use only." - Handle with care begin declare nDbSpaceID int = 0; declare nPageNr bigint = 218; select table_id, table_name, table_page_count, ext_page_count, len(tab_page_list), len(ext_page_list), substr(tab_page_list, nPageNr + 1, 1) as uses_page_as_tab_page, substr(ext_page_list, nPageNr + 1, 1) as uses_page_as_ext_page from sys.systab ST where dbspace_id = nDbSpaceID and (uses_page_as_tab_page = '1' or uses_page_as_ext_page = '1'); select SPX.table_id, table_name, SPX.phys_index_id, index_name, len(allocation_bitmap), substr(allocation_bitmap, nPageNr + 1, 1) as uses_page_as_idx_page from sys.systab ST inner join sys.sysphysidx SPX on ST.table_id = SPX.table_id inner join sys.sysidx SIX on SPX.table_id = SIX.table_id and SPX.phys_index_id = SIX.phys_index_id where SIX.dbspace_id = nDbSpaceID and uses_page_as_idx_page = '1'; end; Thanks Volker that is awesome! For all who want even more comfort I placed Volkers code in a procedure: CREATE PROCEDURE "DBA"."GetDBPageObject"( IN nPageNr bigint)
(20 Apr, 08:46)
Martin
Replies hidden
1
Well, I'd add the nDbSpaceID as 2nd parameter (probably default 0) then :)
(20 Apr, 09:55)
Volker Barth
|
Hm, the SQL Central Fragmentation Tab can be used to display which particular object uses which pages and show their numbers, unfortunately you cannot select more than one object at a time, and there does't seem to be a "vice-versa" display, i.e. to show which object occipies a particular page.
(I would very wildly guess the SYSTAB.tab_page_list and/or ext_page_list columns might reveal more but I don't know their internals...)