Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
prvt_awr_viewer.adjust_realtime_input_times(
p_start_time IN OUT DATE,
p_end_time IN OUT DATE,
p_source OUT VARCHAR2,
p_duration IN NUMBER);
DECLARE
pstart DATE := SYSDATE;
pend DATE := SYSDATE+1;
psrc VARCHAR2(30);
BEGIN
prvt_awr_viewer.adjust_realtime_input_times(pstart, pend, psrc, 10);
dbms_output.put_line(psrc);
END;
/ history
prvt_awr_viewer.awr_rmmetrics_xml(
p_plan_name IN VARCHAR2,
p_awr_period IN OUT PRVT_AWRV_METADATA,
p_bucket_map IN OUT PRVT_AWRV_MAPTAB,
p_bucket_interval IN NUMBER,
p_show_sql IN NUMBER,
p_idname_map IN OUT PRVT_AWRV_INSTTAB,
output_xml IN OUT XMLTYPE);
prvt_awr_viewer.awr_sysmetrics_xml(
awr_period IN OUT PRVT_AWRV_METADATA,
bucket_map IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count IN NUMBER,
p_bucket_interval IN NUMBER,
p_metric_type IN VARCHAR2,
p_show_sql IN NUMBER,
output_xml OUT XMLTYPE);
prvt_awr_viewer.awr_sysstat_totalstat_xml(
awr_period IN OUT PRVT_AWRV_METADATA,
bucket_map IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count IN NUMBER,
p_bucket_interval IN NUMBER,
p_show_sql IN NUMBER,
p_stat_desc IN VARCHAR2,
output_xml OUT XMLTYPE);
prvt_awr_viewer.create_bucket_snap_map(
p_awr_period IN OUT PRVT_AWRV_METADATA,
p_bucket_map IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count IN OUT NUMBER,
p_bucket_calc_interval OUT NUMBER);
prvt_awr_viewer.fet_topsql_xml(
start_time IN DATE,
end_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
is_realtime IN NUMBER,
top_n_detail IN NUMBER,
outer_start_time IN DATE,
outer_end_time IN DATE,
compress_xml IN BINARY_INTEGER,
show_sql IN NUMBER,
db_tz IN VARCHAR2,
is_omx IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.generate_buckets_tag2(p_buckets IN VARCHAR2)
RETURN VARCHAR2;
SELECT prvt_awr_viewer.generate_buckets_tag2('Testing')
FROM dual;
PRVT_AWR_VIEWER.GENERATE_BUCKETS_TAG2('TESTING')
---------------------------------------------------------------------------------
nvl2(Testing, xmlelement("buckets", xmlattributes( :p_bucket_interval as "bucket_interval", :p_bucket_count as "bucket_count", :b_min_time as "start_time", :b_max_time as "end_time"
, :b_duration as "duration"),Testing), null)
prvt_awr_viewer.get_bucket_map(
p_start_time IN DATE,
p_end_time IN DATE,
p_inst_id IN NUMBER,
p_dbid IN NUMBER,
p_bucket_max_count IN OUT NUMBER,
p_bucket_interval OUT NUMBER,
p_awr_period OUT prvt_awrv_metadata;
p_bucket_map IN OUT prvt_awrv_maptab);
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c NUMBER := 1;
d NUMBER := 428676178;
e NUMBER := 5;
f NUMBER;
g prvt_awrv_metadata;
h prvt_awrv_maptab;
BEGIN
prvt_awr_viewer.get_bucket_map(a,b,c,d,e,f,g,h);
prvt_awr_viewer.get_rmmetrics_xml(
is_realtime IN NUMBER,
start_time IN DATE,
end_time IN DATE,
bucket_count IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low IN NUMBER,
inst_id_high IN NUMBER,
awr_period IN sys.prvt_awrv_metadata,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.get_sql_n_parse_time_xml(
is_realtime IN NUMBER,
start_time_UTC IN DATE,
end_time_UTC IN DATE,
bucket_count IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low IN NUMBER,
inst_id_high IN NUMBER,
awr_period IN sys.prvt_awrv_metadata,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.get_sysmetrics_xml(
is_realtime IN NUMBER,
start_time_utc IN DATE,
end_time_utc IN DATE,
bucket_count IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low IN NUMBER,
inst_id_high IN NUMBER,
awr_period IN sys.prvt_awrv_metadata,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.get_wait_time_xml(
is_realtime IN NUMBER,
start_time_utc IN DATE,
end_time_utc IN DATE,
bucket_count IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low IN NUMBER,
inst_id_high IN NUMBER,
awr_period IN sys.prvt_awrv_metadata,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.report_addm_xml(
start_time IN DATE,
end_time IN DATE,
num_days IN NUMBER,
owner IN VARCHAR2,
task_name IN VARCHAR2,
section IN VARCHAR2,
spotrep IN VARCHAR2,
spotlist IN VARCHAR2,
inst_id IN NUMBER,
db_id IN NUMBER,
show_sql IN NUMBER,
top_n_detail IN NUMBER,
compress_xml IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.report_awrrep_xml(
selected_start_time IN DATE,
selected_end_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
compress_xml IN BINARY_INTEGER,
report_reference IN VARCHAR2)
RETURN XMLTYPE;
prvt_awr_viewer.report_awr_xml(
start_time IN DATE,
end_time IN DATE,
instance_list IN VARCHAR2,
dbid IN NUMBER,
member_id IN NUMBER,
bucket_max_count IN NUMBER,
time_model IN VARCHAR2,
wait_class IN VARCHAR2,
wiat_event IN VARCHAR2,
event_class IN VARCHAR2,
sysstat IN VARCHAR2,
sqlstat IN VARCHAR2,
osstat IN VARCHAR2,
iostat IN VARCHAR2,
memory IN VARCHAR2,
space IN VARCHAR2,
key_statistics IN VARCHAR2,
summary IN VARCHAR2,
inst_detail IN VARCHAR2,
members IN VARCHAR2,
timepicker_start IN DATE,
timepicker_end IN DATE,
report_level IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.report_containers_xml(
p_start_time IN DATE,
p_end_time IN DATE,
p_last_refresh_time IN DATE,
p_inst_id IN NUMBER,
p_dbid IN NUMBER,
p_IS_realtime IN NUMBER,
p_top_n_count IN NUMBER,
p_top_n_rankby IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.report_metrics_xml(
start_time IN DATE,
end_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
bucket_max_count IN NUMBER,
bucket_interval IN NUMBER,
inst_detail IN VARCHAR2,
report_level IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);
Table created.
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c NUMBER := 1;
d NUMBER := 2140826538;
e NUMBER := 5;
f NUMBER := 1;
g VARCHAR2(30); -- inst_detail
h VARCHAR2(10) := 'TYPICAL';
i NUMBER := 1; -- show_sql
x XMLTYPE;
BEGIN
x := prvt_awr_viewer.report_metrics_xml(a,b,c,d,e,f,g,h,i);
INSERT INTO t VALUES (x);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM t;
TESTCOL
----------------------------------------------------------------------------
<report db_version="21.0.0.0.0" elapsed_time="0.09" cpu_time="0.07" 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%3fdbid%3d2140826538%26end_time%
3d06%3a20%3a2021%2019%3a20%3a24%
26inst_id%3d1%26report_level%3dTYPICAL%26start_time%3d06%3a19%3a2021%2019%
3a20%3a24]]>
</report_id>
<report_parameters>
<start_time>06/19/2021 19:20:24</start_time>
<end_time>06/20/2021 19:20:24</end_time>
<inst_id>1</inst_id>
<dbid>2140826538</dbid>
<bucket_max_count>5</bucket_max_count>
<bucket_interval>1</bucket_interval>
<report_level>TYPICAL</report_level>
<show_sql>1</show_sql>
</report_parameters>
...
select nvl2(buckets,
xmlelement("stattype",
xmlattributes('allochist' as "name"),
xmltype(:l_total_mem),
nvl2(stat_id,
xmlelement("stat_info",stat_id),
null),
xmlelement("buckets",
xmlattributes(:l_bucket_interval as "bucket_interval",
:l_bucket_count as "bucket_count",
:l_min_time as "start_time",
:l_max_time as "end_time",
:l_duration as "duration"),
buckets)),
null)
from (
select xmlagg(
xmlelement("bucket",
xmlattributes(bucket_id as "bucket_id"),stats)
order by bucket_id) buckets,
xmlagg(stat_id) stat_id
from (
select bucket_id,
xmlagg(stat_id order by id) stat_id,
xmlagg(xmlelement("stat",
xmlattributes(id as "id",
round(bytes,2) as "value"))
order by id) stats
from (
select bucket_id, id, bytes,
nvl2(component,
xmlelement("stat",
xmlattributes(id as "id",
component as "name",
alloc as "alloc",
'bytes' as "unit",
case when alloc != 'paging'
then '1048576'
else null
end as "factor")),
null) stat_id
from alloc_hist)
group by bucket_id))]]></script>
</report>
prvt_awr_viewer.report_rac_xml(
start_time IN DATE,
end_time IN DATE,
last_refresh_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
is_realtime IN NUMBER,
show_sql IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c DATE := SYSDATE;
d NUMBER := 1;
e NUMBER := 2140826538;
f NUMBER := 1;
g NUMBER := 1;
x XMLTYPE;
BEGIN
x := prvt_awr_viewer.report_rac_xml(a,b,c,d,e,f,g);
prvt_awr_viewer.report_timepicker_xml(
start_time IN DATE,
end_time IN DATE,
duration IN NUMBER,
inst_id IN NUMBER,
dbid IN NUMBER,
is_realtime IN NUMBER,
sql_id IN VARCHAR2,
sid IN NUMBER,
serial IN NUMBER,
show_sql IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);
SELECT sql_id, sid, serial#
FROM v$sqlarea
WHERE executions = (
SELECT MAX(executions) FROM v$sqlarea);
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c NUMBER := 1;
d NUMBER := 1;
e NUMBER := 2140826538;
f NUMBER := 1;
g VARCHAR2(13) := '96g93hntrzjtr';
h NUMBER := NULL;
i NUMBER := NULL;
j NUMBER := 1;
x XMLTYPE;
BEGIN
x := prvt_awr_viewer.report_timepicker_xml(a,b,c,d,e,f,g,h,i,j);
prvt_awr_viewer.report_topsql_xml(
start_time IN DATE,
end_time IN DATE,
last_refresh_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
is_realtime IN NUMBER,
top_n_detail IN NUMBER,
outer_start_time IN DATE,
outer_end_time IN DATE,
compress_xml IN BINARY_INTEGER,
show_sql IN NUMBER)
RETURN XMLTYPE
conn / as sysdba
CREATE TABLE t (testcol XMLTYPE);
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c DATE := SYSDATE-1;
d NUMBER := 1;
e NUMBER := 2140826538;
f NUMBER := 1; -- is_realtime
g NUMBER := 10; -- top_n
h DATE;
i DATE;
j NUMBER := 1; -- compress
k NUMBER := 1; -- show SQL
x XMLTYPE;
BEGIN
x := prvt_awr_viewer.report_topsql_xml(a,b,c,d,e,f,g,h,i,j,k);
prvt_awr_viewer.report_workload(
service_type IN VARCHAR2,
is_realtime IN NUMBER
dbid IN NUMBER,
inst_id IN NUMBER,
start_time IN DATE,
end_time IN DATE,
report_level IN VARCHAR2,
db_tz IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.rmmetrics_data_xml(
p_start_time IN DATE,
p_end_time IN DATE,
p_inst_id_low IN NUMBER,
p_inst_id_high IN NUMBER,
p_dbid IN NUMBER,
p_bucket_count IN NUMBER,
p_bucket_interval IN NUMBER,
p_inst_detail IN NUMBER,
p_is_rac IN NUMBER,
p_show_sql IN NUMBER,
p_plan_name IN VARCHAR2)
RETURN XMLTYPE;