Oracle PRVTEMX_DBHOME
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Undocumented
AUTHID CURRENT_USER
Dependencies
DBA_CDB_RSRC_PLAN_DIRECTIVES DUAL V$PARAMETER
DBMS_ASH PDB_PLUG_IN_VIOLATIONS V$PDBS
DBMS_ASSERT PRVTEMX_ADMIN V$RSRC_PLAN
DBMS_REPORT PRVTEMX_RSRCMGR V$SYSTEM_PARAMETER
DBMS_REPORT_LIB PRVT_AWR_VIEWER V$TEMPFILE
DBMS_SQL PRVT_EMX WRI$_REPT_DBHOME
DBMS_SQLTUNE_UTIL0 PRVT_REPORT_TAGS XMLAGG
DBMS_STANDARD SYS_IXMLAGG XMLTYPE
DBMS_SYSTEM V$CONTAINERS XQSEQUENCE
DBMS_SYS_ERROR V$DATAFILE  
Documented No
Exceptions
Error Code Reason
ORA-65011 Pluggable database PDBDEV does not exist.
First Available 12.1
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="21.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <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 see that it verfies the named PDB is valid
SELECT prvtemx_dbhome.clone_pdb_xml('PDBTEST', '/01/orabase/pdbtest', show_sql=>1)
FROM dual;

PRVTEMX_DBHOME.CLONE_PDB_XML('PDBTEST','C:\STAGE',SHOW_SQL=>1)
---------------------------------------------------------------
<report db_version="21.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="-18000" 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>


<report db_version="21.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <report_id>    <![CDATA[/orarep/dbhome/clone_pdb%3ffrom_pdb%3d/01/orabase/pdbtest%
26pdb%3dPDBTEST%26show_sql%3d1%26snapshot%3d0]]>
  </report_id>
  <sql>
    create pluggable database &quot;PDBTEST&quot; from &quot;/01/ORABASE/PDBTEST&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="21.0.0.0.0" elapsed_time="0.01" cpu_time="0.01" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <report_id><![CDATA[/orarep/dbhome/noncdb2pdb]]></report_id>
  <medatafile>
    <file>test21db_iad25g2pdb_202106190518.xml</file>
    <dir>/u01/app/oracle/product/21.0.0.0/dbhome_1/assistants/dbca/templates/</dir>
  </medatafile>
  <sql>
  begin
    dbms_pdb.describe(pdb_descr_file =&gt;
      &apos;/u01/app/oracle/product/21.0.0.0/dbhome_1/assistants/dbca/templates/
      test21db_iad25g2pdb_202106190518.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="21.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
    <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="21.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="-18000" 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
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="21.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="-18000" 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 db_version="21.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <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_ASH_XML (new 21c)
Undocumented prvtemx_dbhome.report_ash_xml(
p_show_sql           IN NUMBER,
p_ash_mode           IN VARCHAR2,
p_ash_begin_time_utc IN VARCHAR2,
p_ash_bucket_size    IN NUMBER)
RETURN XMLTYPE;
TBD
 
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="21.0.0.0.0" elapsed_time="0.79" cpu_time="0.54" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <report_id><![CDATA[/orarep/dbhome/show_containers]]></report_id>
  <region id="status" cpu_time=".05" elapsed_time=".07">
    <status db_id="2140826538" db_name="TEST21DB" container_name="CDB$ROOT" pdb_cnt="1" db_unique_name="test21db_iad25g" oracle_home="/u01/app/oracle/product/21.0.0.0/dbhome_1" db_status="Open" db_version="21.1.0.0.0" db_platform_name="Linux x86 64-bit" db_platform_id="13" db_log_mode="ARCHIVELOG" db_startup_since_sec="13273595" inst_cnt="1" inst_id="1" inst_name="test21db" host_name="test21" parallel="No" thread_num="1" archiver="Started" local_undo="enabled" shutdown="No" active_state="Normal" logins="Allowed" instance_mode="Regular" edition="EE"/>
  </region>
  <report db_version="21.0.0.0.0" elapsed_time="0.19" cpu_time="0.14" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <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>
...
       <open_mode r="0" w="1" c="0" m="0"/>
      <resource_limit cpu_utilized="0" running_sess="0" waiting_sess="0" pga_used="0" sga_used="1363319455" buffer_cache_used="481020815" shared_pool_used="834467024" active="0" que
ued="0" iops="0" iombps="0"/>
      <activity cpu="0" io="0" wait="0" cluster="0"/>
      <violations e="0" w="3"/>
    </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;

PRVTEMX_DBHOME.REPORT_INSTANCE_DETAILS_XML(1,1)
----------------------------------------------------------------------------------
<report db_version="21.0.0.0.0" elapsed_time="0.00" cpu_time="0.01" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <report_id><![CDATA[/orarep/dbhome/show_instance_details]]></report_id>
  <script><![CDATA[
    -- parameters needed to run the show parameter query
    var b_inst_id_low number;
    var b_inst_id_high number;
    var p_top_n_count number;
    var p_top_n_rankby varchar2(30);
...
      union all
      -- other attributes
      select 'instance', null subclass,
              instance_number v1,
              round((sysdate - startup_time)*3600*24) v2,
              thread# v3,
              instance_name v4,
              host_name v5,
              version v6,
              nls_initcap(status) v7,
              nls_initcap(archiver) v8,
              nls_initcap(log_switch_wait) v9,
              nls_initcap(logins) v10,
              nls_initcap(shutdown_pending) v11,
              nls_initcap(instance_role) v12,
              nls_initcap(active_state) v13,
              0 v14
         from v$instance
      ) rv0
      ) rv00
     ) rv1
    ))) rv2
   where inst_id between :b_inst_id_low and :b_inst_id_high) rv3
  where inst_rank <= nvl(:p_top_n_count, 1)
  )
  group by inst_id
  ;]]></script>
</report>
 
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="21.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="-18000" 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="21.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="-18000" 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;

PRVTEMX_DBHOME.REPORT_PERFORMANCE_XML(1,1)
----------------------------------------------------------------------------------------
<region id="performance" cpu_time=".01" elapsed_time=".02"><report db_version="21.0.0.0.0" elapsed_time="0.01" cpu_time="0.01" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB
$ROOT" timezone_offset="0" packs="2" service_type="0">
  <report_id>
    <![CDATA[/orarep/perfpage/main%3finst_id%3d1%26report_level%3dbasic]]>
  </report_id>
  <report_parameters>
    <inst_id>1</inst_id>
    <bucket_max_count>128</bucket_max_count>
    <report_level>basic</report_level>
    <show_sql>1</show_sql>
  </report_parameters>
  <target start_time="06/19/2021 16:33:00" end_time="06/19/2021 17:33:10" duration="3610" bucket_count="61" bucket_interval="60" is_rac="no" is_cdb="yes" inst_id="1"/>
...
      select dim, bucket_id, id, aas, aas_fg, aas_cpu,
      nvl2(name, xmlelement("stat", xmlattributes(id as "id", name as "name")), null)
      stat_id
      from grouping_sets)
      group by dim,bucket_id)
    group by dim)]]></script>
  </report>
</region>
 
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;

PRVTEMX_DBHOME.REPORT_RESOURCES_XML(1,1)
------------------------------------------------------------------------
<region id="resources" 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;
  var b_con_id number;

  -- initialize parameter value
  begin
    :b_date_fmt := dbms_report.date_fmt;
    :b_inst_id_low := 1;
    :b_inst_id_high := 1;
    :b_con_id := 1;
  end;
  /
--
-- SQL building XML for all resources (cpu, active sessions, memory and
-- space)
...
         (select cont_name, max(cont_size)/1024/1024 cont_size
          from table(sys.gv$(cursor(
           select name cont_name,
           total_size cont_size
           from v$pdbs
           where con_id > 2
          ))) group by cont_name)
         )
      end,
      null))
  from sys.dual
;]]></script></region>
 
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="21.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="-18000" 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
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;
SELECT prvtemx_dbhome.report_show_pdb_instances_xml('PDBDEV', 0)
FROM dual;

PRVTEMX_DBHOME.REPORT_SHOW_PDB_INSTANCES_XML('PDBDEV',0)
---------------------------------------------------------
<report db_version="21.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="-18000" packs="2">
  <report_id><![CDATA[/orarep/dbhome/show_pdb_instances]]></report_id>
  <instances>
    <instance name="orabasexix" status="READ WRITE"/>
  </instances>
</report>


SELECT prvtemx_dbhome.report_show_pdb_instances_xml('PDBDEV', 1)
FROM dual;

PRVTEMX_DBHOME.REPORT_SHOW_PDB_INSTANCES_XML('PDBDEV',1)
---------------------------------------------------------
<report db_version="21.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="-18000" 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="21.0.0.0.0" elapsed_time="0.01" cpu_time="0.00" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <report_id>
    <![CDATA[/orarep/dbhome/unplug_pdb%3fpdb%PDBDEV%26show_sql%3d1]]>
  </report_id>
  <medatafile>
    <file>pdb_PDBDEV_202106190539.xml</file>
    <dir>/u01/app/oracle/product/21.0.0.0/dbhome_1/assistants/dbca/templates/</dir>
  </medatafile>
  <sql>
    alter pluggable database &quot;PDBDEV&quot; close IMMEDIATE ;
    alter pluggable database &quot;PDBDEV&quot; unplug into
&apos;/u01/app/oracle/product/21.0.0.0/dbhome_1/assistants/dbca/templates/
pdb_PDBDEV_202106190539.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 21c
What's New In 23c

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