Re: Oracle BG processes and SQL visibility in 10.2.0.2

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Sat, 30 Sep 2006 22:57:11 -0700 (PDT)
Message-ID: <20061001055711.90455.qmail@web52802.mail.yahoo.com>

We noticed that too and with the job queue process it's a real pain. There are 2 workarounds I've tried, but also want to know if there's any better option:

(1) Use PLSQL_OBJECT_ID from v$session, if that job happens to be a PL/SQL.

(2) Use dba_jobs_running and v$open_cursor for the SID being traced.

HTH,
Deepak

Mladen Gogala <mgogala_at_verizon.net> wrote:

> I've had an overly active batch process (J0001) but
> I was unable to
> see the SQL. The address in V$SESSION was empty.
> After that, I tried
> with SMON. No luck, there, either. Essentially, in
> version 10.2.0.2 SQL
> statements executed by the background processes are
> not visible:
>
> SQL> select
> sql_address,sql_id,prev_sql_addr,prev_sql_id,
> program
> 2 from v$session where sid in (69,70,75)
> 3 /
>
> SQL_ADDR SQL_ID PREV_SQL PREV_SQL_ID
> PROGRAM
> -------- ------------- -------- -------------
> --------------------
> 00 00
> oracle_at_medo.noip.com
>
> (SMON)
>
> 00 00
> oracle_at_medo.noip.com
>
> (CKPT)
>
> 00 00
> oracle_at_medo.noip.com
>
> (PMON)
>

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 01 2006 - 00:57:11 CDT