This procedure outlines the steps of performing real-time database operation monitoring via the sqlplus console using the package DBMS_SQL_MONITOR.

 

1. 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>

 

2. 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>

 

 

 

3. Define a variable to hold the execution ID.

 

var opid NUMBER

 

4. Begin the database operation.

 

EXEC :opid := DBMS_SQL_MONITOR.BEGIN_OPERATION(‘op_test’);

 

5. 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;

 

6. End the database operation.

 

EXEC DBMS_SQL_MONITOR.END_OPERATION(‘op_test’, :opid);

 

7. 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>

 

8. This completes manual execution of real_time database operation monitoring with DBMS_SQL_MONITOR.

 

Larry Catt

OCP