Lesson 7	Building the Data Dictionary
Objective	Run the scripts that build the data dictionary views against the COIN database.

Building the Data Dictionary in Oracle AI Database 26ai

Now that you have successfully created the COIN database, there are still several housekeeping tasks to complete before it's really usable. Chief among them: the database doesn't yet have a data dictionary in any queryable sense. The tables that hold Oracle's internal metadata exist, but the views you'll actually query — the ones with names like DBA_TABLES, ALL_USERS, and V$INSTANCE — don't. Building them is this lesson's job, and in a CDB-mandatory database like Oracle AI Database 26ai, it takes one extra layer of understanding beyond simply "run a script."

What catalog.sql Actually Creates

At the center of this process is a script named catalog.sql, found in the $ORACLE_HOME/rdbms/admin directory. Its job is to define the data dictionary views used to obtain information about the database. In a nutshell, catalog.sql defines:

Public synonyms for all the V$ views
The V_$ views (the base views underlying the V$ synonyms)
The DBA_ views
The ALL_ views
The USER_ views
Comments on all of the above views, and on their columns

It's instructive to open catalog.sql and read through it — you'll learn a great deal about how Oracle's data dictionary is actually assembled. But whatever you do, do not modify what Oracle has written there.

That description of catalog.sql's contents hasn't changed in decades and remains accurate. What has changed, and what matters for a hands-on database administrator today, is that you rarely invoke catalog.sql directly anymore. Instead, you run a script that runs it for you — along with several others it depends on.

Why You Run catcdb.sql, Not catalog.sql, By Hand

If you create a database with the Database Configuration Assistant (DBCA), Oracle runs catalog.sql for you automatically as part of database creation. But this course builds the COIN database manually with a scripted CREATE DATABASE statement, which means nobody has run catalog.sql on your behalf — you have to do it yourself.

Here's the part that trips people up coming from older material: the command you actually run by hand isn't @?/rdbms/admin/catalog.sql. It's catcdb.sql, a single top-level script that installs every component required by a CDB — catalog.sql, catproc.sql, and the rest — together, in the correct dependency order, in one pass. Since Oracle AI Database 26ai requires every database to be a container database, catcdb.sql is written specifically with that CDB structure in mind; running catalog.sql alone would leave other required CDB components uninstalled.

Before running catcdb.sql, it's worth setting three environment variables so the script doesn't have to prompt you interactively:

export CATCDB_SYS_PASSWD=your_sys_password
export CATCDB_SYSTEM_PASSWD=your_system_password
export CATCDB_TEMP=TEMP

With those set, connect as SYSDBA and run the script:

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

If you skip setting the environment variables, catcdb.sql prompts you for everything it needs as it runs — a log directory, a log file name, and then the SYS password, SYSTEM password, and temporary tablespace name:

Enter value for 1: /tmp
Enter value for 2: create_cdb.log
Enter new password for SYS: ********
Enter new password for SYSTEM: ********
Enter temporary tablespace name: TEMP

Either way, catcdb.sql takes a while to complete — it's doing considerably more work than catalog.sql alone would. Once it finishes, your CDB root has a fully populated data dictionary: the DBA_/ALL_/USER_ view sets, the V$ performance views and their public synonyms, and the PL/SQL packages that depend on them.

The CDB Question: Root, All PDBs, or Just One?

catcdb.sql answers a narrower question than it might first appear to. It builds the data dictionary in the CDB root, once, at database creation time. It does not, by itself, give you a general-purpose tool for re-running individual component scripts later against specific containers — and eventually, you will need one. A common example: you install an additional Oracle option after initial creation, and its installation instructions tell you to run a particular script. Do you run it just in the root? In every PDB? In one specific PDB?

For that situation, Oracle provides catcon.pl, a Perl utility built specifically for running Oracle-supplied SQL scripts against one or more containers of a CDB. Unlike a plain SQL*Plus @script.sql, which only ever touches whatever container you happen to be connected to, catcon.pl can target the root, every PDB, or a named subset — and it runs the script in each container in the correct order, producing a separate log file per container so you can confirm nothing failed silently. Here's an example that runs a script (catblock.sql, in this case) across every container in a CDB:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
  --usr SYS --script_dir $ORACLE_HOME/rdbms/admin \
  --log_file_base catblock_output catblock.sql

You won't need catcon.pl for this lesson — catcdb.sql already handles the CDB root correctly on its own during initial creation. But it's worth knowing it exists, because "which container does this script need to run in?" is a question you'll be asking regularly once COIN is a working, multi-PDB database rather than a freshly created one.

Connecting to Run These Scripts

Older Oracle material sometimes references two connection methods that no longer apply. The first is svrmgrl, Oracle's original command-line administration tool, which is long deprecated. Its functionality was absorbed into SQL*Plus years ago, and more recently into SQLcl, Oracle's modern, actively developed command-line client. If you're curious how Oracle's connectivity and command-line tooling got from svrmgrl to where it is today, the evolution of Oracle network tools lesson covers that history in detail.

The second is CONNECT INTERNAL, an old SQL*Plus connection syntax that has likewise been retired. The modern equivalent, and the one used throughout this course, is:

sqlplus / as sysdba

If you're connecting remotely rather than from the database server itself, specify the connection identifier:

sqlplus sys@your_tns_alias as sysdba

Before running catcdb.sql, it's worth confirming the database is actually open:

SELECT status FROM v$instance;

If it isn't, start it first with STARTUP;. Both SQL*Plus and SQLcl support all of the commands in this lesson; SQLcl additionally offers command history, tab completion, and built-in support for several modern output formats, which makes it a reasonable default for day-to-day interactive work even though the scripts themselves run identically either way.

The Three View Sets: DBA_, ALL_, and USER_

Once catcdb.sql has run, it's worth understanding the shape of what it built. Data dictionary views are typically grouped in sets of three, distinguished by prefix:

DBA_ — shows every relevant object in the entire database. These views are intended for administrators only. For example, DBA_OBJECTS lists every object owned by every user.
ALL_ — shows objects the current user has access to, whether through ownership or through granted privileges and roles.
USER_ — shows objects the current user owns. These views omit the OWNER column entirely, since it's implied to be whoever is running the query.

Not every set has all three members — there's a DBA_LOCK view, for instance, but no corresponding ALL_LOCK view. A simple query to confirm the dictionary is populated and working:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
ORDER BY OWNER, OBJECT_NAME;

catcdb.sql also creates public synonyms for most of these views, which is why you can query DBA_OBJECTS directly instead of some fully qualified underlying name. If you're writing application code that queries the dictionary, Oracle recommends referencing the public synonym rather than the underlying object directly — synonyms are considerably more stable across Oracle releases than the objects behind them.

Hands-On: Building the Data Dictionary for COIN

With COIN already created from earlier in this module, build its data dictionary now. Set the three environment variables, connect as SYSDBA, and run catcdb.sql:

export CATCDB_SYS_PASSWD=your_sys_password
export CATCDB_SYSTEM_PASSWD=your_system_password
export CATCDB_TEMP=TEMP

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

The script will take some time to complete — longer than catalog.sql alone would, since it's installing every required CDB component, not just the dictionary views. When it finishes, confirm the dictionary is populated by querying DBA_OBJECTS or checking that a familiar view like DBA_USERS returns results. If either query fails or returns nothing, double-check that catcdb.sql actually completed without errors in its log file before moving on.

Additional Considerations

Never edit catalog.sql, catproc.sql, or catcdb.sql. These are Oracle-maintained scripts; modifying them can leave your data dictionary in an inconsistent state that's difficult to diagnose later.
Use public synonyms in your own code rather than referencing dictionary views by their fully qualified names, for the release-to-release stability reason noted above.
Remember catcon.pl exists for the day you need to run a component script against specific PDBs rather than the whole CDB — you won't need it today, but it's the right tool when that day comes.
Keep a log file from any of these scripts (catcdb.sql included) until you've confirmed the dictionary is fully populated and queryable. If something goes wrong partway through, the log is usually the fastest way to find out where.