Today I found myself needing to find out if I had any regions in a particular page template position. The application has multiple page templates in use, and unfortunately, some of the templates put the positions in different locations than others. (Yes, this is an application I inherited, and yes, you should standardize the roles for all of the page template positions to avoid this headache.) This makes it very difficult/daunting to put things on page 0, as they may not display in the proper place on all pages.
Of course, the application has 110 pages, so I didn't want to scan through all of them manually–that would take a really long time, and I know I'd miss something as my eyes glazed over from boredom. So, what to do?Oh, yeah, APEX provides us with some nifty application views! A little bit of tinkering in the SQL Workshop, and I've got this:
select aapr.page_id, aap.page_template, aapr.display_position, aapr.region_name, aapr.template region_template from apex_application_page_regions aapr, apex_application_pages aap where aapr.application_id = :MY_APPLICATION_ID and aap.application_id = aapr.application_id and aap.page_id = aapr.page_id and aapr.display_position_code = 'REGION_POSITION_02' order by aapr.page_id, aapr.display_position, aapr.region_name
It turns out that I had one region on one page that I needed to move before I could update the page templates to standardize where region position 2 shows up. If I hadn't found it, that page would have looked…rather interesting (heh).
So, if you have a question about your application, consider looking in the APEX Application views. There's a wealth of information there.