> > select distinct name from all_source;
> > .......
> > SYS_YOID0000052452$
> > ......
> > 3196 rows selected.

> > Ok no problem.

> > select distinct owner from v$access
> > SQL> /
> > select distinct owner from v$access
> >                            *
> > ERROR at line 1:
> > ORA-01013: user requested cancel of current operation

> > I had to terminate it as it become non-terminating.

> > "select owner from v$access" returns a mere 193 rows, and it cannot
> > sort it?

> > I suspect there is exists an infinite loop somewhere.

> > This was attempted on 10gR2, 11gR1 and both had the same problem.

> I was able to reproduce this problem on Oracle 10.2.0.2 with the
> Oracle October 2006 CPU on 64 bit Windows 2003.

> From the udump trace file:
> ksedmp: internal or fatal error
> ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
> [__intel_new_memcpy+610] [PC:0x3236532] [ADDR:0x0] [UNABLE_TO_READ] []
> Current SQL statement for this session:
> select distinct owner from v$access
> ----- Call Stack Trace -----
> calling              call     entry                argument values in
> hex
> location             type     point                (? means dubious
> value)
> -------------------- -------- --------------------
> ----------------------------
> __intel_new_memcpy+           0000000000000000     000000000 000000000
> 0118AF5A0
> 610                                                7FF970C7598
> 000007FF95D155F0     CALL???  __intel_new_memcpy+  0000007FF 013DF42E8
> 001749686
>                               610                  000000000
> ...

> From a 10046 trace at level 8:
> *** 2008-01-23 06:51:57.901
> WAIT #3: nam='latch: library cache' ela= 8566 address=1398554576
> number=214 tries=1 obj#=-1 tim=5204643696
> WAIT #3: nam='latch: library cache' ela= 859 address=1398554576
> number=214 tries=1 obj#=-1 tim=5205047765
> WAIT #3: nam='latch: library cache' ela= 2958 address=1398554576
> number=214 tries=1 obj#=-1 tim=5205183748
> WAIT #3: nam='latch: library cache' ela= 551 address=1398554576
> number=214 tries=1 obj#=-1 tim=5205267198
> *** 2008-01-23 06:54:39.117
> WAIT #3: nam='latch: library cache' ela= 813 address=1398554576
> number=214 tries=1 obj#=-1 tim=5365848854
> *** 2008-01-23 06:55:06.288
> WAIT #3: nam='latch: library cache' ela= 30 address=1398554576
> number=214 tries=1 obj#=-1 tim=5393019180
> *** 2008-01-23 06:55:30.006
> WAIT #3: nam='latch: library cache' ela= 68 address=1398554576
> number=214 tries=1 obj#=-1 tim=5416746379
> *** 2008-01-23 06:55:50.584
> WAIT #3: nam='latch: library cache' ela= 33 address=1398554576
> number=214 tries=1 obj#=-1 tim=5437323921
> *** 2008-01-23 06:57:09.536
> WAIT #3: nam='latch: library cache' ela= 111 address=1398554576
> number=214 tries=1 obj#=-1 tim=5516279642
> *** 2008-01-23 06:57:20.895
> WAIT #3: nam='latch: library cache' ela= 77 address=1398554576
> number=214 tries=1 obj#=-1 tim=5527627340
> *** 2008-01-23 06:57:36.082
> WAIT #3: nam='latch: library cache' ela= 246 address=1398554576
> number=214 tries=1 obj#=-1 tim=5542815685
> *** 2008-01-23 06:57:57.957
> WAIT #3: nam='latch: library cache' ela= 123 address=1398554576
> number=214 tries=1 obj#=-1 tim=5564704225
> *** 2008-01-23 06:58:14.644
> WAIT #3: nam='latch: library cache' ela= 63 address=1398554576
> number=214 tries=1 obj#=-1 tim=5581385020
> *** 2008-01-23 06:58:26.269
> WAIT #3: nam='latch: library cache' ela= 62 address=1398554576
> number=214 tries=1 obj#=-1 tim=5593004724
> *** 2008-01-23 06:58:48.346
> WAIT #3: nam='latch: library cache' ela= 42 address=1398554576
> number=214 tries=1 obj#=-1 tim=5615094241
> WAIT #3: nam='latch: library cache' ela= 8 address=1398554576
> number=214 tries=1 obj#=-1 tim=5615168161
> ...

> For an average 60 second interval interval, the session had the
> following latch statistics:
> Latch         Child# Level   Gets  Misses  Sleeps  Sleeps1
> LIBRARY CACHE  1       5   529418     25      0       25
> LIBRARY CACHE  2       5   539720     36      0       36
> LIBRARY CACHE  3       5   519189     15      0       15
> LIBRARY CACHE  4       5   516501     55      0       55
> LIBRARY CACHE  5       5   524907   1744      4     1740

> On the waits, P1 is 13985, P2 is 214, P3 is 0, Buf is 99, SQL Hash is
> 3250939240.

> SELECT
>   SQL_TEXT
> FROM
>   V$SQL
> WHERE
>   HASH_VALUE=3250939240

> SQL_TEXT
> -----------------------------------
> select distinct owner from v$access

> SELECT
>   ID,
>   SUBSTR(OPERATION,1,12) OPERATION,
>   SUBSTR(OPTIONS,1,12) OPTIONS,
>   SUBSTR(OBJECT_OWNER||'.'||OBJECT_NAME,1,20) OBJECT,
>   SUBSTR(OBJECT_TYPE,1,13) OBJECT_TYPE,
>   PARENT_ID,
>   DEPTH,
>   POSITION,
>   CPU_COST
> FROM
>   V$SQL_PLAN_STATISTICS_ALL
> WHERE
>   HASH_VALUE=3250939240
>   AND CHILD_NUMBER=1
> ORDER BY
>   ID;

> ID OPERATION    OPTIONS      OBJECT               OBJECT_TYPE
> PARENT_ID      DEPTH   POSITION   CPU_COST
> -- ------------ ------------ -------------------- -------------
> ---------- ---------- ---------- ----------
>  1 HASH
> UNIQUE       .                                          0
> 1          1    2142850
>  2 NESTED
> LOOPS              .
> 1          2          1    1115000
>  3 NESTED
> LOOPS              .
> 2          3          1    1080000
>  4 MERGE JOIN
> CARTESIAN    .                                          3
> 4          1     730000
>  5 FIXED TABLE  FULL         SYS.X$KSUSE          TABLE
> (FIXED)         4          5          1     380000
>  6 BUFFER
> SORT         .                                          4
> 5          2     350000
>  7 FIXED TABLE  FULL         SYS.X$KGLDP          TABLE
> (FIXED)         6          6          1     350000
>  8 FIXED TABLE  FIXED INDEX  SYS.X$KGLLK (ind:1)  TABLE
> (FIXED)         3          4          2       3500
>  9 FIXED TABLE  FIXED INDEX  SYS.X$KGLOB (ind:1)  TABLE
> (FIXED)         2          3          2       3500

> SELECT
>   ID,
>   ACCESS_PREDICATES,
>   FILTER_PREDICATES
> FROM
>   V$SQL_PLAN_STATISTICS_ALL
> WHERE
>   HASH_VALUE=3250939240
>   AND CHILD_NUMBER=1
> ORDER BY
>   ID;

> ID ACCESS_PREDICATES FILTER_PREDICATES
> -- ----------------- -----------------
>  1
>  2
>  3
>  4
>  5                   "S"."INST_ID"=USERENV('INSTANCE')
>  6
>  7
>  8                   ("L"."KGLLKUSE"="S"."ADDR" AND
> "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH")
>  9                   ("O"."KGLNAHSH"="D"."KGLRFHSH" AND
> "O"."KGLHDADR"="D"."KGLRFHDL")

> Why are you attempting to run this SQL statement?

> A quick Google search finds this page:http://download-uk.oracle.com/docs/cd/B13789_01/server.101/b10755/dyn...

> V$ACCESS displays objects in the database that are currently locked
> and the sessions that are accessing them.
> Owner: Owner of the object

> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.