You can do a lot with a few simple SQL queries against the ALUI / WCI database.  Oracle (and I) strongly discourage any direct database updating without using the API, but there’s nothing out there that says you can’t QUERY the database – heck, for Analytics server, it’s actually encouraged (PDF Link).

So, today’s post is an easy one that answers the question: “which pages and communities are my portlets displayed on”?  The SQL is simple:

 select
                        ptcommunities.name community_name,
                        ptpages.name page_name,
                        ptgadgets.name portlet_name
from
                        ptcommunities,
                        ptpages,
                        ptpagegadgets,
                        ptgadgets
where
                        ptcommunities.folderid = ptpages.folderid
and                ptpagegadgets.gadgetid = ptgadgets.objectid
and                ptpages.objectid = ptpagegadgets.pageid

… and you’ll get a list of communities, pages and portlets that you can sort or filter any way you want: