The catproc.sql Script in Oracle AI Database 26ai

catalog.sql, covered in the previous lesson, builds the data dictionary views and V$ synonyms. Its close companion, catproc.sql, found in the same $ORACLE_HOME/rdbms/admin directory, handles everything related to PL/SQL: the built-in packages every Oracle database ships with, the dictionary views that describe PL/SQL objects, and the public synonyms that make both conveniently accessible. Together, catalog.sql and catproc.sql are the two components that build a complete, queryable, PL/SQL-capable data dictionary.

If you completed Lesson 7's hands-on step for the COIN database, you've already run catproc.sql — without necessarily realizing it. This lesson covers what it actually built, why you don't need to run it again right now, and how to run it on your own the day you do need to.

catproc.sql is best understood as a driver script: opening it, you'll find it mostly calls a series of other, more focused scripts, some of which in turn call further scripts of their own. The end result, once everything finishes, is a substantial addition to the data dictionary. Highlights include:

1. The DBMS_STANDARD package — a foundational package that defines much of the standard PL/SQL environment itself
2. PL/SQL-related dictionary views such as DBA_SOURCE, USER_TRIGGERS, and ALL_TRIGGER_COLS
3. A large number of other built-in packages, including DBMS_SESSION, DBMS_UTILITY, and DBMS_SYSTEM
4. Public synonyms for all of the above, so you can reference them directly rather than through fully qualified names

A few of these are worth a closer look, since you'll use them regularly as a DBA:

• DBMS_SESSION gives you programmatic access to ALTER SESSION and SET ROLE from PL/SQL, useful for setting preferences and security levels from within stored code rather than requiring a separate SQL*Plus command. It runs with the calling user's privileges, not SYS's, so it behaves predictably no matter who invokes it.
• DBMS_UTILITY provides a grab-bag of genuinely useful utility routines. One caveat worth knowing up front: a handful of its older subprograms — GET_PARAMETER_VALUE, ANALYZE_PART_OBJECT, and GET_DEPENDENCY — have been deprecated since Oracle Database 12c Release 12.2. If you run across them in older reference material, use V$PARAMETER directly instead of GET_PARAMETER_VALUE, and DBMS_STATS instead of ANALYZE_PART_OBJECT. There's no direct replacement for GET_DEPENDENCY; query the dictionary views directly instead.
• DBMS_SYSTEM is an internal, largely undocumented package used by Oracle itself and by other supplied packages — for instance, DBMS_USERDIAG relies on it under the hood for PDB-level tracing. You won't call it directly often, but its presence underpins other tools you will use.
• DBA_SOURCE, USER_TRIGGERS, and ALL_TRIGGER_COLS follow the same DBA_/USER_/ALL_ prefix pattern covered in Lesson 7: DBA_SOURCE shows the source text of every stored object in the database, USER_TRIGGERS shows triggers you own, and ALL_TRIGGER_COLS shows column usage across triggers you can access.

As with catalog.sql, it's worth opening catproc.sql and reading through it — you'll get a real sense of how much of Oracle's PL/SQL environment is itself built with PL/SQL. But treat it the same way: read it, don't modify it.

Here's the part that matters most for the COIN database specifically. In Lesson 7, building the data dictionary for a manually created CDB meant running catcdb.sql, not catalog.sql directly. That script's own documentation describes its scope as building "data dictionary views, synonyms, and PL/SQL packages" in the CDB root — and that phrase covers exactly the two components discussed across these two lessons: catalog.sql handles the views and synonyms, catproc.sql handles the PL/SQL packages. catcdb.sql runs both, together, in the correct order, in a single pass.

Practically, this means there's nothing left to do for COIN right now. If catcdb.sql completed successfully back in Lesson 7, catproc.sql's work is already done. What's left in this lesson is understanding what it built (above) and knowing how to run it — and its faster cousin, catpcat.sql — on your own for situations catcdb.sql doesn't cover.

It's worth knowing all three approaches, since which one applies depends on the situation:

1. catcdb.sql — the CDB-root-aware wrapper covered in Lesson 7. Use this when creating a new CDB manually. This is what built COIN's dictionary.
2. catalog.sql and catproc.sql, run separately and sequentially — the traditional, granular approach. Useful when you specifically need to (re)run just one of the two, or when working outside a full CDB-creation context.
3. catpcat.sql — runs catalog.sql and catproc.sql together as parallel processes rather than sequentially, which can noticeably speed up the work. Unlike the other two, it isn't invoked directly from SQL*Plus with an @ command; it must be run through the catctl.pl program instead.

For a routine manual CDB build like COIN, option 1 is what you actually want, and it's already done. Options 2 and 3 become relevant later — most commonly after applying a patch, during troubleshooting, or if you're deliberately working below the CDB wrapper to understand or control each component individually.

If you do need to run catproc.sql (or catalog.sql) directly, the connection method is the same one used throughout this module. The old CONNECT INTERNAL syntax is long retired; connect as SYSDBA instead: Confirm the database is open before running anything: If it isn't, start it first with STARTUP;. Then run the script directly by path: The ? substitution variable resolves to ORACLE_HOME, exactly as it does for catalog.sql.

The scenario where you'll actually run catproc.sql by hand — outside of catcdb.sql's automatic handling — is typically after a patch or upgrade, or during troubleshooting when specific PL/SQL components have gone invalid. When that day comes, a systematic approach saves you from surprises partway through:

1. Back up first. Before running any operation of this scope, make sure you have a recent, restorable backup:

   RMAN> BACKUP DATABASE;

2. Confirm version compatibility. The catproc.sql version must match your Oracle database version. Running a mismatched script can leave you with errors or, worse, a partially completed set of components.
3. Plan for downtime. This is not a fast operation on a production-sized database. Schedule it for a maintenance window, not the middle of a busy afternoon.
4. Run it:

   sqlplus / as sysdba
   SQL> @?/rdbms/admin/catproc.sql

5. Monitor while it runs. From a second session, you can check on progress:

   SELECT * FROM V$SESSION WHERE PROGRAM LIKE '%catproc%';

   and watch the alert log for anything unexpected:

   tail -f $ORACLE_BASE/diag/rdbms/<your_db_name>/<your_db_instance>/trace/alert_<your_db_instance>.log

6. Check for invalid objects afterward.

   SELECT object_name, status FROM all_objects
   WHERE object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION')
   AND status = 'INVALID';

   If anything turns up invalid, recompile:

   EXECUTE DBMS_UTILITY.compile_schema(schema => 'SYS', compile_all => TRUE);

7. Refresh statistics if performance looks off afterward.

   EXECUTE DBMS_STATS.gather_schema_stats(ownname => 'SYS');

catproc.sql updates a substantial share of the database's PL/SQL infrastructure, which is exactly why it matters after upgrades and patches — and exactly why the backup-first step at the top of this list isn't optional.

Since catcdb.sql already handled this for COIN back in Lesson 7, today's exercise is verification rather than execution. Connect and confirm a few of the components discussed above are present and valid: All four packages should return with a status of VALID, and both view queries should execute without error — confirming that catproc.sql's components are in place and the data dictionary is fully built.

• Don't modify catproc.sql, catalog.sql, or any of the scripts they call. These are Oracle-maintained; changes can leave the dictionary in a state that's difficult to diagnose.
• Remember catpcat.sql exists for the day you need catalog.sql and catproc.sql run together but faster than sequentially — just remember it goes through catctl.pl, not a direct SQL*Plus @ call.
• Keep the standalone checklist above bookmarked rather than memorized — you'll reach for it after patches and upgrades, not during routine database creation.
• Watch for the deprecated DBMS_UTILITY subprograms if you're working from older code samples or documentation; use the modern replacements noted above instead.