How to migrate non-cdb to pdb

In case you are desesperately looking for an upgrade from non-cdb to pdb with two clicks, here is the answer from Mike : you can’t

If your database is huge and you don’t want to move anything nor change SID nor change path, this is my own receipt.

  1. Backup your database
  2. Create a XML description of your CDB
    shutdown immediate
    startup read only
    exec dbms_pdb.describe('/save/DB01/DB01.xml')
    shutdown immediate
  3. Setup init.ora parameter for PDB
    startup force nomount restrict
    alter system set enable_pluggable_database=true scope=spfile;
    alter system set db_name=CDBDB01 scope=spfile;
    alter system set instance_name=DB01 scope=spfile;
    startup force nomount
  4. Create a CDB database
    Yes, you have read it correctly. You are required to create a database. There is -so far I’ve googled- no way to avoid this step. You can use dbca or sqlplus.
    Don’t overwrite the datafile. Use for instance /data/DB01/cdb/system.dbf instead of /data/DB01/system.dbf
    create database CDBDB01
    user sys identified by ***
    user system identified by ***
    character set  al32utf8 
    national character set al16utf16
    ('/onlinelog/DB01/redo01a.dbf','/onlinelog/DB01/redo01b.dbf') size 32M REUSE,
    ('/onlinelog/DB01/redo02a.dbf','/onlinelog/DB01/redo02b.dbf') size 32M REUSE
    extent management local
    datafile '/data/DB01/cdb/system01.dbf' size 10M autoextend on
    sysaux datafile '/data/DB01/cdb/sysaux01.dbf' size 10M autoextend on
    default tablespace admin datafile '/data/DB01/cdb/admin01.dbf' size 10m autoextend on 
    extent management local autoallocate
    default temporary tablespace temp 
    tempfile '/data/DB01/cdb/temp01.dbf' size 10M autoextend on
    undo tablespace undotbs1 datafile '/data/DB01/cdb/undotbs1_01.dbf' size 10M autoextend on
    set time_zone='Europe/Zurich'
    SEED FILE_NAME_CONVERT = ('/data/DB01/cdb/', '/data/DB01/seed/')
    USER_DATA TABLESPACE users datafile '/data/DB01/cdb/users01.dbf' size 10m autoextend on;
    perl -u sys/*** -d oh/rdbms/admin -b catalog_DB01 -e -l log catalog.sql
    perl -u sys/*** -d oh/rdbms/admin -b catproc_DB01 -e -l log catproc.sql

    It’s a pretty cumbersome step. Maybe you need Oracle Text or maybe you have DEC character set… all those details must be engineered by the DBA. This is pretty insane Oracle doesn’t offer a tool to migrate to a non-deprecated architecture.
  5. Plug-in the database
     CREATE PLUGGABLE DATABASE DB01 USING '/save/DB01/DB01.xml' nocopy tempfile reuse;

    If you miss this step, Ô râge, Ô désespoir, you probably must restart from the beginning.
  6. run noncdb_to_pdb
    alter session set container=DB01;
    alter pluggable database DB01 open;
    alter session set container=DB01;

Once you are so far, you’ll have all advantages of Multi-Tenant. PDB-Clone. PDB-Flashback. Lockdown profiles. And you’ll be able to consolidate if you buy the multitenant option. You’ll save money on hardware (SGA/CPU) to spent on software (Multitenant option).

Most importantly, you’ll have moved to a non-deprecated architecture

Conclusion: there is more than one way to move to pluggable. But there is no way to migrate to pluggable without creating a new database

dynamic linesize in 18.1

Whenever you select and describe in sqlplus it looks ugly

default: pagesize 14 linesize 80

change the default: it is often too large or too narrow

Let’s try WINDOW in sqlplus 18.1, which is available for download on Solaris / Linux / Windows

SQL> set lin window
SQL> sho lin
linesize 95 WINDOW
SQL> sho pages
pagesize 86

And look the result :

Almost perfect 🙂

I think it would nicer if DESC didn’t have this empty line

The cool thing with SET LINESIZE WINDOW is that it is dynamic (as I tested with CMD.EXE/windows and XTERM/Linux). Your window is too narrow, you make it bigger, re-run your select, it looks nicer

but… pagesize cannot be set to NON-WINDOW

SQL> set lin window
SQL> set pages 50000
SQL> sho pages 
pagesize 21

Dynamic number of columns revisited

The ingenious solution of Anton Scheffer using Data Cartridge is now beaten in 18c using polymorphic table function

Anthologic post of Anton :
Chris magic with Oracle 18c :