Monthly Archives: February 2019

Party like it’s 1979!

After 120,000+ track miles on my Lotus Exige, I upgraded to a Radical SR3. (Reason: The Exige was too big and heavy. Reason for the Exige upgrade in 2006: The Miata I was driving was too big and heavy. It’s going to be tough going lighter than the Radical, but it is theoretically possible…) I still had the Exige sitting in the garage and, in theory, I’d be able to drive it when I needed to drive a car. In practice, I ended up driving my F350 back and forth two nights a week where I teach Oracle DBA courses as a way to give back to the community. (The rest of the time I work from my home office.)

Stacey and I decided that it would probably be better for the Exige to go to a new home since we were not really using it anymore. (Stacey daily drives and instructs in a 911). A fellow instructor purchased it, fixed it up a bit and turned it over to someone else. He already had an Exige that he tracked quite a bit and built a few times over, so he had the experience to fix my old car up. It’s nice to know that someone else is using the Exige as it is a truly great car.

However, this meant that I had no choice but to drive the F350 around. It’s an old truck (year 2000) with a lot of miles (190K+) which really isn’t that big of a deal to me. However it is a long bed/dually, so it is not that fun to park. I decided that I should get another car just to drive around in. The smart choice would have been to pick up an old Honda Civic or something for a few K. Who really wants to be smart though…

Enter another track friend, Mark. Mark had a bit of a car problem (too many cars, according to his wife at least) and he had a solution to my dilemma:

1979 Blue Bird Trans Am

That’s a number’s matching, 6.6L 1979 Bluebird Trans Am with a four-speed.

After getting approval from Stacey that this would be OK, Mark and I traded my cash for his car.

It’s been a bit of an adventure driving around in a 40 year old car, but it’s been fun.

I just had the original(!) driver’s seat cushions replaced with new foam by Nikito’s Upholstery here in North West Austin, and I’m about to go put the seat back in and take her for a drive.

Advertisements

Oracle Broke DICTIONARY in 12.2+ PDBs

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.