ALTER SYSTEM SET timed_statistics = true;
ALTER SYSTEM SET max_dump_file_size = unlimited;
ALTER SYSTEM SET tracefile_identifier = 'mapguide';
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, Serial#, TRUE);
SELECT 'EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(' || SID || ', ' || SERIAL# || ', TRUE);' FROM V$SESSION WHERE USERNAME = 'GEOMETRY_USER';
grant execute on dbms_system to BLOGSQL;
CREATE OR REPLACE
PROCEDURE try_sql_trace_for_session (ProgName IN VARCHAR2) IS
-- SQL> set serveroutput on;
-- SQL> execute sys.TRY_SQL_TRACE_FOR_SESSION('SqlNav');
-- SID: 11 Serial#: 58
-- Tracing enabled ... bye, bye
--
-- PL/SQL procedure successfully completed.
nCount NUMBER := 0;
CURSOR curs_get_sid IS
SELECT sid,serial#
FROM v$session
WHERE program LIKE '%'||ProgName||'%';
BEGIN
WHILE nCount = 0
LOOP
FOR rec IN curs_get_sid LOOP
dbms_output.put_line('SID: '||rec.sid||' Serial#: '||rec.serial#);
dbms_system.set_sql_trace_in_session(rec.sid,rec.serial#,TRUE);
nCount := 1;
END LOOP;
dbms_lock.sleep(10);
END LOOP;
dbms_output.put_line('Tracing enabled ... bye, bye');
END;
/
CREATE OR REPLACE PROCEDURE try_sql_trace_for_session_mch (Machine IN VARCHAR2) IS
-- SQL> set serveroutput on;
-- SQL> execute sys.TRY_SQL_TRACE_FOR_SESSION('SqlNav');
-- SID: 11 Serial#: 58
-- Tracing enabled ... bye, bye
--
-- PL/SQL procedure successfully completed.
nCount NUMBER := 0;
CURSOR curs_get_sid IS
SELECT sid,serial#
FROM v$session
WHERE machine LIKE '%'|| Machine ||'%';
BEGIN
WHILE nCount = 0
LOOP
FOR rec IN curs_get_sid LOOP
dbms_output.put_line('SID: '||rec.sid||' Serial#: '||rec.serial#);
dbms_system.set_sql_trace_in_session(rec.sid,rec.serial#,TRUE);
nCount := 1;
END LOOP;
dbms_lock.sleep(10);
END LOOP;
dbms_output.put_line('Tracing enabled ... bye, bye');
END;
/
SET SERVEROUTPUT ON;
EXEC sys.try_sql_trace_for_session_mch('NOME_COMPUTADOR');
-- ARQUIVOS DE TRACE - DEPOIS tkprof NELES;
SET LINESIZE 100
COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');