DBA_SOURCE - Search Oracle PL/SQL Source Code

DBA_SOURCE

Overview

DBA_SOURCE stores the source text of every stored PL/SQL unit in the database — packages, package bodies, procedures, functions, triggers, types, type bodies, and Java source objects. Each row holds one line of source text, identified by owner, object name, object type, and line number. DBAs and developers query it to search for hard-coded values, locate where a specific table or variable is referenced, audit code for security anti-patterns, reconstruct object definitions, and compare code across environments. In the absence of a version control system, DBA_SOURCE is often the only authoritative record of the deployed code.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_SOURCE or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the PL/SQL object
NAME	VARCHAR2(128)	Name of the PL/SQL object
TYPE	VARCHAR2(12)	Object type: PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, TYPE, TYPE BODY, JAVA SOURCE
LINE	NUMBER	Sequential line number within the object, starting from 1
TEXT	VARCHAR2(4000)	Source code text for this line, including any trailing whitespace or inline comments
ORIGIN_CON_ID	NUMBER	Container ID from which the source originated (multitenant only)

Essential Queries

Basic Usage

Retrieve the full source of a specific package body, in line order:

SELECT
    line,
    text
FROM
    dba_source
WHERE
    owner = 'APP_OWNER'
    AND name = 'PKG_ORDER_PROCESSING'
    AND type = 'PACKAGE BODY'
ORDER BY
    line;

Monitoring Query

Search across all schemas for source code lines containing a specific pattern — for example, hard-coded connection strings, schema names, or deprecated function calls:

SELECT
    owner,
    name,
    type,
    line,
    TRIM(text)                                  AS source_line
FROM
    dba_source
WHERE
    UPPER(text) LIKE '%HARD_CODED_VALUE%'
    AND owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
ORDER BY
    owner,
    name,
    type,
    line;

Combined with Other Views

Find all PL/SQL objects that reference a specific table in their source code, then cross-reference with DBA_DEPENDENCIES to confirm the compile-time dependency exists:

SELECT DISTINCT
    s.owner,
    s.name,
    s.type,
    o.status,
    o.last_ddl_time
FROM
    dba_source  s
    JOIN dba_objects o
        ON  o.owner       = s.owner
        AND o.object_name = s.name
        AND o.object_type = s.type
WHERE
    UPPER(s.text) LIKE '%ORDERS%'
    AND s.owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
ORDER BY
    s.owner,
    s.name,
    s.type;

Security Code Audit

Find source code lines containing patterns commonly associated with security anti-patterns — dynamic SQL construction with concatenation, hard-coded passwords, or direct use of AUTHID CURRENT_USER:

SELECT
    owner,
    name,
    type,
    line,
    TRIM(text)                                  AS source_line,
    CASE
        WHEN UPPER(text) LIKE '%EXECUTE IMMEDIATE%'
             AND UPPER(text) LIKE '%||%'        THEN 'Dynamic SQL concatenation'
        WHEN UPPER(text) LIKE '%PASSWORD%'
             AND UPPER(text) LIKE ''':=%''%'    THEN 'Possible hard-coded password'
        WHEN UPPER(text) LIKE '%GRANT%'
             AND UPPER(text) LIKE '%EXECUTE IMMEDIATE%' THEN 'Dynamic GRANT in code'
        ELSE 'Review'
    END                                         AS finding
FROM
    dba_source
WHERE
    owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
    AND (
        (UPPER(text) LIKE '%EXECUTE IMMEDIATE%' AND UPPER(text) LIKE '%||%')
        OR (UPPER(text) LIKE '%PASSWORD%'       AND UPPER(text) LIKE ''':=%''%')
        OR (UPPER(text) LIKE '%GRANT%'          AND UPPER(text) LIKE '%EXECUTE IMMEDIATE%')
    )
ORDER BY
    owner,
    name,
    line;

Object Size and Line Count Report

Count source lines per object type to identify unusually large objects that may need refactoring:

SELECT
    owner,
    name,
    type,
    COUNT(*)                                    AS line_count,
    MAX(line)                                   AS max_line_number
FROM
    dba_source
WHERE
    owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
GROUP BY
    owner,
    name,
    type
HAVING
    COUNT(*) > 500
ORDER BY
    line_count DESC;

Code Change Detection

Compare source code line counts and last DDL time to detect recent changes — useful for post-deployment verification when full diff tooling is unavailable:

SELECT
    o.owner,
    o.object_name,
    o.object_type,
    o.status,
    o.last_ddl_time,
    COUNT(s.line)                               AS source_lines
FROM
    dba_objects o
    JOIN dba_source s
        ON  s.owner = o.owner
        AND s.name  = o.object_name
        AND s.type  = o.object_type
WHERE
    o.oracle_maintained = 'N'
    AND o.last_ddl_time > SYSDATE - 7
    AND o.object_type IN (
        'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY',
        'TRIGGER', 'TYPE', 'TYPE BODY'
    )
GROUP BY
    o.owner,
    o.object_name,
    o.object_type,
    o.status,
    o.last_ddl_time
ORDER BY
    o.last_ddl_time DESC;

Common Use Cases

• Codebase-wide search — When a table is being renamed or deprecated, find every package, procedure, and trigger that references it by searching DBA_SOURCE before any DDL is executed
• Hard-coded value audit — Locate environment-specific values (server names, schema names, IP addresses) embedded in PL/SQL source that should be parameterised before promoting to production
• Post-deployment verification — Confirm that the code deployed to production matches the expected line counts and last DDL timestamps by querying DBA_SOURCE immediately after a release
• Code reconstruction — Recreate the DDL for a procedure or package when the original source file is missing from version control by querying DBA_SOURCE and assembling the CREATE OR REPLACE statement
• Security code review — Identify dynamic SQL built with string concatenation (SQL injection risk), hard-coded credentials, or direct privilege grants issued from within PL/SQL code
• Large object refactoring — Find package bodies exceeding 1000 lines as candidates for decomposition into smaller, more maintainable units

Related Views

• DBA_DEPENDENCIES — Compile-time dependencies; complements DBA_SOURCE by showing which objects reference which without requiring text search
• DBA_OBJECTS — Status (VALID/INVALID) and last DDL time for each object; join to DBA_SOURCE on OWNER, NAME, and TYPE for enriched reports
• DBA_ERRORS — Compilation errors for PL/SQL objects; join to DBA_SOURCE on OWNER, NAME, TYPE, and LINE to see the exact source line that caused a compilation error
• DBA_TRIGGERS — Trigger metadata (enabling status, trigger timing, event); join to DBA_SOURCE for the trigger body source text
• ALL_SOURCE — Session-scoped version showing source for objects accessible to the current user; DBA_SOURCE covers all objects in the database

Version Notes

• Oracle 7: View present from early releases; TEXT column VARCHAR2(2000) in older versions
• Oracle 8i: TEXT column expanded; Java source objects added as an object type
• Oracle 11g: Edition-based redefinition (EBR) introduced; source for editioned objects may appear in EDITIONING_VIEWS and ALL_EDITIONING_VIEWS rather than DBA_SOURCE in some configurations
• Oracle 12c: ORIGIN_CON_ID column added for multitenant visibility from the CDB root; querying from the root returns source for all PDBs with the PDB identified by ORIGIN_CON_ID
• Oracle 19c: No structural changes; Oracle recommends using a proper version control system (Git) for PL/SQL source with DBA_SOURCE as the deployment verification mechanism
• Oracle 23ai: MLE (Multilingual Engine) JavaScript modules stored separately in DBA_MLE_MODULES; PL/SQL units that call JavaScript appear in DBA_SOURCE with CALL to MLE context