Oracle PRVTEMX_DBHOME
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 18cR3
What's New In 19cR3

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