From oracle-l-bounce@freelists.org Tue Jun 28 13:42:33 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j5SIgXOL032021 for ; Tue, 28 Jun 2005 13:42:33 -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 j5SIgSIP032005 for ; Tue, 28 Jun 2005 13:42:29 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D42321C59FD; Tue, 28 Jun 2005 12:38:44 -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 23503-09; Tue, 28 Jun 2005 12:38:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5A2071C59C4; Tue, 28 Jun 2005 12:38:44 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=NpuAY4LcRDnEb6vtoF4VHgrslOHjnZAlvfnzMjoixLsP05HkVD90HN75qR1t8w6A+cBoGeOjMXLVw4VgKV1QVW6rpvu0xB2Cy5xuKuvo3H5LG8+2O/6k59XC8ZZqff3gMEKFhX/eq0iD6XhsDav94ToS9wnBGsvgtMxBQS/fTag= Message-ID: Date: Tue, 28 Jun 2005 13:36:56 -0400 From: Paul Baumgartel To: Oracle-L Freelists Subject: Re: drop user fails due to REVOKE? In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Content-Disposition: inline References: X-archive-position: 21766 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: paul.baumgartel@gmail.com Precedence: normal Reply-To: paul.baumgartel@gmail.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=-2.8 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 The issue for me was this behavior on DROP USER. It may well be that I just never ran into it before. I did a test that confirmed the behavior (a DROP USER did not complete until the termination of another session executing a procedure granted to the user being dropped), so I'm going to assume this is as intended. Thanks for your responses. PB On 6/28/05, Jared Still wrote: > Paul, > > I've had similar issues with GRANTs in version 7. > > Maybe I'm missing something in your post, but this > is expected behavior if the package is in use. > > Jared > > > > On 6/28/05, Paul Baumgartel wrote: > > > > Hi all. I'm noticing some behavior in 10.1.0.4 that seems odd. > > > > I have a user A who has been directly granted EXECUTE on DBMS_LOCK and > UTL_TCP. > > > > Attempts to DROP USER A CASCADE frequently succeed in dropping all of > > the user's objects, but fail (after the default 5-minute timeout > > period) to drop the user itself, reporting "timeout waiting to lock > > object SYS.DBMS_LOCK" (or SYS.UTL_TCP). > > > > Now, I understand that grants and revokes must lock the object, and > > will time out in this manner if the object is in use by another > > session. I don't recall seeing this, though, in previous versions. I > > wonder if there's an explicit REVOKE on the DROP USER, or if it's just > > the recursive SQL to do the revoke. Does any of this ring a bell, > > i.e., is this the expected behavior even pre-10g? Is there a > > workaround? > > > > Thanks, > > > > -- > > Paul Baumgartel > > paul.baumgartel@aya.yale.edu > > -- > > http://www.freelists.org/webpage/oracle-l > > > > > > -- > Jared Still > Certifiable Oracle DBA and Part Time Perl Evangelist > > -- Paul Baumgartel paul.baumgartel@aya.yale.edu -- http://www.freelists.org/webpage/oracle-l