Experimenting with DBCA-scripts of Oracle 26ai: Create Database.

 TL;DR: I've got generated scripts from DBCA (see previous blogs), and I experiment with them. First I morf the initial Create-statement and script into something that is easy to test+repeat. I learn to clean up my own mess. And then I discuss the actual "create database".

image: Creating, Testing, and cleaning up the pieces.

I needed a thorough cleanup-routine to really delete all artifacts of a database for repeat of various tests (and it is not perfect yet...)

-- Background: Got scripts from DBCA, now tweak and re-use them.

My end-goal is still to obtain scripts that can create a fresh database. Scripts that I can take to any (linux) system where oracle is installed, and Create a Database. 

It is still a "Hobby Project" and I would not (yet) recommend to take this to your PRODuction system. But anyone should be able to take the scripts and modify them without too much hassle wherever they see fit.

I will use a base-set generated from the DBCA, under the assumption that those are "complete and usable". I already have created a database from them. I will now morf the scripts into something that I can easily re-use.

For portable, generic, scripts, I want first to avoid hard-coded paths. Furthermore, I want "minimalistic" parameters, minimal code, and use defaults wherever I can. 

Of course I want scripts that I can quickly adapt to different CDB-names, add PDB-additions, and modify them for various "purpose databbases". 

And dont forget: I need a cleanup-system in case my tests need repeating or go horribly wrong: some easy way to "wipe clean". On containers, I can always just re-create the container, but on actual servers I may need to do a cleanup.

-- The Create script: define ORACLE_SID and make it re-runnable.

My first attempts worked OK, and I was able make the very basic statement work:

    SQL > Create Database C001;

See previous blogs (link) for the story. But I needed more.

To obtain a complete, usable database with all the required components, I asked for the help of the DataBase Create Assistant. With the proper choices the DBCA from v26ai was able to generate the scripts. And those scripts created a brand new database, including all the "components". Report in Previous blogs on how to generate - (link to generate) and my initial comments on the generated scripts (link to remarks).

Those scripts can still be fetched from the tar file, link below.

In this blogpost, I will use the generated "create" script, and experiment with the very start of the process: the Create itself. Slowly, I'm developing a more generic, less "hardcoded", more portable version of the scripts.

Originally, the DBCA provided a script that calls everything: C003.sh. I've re-worked that to a sh-script where the base-name is the SID. I expect to "test" this a lot, so I better make it easily-runnable and re-runnable.

I came up with C004.sh as a main-controlling script for my next database:

This way I grab the SID, upper and lowercase to access the subdirs in diagnostic-dest. And I will consider to define items like DATA_DEST, BASE, HOME, and others as well at the top of the script.

Next is the "editing" of the init-ora file. This can happen inside the sh-script to allow the DB_NAME and a few other parameters to be set. Let's use an <<EOF for that:

I'll just create the init file on the fly, from the same script. Keep everything in one place for now. A more detailed discussion of the init-file contents will have to wait. Maybe later.

-- The actual Create Database, adjusted from the DBCA.

After just a little formatting, I inserted the statement from CreateDB.sql in my script:

This statement is still the same one as generated by DBCA, but it uses the ${ORACLE_SID} instead of the hardcoded value. Otherwise it is un-altered (as of yet). I have used this in various tests to try some variations.

Once I could easily re-run this statement, I started experimenting with some modifications.

-- Initial sizing of datafiles and logfiles.

From looking at the previously created C003 (previous blog), I know that the nr of "Resize operations" during create was 200+. Mostly because the initial fileSIZE and the NEXT specifications were too small, especially on the SEED-datafiles. I'll add larger values to the next version of the create-statement. Check C005.sh linked below.

For the redo-log files, I preferred 500M size or even larger to reduce log-switches during busy times. I think there is very little downside to Large redo files. If you worry about delay between log-switches, or delay in archive-generation: use an archive_lag_target and re-think your backup- or DG-settings. Maybe Discuss some other time.

I also managed to include size-specifications for the SEED: system and sysaux, both to eliminate the resize-ops during the running of catalog and other scripts, and to allow future sizes should someone want that. All for the next version of the script. 

The qualification: Extent Management Local: if I leave that out, the SYSTEM tablespace becomes DICTIONARY managed. Not a big problem, but rather old-skool. If you want to have your SYSTEM tablespace as LMT, you have to specify this explicitly. 

I suspect I know the reason why the default for SYSTEM is still Dictionary: If you have older databases with Dict-magaged tablespaces that you want to plug in, you need the SYSTEM of the CDB to be Dictionary managed as well (note: maybe check this, Dict-managed is really old...). 

-- Various MAX-values, affecting the Controlfile - Let's test.

I was slightly worried when I noticed the MAX-parameters in the generated script.  You can find them via V$CONTROLFILE_RECORD_SECTION. In older versions, those MAX-values used to be imposed as "Limits" and sometimes bit you when you didnt expect. Anyone who ever had to "re-create a controlfile" under stress will remember. Nowadays, they mainly determine the space that gets "reserved" in the controlfile, and the space will "expand" automatically when needed. 

I did a quick test: 

First I re-created a database with no MAX values specified and I checked the values in V$CONTROLFILE_RECORD_SECTION using the script chk_ctlrecords.sql (link below).

Those initial, default values look a little low to me. Could that pose a problem?

(Anyone spot the anomaly? something to look into? Maybe Later)

So I created a shell-scrpt to add redo-files, add threads and add tablespaces (e.g. datafiles). I tried to "cross those limits" and see what happened with this test: add_files.sh (link to script below).

And after adding a load of files, I re-check the values:

The values have simply gone up. No problems encountered. That looks Good to me.

Actually, if you have run the catalog script, the alert-log will contain a few messages about expanding the ctlfile:

Oracle silently "Expanded the controlfile" and you were Informed via the alert-log. NB: Without a catalog, that message shows up as an Error with a mention of some missing object - feel free to check for yourself.

From reading the RTFM, you may want to keep the MAXLOGHISTORY if you are running RAC. Maybe something for another test.

My first conclusion is: Those MAX values are there for information, but they dont seem to pose a problem anymore. And I will remove them to simplify my create-statement a little further.

-- Leave out some more options...? Maybe Not.

Since I like "minimal scripts", I have considered to also leave out the spec of SYS and SYSTEM passwords, and the Character-sets.

When I left out the Char-set spec, I ended up with US7ASCII. Not a good idea. 

I found I could leave out the National Character set as it seems to defaults to AL16UTF16 anyway, but I decided to keep it in as reference. Feel free to change those at your convenience... 

You could also leave out the SYS and SYSTEM IDENTIFIED BY. But the RTFM says you will then revert to the very old and well known defaults. To make it look good to your CISO and SecOps ppl, just specify them.

In my case, I usually have the passwords defined in a file that I name "accpwds.sql". During create-time: it mimics the sqlplus-accept command to define the pwds. Just dont upload that file to any git-repo, and dont stick with those values for too long. Hmm, Maybe I'll also mention in a later blog that some of the scripts still can display the pwd-value in the proces-listing, try "ps -ef" to check for yourself.

-- Cleanup, because of all the failed or insufficient (test-) runs...

While building and testing the scripts, and experimenting with the rest of the generated scripts, I often ran into error where files already existed (example:.... ).

Hence I needed a (semi-automatic) cleanup-script as well. This is slightly risky to have laying around: if you accidentally execute that script, it Wipes your Database. Hence semi- and not totally automatic.

The cleanup scripts, an -.sh and an -.sql, find all possible files from the v$ views (the dba_% views only come in after the catalog is created), and generate the rm-commands. They also try to clean up the diag-dest and other file-destinations. Links below.

Feel free to re-use, but be careful. This is not something you want to happen by accident (yes, this happened to me once, in summer of 1996. And I still remember 30yrs later, colleagues HenkG and YuriB, and some customer-ppl will also remember....).

-- Some Questions for Oracle...

Non-critical, but out of curiousity, I will probably try to ask Oracle-folks at some time about the following Items...

1. LMT/Dictionary: Why is the dflt for SYSTEM tablespace still DICTIONARY ? (presumably for compatibility with Really Old tablespaces that need to be plugged in?)  

2. Sizing: Why are the initial tablespaces (SYSTEM, SYSAUX, TEMP, and SYS_UNDO) created with such small values for SIZE and NEXT? They are Very Small on the "naked" create-statement (see previous blog, link) but even on the generated-scripts the values are still low and generate a lot of "resize" events in the alert-log?.

3. Controle-file contents: Why are the MAX-values still specified when these are (probably) no obstacles to a growing databases anymore ? Leaving them out, at least in the generated scripts, would make the create-stmnt more simple, elegant? 

More questions ? dflt charsets? dflt-passwords? maybe later...

Round up, my Create Database is somewhat improved (simplified)

Quite Happy with the new script: it works for me. Have a look at C005.sh (link below)

I have the init.ora and the create-statement in One Single place. I can now easily create databases with other ORACLE_SID-values. And I can add all the other items from the DBCA-generated code such as catalog, scripts for the various components,  and the creation of pluggable-databases. Stay tuned as I continue exploring.

The "cleanup" script to remove files is a little risky, but has proven very useful.

I also know now that some of the definitions at create-time are not Set in Stone (maxfiles...), and I feel confident that I can run with the defaults, rather than having to enter defensively large values at Create-Time. If needed, Oracle will automatically "increase the max" of some of them and the alert-file will show the expand-messages.

Next episode, I want to add the Catalog and run all the other scripts that the DBCA listed. I will try to simplify them a little. Notably to remove some of the hardcoded setting like SID and PATHs.

-- -- -- -- -- End of this blogpost, for now -- -- -- -- --

Appendix 1: Links to scripts and files.

C001.sh : my first example, a single, self-contained script to create a database.

C003_omf2.tar : the generated script from DBCA (with fix for the missing arguments to context.sql...)

C004.sh : a shell script with my "first improvements", runnable. test-able

chk_ctlrecords.sql : list part of the v$contrlfile_record_section.

add_files.sql : Add redo-files, redo-threads and tablespaces to expand controlfile.

mk_rmall.sh : generate rm-script and shutdown-abort the instance.

mk_rmall.sql : sql-file is needed by the mk_rm_all.sh  script (for now...)

C005.sh : my "final" create-statement (for now)

-- -- -- -- -- End of this blogpost, for Real -- -- -- -- --

Oracle 26ai DBCA scripts that (help you to) create a database

TL;DR: Still on my quest to Create an Oracle 26ai Database From Scripts. I'll examine some of the resulting scripts from DBCA.

Image: Critical inspection of a gift, by a geek.

Note: Do not take this too serious. My day-job is more practical (and more meetings). This blog-series is more of a trip back to "the roots of the database". But maybe I learn a few things while I'm digging.

-- Background: Scripts from DBCA, much more complete than my own.

The end-goal is to have scripts that can create a fresh database. My first attempts worked OK, and I was able make the statement "create database" work. Check here (link) for the story.

But to obtain a complete, usable database with all the required components, I asked for the help of the DataBase Create Assistant. With the proper choices the DBCA from v26ai was able to generate the scripts. And those scripts created a brand new database, including all the "components". Report in Previous blog (link).

Those scripts can still be fetched from the tar file, link below.

In this blog, I will examine the generated scripts, make some comments, and start thinking about my own adaptations.

-- The main scripts: C003.sh and C003.sql

The script that runs everything is C003.sh. It creates the necessary directories, sets some env-variables, and kicks off the next script, C003.sql:

The main SQL scripts just calls a set of other, generated, scripts who then do the real work. They are all very similar, and I'll discuss them briefly with some comments and suggestions. 

Note that I already put in a call to "@accpwds.sql" to define the passwords, and thus to avoid having to type into the accept-prompts every time - I'm lazy.

-- The init.ora: Interesting.

Every startup needs an init or an spfile. The one generated by DBCA is interesting on a several points.

Some of the contents of init.ora.

The DB_NAME is specified, and this is Needed. From experimenting, we know that the only parameter required in the init-file is the db_name. Without the db_name, the database wont get created (ora-01502, missing or illegal db_name). 

The DB_CREATE_FILE_DEST is specified, because I told the DBCA to use OMF. From experimenting, I think I can say that only the db_file_create_dest is really needed to determine file-placement.

For testing, and any other "discardable" database, I would probably run without the RECOVERY_DEST parameters, just to save space and capacity and time. Especially with docker-on-laptop situations as myself and some DEVs do.

The DIAGNOSTIC_DEST should probably be set explicitly. If not set, it will point to either ORACLE_BASE or ORACLE_HOME/rdbms/log (where I would not want it). When running in containers, I used to set this value to a mapped volume to have the output always accessible, notably on "broken container". But I am not sure if the extra IO-calls from container to host have an impact. And if they do, whether it is worth the (small?) extra load.

I have a few more (geeky old-skool) remarks on the init.ora that was generated from the DBCA, but I'll save those, maybe for a separate blog sometime (insert future link here). 

The only other thing I want to point out here is the (c) notice : Isnt that a little old ? 

-- The Create-DB, and the specified properties.

The generated statement for "create database" from DBCA is much more elaborated than the one liner that I used for my first experiments (link). Here is the generated file CreateDB.SQL which does the startup (nomount) and then creates the database: 

First thing that struck me: I would do a different, more readable layout. But that is a detail (but will come back in many of the generated files, readable layout was not a priority?).

Notice how the DBCA has taken a much more "controlling" approach when compared to my earlier simple 1-liner:

 - A number of MAX-values specified. They come back in the view  V$CONTROLEFILE_RECORD_SECTION. I'll probably comment on those later.    

 - Explicitly ask for LMTs, via "extent management local" - OK with me.

 - Explicit SIZE and NEXT for the datafiles. Good, this reduces the "resize events" during create, and will make it faster on slow-disk systems (laptops running docker...)

 - Explicit BIGFILE as default, as would be expected from the RTFM anyway?

 - LOGFILEs are now 200M, and larger than the dlft 50M I got in my first attempt. This reduced the number of log-file-switch events during the next create-steps. I tend to make them larger still.

 - Explicit creation of an UNDO tablespace (which may still grow during creation).

 - Explicit Character-set values.

 - ENABLE PLUGGABLE DATABASE, which seems to be the dflt as well.

 - Specify LOCAL UNDO ON - I was a little surprised to find that the dflt for this setting is still "off", but that would make sense for a CDB with many (small) PDBs.

What strikes me as odd is that several of the explicitly specified values seem to be the (current) default settings anyway. In a more simple version of Create Database, I can leave them out and obtain the same result (should test, verify...)

But having them specified Explicitly has educational value: it tells the DBA/user what is happening, should anyone ever read this file...

Note that there is no explicit sizing for the Tablespaces and Datafiles for the SEED datatabase yet. The datafiles for seed (system and sysaux) tend to have a Lot of resize-ops, which can be prevented by creating them larger.

Another remark: I'm not terribly happy with the way the control-files get added to the init-ora file. That went wrong a few times when I used my own SQL*plus sessions. I think I prefer to set the value for control_files in the init.ora myself.

All in all this looked like a usable starting point to do some adaptations of my own.

-- Scripts following after the create: a lot of work gets done.

After the CreateDB.sql, the main script continues and calls some 10+ other SQL scripts to "complete the work". Most of the names are +/- self-explanatory. The contents of those scripts are all similar, and I'll show one:

I've added empty lines between the long, wrapping, host-commands to make it more readable... 

All scripts follow the same pattern:

Firstly they connect as SYS and do a spool-append although some scripts do the spool first, then connect. And some scripts repeat the spool-command multiple times.

Secondly, they all use host-calls to catcon.pl to run scripts that are located somewhere in $ORACLE_HOME. The one exception is the Catalog-script: it uses one call to catctl.pl, but the pattern is the same. The ancient programmer in me would like to simplify this layout.

About the re-connects: Some of the scripts re-connect several times. I am still not sure whether those re-connects are really needed, to create "fresh" connections, or whether they just come from some old code (template, legacy?). But in combining and simplifying the scripts, I dutifully included the re-connects: A fresh connection would never hurt...?

What I didnt like about them: The very long command-lines, the often repeating-pattern, and the hardcoded file-paths. It produces confused-looking code. I'll try to simplify some of that in future versions of my own scripts.

-- Locking users, could be a callable script.

A bit of a no-brainer, to me, was the lockaccount.sql: It does the Exact Same Code on CDB$ROOT and on PDB$SEED (I checked with diff). That can be extracted into a separate script to run twice. Why didnt oracle do that yet? But then again: that is a very small "optimization for the sake of optimizing".

-- Adding PDBs: Repeatable actions, Scriptable.

The creation of the PDB(s) is another repeating pattern. But this activity may have to be repeated several times, whenever a new, fresh, PDB is required.

Each requested PDB came with two scripts: to Create the PDB, and to do the postPDBCreation actions. The scripts differ only in PDB-name and spoolfile-name. And the last script has an "exit" at the end, supposedly to force an exit from SQL*Plus.

Here are the contents of one of the CreatePDB scripts:

The PDBCreate scripts first do the acutal Create. And then some swapping of the Default Temporary tablespace. From the naming of the TEMP tablespaces, it assumes that the original TEMP from the SEED is not (yet) ENCrypted.

The postPDBCreate scripts add a USERS tablespace, and do a datapatch. After that, it just seems to do several re-connects and selects on properties, tablespaces and registry. Not sure why, maybe just to check on the contents of various views (note: Investigate Why those reconnects and queries are done).

I see no reason why the creation of a PDB could not be "scripted" into a single, re-usable script that takes the pdb-name as argument.

Note: in my case, I often add two small PDBs: ORCL and FREEPDB1. Doing this allows scripts that count on those "Traditional names" to connect to known-targets (dont laugh... there are script out there with scott/tiger@orcl still in them). Alternatively, I have sometimes just created services by those names.

-- Round up, and possible follow ups.

Quite Happy with the scripts, They Worked. But I had some comments.

My first comment was the often less than readable layout.

Then there is the fact that they contain hardcoded paths, and are thus only usable on the machine where they were generated, or a system with identical layout. This may be on purpose. Oracle wants to be very sure the paths and files are correct, working, and point to software of correct version (e.g. the perl-version delivered with the same software, and not some other version that happens to be installed)

And some scripts look like they could do with a little work: why do they contain identical-repeating statements ? (probably a quirk from the generating-software).

Next point of attention was the fact that some of the "settings" for the init.ora and the create-statement were actually Defaults. They seem to act as reminders: You Can Set Something Here (charsets, storage-options). I might experiment with these parameters and properties.

The Really Positive outcome is: I have scripts that seem to create a "complete" database: The DBCA has done the RTFM work for me, packed all the components, and provide a Good starting point to compose my own, more "portable" scripts.

My aim is still to obtain scripts with clean, minimal code, and use dflts wherever possible. And then hopefully test-run that on various machines at my disposal.

-- -- -- -- -- End of this blogpost, for now -- -- -- -- --

-- Appendix 1: Links to scripts and files.

C001.sh : my initial attempt, a single, self-contained script to create a database.

C003_omf2.tar : The generated set of scripts from DBCA (with fix for the missing arguments to context.sql...)

-- -- -- -- -- End of this blogpost, for real -- -- -- -- --

Creating a Database: Ask DBCA to help you...

TL;DR: The scripts generated by DBCA work, although some tweaks and caveats apply. But in the end: Yes. They Work!

image: Startup of the DBCA from Oracle 26ai (to generate scripts). 

NB: Running X11 windows in XQuartz on a mac does not always yield nice screenshots, and sometimes the display(-driver) seems to get in the way, see this previous blog (link). Hence I used a camera to snap the screens. With apologies for the image-quality.

-- Background: Scripts to create a database (and using X11).

Since I got on the quest of "Creating a Database from Scripts", I've been moderately successful. I can do:

SQL > Create Database ;

and get a more or less running RDBMS. 

The next steps would be to install things like catalog, catproc, and various (mandatory) components. 

I also found the initial sizes of datafiles and redologs rather small, leading to a lot of messages in the alert-file about significant resource- and time-consuming file operations. 

And maybe I want those fancy "bigfile" tablespaces from the get-go.

Because I didnt feel like ploughing through the RTFM for all the details (link to create database), I asked the DBCA to generate the scripts for me: The Oracle Provided "Assistant" would know how to do this the correct way, right ?

In earlier attempts (link to 2023), when there was only the v23 "FREE" version available, the DBCA would not offer this option (yet). I had to revert to the scripts generated from v21c and adjust them. That worked at the time, but I was never 100% sure if I had everything covered correctly. Now that the version 26ai is General Available, I can do this properly, with the correct version of DBCA. 

This current hobby-project / research was done mostly with v23.26.1 in May 2026 to be precise. And I would expect to re-do this with the version that is supposed to drop any time now and re-verify the findings.

Note however: I am still striving for the "simplest possible scripts", preferably scripts that I can port to any (linux) environment where Oracle 26ai is installed.

-- How I generated the scripts.

My main platform for experimenting are the container-images provided by the oracle container-registry (go into the enterprise-database section - link). I also use/test on the "FREE" images by GVenzl (link). The FREE version does not permit (yet?) to "generate scripts". Hence to re-play this blog, you need the proper Oracle containers. But I do tend to test any generated script on the FREE containers as well.

The DBCA is a gui-tool, running on X-windows (X11). Hence first I had to get X-windows running in my container. I've described that in an earlier blogpost (link)

I'm assuming the vbox-versions still come with a working X11 installed, havnt verified that recently. In case the X11 is missing in your vbox: the install from  previous blog applies to the vbox images as well.

I repeated + tested some of this work on the "FREE" containers provided by Gerald Venzl. Those images are completely hassle-free, license-free, and are very easy to pull and run. Check them if you need something for testing, or any other activity where you quickly need 1 or more database(s) to spin-up.

Without too much ado (see earlier blog), yum was "correctly configured" and I could install X11. I also often use yum pick up a number of other goodies like procps, rlwrap and git. 

Once you get X11 and have "the eyes" (or xterm) running from inside the container, you are Good. 

Let's run the DBCA...

-- Running DBCA, and choosing "generate scripts" 

The screenshot above shows the DBCA (lowercase), and we opt to create a database. On the next screen, we choose "Advanced":

There we go. And once in the advanced mode, we choose "custom":

Custom seems to be the only option that really generates "Create scripts". The other options just generate "recover-scripts" that recover (bakups of) templates.

For my exercise, I accepted a lot of the defaults. I didnt want to experiment much yet. But feel free to explore.

I picked a name (C003), I chose "Local Undo" and I wanted two PDBs:

Two PDBs, just to see how it would handle more than one. Basically to get a nice set of scripts to work with.

For storage: I preferred to choose OMF.

For now, OMF. Reading the RTFM and some experience convinced me that OMF is much more convenient than managing the files myself. But you may have a different opinion, especially if you do not like funny and overly long names (GUIDs) for directories.

In the almost-final screen, I un-ticked the "create", and only ticked the "generate scripts". The actual creation can take quite some time, and I just wanted the scripts:

We do not need the database created for us (yet), but we want the scripts to study and to tweak. So we continue to "finish" and ... Viola.. (yes, Viola, an old aquaintance).

We obtain the scripts in /opt/oracle/admin/C003/scripts:

Those are the scripts that can "Create a Database".

-- Runing the scripts : C003.sh does it all (with 1 fix)

The complete set of script can be run form one shell script: C003.sh. I'll put up a link to a tar-file so you can see for yourself. Feel free to Test:

mkdir /opt/oracle/admin/C003

cd /opt/oracle/admin/C003

tar -xvf /tmp/C003_omf2.tar

cd scripts

./C003.sh 

... and watch the miracle unfold.

Running the script(s) can take up to an hour, or even longer, depending on the speed of your hardware. On an old MBair from 2018, running the container in docker, it takes 2hrs. But on a beefy MBP M5 only about 25min.

Beware: because of bug 37280585, it needs a tweak in the script context.sql: You need to add 4 arguments to the call to catctx. I've fixed that in the tar-ed scripts, so you can just run them on your testbed if you want to try. 

If you do test it: Let me know what you find on your system: Any issues? How many directory-paths did you have to edit? How long did the running take ?

-- First Inspection: looks OK. But some comments...

After creation, I did some inspecting, notably with scripts chk_crdb1.sql and chk_early.sql. From the initial checks, I noticed several things:

I now have (mostly) BIGFILE tablespaces, by default, as confirmed by my self-created tabelespace "ABC".

The sizes of file and tablespaces are much larger than in my previous blogs about "create database" (link). But there are a still quite a lot of "resize" events in the alert-log. I can probably fix that.

All components are VALID, except RAC: that was expected. The container-image I use is not suited for RAC. NB: XDB is Deprecated.

There were no Invalid Objects in the generated database.

So far I am moderately happy with the scripts. And they will serve as "examples" for me to simplify a little more. I'll probably discuss them in some follow-ups. Ultimately, I want to end up with something more "portable": a set of scripts I can easily run on any linux machine to create a database.

-- Roundup so far - We have Scripts. Let's tweak them (in next blogs)

The scripts worked. And I feel confident that I can work with them. I have a lot of remarks though:

1) Those scripts will only work on the machine (or platform or container) that generated them or a system with a Very Similar layout. The SID and the file-paths are hardcoded in many places. I would like some more Generic, Portable version. I might come back to this in next blogs.

2) The scripts are not very readable (to me). I would reformat and re-write a few parts before I was more happy with them (next blogs...). More trendy kids would hand them to an AI and ask for suggestions. I just used eyeball-mark-one, some common sense from hard-gained experience, and some of my "personal preferences".

3) For some of the initial configurations (the init.ora, and the initial sizes of files) I would do some things differently myself. I'll tweak some and blog it..

4) File-paths: I'm choosing OMF notably to avoid/reduce hard-coded paths in the scripts. Note that if we choose "storage location from template", all scripts will contain hardcoded file-paths which makes the scripts less "portable" as we would have to edit those paths for every new database we want to create. Reading the RTFM and some experience convinced me that OMF is much more convenient than managing the files myself. But you may have a different opinion, especially if you do not like funny and overly long names (GUIDs) for directories. 

There is more. Later.

But despite all my stubborn comments, I'm already a happy bunny with those scripts.

-- -- -- -- -- End of this blogpost, for now -- -- -- -- -- 

Appendix: Links to files.

C003_omf2.tar.gz : the generated scripts (with fix for bug 37280585). Try them !

chk_crdb1.sql : script used in previous create-experiments.

chk_early.sql : additional script for 1st check of a newly created database.

-- -- -- -- -- End of this blog, For Real -- -- -- -- -- 

Create an Oracle 26ai database, from minimalistic script (maybe dont)

TL;DR: My Minimalistic script to create a database Worked, sortof (link to previous blog). Now let me Examine the first results and make some comments.

And Please take note: I do this "scripting" mostly for Fun and to Explore. This is not a Serious Suggestion to always create your own database or to ignore the RTFM. 

This is how I felt after some minimal-scripted "create database" attempts:

Image: Nice abakus, but that thing isnt going to work for us...

-- Background : trying to create a database, from scripts.

In the previous post, I showed that this command (link) can still be used:

SQL > Create Database ; 

It was actually very satisfying to see that command run:

That Worked !

But the resulting database was far from "finished". In this post I want to show some of the findings. And maybe make some recommendations to Future Colleagues and to Oracle on how to (not) use the defaults for this command.

-- So we have created this new database. Let's Examine...

Using the slightly altered script, now called C001.sh, I've repeated the work from the previous post, and added some items and SQL-statements to the script (link below).

Without pretending to be "complete", I want to point out a few things and comment on the results.

First, I had to find out What exactly I had created with that very-basic command and only 1 parameter in the pfile. For this, I used an old script: chk_crdb1.sql (link below), and it produced this:

Voila, our database is there. It is running in NO-ARCHIVELOG (which is efficient), and so far it only has the the PDB$SEED attached to it. Let's check the files that make up this database:

We find... 

A single Control-file. Basic, and fine for now - but you should multiply your control-files in Prod.

Two log-files. They are OK, but I used to add more and bigger ones to reduce the "switch-logfile" events (old habit, I know...).

The datafiles for tablespaces SYSTEM, SYSAUX and SYS_UNDO. I did like the short names.

Notice from the previous check: No BIGFILE tablespaces (yet) ? And those files are rather small in size. The alert-file contains a lot of resize-operations already. That seems like a waste of time to me? 

Possibly, the the old "smallfile" type is kept here as dflt for backward-compatibility. And remember, this is only the CDB and the SEED... I would assume any capable DBA to make a choice here, smallfile/bigfile depending on the situation.

But no TEMP files (no rows selected...) and no (default-)TEMP-tablespace? Spotting that as an ommission, I created one...

It appears I can create the TEMP tablespace(s), but it needs a "destination" (or a file-spec).  It feels as if Oracle does not want to place your TEMP-file(s) in the same default as your regular datafiles unless you specify it.

And the initial size is an impressive 100M. You can even specify "bigfile" here if you want that (do try!). 

Spoilers: in the scripts generated by DBCA, the bigfile-option and the temp-tablespaces are covered. And the DBCA produces larger files for all tablespaces.

-- About those file-locations...

On creating a database, you will probably, consciously, place or move your data-, log- and controlfiles somewhere safe and spacious. But a lot of the trace/dump/audit destinations might remain dflt.

Without specifying any parameter, most of the file-destinations end up under the ORACLE_HOME. Understandably, but it presents a risk: if you leave it like that, some of those directories will collect log- and trace-files forever, and that can bite you at some unexpected moment, sometimes years later. I would prefer for most (all) of those parameters to end up in the "diag" destination by default.  

I devised a query to find most of those locations, so I could fix them in the pfile. Check the script info07_files.sql, a development from an old script that I used to check v$parameter. 

The result is a number of parameters set explicitly in the subsequent script: C002.sh. Some will get flagged as obsolete or deprecated but still... That way I feel I have more control over where my files end up. I would point those preferably to either "data" or "diag" destinations, and not keep them under "home". 

(I've been mumbling about a Read-Only O_HOME since ... 2003 or so, and I'm kinda happy Oracle got round to providing for that option since, I think, v18c - different topic, maybe later)

-- Connecting: you need that orapw-file..

To examine this database (a.k.a. geek around inside SQL), I wanted to connect with SQLDeveloper. That, of course, required a password-file. My next version had to include this:

# need a pwdfile, if only to be able to connect SQLDev for inspection

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} \

  password=oracle   \

  force=y format=12

No big deal. I inserted that even Before starting SQL*Plus for the startup-nomount.

Note to self: I had a dflt LISTENER running at port 1521, and the instance had dutifully registered. Way back, I made it a sport to run: 

lsnrctl start 

...without any parameter-file. And that often just worked fine back then, and still does. I might do a topic on that sometime: The Simplest listener.ora is ... No listener.ora (probably not best-practice, but Ideal for Lazy DBAs like me).

And thus, I was able to connect with SQLDeveloper. The tool didnt even complain (yet) about not having "catalog views" - Until of course I tried to open a list of objects. 

To do "more complicated work", it does need the views from "the catalog". Again, no big deal: It Worked Anyway. I could still happily query the existing tables and views from the Query-Window.

SQLDeveloper is still a Very Cool Tool - Tip of the hat to That Jeff Smith and his team.

Here is just one example of what I could query, even on this "proto database":

Notice how SYS already owns 2048 (...2K?) objects. And the other notable presence is VECSYS. Apparently Oracle decided that the Vector-Capability had to go in early and deep. In a serious, well-configured databases, those numbers of objects will be much higher. Do check yours.

There was a lot I stumbled across, but most of it was too nerdy to be of use. I needed to move on if I ever wanted to get to my target of "usable scripts". 

Maybe later, or maybe over a beer with a few geeks sometime...

-- Some improvements to my script: C002.sh

From what I found + learned, I created C002.sh. This script will first create a slightly more complete init.ora file. In the init-file, I wanted to at least put the the various trace-, dump-, log- and data-files in "proper" locations under ../oradata, ../oracle/diag, and ../oracle/admin directories. 

And I wanted a larger chunk of memory, as the dflt half-GB seemed extremely small to me. I've kept them humble: the values in that script should be low enough still work on the FREE-version.

Spoiler: The adjustment of file-sizes for datafiles and logfiles will have to wait, because I already know the "generated" script from DBCA will create much larger datafiles from the get-go. 

Warning: in my C00n.sh scripts I have the scripts "compose the inti-file" on the go. This keeps the script as an "all in one". But beware of this script over-writing an existing pfile!

-- Wrap up: List of Findings (so far..)

I've listed and numbered some items that I found "interesting" and possibly "relevant"...

1. Always have a password-file, an orapw$ORACLE_SID. Not only bcse "security" but also because it allows you to connect from "elsewhere". For inspection of x$views, v$views, and other SQL-exposed information, the use of SQLDeveloper or a similar tool to easily move around in the SQL-space is just more efficient.

2. Files end up in $ORACLE_HOME/dbs, and then some. Setting db_create_file_dest should fix that for "data". But I would prefer for most (all) of the other parameters, even the obsolete and deprecated ones, to point to the "diag" destination by default.

3. The initial files sizes are very small. And the AUTOEXTEND - NEXT value is also small. I would recommend to start with larger sizes, or adjust those values early. Or .. have Oracle provide larger values for start- or default-settings. 

4. No default BIGFILE for tablespaces (yet). This seems to run against what the RTFM says about BIGFILE as being the new default (link to create-db, April 2026) ? No big deal as my demo-system doesn't really need bigfiles..

5. Default memory parameters are very small... You probably want to set those larger yourself. Note that the FREE version has limits here.

6. Tip.. check nr of processes, logwriters and jobqueues, and memory settings.. you can probably economize on those, especially if you are on a very small system under load.

7. Bonus-Geek-Tip-1: Check the object owned by Public and System. Why, and Why so Early in the create-process ?  

8. Bonus-Geek-Tip-2: study the alert file and the ancient sql.bsq. Lots of interesting stuff there but not immediately relevant for my mission of "simple scripts".. Maybe Later.

These are the things that I found and deemed worthy to mention. There is probably a lot more. Feel free to copy the scripts below and explore for yourself...

-- Summary: This was an Interesting Excercise. Now move on...

By looking at all these "default" results, I re-visited some ancient knowledge and I learned a few (useful ??) tidbits. 

The main message is: Use Sensible Defaults. And verify them. You should probably fill your init.ora with reasonable values for memory-  and with proper file-path parameters.

You could do worse than using the scripts provided by the DBCA on "save as scripts". My next post should be about using the DBCA to generate those better "example scripts".

-- -- -- -- -- End of this Blogpost, for now -- -- -- -- --

Appendix: links to scripts

C001.sh : The script to create and do some examining.

C002.sh : The improved version.

chk_crdb1.sql : a script that will peek into a freshly-created instance.

info07_files.sql : find most of the file-destinations, know where your info goes.

-- -- -- -- -- End of this Blogpost, for Real -- -- -- -- --

Creating an Oracle 26ai Database from Scratch using scripts

TL;DR: I want Scripts. Hence I am going to try and create an Oracle 26ai database purely using scripts. Specifically not recovering any templates (backupsets) delivered with the DBCA. The first attempt was interesting.

Outcome: You should probably use the DBCA, at least as a "Trusted Advisor"... 

And for Dev/Test usage: Just pull the container-images and run them. There are License-free images available as well should you need those (link). But running containers and circumventing licenses is not the scope of my current research.

The image: a script kiddy, happily playing around with toys and tools (and SQL). That is how I sometimes feel (minus all the hacking and the 1337 maybe...)

-- Quick (future) Jumps, upcoming links...:

I intend to run this little project in stages, and I hope to compose, approximately, the following items:

1. Create Database (and what goes wrong when you try that too simply)

2. Generate scripts from DBCA (and some comments and suggestions on that)

3. and subsequent: Various experiments and adjustments...

n. Adding PDBs.. and whatever we discover adding those...

End Goal: Present Usable and Portable Scripts, and a wrap-up with learnings, suggestions, and ideas.

At the time of writing (May 2026), I'm using v23.26.1, but over time I expect minor versions to come out. I will probably switch to latest versions whenever possible and I hope my scripts and demos do not change much over time (unless I find serious anomalies or bugs).

-- Background: Create a Database (from scripts ) 

The reference manual for Oracle 26ai still contains the command to Create a Database. Hence it should still be possible to create one Manually.

For fresh-installs, Oracle provides the DataBase Create Assistant (DBCA) which can help you to configure and install your database. In most cases, the DBCA will roll out a template and configure it depending on your choices in the assistant. Those templates are effectively backups from an earlier created database. 

Note: That database was Created by someone else.

There is a lot to be said for using Templates: They are configured and pre-packaged for a purpose, and should contain some "good practices". Using a template pre-empts a number of possible errors or omissions, hence is probably "safer". And the "recovery" of a template is a unit of magnitude faster than creating a database from running the scripts.

We can discuss the pro- and con- arguments for templates forever.  But I think that in the majority of cases those templates are Fine, at least for starting. Other RDBMS products also work with templates, so it is not a "new idea".

But mainly for Challenge and for Curiosity, I want to try and create a v26ai database using the original commands, scripted.

This is not a new idea: in very early versions (before 8.1?) the "creation of a database" was purely from scripts. It could be a a time-consuming process, notably on slow machines. But even with the DBCA, you could always choose "generate scripts". And there was generally a choice whereby the scripts did not "recover a template" but rather ran all the individual scripts that "compose a database".

My preferred option was to "generate the scripts", adjust them slightly to my preferences, and then carry around a set of "create scripts" so I could create one or multiple databases when needed. Some of those databases are still alive...

I'm going to try that scripted approach with v26ai.

-- The plan: Search for the Simplest Possible, linux-portable, Scripts.

As a "minimalist" I would like to have the simplest-possible script(s). Because I don't trust myself to compose really good, complete scripts. Hence I will end up working from a set of "generated scripts", provided by the DBCA.

The resulting database has to be "viable".

To ensure that I cover the essential components, later on I will Generate a set of scripts (and then try to simplify them). The aim is to have scripts that can be used with minimal effort on "any computer", provided it runs linux or unix <insert pinguin-emoticon here>.

Note that I have done this exercise on several versions of Oracle, and I have a collection of similar create-scripts going back to v7.1. Some of you may remember the scripts called "crdb<n>.sql" provided by Oracle. I liked those, I'm tempted to go back to old disks to re-inspect them...

The use of the DBCA has Not significantly changed since as far back as v8. The choice to use "scripts" rather than "recover a template" is still there. However, the resulting scripts and called-scripts have become more and more complex over time.

An my aim with this little "project" is to compose a set of scripts that are portable and usable. Not to create "the ultimate database", but rather to serve as research-material. And who knows what we can Learn in the process.

-- First Simple Attempts: It worked (but not really, not yet)

Just to see what would happen, I tried something very basic. I exported my ORACLE_HOME, ORACLE_SID, I connected, and I just tried it:

Check the above screenshot: 

First we show (echo) the $ORACLE_HOME and $ORACLE_SID. 

We use SQL*Plus to connect to the "idle" instance. 

We try to start the instance. We need to start in nomount state, as there Is No Database Yet... 

First error: it needs a pfile. 

So we touch a file with the right name.

Second error: it needs the DB_NAME in the pfile. 

Hence we echo that parameter into the file: db_name = $ORACLE_SID

And at this point we can start the instance in nomount-mode, no more errors. We see the memory-parameters of the instance and "instance started".

And with a started instance in nomount-state. We should be able to create the database:

Success. Nice.

Note, FYI: This database will be registered with the (dflt) listener but I will need a orapw-file to allow connections from, say, SQLDeveloper from another machine. 

Note also that if you happen to do this on the FREE version, your SID and your db_name have to be "FREE". That is one of the few limitations that the free version has. Check the docu (link) if you want to know more about the possibilities of the free-versions.

In a follow-up, I want to examine this Very Basic database a little further, but I already know from testing and earlier attempts that is is not really a "viable" database. The creation of a serious database needs a little more attention. 

Of course it still needs Catalog and Catproc, and long list of additional script to become a Real Database. But first, some parameters have to be set, and I would strongly recommend to make sure the datafiles are bigger and have a proper "increment" set to prevent enless re-sizing during subsequent scripts.

All of this is material for further Study.

-- First Conclusion: It works (just dont do this yet)

You can still use "Create Database", even with very basic settings.

However, it needs more work. The resulting "database" at this point had several show-stopper problems, and some more issues. I intend to use the next blogpost to dissect and discuss some of those items. 

I can also already give the spolier: The scripts generated from the DBCA Can Work and Do Work: you can create a database that way (at least I could from earlier version and from v23.26.1 with some attention to details). I'll use a few future blog-posts to walk through the process, and I will try to extract some "lessons learned" from that.

For now, please find my "absolute minimum script" in the link below. You can use it for your own amusement and experimenting. And if you do any experimenting, it will always be interesting to Someone: 

Let the world know what you find !

-- -- -- -- -- End of this Blogpost, for now -- -- -- -- --

Appendix: Links to script(s)

C000.sh : the very basic Create-stmnt, tested against the EE-version 23.26.1.

FREE.sh : if you run on the FREE version containers from Gerald.

The images used to test the create-stmnt above were the ones from gvenzl (free) and the database ee-edition from container-restry.oracle.com, both at version 23.26.1 (image created Jan 2026)

-- -- -- -- -- End of this blogpost, For Real -- -- -- -- --

Use X-windows on your Oracle containers, Simple...

TL;DR: To run some of the GUI tools that come with Oracle 26ai, you need X-windows. Let me show you how I did that on my containers.

I needed this "solution" several times in the last few years, and I got tired of searching for it every time,  hence this post is also a "reminder to self".

And this how-to-X11 post, is hopefully a setup to a series where I want to use the output from the Oracle DataBase Configuration Assistant (DBCA) to Create a Database. To use that assistant, I first had to set up X-windows (or X11, as it is often called).

Note that this X11 pre-dates the MS-windows that most you know + use...

Image: the Oracle11 installer. I couldnt find an older one, but this installer goes back to v7 or maybe even further.

Background: I want to run those tools, hence I need X11

For some of my hobby / experiments / research, I am using container-images with Oracle (database) software on them.

One source is the official Oracle container-registry (link). I took mine following the links to "Database" -> "Enterprise".

Another very convenient source is the collection of "FREE" images maintained by Gerald Venzl (link). The latter are hassle-free, license-free, Very Easy to pull and run containers you can use for Dev, Testing, or just playing around with an otherwise Enterprise-Grade database (ok, some limitations apply... rtfm ;-)  )

In both cases, I also wanted to run the "assistants" that come with the Oracle Database. I can normally manage quite well without those, and I would most of the time recommend you do your Research and use proper scripts to do your work. But in some cases, I also recommend to use those assistants to "ask Oracle for a second opinion". 

In my case, I notably have this hobby-project of Creating a Database from Scripts rather than from recovering an existing database or a pre-built template. More on this in some older posts (link to 2023) and maybe in an upcoming blog-series (future links here...).

You can find more on X-windows, or X11 as it is often called, here (link)

Installing x11, using yum, should be simple, right?

(I ended up spending a lot of time on this, several times over, and it isnt perfect yet...)

So I started my container, as usual, with this (simplified) command:

That is (simplified) how my container normally runs. And once the initial pull is done, this is a Fast and convenient way to get a database up.

I have defined variables for :

SRC_IMAGE : where to find the image to pull

CONT : the name and hostname of the new container.

YUM : because not all containers have yum, sometimes YUM=microdnf.

And then I start it up...  

Note that you can do many more sophisticated things on startup: rtfm on the registry-docu and on the page describing the free-images - there are many possibilities! I can notably recommend you map some volumes for data- and diag-data. But that is outside of the scope for this item.

These container images do not yet contain X11, so I had to get that myself. From ancient memory and some googling-around I knew that it could be yum-installed.. (yum works on the enterprise-image, on the FREE images you can use microdnf).

Well, this time yum didnt work so easily:

It didnt find xorg-x11-apps ... 

Yum isnt quite my expertise, but after a lot of searching and a tip from Kamil (the axeman himself, no less), I found I had to zip the file /etc/yum/vars/ociregion. Here is the relevant part of my script, the link below contains the complete script:

Those docker (or podman) commands made it work. 

I used docker-exec to move the ociregion file and create an empty one. From that point on, yum (or dnf, or microdnf) was able to install X11. 

And at every new container, I am now happy to see those eyes appear:

Success !  (for now). Proven by the eyes... 

About the X-server...

Note that when you start an X11 app anywhere, you need to make sure your laptop runs the X-Server to receive and display the resulting windows. That is why everyone will "test" with xeyes or xterm. In my case, I use XQuarts on the macbook. On windows you might use mobaXterm or xming.  Make sure your X-Server is running and reachable, otherwise you will see "cannot open display" or a similar error when starting any X11 applications.

Notice how I told xeyes where to send its output with the --display. Once you log on you need to set+export the DISPLAY value in your shell-session. That DISPLAY needs to point to your X11 server (yes, it is called a server...)

To prove it all worked in my case: I displayed "the eyes", in "violet". I also used to put a "green" set of eyes at the end of the final script to signal "complete". Check the link to the final script at the bottom of the post.

Personal note: My Kids loved those eyes back in the years 00, and I often produced them just for fun. The eyes follow the mouse-pointer, really nice.

That was not all that difficult... however... we are not done yet.

The dbca (not) working ?? (not on 1 laptop, not on the newest...)

Once I had X11 on there, I could normally logon as oracle (the dflt user), export my display, test it with xeyes (looked Good), and then start up the DBCA...

Not in this case. The xeyes worked fine. But not the DBCA.

The DBCA does not display in readable colors. At least not on this screen. But it did always work fine on older laptops. And still does even with the latest container-images. I've asked about this on the Oracle forum but as of 02-May-2026, no solution yet.

My (failing) platform was: 

X-server is XQuartz, running on a macbook M5-pro. 

My screen is the nano-texture variety for macbook (expensive, but Good even in sunlight)

The "linux server" is the container pulled from Oracle, image id 224c51ce1555

At the time of writing, I have not solved this. There is a thread on the oracle forum about it (link).

My workaround was to use another, older laptop where the display of the X11 window was as-expected. That dbca feels a bit old, but also comfortable: it was there already when I started my DBA-career back in the 90s...

Using the older laptop, I was able to generate all the "create scripts" that I wanted, run them, and examine them at my leisure. But that is for another topic.

Summary : Simple, but necessary. X11 install Done.

This was a fairly simple, but the time went into "Researching". Mostly because I never had mastered the install of X11, and partly bcse I had to tweak the yum-variables provided on the (oracle-) container-image.

All is (mostly) Fixed and successfully worked-around.

All well. 

I was ready to generate "The Scripts" 

-- -- -- -- -- End of this how-to blogpost -- -- -- -- --

Appendix: the script

mk_cont_x11.sh : the shell script to create the container.

Do try. And let me know how it works for you. You can of course use this script to build your own running containers with Oracle 26ai in them. Have Fun.

-- -- -- -- -- End of this blogpost, for Real -- -- -- -- --

Oracle 26ai - The Con_ID of CDB$ROOT...

TL;DR: When your query joins V$PDBS to get the name of the PDB, you might miss the contents of CDB$ROOT. I fixed that in various ways for my scripts.

(note: this was a classic case of at least two errors on my part: not reading the RTFM on "containers" careful enough, and a case of an INNER JOIN being too strict)

Image generated in style of X-files: Never Trust your own Scripts...

Background: Do I miss some data ??

When you are "administrating" (monitoring) your Container DataBases from CDB$ROOT, you want to see Everything. All PDBs, all datafiles, all services, all session and all processes. And you want to know in which PDB each item resides. Not just the con_id, but preferably the actual name of the PDB.

But when you join to V$PDBS to get a list of the pluggable databases in your container, there is no entry in that list for CDB$ROOT. 

In some of my scripts, I ended up with discrepancies. As an example, I'll take my daily "storage-check". But I had similar "issues" with v$services and v$session.

The sum of all filesizes from V$DATAFILE was (example) 999 GB. But when I tried to group the data per PDB, the total of that list was clearly less than 999 GB. Huh?

The difference was that the datafiles from the CDB$ROOT were not taken into account, because my (inner) join with V$PDBS automatically excluded the files where CON_ID=1, e.g. the files from the CDB$ROOT.

It got even stranger: in V$database, when you do  select con_id and name from v$database it yields Con_id=0, and name=FREE (e.g. the db_name).

So it seems that CON_ID = 0 and CON_ID = 1 are the exception-cases, and are both referring to CDB$ROOT, or to DB_NAME, depending on where you look ?

Not a huge problem, and there are several simple solutions. But still enough of an anomaly for me to take a closer look (and fix my queries...).

Note: I'll do the demo using the "Free" container from Gerald Venzl (link). If you ever quickly need a demo- or test-database check this out. Some of our devs just love them for their ease-of-use. No license-accept, no login, just pullable-images. I got into the habit of using these containers for just about any "proof of concept" (or any blog-article). You'll have an Instant Database available on any laptop where docker or podman is installed. I might do a blog on "how to" sometime, but in general: Just read the page and get running...  Most DEVOPS-  and DBA folks figure it out in no-time - And then they suddenly "run a local database"...

The Example: I missed GBs diskpace from the sum of my datafiles.

For demo purposes of this blog, I started one of those containers with FREE and FREEPDB1, I then added several PDBs and then I checked for used disk-space (simplified version of my check-script in link below):

Notice how the list of PDBs adds up to 5 x 790 = 3950 MB, but the total reported is 5673 MB. There is some 1700 MB is missing in the first list, even though both queries report from v$datafile.

From looking at the SQL, or by listing just the v$datafiles, it is +/- evident that the first query missed the files where CON_ID = 1, e.g. it missed the files from the CDB$ROOT:

The two files at the top hold the 1700MB that seems missing. And the cause is classic: the simple, old fashioned, join with V$PDB in the previous query had left out the Con_ID=1. 

Aha. A quick fix with LEFT JOIN should do the job. Or.... Even better, after poking around I also found V$CONTAINERS, and that seemed like the Best Fix (for this case):

So Far so Good. This scripts is now working as intended again.

But What About that CON_ID for root ??

On further inspection, I notice that also my session-counts (checking +/- how busy is the system...) were off by a good margin when compared to, say:

select count(*) from v$session;

or even 

ps -ef | grep $ORACLE_SID

Same problem of course: When I had done my quick+dirty equal-joins to V$PDBS on CON_ID to pick up PDB-NAME, I suddenly had eliminated all of the sessions for non-PDB connections.

But it got stranger: Here is my check on  GV$SESSION, after putting in the "fix" with GV$CONTAINERS to pick up the pdb_names and CDB$ROOT.. 

The total shows a count of 86 sessions, but the various containers only hold ... 3+3+1 = Seven ?? How can I still miss 79 sessions ?

This is where I had to go RTFM, and the actual information was buried in an older version (v12, link) of the doc:

The values for con_id are explained as follows:

• Con_ID = 0: Pertains to the Entire CDB (example: V$DATABASE contains value of 0, and this is probably why many "background" session have Con_ID=0)

• Con_ID = 1: Pertains to the CDB$ROOT (for example in V$CONTAINERS and V$DATAFILE, you will find the value of Con_id=1, as I found with my storage-check)

• Con_ID = 2: Pertains to the PDB$SEED.

Other, higher values of Con_ID correspond to proper PDBs that have been created, cloned, or plugged into the CDB.

And because there is no Con_ID = 0 in the V$CONTAINERS, I missed some 79 sessions in my uninformed count...

Note that when you check the same RTFM-page for 26ai (click on "view latest" in the previous link) there is no clear mention of this anymore...? This information has either moved to other parts of the documentation or was considered overly confusing (I'd say: Not Knowing this is even more confusing, but maybe I dig too deep...).

In this case, a little more fixing was needed, and the fix did end up as a LEFT-JOIN:

And QED: we have the sessions, counted per container, and we did include the sessions where Con_ID=0. The sum of the part is equal to the whole again. All is well.

For a more elaborate fix, I even experimented with a view that I named All_Con_IDs: a Union of V$PDBS and V$DATABASE. The result was something along these lines: Here is my check-count for "processes" using an inline-view version:

Using this CTE (or inline view), I got the satisfaction of seeing the proper name of the (container-)database displayed with Con_ID=0, instead of some self-designated hardcoded "-root-" value from an improvised-NVL.

Needless to say: I didnt seriously implement that "fix" in a self-made view (yet), but it was an interesting idea for a moment: unite all the Con_IDs in a single view/place to join that to various sources where and when needed...

Maybe someone out there knows of a similar view that already exists ?

Summary: Check your scripts when using Con_ID (and Trus7No!)

From spotting an anomaly in my scripts, I ended up digging into CON_ID values. I discovered that values for CON_Id of 0 (zero) and 1 (one) mean different things in the various gv$ views, and I ended up with better scripts, and a slightly better understanding of my systems.

For the innocent reader: Never fully Trust your own Scripts.. In my case, I notably "missed" some GB sizes of datafiles in the CDB$ROOT...  Not a huge thing, but still indicative of what can go wrong.

For Oracle folks (if you guys are reading), I would suggest a few tidbits:

 - Document the values for Con_Id=0 and Con_id=1 little better. I found the relevant info in v12c, but not in page "latest version" for v26ai. I suspect my google skipped  the relevant pages for v26ai?

- Consider some extra view that unites and explains all the values for CON_ID in one place,  similar to my inline-view experiment: all_con_ids ?

And yes, my pwd used to be similar to that of agent Mulder: Trus7No!. Until they started demanding a minimum of 42-characters...

-- -- -- -- -- End of this Blogpost - for now -- -- -- -- --

Appendix: Script with demos.

demo_con_ids.sql : run this in some CDB$ROOT (with a lot of busy PDBs) to see for yourself.

-- -- -- -- -- End of this Blogpost - For Real -- -- -- -- --