The real-time SQL monitoring is an 11g new feature that enables DBAs to monitor the performance of SQL statements while they are executing, Please refer the following post for details.
Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database
Because a primary job duty of database developers is to write and tune SQL statements, starting in Oracle Database 19c, Oracle allows database user to view their own Real-Time SQL Monitoring reports without requiring DBA privileges or SELECT_CATALOG_ROLE.
From the official documentation:
New set of V$ views:
V$ALL_SQL_MONITOR
V$ALL_SQL_PLAN_MONITOR
V$ALL_ACTIVE_SESSION_HISTORY
V$ALL_SQL_PLAN
Mainly scenarios is following:
- Database users can generate and view SQL monitor report of the SQL statements issued by themself, without granting any additional privileges.
- If users have not been granted the SELECT_CATALOG_ROLE ,they can not generate and view SQL monitor report of SQL statements executed by other users.
- f users have been granted the SELECT_CATALOG_ROLE ,they can see SQL monitor report of SQL executed by other users.
And you can generate and view SQL monitoring report from the SQL*PLUS command line by DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST and DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST package or from Enterprise Manager (EM) just as 11g.
TEST CASE
--1.create users conn / as sysdba --1-1.create Low-privileged users without DBA privilege --dep1 for SQL Statement using Tables drop user dep1 cascade; create user dep1 identified by dep1; alter user dep1 quota unlimited on users; grant connect,resource to dep1; --2.prepare test data conn dep1/dep1 drop table testa_dep1; drop table testb_dep1; create table testa_dep1(c1 number, c2 char(100)); create table testb_dep1(c1 number, c2 char(100)); begin for i in 1 .. 200 loop for j in 1 .. 100 loop insert into testa_dep1 values(i,'A'); commit; end loop; end loop; end; / begin for i in 1 .. 200 loop for j in 1 .. 100 loop insert into testb_dep1 values(i,'A'); commit; end loop; end loop; end; / --2.Executing SQL and check SQL Monitor Active Report conn dep1/dep1 select /*+ use_nl(a b) */ count(*) from testa_dep1 a, testb_dep1 b where a.c1=b.c1; --3.Generate and view SQL Monitor List and Active Report --should be able to view SQL monitor report of the SQL statements issued by user-self, without granting any additional privileges. --should not be able to view SQL monitor report of SQL statements issued by other users. --REPORT_SQL_MONITOR_LIST SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF spool monitor_list_sql_dep1_active.html SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'ACTIVE',report_level => 'ALL') AS report FROM dual; spool off --REPORT_SQL_MONITOR set trimspool on set trim on set pages 0 set linesize 1000 set long 1000000 set longchunksize 1000000 spool monitor_sql_dep1.html select dbms_sqltune.report_sql_monitor(type=>'active') from dual; spool off