APEX meta information

I use these query to get APEX meta information about APEX version, workspace, application, pages, and table/queries the page are using.

 col comp_name format a30

col procedure format a20

col username format a30

col proxy format a30

col client format a30

col APPLICATION_ID  heading "APP_ID" format 9999

select comp_name,version,schema,procedure from dba_registry where comp_name like '%APEX%';

select username,created from dba_users where username like '%APEX%';

select owner,count(*) from dba_objects where owner like 'APEX%' group by owner;

select owner,object_type,count(*) from dba_objects where owner like 'APEX_LISTENER'  group by owner,object_type;

--select synonym_name,TABLE_OWNER,TABLE_NAME from dba_synonyms where OWNER='APEX_LISTENER';

select * from proxy_users where proxy like '%ORDS%' or proxy like '%APEX%';

-- list workspace and applications

SELECT 

  --  w.workspace_id,

    w.workspace,

    a.application_id,

    a.application_name,

    a.alias,

    a.owner,

    --a.application_status,

    TO_CHAR(a.created_on, 'YYYY-MM-DD') AS creation_date,

    TO_CHAR(a.last_updated_on, 'YYYY-MM-DD') AS last_updated_date

-- w.LAST_LOGGED_PAGE_VIEW,LAST_PURGED_SESSION

FROM 

    apex_workspaces w right outer join 

    apex_applications a ON w.workspace_id = a.workspace_id

ORDER BY 

    w.workspace,

    a.application_name;

col LAST_UPDATED_BY format a20

select workspace,APPLICATION_ID,APPLICATION_NAME,pages,OWNER,CREATED_ON,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATED_ON from apex_applications where workspace<>'INTERNAL';

col page_name format a20

col page_id format 99999

col region_name format a20

col region_source format a38

col query_type_code format a10 

col source_type_code format a21 

col source_type format a21 

col table_name format a30 

col APPLICATION_ID  heading "APP_ID" format 9999

col application_name format a20

-- retrive what table/sql is used in the apex page

select workspace,application_name,application_id,page_id,page_name,region_name,location,SOURCE_TYPE,QUERY_TYPE_CODE,table_name,substr(REGION_SOURCE,1,38) REGION_SOURCE from APEX_APPLICATION_PAGE_REGIONS 

where application_id not in (select distinct application_id from apex_applications where workspace='INTERNAL' or workspace='COM.ORACLE.CUST.REPOSITORY')

-- and application_id=&application_id

;