Oracle DBMS_PDB
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose Provides an interface to examine and manipulate data about pluggable databases.
AUTHID CURRENT_USER
Constants
Name Data Type Value
Rule ID
V_FEAT_INV NUMBER 0
V_FEAT_ALL NUMBER 1
V_FEAT_NETWORK_ACCESS NUMBER 2
V_FEAT_COMMON_SCHEMA_ACCESS NUMBER 3
V_FEAT_TCP NUMBER 4
V_FEAT_HTTP NUMBER 5
V_FEAT_SMTP NUMBER 6
V_FEAT_INADDR NUMBER 7
V_FEAT_JDWP NUMBER 8
V_FEAT_XDB_PROTOCOLS NUMBER 9
V_FEAT_JAVA NUMBER 10
V_FEAT_CTX_PROTOCOLS NUMBER 11
V_FEAT_OS_ACCESS NUMBER 12
V_FEAT_FILE NUMBER 13
V_FEAT_EXTPROC NUMBER 14
V_FEAT_JAVA_OS_ACCESS NUMBER 15
V_FEAT_JAVA_RUNTIME NUMBER 16
V_FEAT_TRACE_VIEWS NUMBER 17
V_FEAT_AQ_PROTOCOLS NUMBER 18
V_FEAT_EXT_GLB_AUTH NUMBER 19
V_FEAT_SECURITY_POLICIES NUMBER 20
V_FEAT_CONNECTIONS NUMBER 21
V_FEAT_LOCSYSOPERRESMOD NUMBER 22
V_FEAT_COMUSERCONN NUMBER 23
V_FEAT_AWR_ACCESS NUMBER 24
V_FEAT_LUSER_CSCHEMA_ACCESS NUMBER 25
V_FEAT_CUSER_LSCHEMA_ACCESS NUMBER 26
V_FEAT_DROP_TS_KEEP_DATAFILES NUMBER 27
V_FEAT_EXT_FILE_ACCESS NUMBER 28
V_FEAT_CTX_LOGGING NUMBER 29
V_FEAT_LOB_FILE NUMBER 30
V_FEAT_ADR_ACCESS NUMBER 31
V_FEAT_FILE_TRANSFER NUMBER 32
V_FEAT_XDB_DEPRECATED NUMBER 33
V_FEAT_SYSDATA NUMBER 34
V_FEAT_MAX NUMBER 35
V_ALTER_SESSION NUMBER 42
V_ALTER_SYSTEM NUMBER 49
Rule Type
V_STATEMENT NUMBER 1
V_OPTION NUMBER 2
V_FEATURE NUMBER 3
Dependencies
DBMS_DATAPUMP_UTL DBMS_PDB_IS_VALID_PATH DBMS_SQLDIAG
DBMS_PDB_ALTER_SHARING DBMS_PDB_LIB DBMS_STATS_INTERNAL
DBMS_PDB_CHECK_LOCKDOWN DBMS_PLUGTS PDB_PLUG_IN_VIOLATIONS
DBMS_PDB_EXEC_SQL DBMS_PREPLUGIN_BACKUP SDO_GEOR_DEF
Documented Yes
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to MDSYS and EXECUTE_CATALOG_ROLE role. The script catqm_int.sql can  grant EXECUTE to XDB if run.
Source {ORACLE_HOME}/rdbms/admin/dbmspdb.sql
Subprograms
 
CHECK_LOCKDOWN (new 18.1)
Checks if the operation is lockdown or not and returns the corresponding BOOLEAN value dbms_pdb.check_lockdown(
rule_type      IN NUMBER,
rule_id        IN NUMBER,
raise_error    IN BOOLEAN,
init_parameter IN NUMBER,
events         IN BOOLEAN)
RETURN BOOLEAN;
TBD
 
CHECK_NFT
Checks and warns if a PDB has nofile tablespaces. This procedure is intended for internal not customer use. dbms_pdb.check_nft;
exec dbms_pdb.check_nft;
 
CHECK_PLUG_COMPATIBILITY (new 18.1 parameter)
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,
fatal_only     IN BOOLEAN  DEFAULT FALSE)
RETURN BOOLEAN;
conn / as sysdba

ALTER PLUGGABLE DATABASE pdbdev CLOSE;

ALTER PLUGGABLE DATABASE pdbdev OPEN READ ONLY;

exec dbms_pdb.describe('/home/oracle/pdbdev.xml', 'PDBDEV');

BEGIN
  IF dbms_pdb.check_plug_compatibility('/home/oracle/pdbdev.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
 
CLEAR_PLUGIN_VIOLATIONS (new 18.1)
Cleans up resolved plugin violations dbms_pdb.clear_plugin_violations(pdb_name IN VARCHAR2 DEFAULT NULL);
exec dbms_pdb.clear_plugin_violations('PDBDEV');
 
CONVERT_TO_LOCAL (new 18.1)
Convert a common object to local object dbms_pdb.convert_to_local(
schema_name    IN VARCHAR2,
object_name    IN VARCHAR2,
namespace      IN NUMBER,
object_subname IN VARCHAR2 DEFAULT NULL,
sig_mismatch   IN BOOLEAN  DEFAULT FALSE);
exec dbms_pdb.convert_to_local('UWCLASS', 'TESTPROC', 1);
 
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
 
EXPORT_RMAN_BACKUP
Exports RMAN backup information that belong to the pluggable database to its dictionary before unplug so that pre-plugin backups can be used. The pluggable database has to be opened in read write mode. If the database is non-cdb, then pdb_name must be omitted. If the pdb_name is omitted, then the pluggable database to which the session is connected will be exported. If the pdb_name is omitted, and the session is connected to the Root, an error is returned. dbms_pdb.exportRMANbackup(pdb_name IN VARCHAR2 DEFAULT NULL);
exec dbms_pdb.exportRMANbackup('PDBTEST');
 
IS_VALID_PATH (new 18.1)
Checks whether given path_name is corresponding to the path_prefix property dbms_pdb.is_valid_path(path_name IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_pdb.is_valid_path('/u04/app/oracle/oradata/orabase/orabase/uwapp/') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
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
Populates pdbsync$ on upgrade from 12.1.0.1. This procedure is intended for use only by internal Oracle scripts 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', '/u02/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
 
SET_DATA_LINKED
Used to mark an object as Data linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers. dbms_pdb.set_data_linked(
schema_name  IN VARCHAR2,
object_name  IN VARCHAR2,
namespace    IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_EXT_DATA_LINKED
Use to mark an object as Extended Data linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, and where there was no support for application containers dbms_pdb.set_ext_data_linked(
schema_name  IN VARCHAR2,
object_name  IN VARCHAR2,
namespace    IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_METADATA_LINKED
Used to mark an object as Metadata linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers. dbms_pdb.set_metadata_linked(
schema_name  IN VARCHAR2,
object_name  IN VARCHAR2,
namespace    IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL)
TBD
 
SET_PROFILE_EXPLICIT
Used to mark a profile as an explicit Application Common role. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers. When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the profiles would have been marked as implicit Application Common profile. dbms_pdb.set_profile_explicit(profile_name IN VARCHAR2);
BEGIN
  dbms_pdb.set_profile_explicit('MATRIX_PROFILE');
END;
/
 
SET_ROLE_EXPLICIT
Used to mark a role as an explicit Application Common role. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers. When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the roles would have been marked as implicit Application Common roles. dbms_pdb.set_role_explicit(role_name IN VARCHAR2);
BEGIN
  dbms_pdb.set_role_explicit('MATRIX_RO');
END;
/
 
SET_SHARING_NONE (new 18.1)
Used to set SHARTING=NONE status on an object in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers. dbms_pdb.set_sharing_none(
schema_name  IN VARCHAR2,
object_name  IN VARCHAR2,
namespace    IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
exec dbms_pdb.set_sharing_none('UWCLASS', 'SSNONE', 1, 'ORA$BASE');
 
SET_USER_EXPLICIT
Used to mark a user as an explicit Application Common user. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers. When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the users would have been marked as implicit Application Common users. dbms_pdb.set_user_explicit(user_name IN VARCHAR2);
BEGIN
  dbms_pdb.set_user_explicit('MATRIX');
END;
/
 
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 and returns 0 if the next scheduled job change time does not need to be changed: N if the next scheduled time should be N seconds from SYSDATE dbms_pdb.update_cdbvw_stats RETURN NUMBER;
set timing on

SQL> SELECT dbms_pdb.update_cdbvw_stats
  2  FROM dual;

UPDATE_CDBVW_STATS
------------------
              3600

Elapsed: 00:04:27.10

set timing off
-- will appear to hang the system for a very long time while stats are collected
 
UPDATE_COMDATA_STATS
Update stats for common data views dbms_pdb.update_comdata_stats RETURN NUMBER;
set timing on

SQL> SELECT dbms_pdb.update_comdata_stats
  2  FROM dual;

UPDATE_COMDATA_STATS
--------------------
                3600

Elapsed: 00:00:02.64

set timing off
 
UPDATE_DATALINK_STATS
Update Stats for Data Linked Views. Cannot be run from inside a PDB. dbms_pdb.update_datalink_stats RETURN NUMBER;
set timing on

SQL> SELECT dbms_pdb.update_datalink_stats
  2  FROM dual;

UPDATE_DATALINK_STATS
---------------------
                 3600

set timing off
 
UPDATE_VERSION
Update PDB's VSN in container$ after upgrade dbms_pdb.update_version;
exec dbms_pdb_update_version;

Related Topics
Application Containers
Built-in Functions
Built-in Packages
CDBVIEW
Container Database
DBMS_PDB_APP_CON
DBMS_PDB_ALTER_SHARING
DBMS_PDB_EXEC_SQL
DBMS_PDB_IS_VALID_PATH
GET_AUD_PDB_LIST
Pluggable Database
What's New In 12cR2
What's New In 18cR3

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-2017 Daniel A. Morgan All Rights Reserved