Oracle PRVTEMX_DBHOME
Version 12.1.0.2

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

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
Purpose Undocumented
AUTHID CURRENT_USER
Dependencies
DBA_CDB_RSRC_PLAN_DIRECTIVES PDB_PLUG_IN_VIOLATIONS V$PDBS
DBMS_ASSERT PRVT_AWR_VIEWER V$SYSTEM_PARAMETER2
DBMS_REPORT PRVT_EMX V$TEMPFILE
DBMS_REPORT_LIB PRVT_REPORT_TAGS WRI$_REPT_DBHOME
DBMS_SQL SYS_IXMLAGG XMLAGG
DBMS_SQLTUNE_UTIL0 V$CONTAINERS XMLTYPE
DBMS_SYS_ERROR V$DATAFILE XQSEQUENCE
DUAL V$PARAMETER  
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;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/dbhome/alter_pdb?action=UNPLUG&pdb=PDBDEV&show_sql=1]]></report_id>
<sql/>
</report>
 
CLONE_PDB_XML
Undocumented 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;
ERROR:
ORA-65011: Pluggable database C:\STAGE does not exist.
ORA-06512: at "SYS.PRVTEMX_DBHOME", line 4721
ORA-06512: at line 1
 
CONVERT_NONCDB2PDB_XML
Undocumented prvtemx_dbhome.convert_noncdb2pdb_xml(show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_dbhome.convert_noncdb2pdb_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.02" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/dbhome/noncdb2pdb]]></report_id>
  <medatafile>
   <file>orabase2pdb_201412280538.xml</file>
   <dir>C:\APP\ORACLE\ORADATA\ORABASE\</dir>
  </medatafile>
  <sql>
   begin
     dbms_pdb.describe(pdb_descr_file =&gt; &apos;C:\APP\ORACLE\ORADATA\ORABASE\orabase2pdb_201412280538.xml&apos;);
   end;
   /
  </sql>
 </report>
 
CREATE_PDB_XML
Undocumented 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;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/dbhome/create_pdb?admin_user=PDBDBA&pdb=PDBTEST&show_sql=1]]></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
Undocumented 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;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/dbhome/drop_pdb?include_datafiles=1&pdb=PDBTEST&show_sql=1]]></report_id>
 <sql>
  alter pluggable database &quot;PDBTEST&quot; close IMMEDIATE;
  drop pluggable database &quot;PDBTEST&quot; INCLUDING DATAFILES;
 </sql>
</report>
 
I_APPLY_RESOURCE_PLAN
Undocumented prvtemx_dbhome.i_apply_resource_plan(
plan_name IN VARCHAR2,
pdb_name  IN VARCHAR2,
shares    IN NUMBER,
cpu_limit IN NUMBER,
px_limit  IN NUMBER,
show_sql  IN NUMBER)
RETURN CLOB;
SELECT prvtemx_dbhome.i_apply_resource_plan('TESTPLAN', 'PDBDEV', 6, show_sql=>1)
FROM dual;

-- declare bind variables
var b1 varchar2(4000);
var b2 varchar2(4000);
var b3 number;
var b4 number;
var b5 number;

-- init bind values
exec :b1 := 'TESTPLAN';
exec :b2 := 'PDBDEV';
exec :b3 := 6;
exec :b4 := 100;
exec :b5 := 100;

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_cdb_plan_directive(
    plan => :b1,
    pluggable_database => :b2,
    shares => :b3,
    utilization_limit => :b4,
    parallel_server_limit => :b5);
  sys.dbms_resource_manager.validate_pending_area();
  sys.dbms_resource_manager.submit_pending_area();
end;
/
 
I_UPDATE_RESOURCE_PLAN
Undocumented prvtemx_dbhome.i_update_resource_plan(
plan_name IN VARCHAR2,
pdb_name  IN VARCHAR2,
shares    IN NUMBER,
cpu_limit IN NUMBER,
px_limit  IN NUMBER,
show_sql  IN NUMBER)
RETURN CLOB;
SELECT prvtemx_dbhome.i_update_resource_plan('TESTPLAN', 'PDBDEV', 6, show_sql=>1)
FROM dual;

-- declare bind variables
var b1 varchar2(4000);
var b2 varchar2(4000);
var b3 number;
var b4 number;
var b5 number;

-- init bind values
exec :b1 := 'TESTPLAN';
exec :b2 := 'PDBDEV';
exec :b3 := 6;
exec :b4 := 100;
exec :b5 := 100;

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.update_cdb_plan_directive(
    plan => :b1,
    pluggable_database => :b2,
    new_shares => :b3,
    new_utilization_limit => :b4,
    new_parallel_server_limit => :b5 );
  sys.dbms_resource_manager.validate_pending_area();
  sys.dbms_resource_manager.submit_pending_area();
end;
/
 
PLUG_PDB_XML
Undocumented 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;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id>
  <![CDATA[/orarep/dbhome/plug_pdb?pdb=PDBTEST&show_sql=1&xmlfile_name=c:\stage\testpdb.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_CDB_RESOURCE_PLANS_XML
Undocumented prvtemx_dbhome.report_cdb_resource_plans_xml(show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_cdb_resource_plans_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/dbhome/cdb_resource_plans]]></report_id>
 <script><![CDATA[
  select xmlelement("cdb_resource_plans", xmlagg(xmlelement("resource_plan", xmlattributes(plan as name),
         xmlelement("default_pdb_directive", xmlattributes(shares as "shares", utilization_limit as "cpu_limit", parallel_server_limit as "px_limit")))))
  from dba_cdb_rsrc_plan_directives
  where plan not like 'ORA$%'
  and pluggable_database = 'ORA$DEFAULT_PDB_DIRECTIVE'
  and status is null;]]>
 </script>
</report>
 
REPORT_CONTAINERS_XML
Undocumented prvtemx_dbhome.report_containers_xml(show_sql IN NUMBER) RETURN XMLTYPE;
-- output formatting is incomplete due to the time required to do it all
SELECT prvtemx_dbhome.report_containers_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.14" cpu_time="0.14" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/dbhome/show_containers]]></report_id>
 <report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="
CDB$ROOT" timezone_offset="-28800" packs="2">
  <report_id><![CDATA[/orarep/perfpage/main?report_level=basic-servicestat-waitclass]]></report_id>
  <report_parameters>
   <bucket_max_count>128</bucket_max_count>
   <report_level>basic-servicestat-waitclass</report_level>
   <show_sql>1</show_sql>
  </report_parameters>
  <target start_time="12/28/2014 17:15:00" end_time="12/28/2014 18:15:15" duration="3615" bucket_count="61" bucket_interval="60" is_rac="no" is_cdb="yes" inst_low="0" inst_high="100000"/>
  <script type="references"><![CDATA[
   var p_inst_id_low number;
   var p_inst_id_high number;
   var p_is_rac number;
   begin
     :p_inst_id_low := 0;
     :p_inst_id_high := 100000;
   end;
   /
   select xmlelement("references",
          xmlelement("reference",
          xmlattributes('instances' as "name", inst as "value")),
          xmlelement("reference",
          xmlattributes('NUM_CPU_CORES' as "name",
          num_cpu_cores as "value")),
          xmlelement("reference",
          xmlattributes('NUM_CPUS' as "name", num_cpus as "value")),
          xmlelement("reference",
          xmlattributes('cpu_count' as "name", cpu_count as "value")),
          xmlelement("reference",
          xmlattributes('db_block_size' as "name",
          db_block_size as "value")))
   from(
     select sum(num_instances) inst,
            sum(num_cpu_cores) num_cpu_cores,
           sum(num_cpus) num_cpus,
           sum(cpu_count) cpu_count,
           max(db_block_size) db_block_size
     from (
       select host_name,
              count(distinct inst_id) num_instances,
              max(num_cpu_cores) num_cpu_cores,
              max(num_cpus) num_cpus,
              max(cpu_count) cpu_count,
              max(db_block_size) db_block_size
       from table(gv$(cursor(
         select userenv('INSTANCE') inst_id,
                max(instance_name) instance_name,
                max(host_name) host_name,
                max(num_cpu_cores) num_cpu_cores,
                max(num_cpus) num_cpus,
                max(cpu_count) cpu_count,
                max(db_block_size) db_block_size
         from (
           select instance_name,
                  host_name,
                  null num_cpu_cores, null num_cpus,
                  null cpu_count, null db_block_size
           from v$instance
           union all
           select null instance_name, null host_name,
                  sum(decode(stat_name,'NUM_CPU_CORES',value,0))
                  num_cpu_cores,
                  sum(decode(stat_name,'NUM_CPUS',value,0))
                  num_cpus,
                  null cpu_count, null db_block_size
           from v$osstat
           where stat_name in ('NUM_CPUS','NUM_CPU_CORES')
           union all
           select null instance_name, null host_name,
                  null num_cpu_cores, null num_cpus,
                  sum(decode(name,'cpu_count',to_number(value),0))
                  cpu_count,
                  sum(decode(name, 'db_block_size',to_number(value), 0)) db_block_size
           from v$system_parameter
           where name in ('cpu_count','db_block_size'))))) gvtf
   where inst_id between :p_inst_id_low and :p_inst_id_high
   group by host_name))]]></script>
  <script type="activity"><![CDATA[
  var p_inst_id_low number;
  var p_inst_id_high number;
  var b_start_time varchar2(32);
  var b_end_time varchar2(32);
  var p_bucket_interval number;
  var p_bucket_count number;
  var b_min_time varchar2(32);
  var b_max_time varchar2(32);
  var b_duration number;
  var date_fmt varchar2(32);
  var p_inst_detail number;
  var p_is_rac number;
  begin
    :p_inst_id_low := 0;
    :p_inst_id_high := 100000;
    :p_is_rac := 0;
    :b_start_time := '12/28/2014 17:15:00';
    :b_end_time := '12/28/2014 18:15:15';
    :p_bucket_interval := 60;
    :p_bucket_count := 61;
    :b_min_time := '12/28/2014 17:15:00';
    :b_max_time := '12/28/2014 18:15:15';
    :b_duration := 3615;
    :date_fmt := 'mm/dd/yyyy hh24:mi:ss';
    :p_inst_detail := null;
    :p_is_rac := 0;
  end;
  /
 var b_minute_group_id number;
 var b_svcminute_group_id number;
 var l_root_name dbms_quoted_id;
 var l_top_n number;
begin
  :b_minute_group_id := 2;
  :b_svcminute_group_id := 6;
  :l_root_name := 'CDB$ROOT';
  :l_top_n := 10;
end;
/
with base_metrics as (select -- sum across all instances
                      source, lpad(to_char(inst_id),5) || '/' || inst_name inst,
                      bucket_id, id, name, sum(aas) aas, sum(aas_fg) aas_fg,
                      sum(aas_cpu) aas_cpu
                      from table(gv$(cursor
                     (select -- pick only first data point per bucket/id combination
                      source, userenv('INSTANCE') inst_id, i.instance_name inst_name,
                      bucket_id, id, name, aas, aas_fg, aas_cpu
                      from v$instance i,
                     (select -- determine first data point in bucket
                      source, bucket_id, id, name, row_number()
                      over (partition by bucket_id, id
                      order by aas desc nulls last, id) rn,
                      aas, aas_fg, aas_cpu
                      from (
(select 'pdbstat' source, h.con_id id,
        case when c.name is null and h.con_id = 1 then :l_root_name
             when c.name is null and h.con_id != 1 then 'con_id:' || to_char(h.con_id)
             else c.name end name, bucket_id, sum(aas) aas, sum(aas_fg) aas_fg, sum(aas_cpu) aas_cpu
 from v$containers c,
 (select con_id, bucket_id, sum(cnt)/:p_bucket_interval aas, sum(cnt_fg)/:p_bucket_interval aas_fg,
        sum(cnt_cpu)/:p_bucket_interval aas_cpu
 from (
select con_id,
trunc(
( (extract(day from
((trunc(sample_time,'mi') +
numtodsinterval(59,'second')) -
cast(trunc(to_date(:b_start_time, :date_fmt),'mi')
as timestamp))) * 86400 +
extract(hour from
((trunc(sample_time,'mi') +
numtodsinterval(59,'second')) -
cast(trunc(to_date(:b_start_time, :date_fmt),'mi')
as timestamp))) * 3600 +
extract(minute from
((trunc(sample_time,'mi') +
numtodsinterval(59,'second')) -
cast(trunc(to_date(:b_start_time, :date_fmt),'mi')
as timestamp))) * 60 +
extract(second from
((trunc(sample_time,'mi') +
numtodsinterval(59,'second')) -
cast(trunc(to_date(:b_start_time, :date_fmt),'mi')
as timestamp))) )
/ :p_bucket_interval)) + 1 bucket_id,
1 cnt,
decode(session_type, 'FOREGROUND', 1, 0) cnt_fg,
nvl2(wait_class, 0, 1) cnt_cpu
from v$active_session_history
where sample_time >= to_date(:b_start_time, :date_fmt)
and sample_time < to_date(:b_end_time, :date_fmt))
group by con_id, bucket_id) h
where h.con_id = c.con_id (+)
group by h.con_id,
case
when c.name is null and h.con_id = 1
then :l_root_name
when c.name is null and h.con_id != 1
then 'con_id:' || to_char(h.con_id)
else c.name
end,
bucket_id)
))
where rn = 1))) gvtf
where inst_id between :p_inst_id_low and :p_inst_id_high
group by source, lpad(to_char(inst_id),5) || '/' || inst_name ,
bucket_id, id, name ),
grouping_sets as (
select dim, id,
case when rn = 1 then name else null end name,
bucket_id, aas, aas_fg, aas_cpu
from (-- find first occurrence of stat
select dim, id, name, bucket_id, aas, aas_fg, aas_cpu,
row_number() over (partition by dim, id, name
order by bucket_id, id) rn
from ( -- group top n + others
select dim, id, name, bucket_id,
sum(aas) aas, sum(aas_fg) aas_fg, sum(aas_cpu) aas_cpu
from (-- find the top n but only for instance/service/pdb
select dim,
case when dim = 'waitclass' or rnk <= :l_top_n
then id
else 0
end id,
case when dim = 'waitclass' or rnk <= :l_top_n
then name
else 'Other'
end name,
bucket_id, aas, aas_fg, aas_cpu
from ( -- rank to get top n
select dim, id, name, bucket_id, aas, aas_fg, aas_cpu,
dense_rank() over
(partition by dim
order by dim_aas desc, id) rnk
from (
-- get sum per dim/id combination to compute top n
select dim, id, name, bucket_id,
aas, aas_fg, aas_cpu,
sum(aas) over (partition by dim, id) dim_aas
from (
select decode(gid_inst,0,'instance',source) dim,
decode(gid_inst,
0,to_number(substr(inst,1,5)),
id) id,
decode(gid_inst,
0,substr(inst,7),
name) name,
bucket_id, aas, aas_fg, aas_cpu
from (
select source,
id, name,
case
when :p_is_rac = 1
and name='Average Active Sessions'
then inst
else null
end inst,
bucket_id,
sum(aas) aas,
sum(aas_fg) aas_fg,
sum(aas_cpu) aas_cpu,
grouping_id (source,
id, name, bucket_id)
gid_id,
grouping_id (
source, bucket_id,
case
when :p_is_rac = 1
and name='Average Active Sessions'
then inst
else null
end) gid_inst
from base_metrics
group by grouping sets (
(source, id, name, bucket_id),
(source,
bucket_id,
case
when :p_is_rac = 1
and name='Average Active Sessions'
then inst
else null
end)))
where nvl(aas,0) + nvl(aas_fg, 0) > 0)
where id is not null)))
group by dim, id, name, bucket_id)))
select xmlagg(nvl2(buckets,
xmlelement("stattype",
xmlattributes('activity' as "name",
dim as "dim"),
nvl2(stat_id,
xmlelement("stat_info",stat_id),
null),
nvl2(buckets, xmlelement("buckets", xmlattributes( :p_bucket_interval as "bucket_interval", :p_bucket_coun
t as "bucket_count", :b_min_time as "start_time", :b_max_time as "end_time", :b_duration as "duration"),buckets), null) )
,
null))
from (
select dim,
xmlagg(xmlelement("bucket", xmlattributes(bucket_id as "bucket_id"), stats) order by bucket_id) buckets ,
xmlagg(stat_id) stat_id
from (
select dim, bucket_id,
xmlagg(stat_id order by id) stat_id,
xmlagg(xmlelement("stat",
xmlattributes(id as "id",
round(aas, 6) as "aas",
round(aas_fg, 6) as "aas_fg",
round(aas_cpu, 6) as "aas_cpu"))
order by id) stats
from (
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>
<containers>
<resource_plan>
<default_pdb_directive/>
</resource_plan>
<default_pdb_datafiles omf="n" snapshot_copy="n">C:\app\oracle/orabase/datafile</default_pdb_datafiles>
<spfile>C:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEORABASE.ORA</spfile>
<container con_id="1" name="CDB$ROOT" open_time="545386" restricted="NO" size="0">
<open_mode r="0" w="1" c="0" m="0"/>
<resource_limit cpu_utilized=".01" running_sess="0" waiting_sess="0"/>
<violations/>
</container>
<container con_id="3" name="PDBDEV" open_time="290150" restricted="NO" size="3043622912">
<open_mode r="0" w="1" c="0" m="0"/>
<resource_limit cpu_utilized="0" running_sess="0" waiting_sess="0"/>
<violations/>
</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 XMLTYPE;
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;

<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(inst_id as "inst_id", ADR_HOME as "home", INCIDENT_ID as "id", PROBLEM_ID as "pb_id", cdate as "cdate"),
          replace(error_message, chr(0), ''))
        order by cdate desc))
 from
  table(gv$(cursor(select /*+ no_merge(ho) leading(ho) use_hash(di) */
        userenv('INSTANCE') inst_id, di.adr_home, di.incident_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
      (select regexp_replace(value, '.*/(.*/.*/.*/.*)', '\1') adr_home
       from v$diag_info
       where name = 'ADR Home') ho
   where di.create_time > systimestamp - interval '1' day -- last day
   and di.adr_home = ho.adr_home -- only incidents of that inst
   and di.status = 2 -- ready status
   and di.close_time is null))) -- not closed
  where inst_id between :b_inst_id_low and :b_inst_id_high;]]>
 </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 db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
<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;

  -- initialize parameter value
  begin
    :b_inst_id_low := 1;
    :b_inst_id_high := 1;
  end;
  /

-- SQL building XML to get overall status
select xmlelement("instances", xmlagg(xmlelement("instance", xmlattributes(inst_id as "inst_id"),
                               xmlagg(xmlelement("info",
                                      xmlattributes(class as "type", decode(class, 'sga', subclass, 'pga', subclass, null) as "component",
                                      decode(class, 'activity', subclass, null) as "class",
                                      decode(class, 'cpu', v1, null) as "other",
                                      decode(class, 'cpu', v2, null) as "foreground",
                                      decode(class, 'cpu', v3, null) as "background",
                                      decode(class, 'instance', v1, null) as "number",
                                      decode(class, 'instance', v2, null) as "startup_since_sec",
                                      decode(class, 'instance', v3, null) as "thread",
                                      decode(class, 'instance', v4, null) as "inst_name",
                                      decode(class, 'instance', v5, null) as "host_name",
                                      decode(class, 'instance', v6, null) as "version",
                                      decode(class, 'instance', v7, null) as "status",
                                      decode(class, 'instance', v8, null) as "archiver",
                                      decode(class, 'instance', v9, null) as "log_switch_wait",
                                      decode(class, 'instance', v10, null) as "logins",
                                      decode(class, 'instance', v11, null) as "shutdown",
                                      decode(class, 'instance', v12, null) as "role",
                                      decode(class, 'instance', v13, null) as "state"),
                                      decode(class, 'sga', v1, 'pga', v1, 'activity', v1, null))
                                 order by class))
                               order by inst_id)) -- gv$ table function
from table(gv$(cursor( -- database time
                      select userenv('INSTANCE') inst_id, rv1.*
                      from ( -- activity
                            select 'activity' class, activity_type subclass,
                                   round(sum(activity_count), 2) v1, null v2, null v3, null v4,
                                   null v5, null v6, null v7, null v8, null v9, null v10,
                                   null v11, null v12, null v13
                            from (
                                  select decode(wc.wait_class, 'Cluster', 'Cluster', 'User I/O', 'User I/O', 'Wait') activity_type,
                                         wm.average_waiter_count activity_count
                                  from v$waitclassmetric wm, v$system_wait_class wc
                                  where wc.WAIT_CLASS# = wm.WAIT_CLASS#
                                  and wc.wait_class != 'Idle'
                                  and round(wm.average_waiter_count, 2) > 0
                            union all
                            select 'Cpu' activity_type, value / 100 activity_count
                            from v$sysmetric
                            where metric_id = 2075
                            and group_id = 2)
                      where activity_count > 0
                      group by activity_type
                      having round(sum(activity_count), 2) > 0
                      union all -- CPU metric
                      select 'cpu' class, null subclass,
                             round((host_perc - cpu_perc), 2) v1,
                             round((cpu_perc - bg_perc), 2) v2,
                             round(bg_perc, 2) v3, null v4, null v5, null v6,
                             null v7, null v8, null v9, null v10, null v11, null v12, null v13
                      from (
                            select ((cpu / greatest(host_cpu, cpu)) * host_perc) cpu_perc,
                                   ((least(bg_cpu, cpu) / greatest(host_cpu, cpu)) * host_perc) bg_perc, host_perc
                            from (
                                  select sum(decode(metric_id, 2057, value, null)) host_perc,
                                         sum(decode(metric_id, 2075, value, null)) cpu,
                                         sum(decode(metric_id, 2153, value, null)) bg_cpu,
                                         sum(decode(metric_id, 2155, value, null)) host_cpu
                                  from v$sysmetric
                                  where metric_id in (2057, 2075, 2153, 2155)
                                  and group_id = 2))
                      union all -- SGA memory consumption (total and broken down by components)
                      select 'sga' class, 'total_sga' subclass, sum(value)/1024/1024 v1, null v2, null v3,
                      null v4, null v5, null v6, null v7, null v8, null v9, null v10, null v11, null v12, null v13
                      from v$sga
                      union all
                      select 'sga' class, component subclass, size_mb v1, null v2, null v3, null v4,
                             null v5, null v6, null v7, null v8, null v9, null v10, null v11, null v12, null v13
                      from (
                            select userenv('INSTANCE') inst_id,
                            case when component = 'DEFAULT buffer cache' then 'buffer cache'
                            when component like 'DEFAULT % buffer cache' then 'buffer cache'
                            else component
                            end component,
current_size/1024/1024 size_mb
                            from v$sga_dynamic_components
                            where current_size > 0)
                      where component in (
                                          'shared pool',
                                          'large pool',
                                          'java pool',
                                          'streams pool',
                                          'buffer cache',
                                          'Shared IO Pool',
                                          'Data Transfer Cache',
                                          'ASM Buffer Cache',
                                          'other buffer cache(s)',
                                          'In-Memory Area')
                      union all -- PGA memory consumption
                      select 'pga' class,
decode(name, 'aggregate PGA target parameter', 'target_pga',
'total_pga') subclass,
round(value/1024/1024, 2) v1, null v2, null v3, null v4,
null v5, null v6, null v7, null v8, null v9,
null v10, null v11, null v12, null v13
                      from v$pgastat
                      where name in ('aggregate PGA target parameter', 'total PGA allocated')
                      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
                      from v$instance) rv1))) rv2
                      where inst_id between :b_inst_id_low and :b_inst_id_high
                      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;

<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
Undocumented 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;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/dbhome/pdb_storage_limits?pdb=ORABASE]]></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
Undocumented 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;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" 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
Undocumented prvtemx_dbhome.report_performance_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_performance_xml(1, 1)
FROM dual;

<region id="performance" cpu_time=".01" elapsed_time=".02"><report db_version="12.1.0.2.0" elapsed_time="0.02" cpu_time="0.01" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/perfpage/main?inst_id=1&report_level=basic]]></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="12/28/2014 19:26:00" end_time="12/28/2014 20:26:43" duration="3643" bucket_count="61" bucket_interval="60" is_rac="no" is_cdb="yes" inst_id="1"/>
  <script type="references"><![CDATA[
   var p_inst_id_low number;
   var p_inst_id_high number;
   var p_is_rac number;
   begin
     :p_inst_id_low := 1;
     :p_inst_id_high := 1;
   end;
   /

   select xmlelement("references",
xmlelement("reference",
xmlattributes('instances' as "name", inst as "value")),
xmlelement("reference",
xmlattributes('NUM_CPU_CORES' as "name",
num_cpu_cores as "value")),
xmlelement("reference",
xmlattributes('NUM_CPUS' as "name", num_cpus as "value")),
xmlelement("reference",
xmlattributes('cpu_count' as "name", cpu_count as "value")),
xmlelement("reference",
xmlattributes('db_block_size' as "name",
db_block_size as "value")))
   from(
        select sum(num_instances) inst, sum(num_cpu_cores) num_cpu_cores, sum(num_cpus) num_cpus,
               sum(cpu_count) cpu_count, max(db_block_size) db_block_size
        from (
              select host_name, count(distinct inst_id) num_instances, max(num_cpu_cores) num_cpu_cores,
                     max(num_cpus) num_cpus, max(cpu_count) cpu_count, max(db_block_size) db_block_size
              from table(gv$(cursor(
                                    select userenv('INSTANCE') inst_id,
max(instance_name) instance_name,
max(host_name) host_name,
max(num_cpu_cores) num_cpu_cores,
max(num_cpus) num_cpus,
max(cpu_count) cpu_count,
max(db_block_size) db_block_size
from (
select instance_name,
host_name,
null num_cpu_cores, null num_cpus,
null cpu_count, null db_block_size
from v$instance
union all
select null instance_name, null host_name,
sum(decode(stat_name,'NUM_CPU_CORES',value,0))
num_cpu_cores,
sum(decode(stat_name,'NUM_CPUS',value,0))
num_cpus,
null cpu_count, null db_block_size
from v$osstat
where stat_name in ('NUM_CPUS','NUM_CPU_CORES')
union all
select null instance_name, null host_name,
null num_cpu_cores, null num_cpus,
sum(decode(name,'cpu_count',to_number(value),0))
cpu_count,
sum(decode(name,
'db_block_size',to_number(value),
0)) db_block_size
from v$system_parameter
where name in ('cpu_count','db_block_size'))))) gvtf
where inst_id between :p_inst_id_low and :p_inst_id_high
group by host_name))]]></script>
<script type="activity"><![CDATA[
var p_inst_id_low number;
var p_inst_id_high number;
var b_start_time varchar2(32);
var b_end_time varchar2(32);
var p_bucket_interval number;
var p_bucket_count number;
var b_min_time varchar2(32);
var b_max_time varchar2(32);
var b_duration number;
var date_fmt varchar2(32);
var p_inst_detail number;
var p_is_rac number;
begin
:p_inst_id_low := 1;
:p_inst_id_high := 1;
:p_is_rac := 0;
:b_start_time := '12/28/2014 19:26:00';
:b_end_time := '12/28/2014 20:26:43';
:p_bucket_interval := 60;
:p_bucket_count := 61;
:b_min_time := '12/28/2014 19:26:00';
:b_max_time := '12/28/2014 20:26:43';
:b_duration := 3643;
:date_fmt := 'mm/dd/yyyy hh24:mi:ss';
:p_inst_detail := null;
:p_is_rac := 0;
end;
/

var b_minute_group_id number;
var b_svcminute_group_id number;
var l_root_name dbms_quoted_id;
var l_top_n number;
begin
:b_minute_group_id := 2;
:b_svcminute_group_id := 6;
:l_root_name := 'CDB$ROOT';
:l_top_n := 10;
end;
/
with base_metrics as (
select -- sum across all instances
source,
lpad(to_char(inst_id),5) || '/' || inst_name inst,
bucket_id, id, name,
sum(aas) aas,
sum(aas_fg) aas_fg,
sum(aas_cpu) aas_cpu
from table(gv$(cursor
(select -- pick only first data point per bucket/id combination
source,
userenv('INSTANCE') inst_id,
i.instance_name inst_name,
bucket_id, id, name,
aas,
aas_fg,
aas_cpu
from v$instance i,
(select -- determine first data point in bucket
source, bucket_id, id, name,
row_number() over
(partition by bucket_id, id
order by aas desc nulls last, id) rn,
aas, aas_fg, aas_cpu
from
(
select 'waitclass' source,
wc.wait_class# id, e.wait_class name,
trunc(
( (extract(day from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 86400 +
extract(hour from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 3600 +
extract(minute from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 60 +
extract(second from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) )
/ :p_bucket_interval)) + 1 bucket_id,
wc.time_waited/intsize_csec aas,
wc.time_waited_fg/intsize_csec aas_fg,
null aas_cpu
from v$waitclassmetric_history wc,
v$system_wait_class e
where wc.wait_class# = e.wait_class#
and e.wait_class != 'Idle'
and wc.end_time >= to_date(:b_start_time,:date_fmt)
and wc.end_time < to_date(:b_end_time,:date_fmt)
union all
select 'waitclass' source,
metric_id id, metric_name name,
trunc(
( (extract(day from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 86400 +
extract(hour from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 3600 +
extract(minute from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 60 +
extract(second from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) )
/ :p_bucket_interval)) + 1 bucket_id,
decode(metric_name,'CPU Usage Per Sec',
value/100, value) aas,
decode(metric_name,'CPU Usage Per Sec',
value/100, value) aas_fg,
null aas_cpu
from v$sysmetric_history s
where group_id = :b_minute_group_id
and end_time >= to_date(:b_start_time,:date_fmt)
and end_time < to_date(:b_end_time,:date_fmt)
and metric_name in ('CPU Usage Per Sec',
'Average Active Sessions')
union all
select 'servicestat' source,
service_name_hash id, service_name name,
trunc(
( (extract(day from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 86400 +
extract(hour from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 3600 +
extract(minute from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) * 60 +
extract(second from
(cast(trunc(end_time,'mi') as timestamp) -
cast(trunc(to_date(:b_start_time,:date_fmt),'mi')
as timestamp))) )
/ :p_bucket_interval)) + 1 bucket_id,
dbtimepersec/100 aas,
dbtimepersec/100 aas_fg,
null aas_cpu
from v$servicemetric_history s
where group_id = :b_svcminute_group_id
and end_time >= to_date(:b_start_time,:date_fmt)
and end_time < to_date(:b_end_time,:date_fmt)
union all
(select 'pdbstat' source,
h.con_id id,
case
when c.name is null and h.con_id = 1
then :l_root_name
when c.name is null and h.con_id != 1
then 'con_id:' || to_char(h.con_id)
else c.name
end name,
bucket_id,
sum(aas) aas,
sum(aas_fg) aas_fg,
sum(aas_cpu) aas_cpu
from v$containers c,
(select con_id, bucket_id,
sum(cnt)/:p_bucket_interval aas,
sum(cnt_fg)/:p_bucket_interval aas_fg,
sum(cnt_cpu)/:p_bucket_interval aas_cpu
from (
select con_id,
trunc(
( (extract(day from
((trunc(sample_time,'mi') +
numtodsinterval(59,'second')) -
cast(trunc(to_date(:b_start_time, :date_fmt),'mi')
as timestamp))) * 86400 +
extract(hour from
((trunc(sample_time,'mi') +
numtodsinterval(59,'second')) -
cast(trunc(to_date(:b_start_time, :date_fmt),'mi')
as timestamp))) * 3600 +
extract(minute from
((trunc(sample_time,'mi') +
numtodsinterval(59,'second')) -
cast(trunc(to_date(:b_start_time, :date_fmt),'mi')
as timestamp))) * 60 +
extract(second from
((trunc(sample_time,'mi') +
numtodsinterval(59,'second')) -
cast(trunc(to_date(:b_start_time, :date_fmt),'mi')
as timestamp))) )
/ :p_bucket_interval)) + 1 bucket_id,
1 cnt,
decode(session_type, 'FOREGROUND', 1, 0) cnt_fg,
nvl2(wait_class, 0, 1) cnt_cpu
from v$active_session_history
where sample_time >= to_date(:b_start_time, :date_fmt)
and sample_time < to_date(:b_end_time, :date_fmt))
group by con_id, bucket_id) h
where h.con_id = c.con_id (+)
group by h.con_id,
case
when c.name is null and h.con_id = 1
then :l_root_name
when c.name is null and h.con_id != 1
then 'con_id:' || to_char(h.con_id)
else c.name
end,
bucket_id)
))
where rn = 1))) gvtf
where inst_id between :p_inst_id_low and :p_inst_id_high
group by source, lpad(to_char(inst_id),5) || '/' || inst_name ,
bucket_id, id, name ),
grouping_sets as (
select dim, id,
case when rn = 1 then name else null end name,
bucket_id, aas, aas_fg, aas_cpu
from (-- find first occurrence of stat
select dim, id, name, bucket_id, aas, aas_fg, aas_cpu,
row_number() over (partition by dim, id, name
order by bucket_id, id) rn
from ( -- group top n + others
select dim, id, name, bucket_id,
sum(aas) aas, sum(aas_fg) aas_fg, sum(aas_cpu) aas_cpu
from (-- find the top n but only for instance/service/pdb
select dim,
case when dim = 'waitclass' or rnk <= :l_top_n
then id
else 0
end id,
case when dim = 'waitclass' or rnk <= :l_top_n
then name
else 'Other'
end name,
bucket_id, aas, aas_fg, aas_cpu
from ( -- rank to get top n
select dim, id, name, bucket_id, aas, aas_fg, aas_cpu,
dense_rank() over
(partition by dim
order by dim_aas desc, id) rnk
from (
-- get sum per dim/id combination to compute top n
select dim, id, name, bucket_id,
aas, aas_fg, aas_cpu,
sum(aas) over (partition by dim, id) dim_aas
from (
select decode(gid_inst,0,'instance',source) dim,
decode(gid_inst,
0,to_number(substr(inst,1,5)),
id) id,
decode(gid_inst,
0,substr(inst,7),
name) name,
bucket_id, aas, aas_fg, aas_cpu
from (
select source,
id, name,
case
when :p_is_rac = 1
and name='Average Active Sessions'
then inst
else null
end inst,
bucket_id,
sum(aas) aas,
sum(aas_fg) aas_fg,
sum(aas_cpu) aas_cpu,
grouping_id (source,
id, name, bucket_id)
gid_id,
grouping_id (
source, bucket_id,
case
when :p_is_rac = 1
and name='Average Active Sessions'
then inst
else null
end) gid_inst
from base_metrics
group by grouping sets (
(source, id, name, bucket_id),
(source,
bucket_id,
case
when :p_is_rac = 1
and name='Average Active Sessions'
then inst
else null
end)))
where nvl(aas,0) + nvl(aas_fg, 0) > 0)
where id is not null)))
group by dim, id, name, bucket_id)))
select xmlagg(nvl2(buckets,
xmlelement("stattype",
xmlattributes('activity' as "name",
dim as "dim"),
nvl2(stat_id,
xmlelement("stat_info",stat_id),
null),
nvl2(buckets, xmlelement("buckets", xmlattributes( :p_bucket_interval as "bucket_interval", :p_bucket_coun
t as "bucket_count", :b_min_time as "start_time", :b_max_time as "end_time", :b_duration as "duration"),buckets), null) )
,
null))
from (
select dim,
xmlagg(xmlelement("bucket", xmlattributes(bucket_id as "bucket_id"), stats) order by bucket_id) buckets ,
xmlagg(stat_id) stat_id
from (
select dim, bucket_id,
xmlagg(stat_id order by id) stat_id,
xmlagg(xmlelement("stat",
xmlattributes(id as "id",
round(aas, 6) as "aas",
round(aas_fg, 6) as "aas_fg",
round(aas_cpu, 6) as "aas_cpu"))
order by id) stats
from (
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
Undocumented 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;

<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 xmlelement("resources", xmlattributes((
         select count(*) from gv$instance where inst_id between :b_inst_id_low and :b_inst_id_high) as "inst_cnt"),
         xmlconcat(
         --
         -- CPU resource - for non-CDB and root
         --
         case when :b_con_id in (0,1) then (select xmlelement("cpu", xmlattributes(other_perc as "other",
                                                   bg_perc as "bg", fg_perc as "fg"), null)
                                            from (
                                                  select round(avg(host_perc - cpu_perc), 2) other_perc,
                                                         round(avg(cpu_perc - bg_perc), 2) fg_perc,
                                                         round(avg(bg_perc), 2) bg_perc
                                                  from ( -- enforce cpu <= host_cpu and bg_cpu <= cpu
select ((cpu/greatest(host_cpu, cpu)) * host_perc) cpu_perc,
((least(bg_cpu, cpu) /
greatest(host_cpu, cpu)) * host_perc) bg_perc,
host_perc
from
(
--
-- compute by host name since several DB instances can run
-- on the same host (this is during our testing)
--
select host_name,
avg(decode(metric_id, 2057, value, null)) host_perc,
sum(decode(metric_id, 2075, value, null)) cpu,
sum(decode(metric_id, 2153, value, null)) bg_cpu,
max(decode(metric_id, 2155, value, null)) host_cpu
from table(gv$(cursor
(
select userenv('INSTANCE') inst_id,
(select host_name from v$instance) host_name,
metric_id,
value
from v$sysmetric
-- assume that metric_id are fixed...
where metric_id in (2057, 2075, 2153, 2155)
and group_id = 2 -- 60s interval
)))
where inst_id between :b_inst_id_low and :b_inst_id_high
group by host_name
)
)
)
) end,
--
-- active sessions - for non-CDB and root
--
case when :b_con_id in (0,1) then
(select xmlelement(
"activity",
xmlattributes(
round(sum(decode(activity_type, 'Cpu',
activity_count, null)), 2) as "cpu",
round(sum(decode(activity_type, 'User I/O',
activity_count, null)), 2) as "io",
round(sum(decode(activity_type, 'Cluster',
activity_count, null)), 2) as "cluster",
round(sum(decode(activity_type, 'Wait',
activity_count, null)), 2) as "wait"),
null)
from table(gv$(cursor
(
select userenv('INSTANCE') inst_id,
decode(wc.wait_class, 'Cluster', 'Cluster',
'User I/O', 'User I/O', 'Wait') activity_type,
wm.average_waiter_count activity_count
from v$waitclassmetric wm,
v$system_wait_class wc
where wc.WAIT_CLASS# = wm.WAIT_CLASS#
and wc.wait_class != 'Idle'
union all
select userenv('INSTANCE') inst_id,
'Cpu' activity_type,
value/100 activity_count
from v$sysmetric
where metric_id = 2075
and group_id = 2
)))
where inst_id between :b_inst_id_low and :b_inst_id_high
) end,
--
-- memory - for non-CDB and root
--
case when :b_con_id in (0,1) then
(select
xmlelement(
"memory",
null,
xmlagg(
xmlelement(
"comp",
xmlattributes(
component as "name"),
round(sum(size_mb), 2))))
from table(gv$(cursor
(
select * from
(
select userenv('INSTANCE') inst_id,
case when component = 'DEFAULT buffer cache'
then 'buffer cache'
when component like 'DEFAULT % buffer cache'
then 'buffer cache'
else component
end component,
current_size/1024/1024 size_mb
from v$sga_dynamic_components
where current_size > 0
)
-- only the known components
where component in
(
'shared pool',
'large pool',
'java pool',
'streams pool',
'buffer cache',
'Shared IO Pool',
'Data Transfer Cache',
'ASM Buffer Cache',
'other buffer cache(s)',
'In-Memory Area'
)
union all
select userenv('INSTANCE') inst_id,
decode(name, 'aggregate PGA target parameter', 'target_pga',
'total_pga') component,
value/1024/1024 size_mb
from v$pgastat
where name in ('aggregate PGA target parameter',
'total PGA allocated')
union all
select userenv('INSTANCE') inst_id,
'total_sga' component,
sum(value)/1024/1024 size_mb
from v$sga
)))
where inst_id between :b_inst_id_low and :b_inst_id_high
group by component) end,
--
-- storage: for non-CDB and PDB
--
case when :b_con_id != 1 then
(select xmlelement(
"space",
xmlattributes(
round(sum(sum(ts_size)), 2) as "total"),
xmlagg(
xmlelement(
"comp",
xmlattributes(
ts_type as "name"),
round(sum(ts_size), 2))))
from
(
select case when ts.tablespace_name = 'SYSTEM'
then 'SYSTEM'
when ts.tablespace_name = 'SYSAUX'
then 'SYSAUX'
when ts.contents = 'PERMANENT'
then 'USER'
else ts.contents
end ts_type,
ts_space.ts_size ts_size
from
(
select df.tablespace_name,
sum(df.BYTES)/1024/1024 ts_size
from dba_data_files df
group by tablespace_name
) ts_space,
dba_tablespaces ts
where ts.tablespace_name = ts_space.tablespace_name
union all
select 'TEMPORARY' ts_type,
sum(BYTES)/1024/1024
from dba_temp_files ts_size
union all
select 'LOGS' ts_type,
sum(BYTES)/1024/1024
from v$log
)
group by ts_type) end,
null))
from dual
;]]>
 </script>
</region>
 
REPORT_SHOW_DBLINKS_XML
Undocumented prvtemx_dbhome.report_show_dblinks_xml(show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_dbhome.report_show_dblinks_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" 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_SQLMONITOR_XML
Undocumented 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;
-- the following listing, due to its size, is mostly unformatted
SELECT prvtemx_dbhome.report_sqlmonitor_xml(1, 10, p_show_sql=>1)
FROM dual;

<region id="sqlmonitor" cpu_time="0" elapsed_time="0"><script><![CDATA[

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

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

 --
 -- SQL building XML to get the last N sql statements which were monitored
 -- in the last hour
 --
 SELECT xmlelement("sql_monitor_list_report", xmlattributes(1 as "version", to_char(sysdate, :b_date_fmt) as "sysdate"),
        xmlagg(xmlelement(EVALNAME op_type, xmlattributes(decode(op_type, 'sql', mo.sql_id, null) as "sql_id",
        decode(op_type, 'sql', to_char(mo.sql_exec_start,:b_date_fmt), null) as "sql_exec_start",
        decode(op_type, 'sql', mo.sql_exec_id, null) as "sql_exec_id",
        decode(op_type, 'dbop', mo.sql_id, null) as "dbop_name",
        decode(op_type, 'dbop', to_char(mo.sql_exec_start,:b_date_fmt), null) as "dbop_exec_start",
        decode(op_type, 'dbop', mo.sql_exec_id, null) as "dbop_exec_id"),
xmlelement(
"sql_text",
xmlattributes(mo.is_full_sqltext as "is_full"),
mo.sql_text),
xmlforest(
mo.status as "status",
mo.user# as "user_id",
mo.username as "user",
decode(mo.con_id, 0, null, mo.con_id) "con_id",
decode(mo.con_id, 0, null, mo.con_name) "con_name",
mo.sid as "session_id",
mo.session_serial# as "session_serial",
mo.inst_id as "inst_id",
mo.module as "module",
mo.action as "action",
mo.service_name as "service",
mo.client_identifier as "client_id",
mo.client_info as "client_info",
mo.program as "program",
mo.sql_plan_hash_value as "plan_hash",
mo.px_maxdop as "dop",
decode(mo.px_maxdop, null, null,
mo.px_is_cross_instance) as "is_cross_instance",
mo.px_maxdop_instances as "instances",
mo.px_servers_requested as "px_servers_requested",
mo.px_servers_allocated as "px_servers_allocated",
to_char(mo.first_refresh_time,
:b_date_fmt) as "first_refresh_time",
to_char(mo.last_refresh_time,
:b_date_fmt) as "last_refresh_time"),
nvl2(error_number,
xmlelement("error",
xmlattributes(error_number as "number",
error_facility as "facility"),
error_message),
NULL),
xmlelement("stats",
xmlattributes('monitor' as "type"),
xmlelement("stat",
xmlattributes('duration' as "name"),
round(greatest(mo.duration,
least(mo.elapsed_time/1000000, 1),
ceil(mo.queuing_time/1000000)), 2)),
decode(mo.elapsed_time, 0, NULL,
xmlelement("stat",
xmlattributes('elapsed_time' as "name"),
mo.elapsed_time)),
decode(mo.queuing_time, 0, NULL,
xmlelement("stat",
xmlattributes('queuing_time' as "name"),
mo.queuing_time)),
decode(mo.cpu_time, 0, NULL,
xmlelement("stat",
xmlattributes('cpu_time' as "name"),
mo.cpu_time)),
decode(mo.user_io_wait_time, 0, NULL,
xmlelement("stat",
xmlattributes('user_io_wait_time' as "name"),
mo.user_io_wait_time)),
decode(mo.application_wait_time, 0, NULL,
xmlelement("stat",
xmlattributes('application_wait_time' as "name"),
mo.application_wait_time)),
decode(mo.concurrency_wait_time, 0, NULL,
xmlelement("stat",
xmlattributes('concurrency_wait_time' as "name"),
mo.concurrency_wait_time)),
decode(mo.cluster_wait_time, 0, NULL,
xmlelement("stat",
xmlattributes('cluster_wait_time' as "name"),
mo.cluster_wait_time)),
decode(mo.java_exec_time, 0, NULL,
xmlelement("stat",
xmlattributes('java_exec_time' as "name"),
mo.java_exec_time)),
decode(mo.other_wait_time, 0, NULL,
xmlelement("stat",
xmlattributes('other_wait_time' as "name"),
mo.other_wait_time)),
decode(mo.fetches, 0, NULL,
xmlelement("stat",
xmlattributes('user_fetch_count' as "name"),
mo.fetches)),
decode(mo.buffer_gets, 0, NULL,
xmlelement("stat",
xmlattributes('buffer_gets' as "name"),
mo.buffer_gets)),
decode(mo.read_reqs, 0, NULL,
xmlelement("stat",
xmlattributes('read_reqs' as "name"),
mo.read_reqs)),
decode(mo.read_bytes, 0, NULL,
xmlelement("stat",
xmlattributes('read_bytes' as "name"),
mo.read_bytes)),
decode(mo.write_reqs, 0, NULL,
xmlelement("stat",
xmlattributes('write_reqs' as "name"),
mo.write_reqs)),
decode(mo.write_bytes, 0, NULL,
xmlelement("stat",
xmlattributes('write_bytes' as "name"),
mo.write_bytes))))
order by decode(status, 'EXECUTING', 1, 'QUEUED', 1, 2),
decode(status, 'EXECUTING', sql_exec_start,
'QUEUED', sql_exec_start,
last_refresh_time) desc))
from
(select (row_number()
over(order by decode(status, 'EXECUTING', 1, 'QUEUED', 1, 2),
decode(status, 'EXECUTING', sql_exec_start,
'QUEUED', sql_exec_start,
last_refresh_time) desc))
position,
v.*
from
(
-- bug 18812859: group by was getting eliminated since same gby
-- cols are specified in inner and outer query.
-- Add the groupby_elim hint below to ensure that group
-- by is not eliminated because inner query is on gv$
-- table function
select /*+ opt_param('_optimizer_aggr_groupby_elim' 'false') */
sql_id,
sql_exec_start,
sql_exec_id,
op_type,
max(status) status,
max(user#) user#,
max(username) username,
max(module) module,
max(action) action,
max(service_name) service_name,
max(client_identifier) client_identifier,
max(client_info) client_info,
max(program) program,
max(sql_plan_hash_value) sql_plan_hash_value,
min(first_refresh_time) first_refresh_time,
min(last_refresh_time) last_refresh_time,
max(duration) duration,
max(sid) sid,
max(process_name) process_name,
max(sql_text) sql_text,
max(is_full_sqltext) is_full_sqltext,
max(session_serial#) session_serial#,
max(inst_id) inst_id,
max(con_id) con_id,
max(con_name) con_name,
max(px_is_cross_instance) px_is_cross_instance,
max(px_maxdop) px_maxdop,
max(px_maxdop_instances) px_maxdop_instances,
max(px_servers_requested) px_servers_requested,
max(px_servers_allocated) px_servers_allocated,
max(error_number) error_number,
max(error_facility) error_facility,
max(error_message) error_message,
sum(elapsed_time) elapsed_time,
sum(queuing_time) queuing_time,
sum(cpu_time) cpu_time,
sum(fetches) fetches,
sum(buffer_gets) buffer_gets,
sum(physical_read_requests) read_reqs,
sum(physical_read_bytes) read_bytes,
sum(physical_write_requests) write_reqs,
sum(physical_write_bytes) write_bytes,
sum(application_wait_time) application_wait_time,
sum(concurrency_wait_time) concurrency_wait_time,
sum(cluster_wait_time) cluster_wait_time,
sum(user_io_wait_time) user_io_wait_time,
sum(plsql_exec_time) plsql_exec_time,
sum(java_exec_time) java_exec_time,
case
when sum(elapsed_time) > sum(all_wait_time)
then sum(elapsed_time) - sum(all_wait_time)
else 0
end other_wait_time
from table(gv$(cursor
(
select decode(dbop_name, null, sql_id, dbop_name) sql_id,
decode(dbop_name, null, 'sql', 'dbop') op_type,
nvl(sql_exec_start, first_refresh_time) sql_exec_start,
decode(dbop_name,null,sql_exec_id,dbop_exec_id) sql_exec_id,
max(decode(px_qcsid, null, status, null)) status,
max(decode(px_qcsid, null, user#, null)) user#,
max(decode(px_qcsid, null, username, null)) username,
max(decode(px_qcsid, null, module, null)) module,
max(decode(px_qcsid, null, action, null)) action,
max(decode(px_qcsid, null, service_name,
null)) service_name,
max(decode(px_qcsid, null, client_identifier,
null)) client_identifier,
max(decode(px_qcsid, null, client_info, null)) client_info,
max(decode(px_qcsid, null, program, null)) program,
max(decode(px_qcsid, null, sql_plan_hash_value, null))
sql_plan_hash_value,
max(decode(px_qcsid, null, con_id, null)) con_id,
max(decode(px_qcsid, null, con_name, null)) con_name,
min(first_refresh_time) first_refresh_time,
max(last_refresh_time) last_refresh_time,
max((last_refresh_time -
nvl(sql_exec_start, first_refresh_time)
)*3600*24) duration,
max(decode(px_qcsid, null, sid, null)) sid,
max(decode(px_qcsid, null, process_name,
null)) process_name,
max(decode(px_qcsid, null, sql_text, null)) sql_text,
max(decode(px_qcsid, null, is_full_sqltext,
null)) is_full_sqltext,
max(decode(px_qcsid, null, session_serial#,
null)) session_serial#,
max(decode(px_qcsid, null, userenv('INSTANCE'),
null)) inst_id,
max(decode(px_qcsid, null, px_is_cross_instance,
null)) px_is_cross_instance,
max(decode(px_qcsid, null, px_maxdop, null)) px_maxdop,
max(decode(px_qcsid, null, px_maxdop_instances,
null)) px_maxdop_instances,
max(decode(px_qcsid, null, px_servers_requested,
null)) px_servers_requested,
max(decode(px_qcsid, null, px_servers_allocated,
null)) px_servers_allocated,
max(decode(px_qcsid, null, error_number,
null)) error_number,
max(decode(px_qcsid, null, error_facility,
null)) error_facility,
max(decode(px_qcsid, null, error_message,
null)) error_message,
sum(elapsed_time) elapsed_time,
sum(queuing_time) queuing_time,
sum(cpu_time) cpu_time,
sum(fetches) fetches,
sum(buffer_gets) buffer_gets,
sum(physical_read_requests) physical_read_requests,
sum(physical_read_bytes) physical_read_bytes,
sum(physical_write_requests) physical_write_requests,
sum(physical_write_bytes) physical_write_bytes,
sum(application_wait_time) application_wait_time,
sum(concurrency_wait_time) concurrency_wait_time,
sum(cluster_wait_time) cluster_wait_time,
sum(user_io_wait_time) user_io_wait_time,
sum(plsql_exec_time) plsql_exec_time,
sum(java_exec_time) java_exec_time,
sum(cpu_time +
application_wait_time +
concurrency_wait_time +
cluster_wait_time +
user_io_wait_time +
queuing_time) all_wait_time
from v$sql_monitor
where -- SQL running or has finished recently (def 1 hour)
(status in ('EXECUTING', 'QUEUED')
or last_refresh_time >=
sysdate - (:b_ago_seconds/3600/24))
-- apply any instance filter
and ((px_qcinst_id between :b_inst_id_low and
:b_inst_id_high)
or (px_qcinst_id is null
and userenv('INSTANCE') between :b_inst_id_low and
:b_inst_id_high))
group by decode(dbop_name, null, sql_id, dbop_name),
decode(dbop_name, null, 'sql', 'dbop'),
nvl(sql_exec_start, first_refresh_time),
decode(dbop_name, null, sql_exec_id, dbop_exec_id)
)))
where con_id = nvl(:b_con_id, con_id)
group by sql_id, sql_exec_start, sql_exec_id, op_type
) v) mo
where position <= :b_top_n
;]]>
 </script>
</region>
 
REPORT_STATUS_XML
Undocumented 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;

<region id="status" 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_container_name varchar2(128);
 var b_num_pdbs number;
 var b_oracle_home varchar2(4000);

 -- initialize parameter value
 begin
:b_date_fmt := dbms_report.date_fmt;
:b_inst_id_low := 1;
:b_inst_id_high := 1;
:b_container_name := 'CDB$ROOT';
:b_num_pdbs := 1;
:b_oracle_home := 'C:\app\oracle\product\12.1.0\dbhome_1';
 end;
 /

 -- SQL building XML to get overall status
 select xmlelement("status", xmlattributes(db_id as "db_id", db_name as "db_name", :b_container_name as "container_name",
        :b_num_pdbs as "pdb_cnt", db_unique_name as "db_unique_name", :b_oracle_home as "oracle_home", status as "db_status",
        version as "db_version", platform_name as "db_platform_name", platform_id as "db_platform_id",
        -- if pdb_startup_sec is not null, then this is a PDB
        decode(pdb_startup_sec,null,startup_since_sec,pdb_startup_sec) as "db_startup_since_sec",
        inst_cnt as "inst_cnt", inst_id as "inst_id", inst_name as "inst_name", host_name as "host_name",
        parallel as "parallel", thread# as "thread_num", archiver as "archiver", log_switch_wait as "log_switch_wait",
        shutdown as "shutdown", active_state as "active_state", logins as "logins", instance_mode as "instance_mode",
        edition as "edition", substr(last_status, 1, instr(last_status, '@') -1) as "rman_end_date",
        nls_initcap(substr(last_status, instr(last_status, '@')+1)) as "rman_status"), null)
 from ( -- instance level data
select max(d1) db_id,
max(d2) db_name,
max(d3) db_unique_name,
max(d4) platform_name,
max(d5) platform_id,
count(distinct inst_id) inst_cnt,
decode(min(inst_id), max(inst_id), max(inst_id), null) inst_id,
decode(min(i2), max(i2), max(i2), null) inst_name,
round(max(i1)) startup_since_sec,
decode(min(i3), max(i3), max(i3), null) host_name,
max(i4) version,
decode(min(i5), max(i5), max(i5), null) status,
decode(min(i6), max(i6), max(i6), null) parallel,
decode(min(i7), max(i7), max(i7), null) thread#,
decode(min(i8), max(i8), max(i8), null) archiver,
decode(min(i9), max(i9), max(i9), null) log_switch_wait,
decode(min(i10), max(i10), max(i10), null) shutdown,
decode(min(i11), max(i11), max(i11), null) active_state,
decode(min(i12), max(i12), max(i12), null) logins,
decode(min(i13), max(i13), max(i13), null) instance_mode,
decode(min(i14), max(i14), max(i14), null) edition,
round(max(p1)) pdb_startup_sec
from table(gv$(cursor(
select userenv('INSTANCE') inst_id, vr1.*
from (
-- attributes from v$instance
select (sysdate - startup_time)*3600*24 i1,
instance_name i2,
host_name i3,
version i4,
nls_initcap(status) i5,
nls_initcap(parallel) i6,
thread# i7,
nls_initcap(archiver) i8,
nls_initcap(log_switch_wait) i9,
nls_initcap(shutdown_pending) i10,
nls_initcap(active_state) i11,
nls_initcap(logins) i12,
nls_initcap(instance_mode) i13,
edition i14,
null d1, null d2, null d3, null d4, null d5,
null p1
from v$instance
-- attributes from v$database
union all
select null i1, null i2, null i3, null i4, null i5, null i6,
null i7, null i8, null i9, null i10, null i11, null i12,
null i13, null i14, dbid d1, name d2, db_unique_name d3,
platform_name d4, platform_id d5,
null p1
from v$database
-- pdb open time --
) vr1))) vr2
where inst_id between :b_inst_id_low and :b_inst_id_high) vr3,
(select max(to_char(end_time, :b_date_fmt) || '@' || status) last_status
from v$rman_status
where ((operation like '%AUTOBACKUP%' and row_level > 1) or
(operation like 'BACKUP%' and row_level=1)) and
status != 'RUNNING%') vr4
;]]></script></region>
 
SET_CDB_resource_plan_XML
Undocumented prvtemx_dbhome.set_cdb_resource_plan_xml(
resource_plan IN VARCHAR2,
shares        IN NUMBER,
cpu_limit     IN NUMBER,
px_limit      IN NUMBER,
show_sql      IN NUMBER)
RETURN XMLTYPE;
TBD
 
UNPLUG_PDB_XML
Undocumented 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;

<report db_version="12.1.0.2.0" elapsed_time="0.02" cpu_time="0.02" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/dbhome/unplug_pdb?pdb=PDBDEV&show_sql=1]]></report_id>
  <medatafile>
   <file>pdb_PDBDEV_201412280553.xml</file>
   <dir>C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\</dir>
  </medatafile>
  <sql>alter pluggable database &quot;PDBDEV&quot; close IMMEDIATE ;
       alter pluggable database &quot;PDBDEV&quot; unplug into &apos;C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\pdb_PDBDEV_201412280553.xml&apos;;
       drop pluggable database &quot;PDBDEV&quot; KEEP DATAFILES;
  </sql>
</report>

Related Topics
DBMS_REPORT
Packages
PRVTEMX_ADMIN
PRVTEMX_CELL
PRVTEMX_MEMORY
PRVTEMX_PERF
What's New In 12cR1
What's New In 12cR2

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