Oracle PL/SQL Packages Reference - DBMS & UTL Package Guides

Oracle ships hundreds of built-in PL/SQL packages, but a handful do most of the heavy lifting in day-to-day DBA work. Each guide below covers the procedures you actually use, with working examples and the gotchas the documentation glosses over.

Performance Packages

DBMS_STATS - Gather & Manage Optimizer Statistics — table, index, and schema statistics; preferences; stale stats
DBMS_SQLTUNE - SQL Tuning Advisor — tuning tasks, SQL profiles, and recommendations

Administration Packages

DBMS_METADATA - Extract DDL & Object Definitions — GET_DDL for tables, indexes, users, and full schemas
DBMS_REDEFINITION - Online Table Reorganization — move, repartition, and restructure tables without downtime

Data Management Packages

DBMS_LOB - Read, Write & Manage LOB Data — CLOB, BLOB, and BFILE operations with chunked processing

File & Network Packages

UTL_FILE - Read & Write OS Files from PL/SQL — directory objects, file modes, and error handling

Utility Packages

DBMS_OUTPUT - Debug & Display Output — SERVEROUTPUT, buffer management, and production logging alternatives

Common Package Queries

Find what packages are available and who can run them:

-- List all DBMS_ and UTL_ packages in the database
SELECT object_name, status
FROM dba_objects
WHERE owner = 'SYS'
  AND object_type = 'PACKAGE'
  AND (object_name LIKE 'DBMS%' OR object_name LIKE 'UTL%')
ORDER BY object_name;

-- Check EXECUTE privileges on a package
SELECT grantee, privilege
FROM dba_tab_privs
WHERE table_name = 'UTL_FILE'
  AND owner = 'SYS';

Oracle DBMS_SCHEDULER Guide — job scheduling with DBMS_SCHEDULER
PL/SQL Exception Handling — error handling patterns for package code
ORA-06512: At Line - PL/SQL Error Stack — debugging package errors
ORA-06508: Could Not Find Program Unit — invalid package state errors