One of the most underappreciated cost-saving opportunities in Oracle shops is APEX. Every Oracle Database license includes APEX at no additional cost. On OCI, Oracle offers APEX Application Development as a managed service where you don’t even manage the database — you just build. I’ve watched teams pay for Retool, internal dashboard tools, and custom-built React applications when APEX could have delivered the same functionality in a fraction of the time, at zero additional cost, running inside their existing Oracle infrastructure.

This post is for DBAs who want to understand what APEX can actually deliver in 2025, beyond the outdated “it looks like a 2005 forms application” perception.

What APEX Actually Is in 2025

APEX is a low-code development platform that runs inside Oracle Database. Every APEX application is, at its core, a set of database objects (tables, views, PL/SQL packages) plus metadata that APEX’s runtime engine interprets to generate web pages.

The current release (APEX 24.x) ships with:

• A modern component library (Universal Theme) that produces responsive, mobile-friendly interfaces
• Drag-and-drop page designer
• Built-in REST API consumption and publication
• Native integration with Oracle AI services
• Progressive Web App (PWA) support
• Native PDF generation
• Git integration for version control of application definitions

The “it looks old” perception comes from APEX applications built 10+ years ago that were never updated. A well-built APEX 24.x application is visually indistinguishable from a custom web application to most users.

OCI APEX Application Development Service

On OCI, you can provision APEX without managing a database:

1. Go to OCI Console → Developer Services → APEX Application Development
2. Create a workspace — this provisions an Autonomous Database with APEX pre-configured
3. Access your workspace at https://<your-instance>.adb.<region>.oraclecloudapps.com/ords/apex
4. Start building

The provisioning takes about 3 minutes. You’re paying for Autonomous Database compute (or using Always Free tier for development).

For development and small production workloads, the Always Free APEX service (backed by Always Free ADB) is genuinely useful:

• 20GB storage
• 1 OCPU
• Unlimited APEX applications
• No time limit
• Zero cost

Use Cases Where APEX Outperforms Alternatives

Internal data management tools: Any application where the primary function is viewing, editing, and reporting on Oracle Database data. APEX generates these from table definitions automatically — an Interactive Report on any table takes minutes to build, not hours.

Approval workflows: APEX has built-in workflow components. Purchase approval, leave requests, change management — anything requiring a form, a routing rule, and an email notification. Building this in APEX takes a day. Building it custom takes a sprint.

DBA monitoring dashboards: A personal favorite. I’ve built APEX applications that expose AWR data, session activity, tablespace usage, and job status in a clean web interface — accessible to managers and developers without giving them direct database access.

sql

-- Example: Simple tablespace monitoring page data source
SELECT tablespace_name,
       ROUND(used_space * 8192 / 1024 / 1024 / 1024, 2) used_gb,
       ROUND(tablespace_size * 8192 / 1024 / 1024 / 1024, 2) total_gb,
       ROUND(used_percent, 1) pct_used
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

Put this in an APEX Interactive Report with conditional row highlighting (red when >85%, orange when >70%) and you have a tablespace monitoring dashboard in 15 minutes.

REST API backends: APEX’s RESTful Services module (ORDS, which is bundled) lets you expose any SQL query or PL/SQL procedure as a REST endpoint in minutes. For mobile app backends or microservice integration, this eliminates the need for a separate API layer.

APEX and Oracle AI Services Integration

In APEX 24.x, Oracle has added native AI integration. You can call OCI Generative AI (which includes access to models including Cohere and Meta Llama) directly from APEX PL/SQL:

sql

-- Call OCI Generative AI from APEX PL/SQL
DECLARE
    l_response CLOB;
BEGIN
    l_response := APEX_AI.GENERATE(
        p_prompt     => 'Summarize the following support ticket: ' || :P1_TICKET_TEXT,
        p_credential => 'OCI_GENERATIVE_AI',
        p_model      => 'cohere.command-r-plus'
    );
    :P1_AI_SUMMARY := l_response;
END;

This is genuinely powerful for internal tools. A support ticket triage application that auto-summarizes tickets, a contract review tool, an internal chatbot over your Oracle data — all buildable in APEX without any external infrastructure.

The Limitations Worth Knowing

APEX is not the right tool for everything:

Customer-facing, brand-critical applications: If your marketing team needs pixel-perfect control over every visual element, APEX’s theme system may feel constraining. Custom JavaScript and CSS are possible but require developer effort.

Very high-concurrency public applications: APEX applications with thousands of concurrent users work, but you need to size your Autonomous Database accordingly and understand ORDS connection pool behavior.

Complex client-side interactivity: Single-page applications with heavy JavaScript interaction are possible in APEX but feel unnatural — APEX’s page model is server-rendered. Pure SPA requirements are better served by React or Vue.

Real-time data: APEX doesn’t natively support WebSockets for real-time push. Polling-based refresh is the standard approach, which works for most monitoring use cases but not for true real-time dashboards.

For internal tools, operational applications, and data management — which constitute the majority of Oracle shop application needs — APEX is the right answer more often than teams realize.

BLOG POST 21

Title: ORA-01031 Insufficient Privileges — The Permission Problem That’s Rarely About Permissions

ORA-01031 is Oracle’s “insufficient privileges” error. On the surface, it seems obvious: you don’t have permission to do what you’re trying to do, so get the right permission. In practice, ORA-01031 appears in scenarios where you think you absolutely should have permission, where you did have permission yesterday, or where the object you’re accessing exists and is owned by you. Understanding why ORA-01031 fires in non-obvious scenarios is what separates DBAs who resolve it quickly from those who spend hours granting and revoking privileges in circles.

The PL/SQL Definer’s Rights Trap

This is the most common non-obvious ORA-01031 scenario. A developer has a stored procedure that worked fine when tested. In production, it fails with ORA-01031 when called by the application user.

The mechanism: PL/SQL stored procedures execute by default with definer’s rights — they run with the privileges of the user who owns the procedure, not the user who calls it. This is usually what you want. But it breaks in a specific pattern:

sql

-- Procedure owner: PROC_OWNER
-- Calls a table owned by: DATA_OWNER
-- Application user: APP_USER has EXECUTE on proc_owner.my_procedure
-- This works: PROC_OWNER has direct SELECT on data_owner.orders
CREATE OR REPLACE PROCEDURE proc_owner.get_orders AS
BEGIN
    FOR rec IN (SELECT * FROM data_owner.orders) LOOP
        ...
    END LOOP;
END;
/
-- This fails with ORA-01031 at runtime:
-- If PROC_OWNER's access to data_owner.orders was granted through a ROLE
-- Direct grants work in definer's rights procedures
-- Role-based grants DO NOT work in definer's rights procedures

This is one of Oracle’s most confusing behaviors. Roles are disabled inside stored procedures running with definer’s rights. If PROC_OWNER has SELECT on DATA_OWNER.ORDERS only through a role (not a direct grant), the procedure fails even though PROC_OWNER can query the table interactively.

sql

-- Diagnose: check if access is direct or through role
SELECT grantee, owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE table_name = 'ORDERS'
AND owner = 'DATA_OWNER'
AND grantee = 'PROC_OWNER';
-- If no rows: access is only through a role, not direct
-- Fix: grant directly
GRANT SELECT ON data_owner.orders TO proc_owner;

ORA-01031 When Connecting AS SYSDBA

A specific scenario that appears in production support situations: sqlplus / as sysdba returns ORA-01031 on a server where the DBA is certain they have SYSDBA privilege.

$ sqlplus / as sysdba
ORA-01031: insufficient privileges

On Linux/Unix: the OS user attempting to connect must be a member of the dba OS group (or whichever group is specified in $ORACLE_HOME/rdbms/lib/config.c as OSDBA_GROUP). Check:

bash

# Check group membership
id oracle
groups oracle
# Check which group is the OSDBA group
cat $ORACLE_HOME/rdbms/lib/config.c | grep dba_group
# Add user to dba group if missing (requires root)
usermod -a -G dba oracle

On Windows: the user must be a member of the ORA_DBA local group. Check Computer Management → Local Users and Groups → Groups → ORA_DBA.

This scenario commonly occurs after OS migrations, user account recreations, or when the DBA is SSH’d in as a different user than expected.

ORA-01031 on Views with Underlying Tables in Different Schemas

sql

-- Schema A owns a view over Schema B's table
CREATE VIEW schema_a.order_view AS
    SELECT * FROM schema_b.orders;
-- App user has SELECT on schema_a.order_view
GRANT SELECT ON schema_a.order_view TO app_user;
-- App user queries the view: ORA-01031
SELECT * FROM schema_a.order_view;

The issue: SCHEMA_A (the view owner) needs direct SELECT privilege on SCHEMA_B.ORDERS. If SCHEMA_A accesses SCHEMA_B.ORDERS only through a role, the view fails — same definer’s rights behavior as stored procedures.

sql

-- Fix:
GRANT SELECT ON schema_b.orders TO schema_a;

This catches teams who carefully set up role-based access between schemas and then wonder why views and procedures fail even though interactive queries work.

The WITH GRANT OPTION Chain Break

sql

-- DBA grants to SCHEMA_A with grant option
GRANT SELECT ON table1 TO schema_a WITH GRANT OPTION;
-- SCHEMA_A grants to SCHEMA_B
GRANT SELECT ON table1 TO schema_b;  -- (from schema_a's session)
-- DBA revokes from SCHEMA_A
REVOKE SELECT ON table1 FROM schema_a;
-- SCHEMA_B now gets ORA-01031 — cascade revoke

When a privilege is revoked from the grantor, Oracle automatically revokes all downstream grants made by that grantor. This is documented behavior but surprises people regularly. SCHEMA_B never had its grant explicitly revoked — the revoke cascaded from the parent.

sql

-- Find privilege grant chains to understand cascade risk
SELECT grantee, owner, table_name, privilege, grantable, grantor
FROM dba_tab_privs
WHERE owner = 'SCHEMA_OWNER'
AND table_name = 'TABLE1'
ORDER BY grantor, grantee;

Diagnosing Quickly: The Privilege Check Query

When ORA-01031 appears and the cause isn’t immediately obvious:

sql

-- Check all privileges for a user (direct + through roles)
SELECT 'SYSTEM PRIV' priv_type, privilege, admin_option, '-' grantable
FROM dba_sys_privs WHERE grantee = 'YOUR_USER'
UNION ALL
SELECT 'OBJECT PRIV', privilege, '-', grantable
FROM dba_tab_privs WHERE grantee = 'YOUR_USER'
UNION ALL
SELECT 'ROLE', granted_role, admin_option, default_role
FROM dba_role_privs WHERE grantee = 'YOUR_USER'
ORDER BY 1, 2;
-- Check if the object even exists
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name = 'THE_OBJECT_YOU_CANT_ACCESS'
ORDER BY owner;

If the object doesn’t appear in dba_objects for the user, they may be looking in the wrong schema or the object truly doesn’t exist — which Oracle also reports as ORA-01031 in some contexts (rather than ORA-00942 “table or view does not exist”), specifically when the user doesn’t have privilege to even know the object exists.