Thanks for the question, Daniel.

Asked: February 16, 2017 - 5:28 pm UTC

Last updated: December 13, 2019 - 3:18 am UTC

Version: 10.2.0.5.0

Viewed 10K+ times! This question is

You Asked

Hi,
We are having slowness issues so, I would like to activate the trace for 1 hour in my database for an specific schema, I have reviewed in several websites and I have these steps:

1- ALTER SESSION SET TRACEFILE_IDENTIFIER = "TEST_TRACE";
2- ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
3- ALTER SESSION SET EVENTS '10046 trace name context off'; --after 1 hour

I have also seen that using the below query we can do it for an specific SID & SERIAL# but when I do that it throws me around 400+ SIDs/SERIALs#

SELECT SID, SERIAL#, MACHINE, TERMINAL, PROGRAM
FROM SYS.V_$SESSION
WHERE USERNAME = 'TEST12';

My question is, How can I do the same but for schema TEST12 and also if I am missing steps to track enough information for performance issues

Thank you and best regards,
Daniel

and Connor said...

You can create a login trigger, for example - this one does it for a schema and for a nominated program.

create or replace
trigger TRACE_ALL_LOGINS
after logon on MYUSER.schema
disable
declare
  l_program varchar2(200);
begin
  select program
  into   l_program
  from   v$session
  where  sid = sys_context('USERENV','SID');
  
  if l_program like 'sqlplus%' then
    execute immediate 'alter session set tracefile_identifier = MYUSER';
    dbms_monitor.SESSION_TRACE_ENABLE(waits=>true);
  end if;
--
-- if i dont manage to set the trace, i'll allow the login anyway
--
exception
  when others then null;
end;
/

alter trigger TRACE_ALL_LOGINS enable;

and to turn it off, you can do:

begin
for i in ( select sid, serial#
           from v$session
           where username MYUSER
           ) loop
       dbms_monitor.SESSION_TRACE_DISABLE(i.sid, i.serial#);
end loop;
end;
/

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Gives Syntax Error in 12c

SJ, July 18, 2018 - 5:13 pm UTC

Please run this in 12c and check - it gives an error. Also give the username to be substituted in a different font. Using MYUSER doesn't help if you don't explain it properly.

July 20, 2018 - 10:35 am UTC

The trigger looks fine to me

SQL> create or replace
  2  trigger TRACE_ALL_LOGINS
  3  after logon on SCOTT.schema
  4  disable
  5  declare
  6    l_program varchar2(200);
  7  begin
  8    select program
  9    into   l_program
 10    from   v$session
 11    where  sid = sys_context('USERENV','SID');
 12
 13    if l_program like 'sqlplus%' then
 14      execute immediate 'alter session set tracefile_identifier = MYUSER';
 15      dbms_monitor.SESSION_TRACE_ENABLE(waits=>true);
 16    end if;
 17  --
 18  -- if i dont manage to set the trace, i'll allow the login anyway
 19  --
 20  exception
 21    when others then null;
 22  end;
 23  /

Trigger created.

the other block was missing a quote and an equals sign

SQL> begin
  2  for i in ( select sid, serial#
  3             from v$session
  4             where username = 'MYUSER'
  5             ) loop
  6         dbms_monitor.SESSION_TRACE_DISABLE(i.sid, i.serial#);
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

But dude... was that such a big deal?

purpose of disable statement

A reader, December 12, 2019 - 2:59 pm UTC

Thanks Connor for your answer.

I couldn't find an explanation about the 'disable' statement post 'after statement'

create or replace
trigger TRACE_ALL_LOGINS
after logon on MYUSER.schema
disable
declare
l_program varchar2(200);

What are we disabling exactly?

Thanks in advance for your feedback

December 13, 2019 - 3:18 am UTC

Its just my standard practice for creating triggers.

If you create a trigger (which by default is enabled automatically) and that trigger contains a compilation error, then no-one use that trigger, and hence MYUSER can no longer login!

Create it as disabled, which means it is "there" but not active. When the trigger has compiled happily, then you enable it to reduce the risk of blocking all logins.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Gives Syntax Error in 12c

purpose of disable statement

More to Explore

PL/SQL demos

PL/SQL docs

enable trace for specific schema

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Gives Syntax Error in 12c

purpose of disable statement

More to Explore

PL/SQL demos

PL/SQL docs