| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Package becomes INVALID when a partiiton is dropped
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
> 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_at_labor.state.ny.us>:
> > 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_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org] On Behalf
> Of Deepak Sharma
> > Sent: Monday, June 27, 2005 5:01 PM
> > To: oracle-l_at_freelists.org
> > 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
> > --
> > http://www.freelists.org/webpage/oracle-l
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 28 2005 - 10:40:49 CDT
- application/octet-stream attachment: 326894499-test_case.log
 |
 |