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 18.104.22.168.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.
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 22.214.171.124.0 (34160635) 34086870;OJVM RELEASE UPDATE: 126.96.36.199.220719 (34086870) 34133642;Database Release Update : 188.8.131.52.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 184.108.40.206.0 - Production Version 220.127.116.11.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.
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 18.104.22.168.0 (34444834) 34411846;OJVM RELEASE UPDATE: 22.214.171.124.221018 (34411846) 34419443;Database Release Update : 126.96.36.199.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 188.8.131.52.0 - Production Version 184.108.40.206.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