Oracle PRVTEMX_DBHOME
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 Undocumented
AUTHID CURRENT_USER
Dependencies
DBA_CDB_RSRC_PLAN_DIRECTIVES PDB_PLUG_IN_VIOLATIONS V$PARAMETER
DBMS_ASSERT PRVTEMX_ADMIN V$PDBS
DBMS_REPORT PRVTEMX_RSRCMGR

V$RSRC_PLAN

DBMS_REPORT_LIB PRVT_AWR_VIEWER V$SYSTEM_PARAMETER
DBMS_SQL PRVT_EMX V$TEMPFILE
DBMS_SQLTUNE_UTIL0 PRVT_REPORT_TAGS WRI$_REPT_DBHOME
DBMS_STANDARD SYS_IXMLAGG XMLAGG
DBMS_SYSTEM V$CONTAINERS XMLTYPE
DBMS_SYS_ERROR V$DATAFILE XQSEQUENCE
DUAL    
Documented No
First Available 12.1.0
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsemx_dbhome.plb
Subprograms
 
ALTER_PDB_XML
Undocumented prvtemx_dbhome.alter_pdb_xml(
pdb_name    IN VARCHAR2,
action      IN VARCHAR2,
show_sql    IN NUMBER,
action_arg1 IN VARCHAR2,
action_arg2 IN VARCHAR2,
action_arg3 IN VARCHAR2,
action_arg4 IN VARCHAR2)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.alter_pdb_xml('PDBDEV', 'UNPLUG', 1)
FROM dual;

PRVTEMX_DBHOME.ALTER_PDB_XML('PDBDEV','UNPLUG',1)
--------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id> <![CDATA[/orarep/dbhome/alter_pdb%3faction%3dUNPLUG%26pdb%3dPDBDEV%26show_sql%3d1]]> </report_id>
  <sql/>
</report>
 
CLONE_PDB_XML
Writes the SQL to clone a PDB prvtemx_dbhome.clone_pdb_xml(
pdb_name       IN VARCHAR2,
pdb_source     IN VARCHAR2,
dblink         IN VARCHAR2,
datafile_src   IN VARCHAR2,
datafile_path  IN VARCHAR2,
oracle_managed IN NUMBER,
snapshot       IN NUMBER,
resource_plan  IN VARCHAR2,
shares         IN NUMBER,
cpu_limit      IN NUMBER,
px_limit       IN NUMBER,
show_sql       IN NUMBER)
RETURN XMLTYPE;
-- from the following demo you can clearly see that it validates that the named PDB is valid
SELECT prvtemx_dbhome.clone_pdb_xml('PDBTEST', 'c:\stage', show_sql=>1)
FROM dual;

PRVTEMX_DBHOME.CLONE_PDB_XML('PDBTEST','C:\STAGE',SHOW_SQL=>1)
---------------------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.01" cpu_time="0.02" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id> <![CDATA[/orarep/dbhome/clone_pdb%3ffrom_pdb%3dc%3a%5cstage%26pdb%3dPDBTEST%26show_sql%3d1%26snapshot%3d0]]> </report_id>
  <sql>
    create pluggable database &quot;PDBTEST&quot; from &quot;C:\STAGE&quot;
    tempfile reuse ;
    alter pluggable database &quot;PDBTEST&quot; open read write;
  </sql>
</report>
 
CONVERT_NONCDB2PDB_XML
Writes the SQL to convert a non-CDB database to a PDB prvtemx_dbhome.convert_noncdb2pdb_xml(show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_dbhome.convert_noncdb2pdb_xml(1)
FROM dual;

<PRVTEMX_DBHOME.CONVERT_NONCDB2PDB_XML(1)
-------------------------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.02" cpu_time="0.02" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/dbhome/noncdb2pdb]]></report_id>
  <medatafile>
    <file>orabase182pdb_201811191033.xml</file>
    <dir>C:\APPS18\ORADATA\ORABASE18\</dir>
  </medatafile>
  <sql>
begin
dbms_pdb.describe(pdb_descr_file =&gt; &apos;C:\APPS18\ORADATA\ORABASE18\orabase182pdb_201811191033.xml&apos;);
end;
/</sql>
</report>
 
CREATE_PDB_XML
Write the SQL to create a pluggable database prvtemx_dbhome.create_pdb_xml(
pdb_name      IN VARCHAR2,
admin_user    IN VARCHAR2,
admin_pwd     IN VARCHAR2,
datafile_path IN VARCHAR2,
max_size      IN VARCHAR2,
max_tempsize  IN VARCHAR2,
resource_plan IN VARCHAR2,
shares        IN NUMBER,
cpu_limit     IN NUMBER,
px_limit      IN NUMBER,
show_sql      IN NUMBER)
RETURN XMLTYPE;
-- nice to see that Oracle knows how to obfuscate a password
SELECT prvtemx_dbhome.create_pdb_xml('PDBTEST', 'PDBDBA', 'NoWay!', show_sql=>1)
FROM dual;

PRVTEMX_DBHOME.CREATE_PDB_XML('PDBTEST','PDBDBA','NOWAY!',SHOW_SQL=>1)
----------------------------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id> <![CDATA[/orarep/dbhome/create_pdb%3fadmin_user%3dPDBDBA%26pdb%3dPDBTEST%26show_sql%3d1]]> </report_id>
  <sql>
    create pluggable database &quot;PDBTEST&quot; admin user &quot;PDBDBA&quot; identified by ********** ;
    alter pluggable database &quot;PDBTEST&quot; open read write;
  </sql>
</report>
 
DROP_PDB_XML
Writes the SQL to drop a PDB prvtemx_dbhome.drop_pdb_xml(
pdb_name          IN VARCHAR2,
include_datafiles IN NUMBER,
include_plan      IN VARCHAR2,
show_sql          IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.drop_pdb_xml('PDBTEST', 1, show_sql=>1)
FROM dual;

PRVTEMX_DBHOME.DROP_PDB_XML('PDBTEST',1,SHOW_SQL=>1)
-------------------------------------------------------------------------------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.04" cpu_time="0.03" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id> <![CDATA[/orarep/dbhome/drop_pdb%3finclude_plan%3d1%26pdb%3dPDBTEST%26show_sql%3d1]]> </report_id>
  <sql>alter pluggable database &quot;PDBTEST&quot; close IMMEDIATE ;
       drop pluggable database &quot;PDBTEST&quot; INCLUDING DATAFILES;
    -- declare bind variables
    var b1 varchar2(4000);
    var b2 varchar2(4000);

    -- init bind values
    exec :b1 := &apos;1&apos;;
    exec :b2 := &apos;PDBTEST&apos;;

    declare
     plan_not_applied exception;
     pragma exception_init(plan_not_applied, -29362);
     pending_area_ex exception;
     pragma exception_init(pending_area_ex, -29370);
     wrong_plan_ex exception;
     pragma exception_init(wrong_plan_ex, -29358);

    begin
      sys.dbms_resource_manager.clear_pending_area();
      sys.dbms_resource_manager.create_pending_area();
      sys.dbms_resource_manager.delete_cdb_plan_directive(
      plan =&gt; :b1,
      pluggable_database =&gt; :b2 );
      sys.dbms_resource_manager.validate_pending_area();
      sys.dbms_resource_manager.submit_pending_area();
    exception
      when plan_not_applied or pending_area_ex or wrong_plan_ex then
        null;
    end;
    /
  </sql>
</report>
 
I_BUILD_ERROR_XML (new 18.1)
Writes the XML for an PDB build error prvtemx_dbhome.i_build_error_xml(
pdb_name IN VARCHAR2,
action   IN VARCHAR2,
err_mesg IN VARCHAR2)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.i_build_error_xml('PDBTEST', 'UNPLUG', 'Oops!')
FROM dual;

PRVTEMX_DBHOME.I_BUILD_ERROR_XML('PDBTEST','UNPLUG','OOPS!')
-------------------------------------------------------------
<error action="UNPLUG" pdb="PDBTEST">Oops!</error>
 
PLUG_PDB_XML
Writes the SQL to plug in a PDB prvtemx_dbhome.plug_pdb_xml(
pdb_name       IN VARCHAR2
xmlfile_name   IN VARCHAR2
datafile_src   IN VARCHAR2
datafile_dst   IN VARCHAR2
oracle_managed IN NUMBER
resource_plan  IN VARCHAR2
shares         IN NUMBER
cpu_limit      IN NUMBER
px_limit       IN NUMBER
show_sql       IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.plug_pdb_xml('PDBTEST', 'c:\stage\testpdb.xml', show_sql=>1)
FROM dual;

RVTEMX_DBHOME.PLUG_PDB_XML('PDBTEST','C:\STAGE\TESTPDB.XML',SHOW_SQL=>1)
-------------------------------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id> <![CDATA[/orarep/dbhome/plug_pdb%3fpdb%3dPDBTEST%26show_sql%3d1%26xmlfile_name%3dc%3a%5cstage%5ctestpdb.xml]]> </report_id>
  <sql>create pluggable database &quot;PDBTEST&quot;
    using &apos;c:\stage\testpdb.xml&apos;
    nocopy
    tempfile reuse ;

    alter pluggable database &quot;PDBTEST&quot; open read write;
  </sql>
</report>
 
REPORT_CONTAINERS_XML
Undocumented prvtemx_dbhome.report_containers_xml(show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_containers_xml(1)
FROM dual;

PRVTEMX_DBHOME.REPORT_CONTAINERS_XML(1)
----------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.22" cpu_time="0.21" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/dbhome/show_containers]]></report_id>
  <region id="status" cpu_time=".03" elapsed_time=".04">
    <status db_id="1262297360" db_name="ORABASE1" container_name="CDB$ROOT" pdb_cnt="1" db_unique_name="orabase18" oracle_home="C:\app18" db_status="Open" db_version="18.3.0.0.0" db_platform_name="Microsoft Windows x86 64-bit" db_platform_id="12" db_log_mode="NOARCHIVELOG" db_startup_since_sec="179889" inst_cnt="1"  inst_id="1" inst_name="orabase18" host_name="PERRITO5" parallel="No" thread_num="1" archiver="Stopped" local_undo="enabled" shutdown="No" active_state="Normal" logins="Allowed" instance_mode="Regular" edition="EE"/>
  </region>
  <report db_version="18.0.0.0.0" elapsed_time="0.06" cpu_time="0.07" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
    <report_id><![CDATA[/orarep/perfpage/main%3freport_level%3dbasic-servicestat-waitclass]]></report_id>
    <report_parameters>
      <bucket_max_count>128</bucket_max_count>
      <report_level>basic-servicestat-waitclass</report_level>
      <show_sql>0</show_sql>
    </report_parameters>
    <target start_time="11/19/2018 09:49:00" end_time="11/19/2018 10:49:56" duration="3656" bucket_count="61" bucket_interval="60" is_rac="no" is_cdb="yes" inst_low="0" inst_high="10000"/>
    <references>
      <reference name="instances" value="1"/>
      <reference name="NUM_CPU_CORES" value="2"/>
      <reference name="NUM_CPUS" value="4"/>
      <reference name="cpu_count" value="4"/>
      <reference name="db_block_size" value="8192"/>
    </references>
    <cpu_info>
      <host name="PERRITO5" num_cores="4" num_cpus="2">
      <instance id="1"/>
      </host>
    </cpu_info>
    <stattype name="activity" dim="pdbstat">
      <stat_info>
        <stat id="1" name="CDB$ROOT"/>
        <stat id="3" name="PDBDEV"/>
      </stat_info>
      <buckets bucket_interval="60" bucket_count="61" start_time="11/19/2018 09:49:00" end_time="11/19/2018 10:49:56" duration="3656">
        <bucket bucket_id="27">
        <stat id="1" aas=".333333" aas_fg="0" aas_cpu=".233333"/>
        <stat id="3" aas=".016667" aas_fg="0" aas_cpu=".016667"/>
      </bucket>
      <bucket bucket_id="28">
        <stat id="1" aas=".2" aas_fg="0" aas_cpu=".2"/>
        <stat id="3" aas=".05" aas_fg="0" aas_cpu=".05"/>
      </bucket>
      <bucket bucket_id="29">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="30">
        <stat id="1" aas=".166667" aas_fg="0" aas_cpu=".166667"/>
      </bucket>
      <bucket bucket_id="31">
        <stat id="1" aas=".116667" aas_fg="0" aas_cpu=".116667"/>
      </bucket>
      <bucket bucket_id="32">
        <stat id="1" aas=".283333" aas_fg="0" aas_cpu=".283333"/>
      </bucket>
      <bucket bucket_id="33">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="34">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="35">
        <stat id="1" aas=".116667" aas_fg="0" aas_cpu=".116667"/>
      </bucket>
      <bucket bucket_id="36">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="37">
        <stat id="1" aas=".116667" aas_fg="0" aas_cpu=".116667"/>
      </bucket>
      <bucket bucket_id="38">
        <stat id="1" aas=".166667" aas_fg="0" aas_cpu=".166667"/>
      </bucket>
      <bucket bucket_id="39">
        <stat id="1" aas=".1" aas_fg="0" aas_cpu=".1"/>
      </bucket>
      <bucket bucket_id="40">
        <stat id="1" aas=".116667" aas_fg="0" aas_cpu=".116667"/>
      </bucket>
      <bucket bucket_id="41">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="42">
        <stat id="1" aas=".183333" aas_fg="0" aas_cpu=".183333"/>
      </bucket>
      <bucket bucket_id="43">
        <stat id="1" aas=".166667" aas_fg="0" aas_cpu=".166667"/>
      </bucket>
      <bucket bucket_id="44">
        <stat id="1" aas=".15" aas_fg="0" aas_cpu=".15"/>
      </bucket>
      <bucket bucket_id="45">
        <stat id="1" aas=".166667" aas_fg="0" aas_cpu=".166667"/>
      </bucket>
      <bucket bucket_id="46">
        <stat id="1" aas=".15" aas_fg="0" aas_cpu=".15"/>
      </bucket>
      <bucket bucket_id="47">
        <stat id="1" aas=".166667" aas_fg="0" aas_cpu=".166667"/>
      </bucket>
      <bucket bucket_id="48">
        <stat id="1" aas=".15" aas_fg="0" aas_cpu=".15"/>
      </bucket>
      <bucket bucket_id="49">
        <stat id="1" aas=".1" aas_fg="0" aas_cpu=".1"/>
      </bucket>
      <bucket bucket_id="50">
        <stat id="1" aas=".05" aas_fg="0" aas_cpu=".05"/>
      </bucket>
      <bucket bucket_id="51">
        <stat id="1" aas=".15" aas_fg="0" aas_cpu=".15"/>
      </bucket>
      <bucket bucket_id="52">
        <stat id="1" aas=".15" aas_fg="0" aas_cpu=".15"/>
      </bucket>
      <bucket bucket_id="53">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="54">
        <stat id="1" aas=".116667" aas_fg="0" aas_cpu=".116667"/>
      </bucket>
      <bucket bucket_id="55">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="56">
        <stat id="1" aas=".15" aas_fg="0" aas_cpu=".15"/>
      </bucket>
      <bucket bucket_id="57">
        <stat id="1" aas=".15" aas_fg="0" aas_cpu=".15"/>
      </bucket>
      <bucket bucket_id="58">
        <stat id="1" aas=".116667" aas_fg="0" aas_cpu=".116667"/>
      </bucket>
      <bucket bucket_id="59">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="60">
        <stat id="1" aas=".133333" aas_fg="0" aas_cpu=".133333"/>
      </bucket>
      <bucket bucket_id="61">
        <stat id="1" aas=".083333" aas_fg="0" aas_cpu=".083333"/>
      </bucket>
    </buckets>
    </stattype>
  </report>
  <containers>
    <resource_plan>
      <default_pdb_directive/>
    </resource_plan>
    <default_pdb_datafiles omf="n" snapshot_copy="n">C:\apps18\orabase18\datafile</default_pdb_datafiles>
    <spfile>C:\APP18\DATABASE\SPFILEORABASE18.ORA</spfile>
    <container con_id="3" name="PDBDEV" open_time="161816" restricted="NO" app_root="NO" app_pdb="NO" status="NORMAL" is_proxy_pdb="NO" size="2673475584" pdb_plan="INTERNAL_PLAN">
    <open_mode r="0" w="1" c="0" m="0"/>
    <resource_limit cpu_utilized="0" running_sess="0" waiting_sess="0" pga_used="33756076" sga_used="225224816" buffer_cache_used="129056768" shared_pool_used="96168048" active="0" queued="0" iops=".22" iombps="0"/>
    <activity cpu="0" io="0" wait="0" cluster="0"/>
    <violations e="0" w="2"/>
    </container>
  </containers>
</report>
 
REPORT_DBHOME_XML
Undocumented prvtemx_dbhome.report_db_home_xml(
p_regions           IN VARCHAR2,
p_inst_id           IN NUMBER,
p_top_n             IN NUMBER,
p_ago_seconds       IN NUMBER,
p_last_refresh_time IN DATE,
p_show_sql          IN NUMBER)
RETURN XMLTYP
TBD
 
REPORT_INCIDENTS_XML
Undocumented prvtemx_dbhome.report_incidents_xml(
p_inst_id  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_incidents_xml(1, 1)
FROM dual;

PRVTEMX_DBHOME.REPORT_INCIDENTS_XML(1,1)
-----------------------------------------------------------------------
<region id="incidents" cpu_time="0" elapsed_time="0"><script><![CDATA[

 -- parameters needed to run the show parameter query
 var b_date_fmt varchar2(100);
 var b_inst_id_low number;
 var b_inst_id_high number;

 -- initialize parameter value
begin
  :b_date_fmt := dbms_report.date_fmt;
  :b_inst_id_low := 1;
  :b_inst_id_high := 1;
end;
/

-- SQL building XML to get all incidents for the last day
-- Note: use gv$ to make sure we get all incidents cluster
-- wide in case ADR is not shared between these
-- instances
select
  xmlelement("incidents", null,
    xmlagg(
      xmlelement("incident",
        xmlattributes(inc.inst_id as "inst_id", cont.name as "con_name", ADR_HOME as "home", INCIDENT_ID as "id", PROBLEM_ID as "pb_id", cdate as "cdate"),
      replace(regexp_replace(error_message, '[[:cntrl:]]', ''), chr(0), ''))
    order by cdate desc))
from
  table(sys.gv$(cursor(
    select /*+ no_merge(ho) leading(ho) use_hash(di) */
      userenv('INSTANCE') inst_id,
      di.adr_home,
      di.incident_id,
      di.con_id,
      di.problem_id,
      to_char(di.create_time,
      :b_date_fmt) cdate,
      dbms_report.format_message(di.error_number, di.error_facility, null, error_arg1, error_arg2, error_arg3, error_arg4, error_arg6, error_arg7, error_arg8, error_arg9, error_arg10, error_arg11, error_arg12) error_message
    from v$diag_incident di -- all incidents in adr of the current inst
    where di.create_time > systimestamp - interval '1' day -- last day
    and di.status = 2 -- ready status
    and di.close_time is null))) inc, -- not closed
    v$containers cont
where inc.inst_id between :b_inst_id_low and :b_inst_id_high
and inc.con_id = cont.con_id
;]]></script></region>
 
REPORT_INSTANCE_DETAILS_XML
Undocumented prvtemx_dbhome.report_instance_details_xml(
p_inst_id  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_instance_details_xml(1, 1)
FROM dual;
 
REPORT_JOBS_XML
Undocumented prvtemx_dbhome.report_jobs_xml(
p_inst_id  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_jobs_xml(1,1)
FROM dual;

PRVTEMX_DBHOME.REPORT_JOBS_XML(1,1)
-----------------------------------------------------------------
<region id="jobs" cpu_time="0" elapsed_time="0"><script><![CDATA[

-- parameters needed to run the show parameter query
var b_date_fmt varchar2(100);
var b_inst_id_low number;
var b_inst_id_high number;

-- initialize parameter value
begin
  :b_date_fmt := dbms_report.date_fmt;
  :b_inst_id_low := 1;
  :b_inst_id_high := 1;
end;
/

-- SQL building XML for the jobs region
select
  xmlelement("jobs", null,
   xmlagg(
      xmlelement("job",
        xmlattributes(
          running_instance as "inst_id",
          decode(con_id,null,to_char(con_id),name) as "con_name",
          owner as "owner",
          job_name as "name",
          start_date as "start",
          round(elapsed_sec,0) as "elapsed"),
          null) order by elapsed_sec))
from
  (select v.*, to_char((sysdate - (elapsed_sec/3600/24)), :b_date_fmt) start_date
   from (
     select running_instance, owner, job_name, job_subname, (trunc(sysdate) + elapsed_time - trunc(sysdate))*24*3600 elapsed_sec,c.con_id, c.name
     from CDB_SCHEDULER_RUNNING_JOBS t, v$CONTAINERS c
     where running_instance between :b_inst_id_low and :b_inst_id_high
     and t.con_id = c.con_id (+)) v)
;]]></script></region>
 
REPORT_PDB_STORAGE_LIMITS_XML
SQL to create a PDB storage limits report prvtemx_dbhome.report_pdb_storage_limits_xml(
pdb_name IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT instance_name
FROM v$instance;

SELECT prvtemx_dbhome.report_pdb_storage_limits_xml('ORABASE', 1)
FROM dual;

PRVTEMX_DBHOME.REPORT_PDB_STORAGE_LIMITS_XML('ORABASE',1)
---------------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id> <![CDATA[/orarep/dbhome/pdb_storage_limits%3fpdb%3dORABASE]]> </report_id>
  <script><![CDATA[
    select xmlelement(
      "storage",
      xmlagg(
        xmlelement("limit", xmlattributes(property_name as name), property_value)))
    from database_properties
    where property_name in ('MAX_PDB_STORAGE', 'MAX_SHARED_TEMP_SIZE');]]>
  </script>
</report>
 
REPORT_PDB_VIOLATIONS_XML
SQL to reporton PDB Plug-in Violations prvtemx_dbhome.report_pdb_violations_xml(
pdb_name IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_pdb_violations_xml('PDBDEV', 1)
FROM dual;

PRVTEMX_DBHOME.REPORT_PDB_VIOLATIONS_XML('PDBDEV',1)
-----------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/dbhome/pdb_violations]]></report_id>
  <script><![CDATA[
    select xmlelement("pdb_violations",
      xmlagg(
        xmlelement(
         "violation",
         xmlattributes(type as type),
         xmlelement("cause", cause),
         xmlelement("message", message),
         xmlelement("action", action))
         order by type))
     from pdb_plug_in_violations
     where name = :pdb_name and status = 'PENDING';]]>
  </script>
</report>
 
REPORT_PERFORMANCE_XML
Creates the SQL to generate a performance report prvtemx_dbhome.report_performance_xml(
p_inst_id  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_performance_xml(1, 1)
FROM dual;
 
REPORT_RESOURCES_XML
Creates the SQL to generate a resources report prvtemx_dbhome.report_resources_xml(
p_inst_id  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
-- the following listing, due to its size, is mostly unformatted
SELECT prvtemx_dbhome.report_resources_xml(1, 1)
FROM dual;
 
REPORT_SHOW_DBLINKS_XML
Creates the SQL to view the DB Links Report prvtemx_dbhome.report_show_dblinks_xml(show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_show_dblinks_xml(1)
FROM dual;

RVTEMX_DBHOME.REPORT_SHOW_DBLINKS_XML(1)
---------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/dbhome/show_dblinks]]></report_id>
  <script><![CDATA[
    select xmlelement("dblinks",
      xmlagg(
        xmlelement("dblink",
          xmlattributes(dl.db_link as "name")) order by dl.db_link))
    from dba_db_links dl
    ;]]>
  </script>
</report>
 
REPORT_SHOW_PDB_INSTANCES_XML (new 18.1)
Writes the XML to show PDB instance information prvtemx_dbhome.report_show_pdb_instances_xml(
pdb_name IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
SQL> SELECT prvtemx_dbhome.report_show_pdb_instances_xml('PDBDEV', 0)
  2* FROM dual;

PRVTEMX_DBHOME.REPORT_SHOW_PDB_INSTANCES_XML('PDBDEV',0)
---------------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.01" cpu_time="0.02" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/dbhome/show_pdb_instances]]></report_id>
  <instances>
    <instance name="orabase18" status="READ WRITE"/>
  </instances>
</report>

SQL> SELECT prvtemx_dbhome.report_show_pdb_instances_xml('PDBDEV', 1)
  2* FROM dual;

PRVTEMX_DBHOME.REPORT_SHOW_PDB_INSTANCES_XML('PDBDEV',1)
---------------------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/dbhome/show_pdb_instances]]></report_id>
  <script><![CDATA[
    select
      xmlelement("instances",
        xmlagg(xmlelement("instance",
          xmlattributes(i.instance_name as "name", c.open_mode as "status"))
          order by i.instance_name))
    from gv$instance i, gv$containers c
    where c.name = :pdb_name
    and c.inst_id = i.inst_id;]]>
  </script>
</report>
 
REPORT_SQLMONITOR_XML
Writes the SQL for a SQL Monitor report prvtemx_dbhome.report_sqlmonitor_xml(
p_inst_id     IN NUMBER,
p_top_n       IN NUMBER,
p_ago_seconds IN NUMBER,
p_show_sql    IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_sqlmonitor_xml(1, 10, p_show_sql=>1)
FROM dual;
 
REPORT_STATUS_XML
Write the SQL for an instance status report prvtemx_dbhome.report_status_xml(
p_inst_id  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;

-- the following listing, due to its size, is mostly unformatted
SELECT prvtemx_dbhome.report_status_xml(1, 1)
FROM dual;

 
UNPLUG_PDB_XML
Writes the SQL to unplug a PDB prvtemx_dbhome.unplug_pdb_xml(
pdb_name IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_dbhome.unplug_pdb_xml('PDBDEV', 1)
FROM dual;

PRVTEMX_DBHOME.UNPLUG_PDB_XML('PDBDEV',1)
------------------------------------------
<report db_version="18.0.0.0.0" elapsed_time="0.02" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/dbhome/unplug_pdb%3fpdb%3dPDBDEV%26show_sql%3d1]]></report_id>
  <medatafile>
    <file>pdb_PDBDEV_201811190234.xml</file>
    <dir>C:\APPS18\ORADATA\ORABASE18\PDBDEV\</dir>
  </medatafile>
  <sql>
    alter pluggable database &quot;PDBDEV&quot; close IMMEDIATE ;
    alter pluggable database &quot;PDBDEV&quot; unplug into &apos;C:\APPS18\ORADATA\ORABASE18\PDBDEV\pdb_PDBDEV_201811190234.xml&apos;;
    drop pluggable database &quot;PDBDEV&quot; KEEP DATAFILES;
  </sql>
</report>

Related Topics
Built-in Functions
Built-in Packages
DBMS_REPORT
PRVTEMX_ADMIN
PRVTEMX_CELL
PRVTEMX_MEMORY
PRVTEMX_PERF
Built-in Functions
Built-in Packages
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