Oracle Broke DICTIONARY in 12.2+ PDBs

2023-01-06 Update: There is now also a fix for 19.17. Patch 34754803: REGRESSION TRACKING BUG FILED FROM MERGEREQ

2023-01-05 Update: There is a fix for 19.16! Patch 28998679: COMMENTS MISSING FROM DICTIONARY IN PDB

But that fix ONLY appears to work in 19.16.  When I tried to apply the patch to my 19.17 database, it told me that I was missing the 19.16 patch. So I’m off to log a bug against the patch and to see if we can get the patch into the actual database patches…

2019-08-09 Update: Still broken in 18.6 on Oracle Cloud.

When Oracle introduced pluggable databases in Oracle 12.1, they were supposed to be indistinguishable from non-pluggable databases as far as clients were concerned. Basically, this was true. But one thing that has since broken, at least in 12.2 and higher (confirmed in 18.3), is the comments on the DICTIONARY view.

I (and my students at ACC) use the DICTIONARY view or DICT for short quite a bit as it’s a quick way to find the data dictionary view that you are looking for.

DICTIONARY (or the DICT synonym) is great. It’s got two columns, TABLE_NAME and COMMENTS. Comments are very useful to search through (make sure to use UPPER as they are mixed case) because sometimes the table name can be a bit opaque.

The entry for DICTIONARY is pretty clear:

select * 
  from dict 
 where table_name = 'DICTIONARY';

TABLE_NAME COMMENTS 
DICTIONARY Description of data dictionary tables and views

The above is exactly what you get when you are connected to the root container (CDB$ROOT) of any version of a multitenant database.

However, if you connect to any pluggable database other than version 12.1 (12.1 worked as expected), you’ll get the following for the same query:

TABLE_NAME   COMMENTS   
DICTIONARY   (null)

It turns out that in 12.2 databases and higher, there is only a single comment in the DICTIONARY view when you are connected to a pluggable database:

select * 
  from dict 
 where comments is not null;

TABLE_NAME         COMMENTS 
DBA_CONTAINER_DATA Describes default and object-specific CONTAINER_DATA attributes

And, interestingly enough, in a PDB, DBA_CONTAINER_DATA has no rows! So the only comment in the DICTIONARY view in a PDB is for a view that has no data in PDBs. Obviously, someone dropped the ball here.

In a 12.1 pluggable database, you’d get a LOT of comments in the DICTIONARY view:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 64bit Production

SYS@devpdb > select count(*) from dict where comments is not null;

COUNT(*)
----------
2882

I pointed this out to Oracle Support back in August of 2018. After first not believing me and asking me what I might have done to my data dictionary, and claiming that they couldn’t reproduce it, and then admitting that they could reproduce it, a bug was logged.

Currently, they are here:

“The bug is at Sev2 and will be worked upon by Dev as per the priority.”

The bug number is 28998679.

You can make the DICTIONARY view work in PDBs by creating a database link to the root container and getting the comments from there. Obviously (I hope), updating the actual view name (DICTIONARY) and/or public synonym (DICT) wouldn’t be supported, so you should create your own public synonym and/or view. Of course, I’m not sure how you’d get developers to use it since they are expecting DICTIONARY/DICT to just work…

As an aside, I came across this when building an APEX application for my students that would let them use an interactive report against DICTIONARY to search for interesting data dictionary views.

2023-01-05 Update

I was able to verify that if you have a 19.16 database, the patch does fix the issue.

[oracle@databaseserver DB:dbacon ~]
$ opatch lspatches
28998679;COMMENTS MISSING FROM DICTIONARY IN PDB
34160635;OCW RELEASE UPDATE 19.16.0.0.0 (34160635)
34086870;OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)
34133642;Database Release Update : 19.16.0.0.220719 (34133642)

OPatch succeeded.

[oracle@databaseserver DB:dbacon ~]
$ sql sys@orcl as sysdba


SQLcl: Release 22.4 Production on Thu Jan 05 21:44:41 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Password? (**********?) *********
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SYS@orcl AS SYSDBA > select count(*) as "Total Comments" 
                       from dict 
                      where comments is not null;


Total Comments
______________
          4453

However, this patch will then block you from upgrading to 19.17.

2023-01-06 Update

There is now also a fix for 19.17. If you apply patch 34754803 then life is good:

[oracle@databaseserver DB:dbacon ~]
$ opatch lspatches
34754803;REGRESSION TRACKING BUG FILED FROM MERGEREQ
34444834;OCW RELEASE UPDATE 19.17.0.0.0 (34444834)
34411846;OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846)
34419443;Database Release Update : 19.17.0.0.221018 (34419443)

OPatch succeeded.

[oracle@databaseserver DB:dbacon ~]
$ sql / as sysdba


SQLcl: Release 22.4 Production on Fri Jan 06 15:37:33 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SYS@CDB$ROOT AS SYSDBA > select count(*) from dict where comments is not null;

   COUNT(*)
___________
       4458

SYS@CDB$ROOT AS SYSDBA > alter session set container=orcl;

Session altered.

SYS@CDB$ROOT AS SYSDBA > select count(*) from dict where comments is not null;

   COUNT(*)
___________
       4459