Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules and debugging.
When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views.
DECLARE x VARCHAR2(100); BEGIN dbms_application_info.read_client_info(x); dbms_output.put_line(x); END; /
-- the following will not work but try it so that you understand why -- you can not use a stored procedure in a WHERE clauseCREATE OR REPLACE VIEW airplanes_view AS SELECT * FROM airplanes WHERE program_id = dbms_application_info.read_client_info(x);
-- wrap the stored procedure so that it presents itself as a functionCREATE OR REPLACE FUNCTION app_info_wrapper RETURN VARCHAR2 IS x VARCHAR2(64); BEGIN dbms_application_info.read_client_info(x); RETURN x; END app_info_wrapper; /
-- now you can create the view CREATE OR REPLACE VIEW airplanes_view AS SELECT * FROM airplanes WHERE program_id = app_info_wrapper;
dbms_application_info.set_session_longops( rindex IN OUT BINARY_INTEGER, slno IN OUT BINARY_INTEGER, op_name IN VARCHAR2(64) DEFAULT NULL, target IN BINARY_INTEGER DEFAULT 0, context IN BINARY_INTEGER DEFAULT 0, sofar IN NUMBER DEFAULT 0, totalwork IN NUMBER DEFAULT 0, target_desc IN VARCHAR2(32) DEFAULT 'unknown_target', units IN VARCHAR2(32) DEFAULT NULL);
rindex constant to start a new row set_session_longops_nohint constant BINARY_INTEGER := -1; use returned value from previous call to reuse a row
do not use slno ... for internal use by Oracle
target is the object number being worked on
sofar is any number indicating proress ... so far
totalwork a best guess as to the 100% value ... on completion
units used for sofar and totalwork
CREATE TABLE test ( testcol NUMBER(10));
-- Session 1 SELECT DISTINCT sid FROM gv$mystat; -- use this sid number in the session 2 query below
DECLARE rindex BINARY_INTEGER; slno BINARY_INTEGER; sofar NUMBER(6,2); target BINARY_INTEGER; totwork NUMBER := 100; BEGIN rindex := dbms_application_info.set_session_longops_nohint;
SELECT object_id INTO target FROM all_objs WHERE object_name = 'TEST';
FOR i IN 1 .. totwork LOOP sofar := i; dbms_application_info.set_session_longops(rindex, slno, 'PSOUG', target, 0, sofar, 100, 'Pct Complete');
INSERT INTO test VALUES (i);
dbms_lock.sleep(0.25); END LOOP; COMMIT; END; /
-- Session 2 substitute the sid returned above from session 1SELECT sid, serial#, schemaname FROM gv$session; SELECT start_time, sofar, totalwork, time_remaining, elapsed_seconds FROM gv$session_longops WHERE sid = 140 AND serial# = 266;
FOR x IN 1..5 LOOP FOR i IN 1 ..60 LOOP INSERT INTO test VALUES (i); COMMIT; dbms_lock.sleep(1); END LOOP;
act_name := 'deleting'; dbms_application_info.set_action(act_name); FOR i IN 1 ..60 LOOP DELETE FROM test WHERE testcol = i; COMMIT; dbms_lock.sleep(1); END LOOP; END LOOP; END; /
-- session 2 col module format a20 col action format a20
SELECT module, action FROM gv$session;
SELECT module, action FROM gv$sqlarea;
SELECT sql_text, disk_reads, module, action FROM gv$sqlarea WHERE action = 'deleting';