šŸ” Ultimate Guide to Oracle APEX Metadata Views
Posted inOracle APEX

šŸ” Ultimate Guide to Oracle APEX Metadata Views

No CommentsPosted inOracle APEX
Post Views: 164

(Complete Developer Reference with Sample Queries)

Oracle APEX stores everything as metadata.

Every page, region, item, process, automation, REST source, workflow, theme ā€” all of it lives inside database views.

These views start with:

APEX_*

Understanding them makes you:

  • šŸ”„ Advanced APEX Developer
  • šŸ”Ž Metadata Auditor
  • šŸ“Š Governance Engineer
  • šŸš€ DevOps Automation Builder

šŸ— 1ļøāƒ£ Workspace & Instance Views

These operate at the workspace level.

šŸ”¹ APEX_WORKSPACES

Available Oracle APEX workspaces.

SELECT *
FROM apex_workspaces;

šŸ”¹ APEX_WORKSPACE_APEX_USERS

Workspace users.

SELECT *
FROM apex_workspace_apex_users

šŸ”¹ APEX_WORKSPACE_SESSIONS

Active APEX sessions.

SELECT *
FROM apex_workspace_sessions;

šŸ”¹ APEX_WORKSPACE_ACTIVITY_LOG

Page view activity.

SELECT *
FROM apex_workspace_activity_log
WHERE view_date > SYSDATE - 1;

šŸ“¦ 2ļøāƒ£ Application Core Views

šŸ”¹ APEX_APPLICATIONS

Applications in workspace.

SELECT *
FROM apex_applications;

šŸ”¹ APEX_APPLICATION_GROUPS

Application Groups.

SELECT group_name,
       workspace
FROM apex_application_groups;

šŸ”¹ APEX_APPLICATION_SETTINGS

SELECT *
FROM apex_application_settings

šŸ”¹ APEX_APPLICATION_STATIC_FILES

SELECT file_name,
       mime_type
FROM apex_application_static_files
WHERE application_id = :APP_ID;

šŸ” 3ļøāƒ£ Authentication & Authorization

šŸ”¹ APEX_APPLICATION_AUTH

SELECT authentication_scheme_name,
       scheme_type
FROM apex_application_auth
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_AUTHORIZATION

SELECT authorization_scheme_name,
       scheme_type
FROM apex_application_authorization
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_ALL_AUTH

SELECT *
FROM apex_application_all_auth;

šŸ“„ 4ļøāƒ£ Page Metadata Views

šŸ”¹ APEX_APPLICATION_PAGES

SELECT page_id,
       page_name,
       page_mode
FROM apex_application_pages
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_PAGE_REGIONS

SELECT *
FROM apex_application_page_regions
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_PAGE_ITEMS

SELECT *
FROM apex_application_page_items
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_PAGE_PROC

SELECT page_id,
       process_name,
       process_type
FROM apex_application_page_proc
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_PAGE_VAL

SELECT page_id,
       validation_name,
       validation_type
FROM apex_application_page_val
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_PAGE_BRANCHES

SELECT page_id,
       branch_name,
       branch_action
FROM apex_application_page_branches
WHERE application_id = :APP_ID;

āš” 5ļøāƒ£ Dynamic Actions

šŸ”¹ APEX_APPLICATION_PAGE_DA

SELECT page_id,
       dynamic_action_name
FROM apex_application_page_da
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_PAGE_DA_ACTS

SELECT dynamic_action_name,
       action_name
FROM apex_application_page_da_acts
WHERE application_id = :APP_ID;

šŸ“Š 6ļøāƒ£ Reports & Interactive Grid

šŸ”¹ APEX_APPLICATION_PAGE_IR

SELECT page_id,
       region_name
FROM apex_application_page_ir
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPL_PAGE_IGS

SELECT page_id,
       region_name
FROM apex_appl_page_igs
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPL_PAGE_IG_COLUMNS

SELECT *
FROM apex_appl_page_ig_columns
WHERE application_id = :APP_ID;

šŸ“‹ 7ļøāƒ£ LOVs & Lists

šŸ”¹ APEX_APPLICATION_LOVS

SELECT list_of_values_name,
       lov_type
FROM apex_application_lovs
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_LOV_ENTRIES

SELECT *
FROM apex_application_lov_entries
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_LISTS

SELECT list_name
FROM apex_application_lists
WHERE application_id = :APP_ID;

šŸŽØ 8ļøāƒ£ Themes & Templates

šŸ”¹ APEX_APPLICATION_THEMES

SELECT theme_id,
       theme_name
FROM apex_application_themes
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPLICATION_TEMPLATES

SELECT template_name,
template_type
FROM apex_application_templates
WHERE application_id = :APP_ID;

šŸ”Œ 9ļøāƒ£ REST & Web Sources

šŸ”¹ APEX_APPL_WEB_SRC_MODULES

SELECT *
FROM apex_appl_web_src_modules
WHERE application_id = :APP_ID;

šŸ”¹ APEX_REST_RESOURCE_MODULES

SELECT *
FROM apex_rest_resource_modules;

šŸ¤– šŸ”Ÿ AI & RAG Views (Latest APEX Versions)

šŸ”¹ APEX_APPL_AI_CONFIGS

SELECT *
FROM apex_appl_ai_configs
WHERE application_id = :APP_ID;

šŸ”¹ APEX_APPL_AI_CONFIG_RAG_SRCS

SELECT *
FROM apex_appl_ai_config_rag_srcs
WHERE application_id = :APP_ID;

šŸ”„ 1ļøāƒ£1ļøāƒ£ Automations

šŸ”¹ APEX_APPL_AUTOMATIONS

SELECT *
FROM apex_appl_automations
WHERE application_id = :APP_ID;

šŸ”¹ APEX_AUTOMATION_LOG

SELECT *
FROM apex_automation_log;

šŸ” 1ļøāƒ£2ļøāƒ£ Workflows & Tasks

šŸ”¹ APEX_APPL_WORKFLOWS

SELECT *
FROM apex_appl_workflows
WHERE application_id = :APP_ID;

šŸ”¹ APEX_TASKS

SELECT *
FROM apex_tasks
WHERE application_id = :APP_ID;

šŸ§Ŗ 1ļøāƒ£3ļøāƒ£ Data Generator Views

šŸ”¹ APEX_DG_BLUEPRINTS

SELECT *
FROM apex_dg_blueprints;

šŸ”¹ APEX_DG_BUILTIN_PERSONS

SELECT first_name,
       last_name
FROM apex_dg_builtin_persons
FETCH FIRST 10 ROWS ONLY;

šŸž 1ļøāƒ£4ļøāƒ£ Issue Tracking System

šŸ”¹ APEX_ISSUES

SELECT *
FROM apex_issues;

šŸ”¹ APEX_ISSUE_COMMENTS

SELECT *
FROM apex_issue_comments;

šŸ§  Pattern to Query Any APEX View

Most views follow this filter structure:

WHERE application_id = :APP_ID

Or

WHERE WORKSPACE_NAME = :WORKSPACE_NAME;

šŸ”Ž How to Explore More APEX Metadata Views

If you want to explore all available Oracle APEX metadata views directly from the UI, follow these steps:

šŸ“ Navigation Path

App Builder => Workspace Utilities => APEX Views

šŸ’” Pro Tip (Advanced Developers)

You can also query metadata dictionary directly:

SELECT view_name
FROM all_views
WHERE view_name LIKE 'APEX_%'
ORDER BY view_name;

From there you can:

  • šŸ” Search any APEX_* view
  • šŸ“– Read view descriptions
  • šŸ§¾ See column definitions
  • šŸ—‚ Understand relationships between views
  • šŸ§  Discover newly added views in latest APEX versions

šŸš€ Why This Matters

With these metadata views you can:

  • Generate full application documentation
  • Audit security
  • Compare Dev vs Prod
  • Build governance dashboards
  • Detect unused components
  • Analyze database dependencies

šŸ Final Thoughts

Oracle APEX metadata is not just system data ā€”

It is a complete development intelligence layer.

If you master these views, you unlock:

āœ” Automation

āœ” Governance

āœ” DevOps

āœ” Security Auditing

āœ” AI-based App Analysis

Happy APEXING!

Comments

No comments yet. Why don’t you start the discussion?

    Leave a Reply

    Your email address will not be published. Required fields are marked *