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;
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);
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);
PRVTEMX_DBHOME.CREATE_PDB_XML('PDBTEST','PDBDBA','NOWAY!',SHOW_SQL=>1)
------------------------------------------------------------------------------------
<report db_version="23.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 "PDBTEST" admin user "PDBDBA" identified by ********** ;
alter pluggable database "PDBTEST" open read write;</sql>
</report>
begin
sys.dbms_resource_manager.clear_pending_area();
sys.dbms_resource_manager.create_pending_area();
sys.dbms_resource_manager.delete_cdb_plan_directive(
plan => :b1,
pluggable_database => :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>
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;
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;
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
-- 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>
-- 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>
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);
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>
PRVTEMX_DBHOME.REPORT_SHOW_PDB_INSTANCES_XML('PDBDEV',1)
---------------------------------------------------------
<report db_version="23.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>