Oracle DBMS_PDB
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Provides an interface to examine and manipulate data about pluggable databases.
AUTHID CURRENT_USER
Dependencies
DBMS_ASSERT DBMS_PDB_EXEC_SQL X$KSPPCV
DBMS_OUTPUT PDB_PLUG_IN_VIOLATIONS X$KSPPI
Documented Yes
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to EXECUTE_CATALOG_ROLE role. The script catqm_int.sql also grants EXECUTE to XDB.
Source {ORACLE_HOME}/rdbms/admin/dbmspdb.sql
Subprograms
 
CHECK_PLUG_COMPATIBILITY
Determines whether a pluggable database described by file pdb_descr_file is compatibile with the current CDB dbms_pdb.check_plug_compatibility(
pdb_descr_file IN VARCHAR2,
pdb_name       IN VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN;
conn / as sysdba

ALTER PLUGGABLE DATABASE pdborcl CLOSE;

ALTER PLUGGABLE DATABASE pdborcl OPEN READ ONLY;

exec dbms_pdb.describe('/home/oracle/pdborcl.xml', 'PDBORCL');

BEGIN
  IF dbms_pdb.check_plug_compatibility('/home/oracle/pdborcl.xml', 'PDBDEV') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

SELECT *
FROM pdb_plug_in_violations;
 
CLEANUP_TASK
Performs cleanup task previously performed by SMON -- returns 0 if the next scheduled time for the job does not need to be changed. N if the next schedule job should be N seconds from now (not my experience as shown below)

dbms_pdb.cleanup_task(task_id IN NUMBER) RETURN NUMBER;
SELECT dbms_pdb.cleanup_task(153)
FROM dual;
       *
ORA-00600: internal error code, arguments: [kpdbCleanupTaskInt:taskid], [153], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_PDB", line 116
 
CREATEX$PERMANENTTABLES
Create permanent tables corresponding to controlfile related Fixed Tables. It should be invoked in ROOT. dbms_pdb.dropx$permanettables;
exec dbms_pdb.createx$permanttables;
 
DESCRIBE
Generates XML that describes tablespaces and datafiles belonging to a pluggable database dbms_pdb.describe(pdb_descr_file IN VARCHAR2, pdb_name IN VARCHAR2 DEFAULT NULL);
exec dbms_pdb.describe('$HOME/orabase_desc.xml', 'ORABASE1')

<?xml version="1.0" encoding="UTF-8"?>
<PDB>
  <xmlversion>1</xmlversion>
  <pdbname>ORABASE1</pdbname>
  <cid>3</cid>
  <byteorder>1</byteorder>
  <vsn>202375680</vsn>
  <vsns>
    <vsnnum>12.1.0.2.0</vsnnum>
    <cdbcompt>12.1.0.2.0</cdbcompt>
    <pdbcompt>12.1.0.2.0</pdbcompt>
    <vsnlibnum>0.0.0.0.22</vsnlibnum>
    <vsnsql>22</vsnsql>
    <vsnbsv>8.0.0.0.0</vsnbsv>
  </vsns>
  <dbid>3839941281</dbid>
  <ncdb2pdb>0</ncdb2pdb>
  <cdbid>1929260586</cdbid>
  <guid>4C690F3954EC4B2FBECFAA0CFA1BD955</guid>
  <uscnbas>2542036</uscnbas>
  <uscnwrp>0</uscnwrp>
  <rdba>4194824</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/app/oracle/datadata/orabase/orabase1/system01.dbf</path>
      <afn>7</afn>
      <rfn>1</rfn>
      <createscnbas>2244538</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>33280</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <fdbid>3839941281</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2535793</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>2233668</frlsb>
      <frlt>860956911</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>SYSAUX</name>
    <type>0</type>
    <tsn>1</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/app/oracle/datadata/orabase/orabase1/sysaux01.dbf</path>
      <afn>8</afn>
      <rfn>4</rfn>
      <createscnbas>2244541</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>67840</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <fdbid>3839941281</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2535793</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>2233668</frlsb>
      <frlt>860956911</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>TEMP</name>
    <type>1</type>
    <tsn>2</tsn>
    <status>1</status>
    <issft>0</issft>
    <bmunitsize>128</bmunitsize>
    <file>
      <path>/app/oracle/oradata/orabase/orabase1/orabase1_temp.dbf</path>
      <afn>3</afn>
      <rfn>1</rfn>
      <createscnbas>2244539</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>2560</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>80</incsize>
    </file>
  </tablespace>
  <tablespace>
    <name>USERS</name>
    <type>0</type>
    <tsn>3</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/app/oracle/datadata/orabase/orabase1/orabase1_users01.dbf</path>
      <afn>9</afn>
      <rfn>9</rfn>
      <createscnbas>2245011</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>640</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <fdbid>3839941281</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>2535793</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>2233668</frlsb>
      <frlt>860956911</frlt>
    </file>
  </tablespace>
  <optional>
    <ncdb2pdb>0</ncdb2pdb>
    <csid>178</csid>
    <ncsid>2000</ncsid>
    <options>
      <option>APS=12.1.0.2.0</option>
      <option>CATALOG=12.1.0.2.0</option>
      <option>CATJAVA=12.1.0.2.0</option>
      <option>CATPROC=12.1.0.2.0</option>
      <option>CONTEXT=12.1.0.2.0</option>
      <option>DV=12.1.0.2.0</option>
      <option>JAVAVM=12.1.0.2.0</option>
      <option>OLS=12.1.0.2.0</option>
      <option>ORDIM=12.1.0.2.0</option>
      <option>OWM=12.1.0.2.0</option>
      <option>SDO=12.1.0.2.0</option>
      <option>XDB=12.1.0.2.0</option>
      <option>XML=12.1.0.2.0</option>
      <option>XOQ=12.1.0.2.0</option>
    </options>
    <olsoid>0</olsoid>
    <dv>0</dv>
    <APEX>4.2.5.00.08:1</APEX>
    <parameters>
      <parameter>processes=300</parameter>
      <parameter>memory_target=2097152000</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>compatible='12.1.0.2.0'</parameter>
      <parameter>open_cursors=300</parameter>
      <parameter>enable_pluggable_database=TRUE</parameter>
    </parameters>
    <tzvers>
      <tzver>primary version:18</tzver>
      <tzver>secondary version:0</tzver>
    </tzvers>
    <walletkey>0</walletkey>
    <hasclob>1</hasclob>
    <awr>
      <loadprofile>CPU Usage Per Sec=0.000000</loadprofile>
      <loadprofile>DB Block Changes Per Sec=0.000000</loadprofile>
      <loadprofile>Database Time Per Sec=0.000000</loadprofile>
      <loadprofile>Executions Per Sec=0.000000</loadprofile>
      <loadprofile>Hard Parse Count Per Sec=0.000000</loadprofile>
      <loadprofile>Logical Reads Per Sec=0.000000</loadprofile>
      <loadprofile>Logons Per Sec=0.000000</loadprofile>
      <loadprofile>Physical Reads Per Sec=0.000000</loadprofile>
      <loadprofile>Physical Writes Per Sec=0.000000</loadprofile>
      <loadprofile>Redo Generated Per Sec=0.000000</loadprofile>
      <loadprofile>Total Parse Count Per Sec=0.000000</loadprofile>
      <loadprofile>User Calls Per Sec=0.000000</loadprofile>
      <loadprofile>User Rollbacks Per Sec=0.000000</loadprofile>
      <loadprofile>User Transaction Per Sec=0.000000</loadprofile>
    </awr>
    <hardvsnchk>0</hardvsnchk>
  </optional>
</PDB>
 
DROPX$PERMANENTTABLES
Drop permanent tables corresponding to controlfile related Fixed Tables. It should be invoked in ROOT. dbms_pdb.dropx$permanettables;
exec dbms_pdb.dreatex$permanttables;
 
EXEC_AS_ORACLE_SCRIPT
This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB Note: This procedure is called by the package initialization section. This is accomplished by running the statement as an oracle script (i.e. with the parameter _oracle_script set to TRUE). This is for use by Oracle internal packages only.

dbms_pdb.exec_as_oracle_script(sql_stmt IN VARCHAR2);
TBD
 
NONCDB_TO_PDB
Helper procedure for noncdb_to_pdb.sql dbms_pdb.noncdb_to_pdb(phase IN NUMBER);
exec dbms_pdb.noncdb_to_pdb(1);
exec dbms_pdb.noncdb_to_pdb(1);
 
POPULATESYNCTABLE
  dbms_pdb.populatesynctable;
exec dbms_pdb.populatesynctable;
 
RECOVER
Generates an XML file describing a pluggable database by using data files belonging to the database. The XML file can be used to plug the PDB into a CDB using CREATE PLUGGABLE DATABASE. dbms_pdb.recover(
pdb_descr_file IN VARCHAR2,
pdb_name       IN VARCHAR2,
filenames      IN VARCHAR2);
exec dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf');

SQL> exec dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf');
BEGIN dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf'); END;
      *
ERROR at line 1:
ORA-65128: PDB recover data file name not specified
ORA-06512: at "SYS.DBMS_PDB", line 104
ORA-06512: at line 1
 
SYNC_PDB
After plug, syncs the PDB with the CDB, so that it will be ready for use dbms_pdb.sync_pdb;
exec dbms_pdb.sync_pdb;
 
UPDATE_CDBVW_STATS
Updates CDB View Stats -- returns 0 if the next scheduled job change time does not need to be changed: N if the next scheduld time should be N seconds from SYSDATE

dbms_pdb.update_cdbvw_stats RETURN NUMBER;
SELECT dbms_pdb_update_cdbvw_stats
FROM dual;

-- hung the system for a very long time: try again after rebuild
 
UPDATE_COMDATA_STATS
Update stats for common data views dbms_pdb.update_comdata_stats RETURN NUMBER;
SELECT dbms_pdb.update_comdata_stats
FROM dual;
 
UPDATE_OBJLINK_STATS
Updates Object Linked View stats. It must to be invoked in ROOT dbms_pdb.update_objlink_stats RETURN NUMBER;
SELECT dbms_pdb.update_objlink_stats
FROM dual;
 
UPDATE_VERSION
Update PDB's VSN in container$ after upgrade dbms_pdb.update_version;
exec dbms_pdb_update_version;

Related Topics
CDBVIEW
Container Database
DBMS_PDB_EXEC_SQL
Packages
Pluggable Database
What's New In 12cR1

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved