Performance Hub for On-Premises Database

Previously, I have demonstrated the Performance Hub on an Oracle Autonomous Transaction Processing database on the Oracle Cloud.

 However, you can use the DBMS_PERF package for a similar report on your On-Premises database.

The code to generate such a report is (you can connect explicitly to the target PDB to generate the Performance Hub report) :

set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool DBMS_PERF_REPORT.html
select dbms_perf.report_perfhub(is_realtime=>1,type=>'active') from dual;
-- is_realtime 1 and active shows the report for the last 1hour
-- for more options see the documentation on DBMS_PERF
spool off
-- then edit the html file to remove the first line and the spool off command if necessary

I have generated a similar report for the database in my On-Premises VM.

See the 19c documentation on DBMS_PERF for more details.

Note :   The window is only 5minutes because the default selected_start_time parameter is 5minutes before the current time (i.e. the time when the report is generated)

A DDL Trigger ... and writing to the alert log file

 Below is sample code for a DDL Trigger that writes to the alert log when a specific condition is encountered.

SQL> show user
USER is "SYS"
SQL> alter session set container=orclpdb1;

Session altered.

SQL> @Drop_Table_not_permitted.sql
SQL> create or replace trigger DROP_TABLE_NOT_PERMITTED
  2  after ddl
  3  on database
  4  begin
  5    if (ora_sysevent='DROP'  and  ora_dict_obj_type = 'TABLE')
  6    then
  7   --- code for INSERT into an audit log table
  8   --- INSERT INTO ....
  9   ---
 10   --- code below is to write to the alert log file
 11   --- dbms_log is undocumented but available since 12c or 11.2.0.4
 12   ---   see https://jonathanlewis.wordpress.com/2018/10/12/dbms_log/
 13   --- dbms_log.ksdwrt is the same as dbms_system.ksdwrt
 14   ---   see https://hemantoracledba.blogspot.com/2008/11/database-event-trigger-and-sysoper.html
 15       dbms_log.ksdwrt(2,' Warning :  ' || sys_context('USERENV','SESSION_USER')
 16                      || ' tried to drop  ' || ora_dict_obj_name
 17                      || '  in SID ' || sys_context('USERENV','SID')
 18                      || ', AUDSID ' || sys_context('USERENV','SESSIONID')
 19                      || '  and service name  ' || sys_context('USERENV','SERVICE_NAME'));
 20      raise_application_error(-20001,'You are not permitted to Drop Tables');
 21    end if;
 22  end;
 23  /

Trigger created.

SQL>

When I connect to the PDB and attempt to execute a DROP TABLE ...

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> desc dummy_tab
 Name                                                           Null?    Type
 -------------------------------------------------------------- -------- ------------------------
 ID_COL                                                                  NUMBER

SQL> drop table dummy_tab;
drop table dummy_tab
           *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.DROP_TABLE_NOT_PERMITTED'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not permitted to Drop Tables
ORA-06512: at line 17


SQL>

The alert log shows this entry :

2021-05-27T21:38:11.859746+08:00
ORCLPDB1(3): Warning :  HEMANT tried to drop  DUMMY_TAB  in SID 272, AUDSID 1061583  and service name  orclpdb1

You could use more information from SYS_CONTEXT ( I have retrieved only some pieces of information in my PL/SQL Trigger code above) and populate an Audit Log table with more information.

Comment : The "2" as the first parameter to dbms_log.ksdwrt references the instance's alert log. "1" would reference the session's trace file.  "3" would reference both files.

All Email Subscriptions have been removed

 I have removed email subscriptions to this blog.

Please try the "Follow This Blog On follow.it"  link on the right