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.
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.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.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 "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;
SELECT prvtemx_dbhome.plug_pdb_xml('PDBTEST', 'c:\stage\testpdb.xml', show_sql=>1)
FROM dual;
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)
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>
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>
