Re: Package becomes INVALID when a partiiton is dropped
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: Eric List <listeric1@xxxxxxxxx>
- Date: Tue, 28 Jun 2005 07:35:03 -0700 (PDT)
Eric,
Yes, we too are in situation where it affects a
production application. The PLSQL package itelf is
created dynamically (from another PLSQL), so
hard-coding the column datatype is not possible. While
the package is running, say it takes 2 Hrs and runs at
10 a.m., and if we drop a partition at 10:30, the
package gets invalidated. It gets created/compiled at
the next run of the application, and works fine.
One solution, as you said, is to separate the two jobs
(application and drop partition), but there is no
certain time the application runs. It typically runs
after 4Hrs of the previous run, so the 10 a.m. as in
this example, is not always 10 a.m. To accomplish the
above, we either need to come up with a hand-shaking
process between the app and database job, or ask
Oracle's help. I have currently open a TAR with Oracle
on this issue.
PS: Attaching a Test Case.
Thanks,
Deepak
--- Eric List <listeric1@xxxxxxxxx> wrote:
> Hello,
>
> I hate to disagree here. I had a similar situation
> at a customer some
> time where adding a partition invalidated
> "dependent" plsql objects.
> The problem then was that there were sessions using
> the packages
> at the time of addition such that they ran into
> errors - unfortunately
> it was on production and we had to restart the
> application. Compiling
> the package was not possible because of locks on the
> data dictionary.
> The version of Oracle was at that time 9.0.1. You
> might want to take
> a look at Note 165510.1: Top Partitioned Tables
> Bugs and look for
> "Adding" this will take you to bug 1213768.
>
> Now, here we have a situation where a partition is
> dropped and in my
> opinion this should not invalidate pl/sql objects
> that are dependent on
> the <table> and not on the <partition>.
>
> Of course, if possible postpone table (partition)
> management to off-hours
> if there are any.
>
> Hth,
> regards
> Eric Valk
>
>
>
> 2005/6/28, Mercadante, Thomas F (LABOR)
> <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>:
> > Deepak,
> >
> > Is this a problem for you? Oracle is working as
> expected - DDL is
> > executing against the table, so all dependent
> objects need to be
> > recompiled.
> >
> > You could change your package to say "col1
> varchar2(whatever);" and this
> > problem would go away. Or you could simple
> recompile all invalid
> > objects in your schema every time you drop a
> partition. Or you could
> > simply ignore the problem and let Oracle
> automatically recompile the
> > object when it gets referenced. I vote for the
> last option because this
> > really is not an issue. Oracle cleans things up
> nicely.
> >
> > Tom
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
> Of Deepak Sharma
> > Sent: Monday, June 27, 2005 5:01 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: Package becomes INVALID when a partiiton
> is dropped
> >
> > I have a table, say T1, having 3 partitions P1, P2
> and
> > P3.
> >
> > There exists a PL/SQL procedure that declares
> > variables as :
> >
> > col1 T1.col1%TYPE
> >
> > When I drop a partition P1 of table T1, the PL/SQL
> > procedure becomes INVALID.
> >
> > The PL/SQL doesn't reference the partition P1 at
> all.
> >
> >
> ____________________________________________________
> > Yahoo! Sports
> > Rekindle the Rivalries. Sign up for Fantasy
> Football
> > http://football.fantasysports.yahoo.com
> > --
> > //www.freelists.org/webpage/oracle-l
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
____________________________________________________
Yahoo! Sports
Rekindle the Rivalries. Sign up for Fantasy Football
http://football.fantasysports.yahoo.com
Other related posts:
