Select from cdb_* views
There is no privileges strong enough for you to view all objects in all databases Let’s try as sys: SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ———- ———- 1 22749 3 22721 as non-sys SQL> create user c##u identified by ***; User created. SQL> grant create session, select any dictionary to […]
changing container in plsql
One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware. If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_* OLD: SQL> select count(*) from dba_users; COUNT(*) ———- 121 NEW: non-cdb SQL> select con_id, count(*) from cdb_users group by con_id; CON_ID […]
Audit pluggable database
In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba. In single-tenant (or multi-tenant), things get complicated. Once again, the doc must be read at least twice 😉 If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in […]
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 Nope – not via "upgrade". You can use Data Pump, Transportable Tablespaces or Full Transportable Export/Import as a direct option but you can't upgrade and plugin in one pass unfortunately […]
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 […]
Dynamic number of columns revisited
Itching to start playing with 18c? Now you can on @oraclelivesql ! Here's a script to get you started: a dynamic CSV-to-columns converter using polymorphic table functionshttps://t.co/UfddLQ2tn5 pic.twitter.com/BxnXeIKoCx — Chris Saxon (@chrisrsaxon) February 17, 2018 The ingenious solution of Anton Scheffer using Data Cartridge is now beaten in 18c using polymorphic table function Anthologic post […]