This procedure outlines the steps of performing real-time database operation monitoring via the sqlplus console using the package DBMS_SQL_MONITOR.
- Start SQLPLUS and connect as a user with the appropriate privileges.
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 20 08:00:16 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Create user test and table test_tab, than insert 10 miliion records into test.test_tab for monitoring test.
create user test identified by test
default tablespace users quota unlimited on users;
grant dba to test;
create table test.test_tab(col_a number,
col_b varchar2(10));
Jullare
val_a number:=1;
val_b varchar2(10):=’a’;
begin
while val_a<20000001
loop
insert into test.test_tab(col_a, col_b)
values(val_a, val_b);
val_a:=val_a+1;
if val_b=’z’
then
val_b:=’a’;
else
val_b:=chr(ascii(val_b) + 1);
end if;
end loop;
commit;
end;
/
Execution:
SQL> create user test identified by test
default tablespace users quota unlimited on users;
2
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> create table test.test_tab(col_a number,
col_b varchar2(10));
2
Table created.
SQL> Jullare
val_a number:=1;
2 3 val_b varchar2(10):=’a’;
4 begin
5 while val_a<20000001
6 loop
7 insert into test.test_tab(col_a, col_b)
8 values(val_a, val_b);
9 val_a:=val_a+1;
10 if val_b=’z’
11 then
12 val_b:=’a’;
13 else
14 val_b:=chr(ascii(val_b) + 1);
15 end if;
16 end loop;
17 commit;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL>
- Define a variable to hold the execution ID.
var opid NUMBER
- Begin the database operation.
EXEC :opid := DBMS_SQL_MONITOR.BEGIN_OPERATION(‘op_test’);
- Run the queries in the operation.
SELECT col_a FROM test.test_tab order by col_a;
SELECT col_b FROM test.test_tab order by col_a;
- End the database operation.
EXEC DBMS_SQL_MONITOR.END_OPERATION(‘op_test’, :opid);
- Confirm that the database operation completed.
SQL> SELECT dbop_name, status FROM v$sql_monitor WHERE dbop_name = ‘op_test’;
DBOP_NAME STATUS
—————————— ——————-
rtsm_op DONE
SQL>
- This completes manual execution of real_time database operation monitoring with DBMS_SQL_MONITOR.
Larry Catt
OCP