Same query does not run on 9i

[samdba]

Hi,

I just upgraded database from 8i to 9i (9206). Following INSERT statement used to run on 8i without any problem But on 9i it runs but does not insert any rows.
If I comment "where owner||name||type not in (select owner||name||type from ode.ode_db_object_pin)", then it has no problem.

insert
into ode.ode_db_object_pin
select
owner,
name,
type,
sharable_mem
from
v$db_object_cache
where owner||name||type not in (select owner||name||type from ode.ode_db_object_pin)
and
type in ('PACKAGE','PACKAGE BODY','SEQUENCE','TRIGGER','FUNCTION')
and owner not in ('SYS','SYSTEM')
and executions > 0
order by sharable_mem desc

Pl. help.

Thanks in Adv.

[marist89]

not sure why you would do:

where owner||name||type not in (select owner||name||type from ode.ode_db_object_pin 

when you can:

where (owner, name, type) 
   not in (select owner, name, type)
           from ode.ode_db_object_pin) 

...but that's probably not your problem. Sounds like a data condition to me.

[engiri]

The condition
---------------------
where owner||name||type not in (select owner||name||type from ode.ode_db_object_pin)
---------------------

is not at all required if you just defer the currently pinned (Using)
user objects.
You can add this condition to do so
---------------------
and pins>0
---------------------

[samdba]

Originally posted by engiri
The condition
---------------------
where owner||name||type not in (select owner||name||type from ode.ode_db_object_pin)
---------------------

is not at all required if you just defer the currently pinned (Using)
user objects.
You can add this condition to do so
---------------------
and pins>0
---------------------

With above query, I am trying to populate ode_db_object_pin table at the database shutdown time with all the objects that were executed/loaded and then when database starts back, it uses this table to pin those objects. I don't think "and pins > 0" will work in this case.

Thanks,

[Axr2]

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> conn dropthis
Enter password:
Connected.

SQL> create or replace package pkg_b
2 as
3 procedure b;
4 end;
5 /

Package created.

SQL> create or replace package body pkg_b
2 as
3 procedure b
4 is
5 begin
6 null;
7 end;
8 end;
9 /

Package body created.

SQL> exec pkg_b.b;

PL/SQL procedure successfully completed.

SQL> exec pkg_b.b;

PL/SQL procedure successfully completed.

SQL> exec pkg_b.b;

PL/SQL procedure successfully completed.

SQL> Create table ode_db_object_pin as
2 (select owner,
3 name,
4 type,
5 sharable_mem
6 from
7 v$db_object_cache where 1=2)
8 /

Table created.

SQL> insert
2 into ode_db_object_pin
3 select
4 owner,
5 name,
6 type,
7 sharable_mem
8 from
9 v$db_object_cache
10 where owner||name||type not in (select owner||name||type from ode_db_object_pin)
11 and
12 type in ('PACKAGE','PACKAGE BODY','SEQUENCE','TRIGGER','FUNCTION')
13 and owner not in ('SYS','SYSTEM')
14 and executions > 0
15 order by sharable_mem desc
16 /

1 rows created.

SQL> select * from ode_db_object_pin
2 /

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE SHARABLE_MEM
---------------------------- ------------
DROPTHIS
PKG_B
PACKAGE BODY 5990