From oracle-l-bounce@freelists.org Tue Jun 28 10:40:49 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j5SFenge002843 for ; Tue, 28 Jun 2005 10:40:49 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180] (may be forged)) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j5SFedIP002810 for ; Tue, 28 Jun 2005 10:40:40 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1BD651C56F6; Tue, 28 Jun 2005 09:36:56 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00695-03; Tue, 28 Jun 2005 09:36:55 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 86D971C5606; Tue, 28 Jun 2005 09:36:55 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=1KVcOFNfxA3hyqHVEacRuiTtwUX9pmS9dx9syPDAIfgb5XdAC3vmKqZOEGZ/dpPTOdxhkOW+VJOlsBJaLi2tgujfNK6YXEatu5Xj8HNSnh0MAk1PJbJfkhCe0FnIHM8RhCxZYUMuIY5KHng5j8O7nXl5B0Ub9cMQ2qv3RfpbWIM= ; Message-ID: <20050628143503.18427.qmail@web31213.mail.mud.yahoo.com> Date: Tue, 28 Jun 2005 07:35:03 -0700 (PDT) From: Deepak Sharma Subject: Re: Package becomes INVALID when a partiiton is dropped To: Eric List Cc: oracle-l@freelists.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="0-548970808-1119969303=:18275" X-archive-position: 21749 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sharmakdeep_oracle@yahoo.com Precedence: normal Reply-To: sharmakdeep_oracle@yahoo.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.0 required=5.0 tests=AWL,BAYES_00, FORGED_YAHOO_RCVD autolearn=no version=2.63 --0-548970808-1119969303=:18275 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Id: Content-Disposition: inline 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 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 and not on the . > > 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) > : > > 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@freelists.org > > [mailto:oracle-l-bounce@freelists.org] On Behalf > Of Deepak Sharma > > Sent: Monday, June 27, 2005 5:01 PM > > To: oracle-l@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 > > > ____________________________________________________ Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com --0-548970808-1119969303=:18275 Content-Type: application/octet-stream; name="test_case.log" Content-Transfer-Encoding: base64 Content-Description: 326894499-test_case.log Content-Disposition: attachment; filename="test_case.log" U1FMPiBAY3JfdDENClNRTD4gZHJvcCB0YWJsZSB0MQ0KICAyICAvDQoNClRh YmxlIGRyb3BwZWQuDQoNClNRTD4gDQpTUUw+IGNyZWF0ZSB0YWJsZSB0MSAo DQogIDIgIAkgICAgIGNvbDFfa2V5CSAgICAgCW51bWJlciwNCiAgMyAgCSAg ICAgY29sMl9udW0gCQl2YXJjaGFyMigzMiksDQogIDQgIAkgICAgIGNvbDNf aWQgICAgIAkJY2hhcigyKSwNCiAgNSAgCSAgICAgY29sNF9jaGFyCSAgICAg CWNoYXIoMSksDQogIDYgIAkgICAgIHBhcnRpdGlvbl9jb2RlIAljaGFyKDMp DQogIDcgICkNCiAgOCAgcGFydGl0aW9uIGJ5IGxpc3QgKCBwYXJ0aXRpb25f Y29kZSApDQogIDkgICgNCiAxMCAgCSAgICAgcGFydGl0aW9uIFAxIHZhbHVl cyAoICdBQkMnICksDQogMTEgIAkgICAgIHBhcnRpdGlvbiBQMiB2YWx1ZXMg KCAnR0VIJyApLA0KIDEyICAJICAgICBwYXJ0aXRpb24gUDMgdmFsdWVzICgg J1hZWicgKSwNCiAxMyAgCSAgICAgcGFydGl0aW9uIFBfT1RIRVJTIHZhbHVl cyAoREVGQVVMVCkNCiAxNCAgKQ0KIDE1ICB0YWJsZXNwYWNlIHVzZXJzMDEN CiAxNiAgLw0KDQpUYWJsZSBjcmVhdGVkLg0KDQpTUUw+IEB4LnBsDQpTUUw+ IENSRUFURSBPUiBSRVBMQUNFIFBBQ0tBR0UgVDFfUEtHDQogIDIgIElTDQog IDMgIAkgICAgIFBST0NFRFVSRSB0MV9wcm9jICgNCiAgNCAgCQkgICAgIHBf Y29sMSAJICAgICBJTiBUMS5DT0wxX0tFWSVUWVBFLA0KICA1ICAJCSAgICAg cF9jb2wyX251bQkgICAgIElOIFQxLkNPTDJfTlVNJVRZUEUNCiAgNiAgCSAg ICAgKTsNCiAgNyAgRU5EOw0KICA4ICAvDQoNClBhY2thZ2UgY3JlYXRlZC4N Cg0KU1FMPiANClNRTD4gQ1JFQVRFIE9SIFJFUExBQ0UgUEFDS0FHRSBCT0RZ IFQxX1BLRw0KICAyICBJUw0KICAzICAJICAgICBQUk9DRURVUkUgdDFfcHJv YyAoDQogIDQgIAkJICAgICBwX2NvbDEgCSAgICAgSU4gVDEuQ09MMV9LRVkl VFlQRSwNCiAgNSAgCQkgICAgIHBfY29sMl9udW0JICAgICBJTiBUMS5DT0wy X05VTSVUWVBFDQogIDYgIAkgICAgICkNCiAgNyAgCSAgICAgQVMNCiAgOCAg CSAgICAgQkVHSU4NCiAgOSAgCQkgICAgIGRibXNfb3V0cHV0LnB1dF9saW5l KCAnSW4gcHJvY2VkdXJlJyApOw0KIDEwICAJICAgICBFTkQgdDFfcHJvYzsN CiAxMSAgRU5EOw0KIDEyICAvDQoNClBhY2thZ2UgYm9keSBjcmVhdGVkLg0K DQpTUUw+IEBzaG93X2ludmFsaWQNClNRTD4gc2V0IGxpbmVzIDEzMg0KU1FM PiBjb2wgb2JqZWN0X25hbWUgZm9yIGEzMg0KU1FMPiANClNRTD4gc2V0IGVj aG8gb24NClNRTD4gDQpTUUw+IFNFTEVDVCBvYmplY3RfbmFtZSwgb2JqZWN0 X3R5cGUsIENPVU5UKCopDQogIDIgIEZST00gVVNFUl9PQkpFQ1RTDQogIDMg IFdIRVJFIHN0YXR1cz0nSU5WQUxJRCcNCiAgNCAgICBBTkQgb2JqZWN0X25h bWUgbGlrZSAnJVQxJScNCiAgNSAgR1JPVVAgQlkgb2JqZWN0X25hbWUsIG9i amVjdF90eXBlDQogIDYgIC8NCg0Kbm8gcm93cyBzZWxlY3RlZA0KDQpTUUw+ IGFsdGVyIHRhYmxlIFQxIGRyb3AgcGFydGl0aW9uIFAzOw0KDQpUYWJsZSBh bHRlcmVkLg0KDQpTUUw+IEBzaG93X2ludmFsaWQNClNRTD4gc2V0IGxpbmVz IDEzMg0KU1FMPiBjb2wgb2JqZWN0X25hbWUgZm9yIGEzMg0KU1FMPiANClNR TD4gc2V0IGVjaG8gb24NClNRTD4gDQpTUUw+IFNFTEVDVCBvYmplY3RfbmFt ZSwgb2JqZWN0X3R5cGUsIENPVU5UKCopDQogIDIgIEZST00gVVNFUl9PQkpF Q1RTDQogIDMgIFdIRVJFIHN0YXR1cz0nSU5WQUxJRCcNCiAgNCAgICBBTkQg b2JqZWN0X25hbWUgbGlrZSAnJVQxJScNCiAgNSAgR1JPVVAgQlkgb2JqZWN0 X25hbWUsIG9iamVjdF90eXBlDQogIDYgIC8NCg0KT0JKRUNUX05BTUUgICAg ICAgICAgICAgICAgICAgICAgT0JKRUNUX1RZUEUgICAgICAgICAgQ09VTlQo KikgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgDQotLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0t LSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICANClQxX1BLRyAgICAgICAgICAg ICAgICAgICAgICAgICAgIFBBQ0tBR0UgICAgICAgICAgICAgICAgICAgICAx ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgIA0KVDFfUEtHICAgICAgICAgICAg ICAgICAgICAgICAgICAgUEFDS0FHRSBCT0RZICAgICAgICAgICAgICAgIDEg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgDQoNClNRTD4gc3Bvb2wgb2ZmDQo= --0-548970808-1119969303=:18275-- -- http://www.freelists.org/webpage/oracle-l