Oracle APEX Useful Queries Every Developer Should Know

 

 Introduction

In real-world Oracle APEX applications, developers often face challenges diagnosing performance issues, auditing components, and monitoring activity.

Oracle APEX provides a rich set of internal views that can help diagnose, monitor, and optimize applications. This guide transforms commonly used queries into practical, real-world solutions with insights and best practices.

1) Identify Slow Pages

Problem

Users report that some pages are slow, but it's unclear which ones are causing performance issues.

Solution

SELECT APPLICATION_ID,

       PAGE_ID,

       COUNT(*)                        AS EXECUTIONS,

       ROUND(AVG(ELAPSED_TIME), 2)     AS AVG_SECONDS,

       ROUND(MAX(ELAPSED_TIME), 2)     AS MAX_SECONDS

FROM   APEX_WORKSPACE_ACTIVITY_LOG

WHERE  VIEW_DATE >= SYSDATE - 30

GROUP BY APPLICATION_ID, PAGE_ID

ORDER BY AVG_SECONDS DESC

FETCH FIRST 20 ROWS ONLY;

2) Application & Workspace Info

 APEX applications in workspace

SELECT APPLICATION_ID,

       APPLICATION_NAME,

       ALIAS,

       LAST_UPDATED_ON,

       LAST_UPDATED_BY,

       VERSION

FROM   APEX_APPLICATIONS

ORDER BY APPLICATION_ID;

  APEX version installed

SELECT VERSION_NO,

       API_COMPATIBILITY,

       PATCH_APPLIED

FROM   APEX_RELEASE;

 All workspaces Detail

SELECT WORKSPACE_ID,

       WORKSPACE,

       WORKSPACE_DISPLAY_NAME

FROM   APEX_WORKSPACES;

3)   Pages & Components

All Pages in an Application

Note : Get App_id from query APEX applications in workspace in our example we use app_id =102

SELECT PAGE_ID,

       PAGE_NAME,

       PAGE_ALIAS,

       PAGE_MODE,

       AUTHORIZATION_SCHEME,

       LAST_UPDATED_BY,

       LAST_UPDATED_ON

FROM   APEX_APPLICATION_PAGES

WHERE  APPLICATION_ID =:APP_ID

ORDER BY PAGE_ID;

All Interactive Reports in an Application

Note : Get App_id from query APEX applications in workspace in our example we use app_id =102

SELECT PAGE_ID,

       REGION_NAME,

       SQL_QUERY

FROM   APEX_APPLICATION_PAGE_IR

WHERE  APPLICATION_ID = :APP_ID;

 

 All page items (fields/inputs)

Note : Get App_id from query APEX applications in workspace in our example we use app_id =102

 SELECT PAGE_ID,

       ITEM_NAME,

       DISPLAY_AS,

       ITEM_DEFAULT

FROM   APEX_APPLICATION_PAGE_ITEMS

WHERE  APPLICATION_ID = :APP_ID

ORDER BY PAGE_ID, ITEM_NAME;

4) Activity Monitoring

Most Visited Pages (Last 20 Days)

SELECT APPLICATION_ID,

       PAGE_ID,

       COUNT(*)        AS HITS,

       MAX(VIEW_DATE)  AS LAST_VISITED

FROM   APEX_WORKSPACE_ACTIVITY_LOG

WHERE  VIEW_DATE >= SYSDATE - 20

GROUP BY APPLICATION_ID, PAGE_ID

ORDER BY HITS DESC

FETCH FIRST 20 ROWS ONLY;

5) Page Validations 

 Note : Get App_id from query APEX applications in workspace in our example we use app_id =102

  All validations

SELECT PAGE_ID,

       VALIDATION_NAME,

       VALIDATION_TYPE,

       VALIDATION_EXPRESSION1,

       ERROR_MESSAGE

FROM   APEX_APPLICATION_PAGE_VAL

WHERE  APPLICATION_ID = :APP_ID

ORDER BY PAGE_ID;

 6) List of Values (LOVs)

Note : Get App_id from query APEX applications in workspace in our example we use app_id =102

All LOVs in an application

SELECT LIST_OF_VALUES_NAME,

       LIST_OF_VALUES_QUERY,

       SOURCE_TYPE

FROM   APEX_APPLICATION_LOVS

WHERE  APPLICATION_ID = :APP_ID

ORDER BY LIST_OF_VALUES_NAME;

Conclusion

 Oracle APEX's internal views unlock a level of visibility that most developers need. Whether you're diagnosing slow page renders, auditing session state, or tracking workspace activity, these views give you direct access to the data APEX itself relies on.