Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DECLARE
l_roles owa.vc_arr;
l_patterns owa.vc_arr;
l_modules owa.vc_arr;
BEGIN
l_roles(1) := 'SQL Developer';
l_patterns(1):= '/dba/*';
l_modules(1) := 'dba/awr';
ORDS.DEFINE_PRIVILEGE(
p_privilege_name => 'dba.demo',
p_roles => l_roles,
p_patterns => l_patterns,
p_modules => l_modules,
p_label => '',
p_description => 'protected by SQL Developer Role',
p_comments => NULL);
ORDS.DEFINE_MODULE(
p_module_name => 'dba/awr',
p_base_path => '/dba/perf/awr/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'dba/awr',
p_pattern => '/snapshot',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'dba/awr',
p_pattern => '/snapshot',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 25,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
q'[select
SNAP_ID,DBID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME ,
'../awr/report/'||DBID||'/1/'||(SNAP_ID-1)||'/'||SNAP_ID "$report-1",
'../awr/report/'||DBID||'/1/'||(SNAP_ID-2)||'/'||SNAP_ID "$report-2",
'../awr/report/'||DBID||'/1/'||(SNAP_ID-3)||'/'||SNAP_ID "$report-3",
'../awr/report/'||DBID||'/1/'||(SNAP_ID-4)||'/'||SNAP_ID "$report-4",
'../awr/report/'||DBID||'/1/'||(SNAP_ID-5)||'/'||SNAP_ID "$report-5"
from dba_hist_snapshot
where INSTANCE_NUMBER=1 order by SNAP_ID desc]');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'dba/awr',
p_pattern => '/snapshot/:id',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'dba/awr',
p_pattern => '/snapshot/:id',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 25,
p_mimes_allowed => '',
p_comments => NULL,
p_source => 'select * from dba_hist_snapshot where SNAP_ID = :id');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'dba/awr',
p_pattern => 'report/:dbid/:inst/:snapbegin/:snapend',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'dba/awr',
p_pattern => 'report/:dbid/:inst/:snapbegin/:snapend',
p_method => 'GET',
p_source_type => 'plsql/block',
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'begin
FOR cur_rep IN (SELECT output
FROM
TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(:dbid,:inst,:snapbegin,:snapend )))
LOOP
htp.prn(cur_rep.output);
END LOOP;
end;');
COMMIT;
END;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment