Oracle 12c Pluggable Database (PDB) – SQL code examples

March 15, 2013 by 4 Comments 

Oracle database 12c is still not there and last time I predicted it will be released in March. Let’s see if Oracle can fix all the release issues and provide the nice and clean new major database release 12c that will greatly help enterprises in their consolidation strategies and enable them building Private Database Clouds.

With this I’ll continue my series of articles on Oracle 12c Pluggable Database (PDB) feature including SQL code.  I strongly recommend first reading my previous articles on this topic to understand the rest of the material:

Oracle 12c Pluggable Database is a fundamental architectural change
Oracle 12c Pluggable Database feature insights
Oracle 12c Pluggable Database (PDB) feature hands-on experience
Oracle 12c Pluggable Database – Plug Unplug and Clone
Oracle 12c Pluggable Database (PDB) – FAQ

This time I focus on a few interesting SQL code examples. I’ll show how to create lots of empty PDBs programmatically from scratch and from the Gold images, how to open all Pluggable Databases (PDBs) automatically after CDB startup as well as to drop all PDBs. Once again I’d like to mention that the below code should work on Oracle database 12.1 Beta2. So let’s move on.

Create AFTER STARTUP trigger to open all PDBs of a CDB

As I mentioned in previous article, after a startup of a Container Database (CDB) the Pluggable Databases (PDBs) are not opened automatically. I wonder why this is not implemented and unfortunately I’m not aware of any additional option in a startup command that can do that. So, as usual with Oracle, let’s implement a workaround using an AFTER STARTUP trigger that will open our PDBs automatically after a database startup. See the below PL/SQL code:
CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

Drop all the PDBs in the CDB

Of course, in normal life, this would be a shocking idea. But it’s very useful in a learning situation to be able to clean all Pluggable databases and try various tests afresh. Precisely because all the operations on PDBs as entities are done using ordinary SQL statements, it’s trivial to automate such tasks using PL/SQL.
DECLARE
TYPE names_t IS TABLE OF v$pdbs.name%TYPE;
names        names_t;
TYPE open_modes_t IS TABLE OF v$pdbs.open_mode%TYPE;
open_modes   open_modes_t;
BEGIN
SELECT   name, open_mode
BULK   COLLECT
INTO   names, open_modes
FROM   v$pdbs
WHERE   name NOT IN ('PDB$SEED', 'SAMPLE_SCHEMAS');
FOR j IN 1 .. names.COUNT ()
LOOP
IF open_modes (j) <> 'MOUNTED'
THEN
EXECUTE IMMEDIATE   'alter pluggable database "'
|| names (j)
|| '" close immediate';
END IF;
EXECUTE IMMEDIATE   'drop pluggable database "'
|| names (j)
|| '" including datafiles';
END LOOP;
END;
/

Notice the use of the immediate keyword in the alter pluggable database statement.This corresonds to the SQL*Plus command SHUTDOWN IMMEDIATE — so it waits until no SQL-issuing background processes have the to-be-closed PDB as their current container. There is no more forceful way to close a PDB. The SQL*Plus command SHUTDOWN ABORT brutally kills the background processes — but of course a CDB’s background processes are managing all the its PDBs and CDB$Root.

Create lots of empty PDBs programmatically from PDB$Seed

It’s often useful to be able to create an arbitrary number of PDBs with the minimum of fuss. This PL/SQL block shows how to do it. It also makes, indirectly, the very important point that PDB maintenance can be easily automated:

DECLARE
TYPE names_t IS TABLE OF v$pdbs.name%TYPE;
names         names_t := names_t ('Globally_Unique_1', 'Globally_Unique_2');
orabase_dir   VARCHAR2 (32767);
cr_pdb_template CONSTANT VARCHAR2 (32767) NOT NULL := q'{
create pluggable database ?
admin user App_Admin identified by p
storage (maxsize unlimited max_shared_temp_size unlimited)
file_name_convert = ('/pdbseed/', '/?/')}';
cr_temp_tablespace_template CONSTANT VARCHAR2 (32767) NOT NULL := q'{
create tablespace Users
datafile '?1/oradata/?2/?3/users01.dbf'
size 20M
autoextend on next 1M maxsize unlimited
segment space management auto}';
cdb_name      v$database.name%TYPE;
BEGIN
SELECT   VALUE
INTO   orabase_dir
FROM   v$parameter
WHERE   name = 'diagnostic_dest';
-- Linux only
orabase_dir := RTRIM (orabase_dir, '/');
SELECT   name INTO cdb_name FROM v$database;
FOR j IN 1 .. names.COUNT ()
LOOP
EXECUTE IMMEDIATE 'alter session set container = CDB$Root';
DECLARE
create_pdb VARCHAR2 (32767)
NOT NULL:= REPLACE (cr_pdb_template,
'?',
LOWER (names (j)));
BEGIN
EXECUTE IMMEDIATE create_pdb;
END;
EXECUTE IMMEDIATE 'alter pluggable database ' || names (j) || ' open';
EXECUTE IMMEDIATE 'alter session set container = ' || names (j);
EXECUTE IMMEDIATE 'grant Sysdba to App_Admin';
DECLARE
cr_temp_tablespace VARCHAR2 (32767)
NOT NULL:= REPLACE (cr_temp_tablespace_template,
'?1',
orabase_dir);
BEGIN
cr_temp_tablespace :=
REPLACE (cr_temp_tablespace, '?2', LOWER (cdb_name));
cr_temp_tablespace :=
REPLACE (cr_temp_tablespace, '?3', LOWER (names (j)));
EXECUTE IMMEDIATE cr_temp_tablespace;
END;
EXECUTE IMMEDIATE 'alter database default tablespace Users';
END LOOP;
EXECUTE IMMEDIATE 'alter session set container = cdb$root';
END;
/

Create lots of empty PDBs programatically from the gold image Sample Schemas

This is a slight variation on an example from previse article. This PL/SQL code lets to stress the capacity of a CDB.

DECLARE
cr_pdb CONSTANT VARCHAR2 (32767)
NOT NULL := q'{
create pluggable database Sample_Schemas_? as clone
using '/u01/app/oracle/oradata/gold_pdbs/sample_schemas/sample_schemas.xml'
storage (maxsize unlimited max_shared_temp_size unlimited)
copy
file_name_convert = ('/gold_pdbs/sample_schemas/', '/cdb1/sample_schemas_?/')
}';
BEGIN
FOR j IN 1 .. 5
LOOP
DECLARE
pdb   CONSTANT VARCHAR2 (30) := LTRIM (TO_CHAR (j, '09'));
t0    CONSTANT INTEGER NOT NULL := DBMS_UTILITY.get_time ();
t     VARCHAR2 (80) NOT NULL := '?';
BEGIN
EXECUTE IMMEDIATE REPLACE (cr_pdb, '?', pdb);
t := TO_CHAR ( ( (DBMS_UTILITY.get_time () - t0) / 100.0), '999');
IF j > 5
THEN
DBMS_OUTPUT.put_line (
CHR (10) || 'Created ' || (j - 1) || ' PDBs' || CHR (10));
EXIT;
END IF;
DBMS_OUTPUT.put_line(   'Created PDB Sample_Schemas_'
|| pdb
|| ' -- elapsed time ='
|| t);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Can''t create any more PDPs');
RAISE;
END;
/

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

4 Responses to “Oracle 12c Pluggable Database (PDB) – SQL code examples”
  1. Hi Kirill, nice writeups on PDB archs. But may I know from where did you got access to the beta VMs? Were you part of beta program from Oracle?

  2. Kirill Loifman says:

    Hi Saurabh
    Oracle 12c is available now for download. Feel free to test the official version.
    — Kirill

  3. Chris says:

    Great work on Oracle 12c. I have been trying to install it on my Windows 8.1 but have not been able to as I keep getting errors. What is the best way to start learning Oracle 12c (Admin and Developer)?

  4. Kirill Loifman says:

    Oracle doc and play around!
    — Kirill

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email