From oracle-l-bounce@freelists.org Fri Jun 25 17:22:52 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5PMMRn23372 for ; Fri, 25 Jun 2004 17:22:37 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5PMMH623348 for ; Fri, 25 Jun 2004 17:22:27 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 757F572C3F9; Fri, 25 Jun 2004 17:05:27 -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 23050-02; Fri, 25 Jun 2004 17:05:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BA41072C12C; Fri, 25 Jun 2004 17:05:26 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 25 Jun 2004 17:04:04 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 05E2F72C233 for ; Fri, 25 Jun 2004 17:04:04 -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 22910-02 for ; Fri, 25 Jun 2004 17:04:03 -0500 (EST) Received: from mta10-svc.ntlworld.com (mta10-svc.ntlworld.com [62.253.162.94]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 67EDC72C130 for ; Fri, 25 Jun 2004 17:04:03 -0500 (EST) Received: from GOFASTER3 ([80.2.138.133]) by mta10-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with SMTP id <20040625215811.DHDE29369.mta10-svc.ntlworld.com@GOFASTER3> for ; Fri, 25 Jun 2004 22:58:12 +0100 From: "David Kurtz" To: Subject: RE: Script to overcome ORA-54 during DDL Date: Fri, 25 Jun 2004 22:59:09 +0100 Message-ID: MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) In-Reply-To: <011201c45af4$29886060$7102a8c0@Primary> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Importance: Normal X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3739 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: info@go-faster.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I've used something like this in the past, except I also exclusively lock the table to which I am trying to apply the DDL. The idea being that I wait to get the exclusive lock, and having got the exclusive lock the DDL can acquire the lock in nowait mode. Its not perfect, but I find I need less attempts to get the DDL to execute. _________________________ David Kurtz Go-Faster Consultancy Ltd. tel: +44 (0)7771 760660 fax: +44 (0)7092 348865 mailto:david.kurtz@go-faster.co.uk web: www.go-faster.co.uk PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Jonathan Lewis Sent: 25 June 2004 21:37 To: oracle-l@freelists.org Subject: Re: Script to overcome ORA-54 during DDL This looks about right (except for the formatting); create or replace procedure do_ddl(m_sql varchar2) as in_use exception ; pragma exception_init(in_use, -54); begin while true loop begin execute immediate m_sql; exit; exception when in_use then null; when others then raise; end; dbms_lock.sleep(0.01); end loop; end; / Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st ----- Original Message ----- From: "Jeremiah Wilton" To: Sent: Friday, June 25, 2004 7:55 PM Subject: Script to overcome ORA-54 during DDL Sometimes when trying to perform DDL on really hot objects (heavy read/DML), I get: ORA-00054: resource busy and acquire with NOWAIT specified. I guess this is because you need to obtain a library cache lock in order to change the object definition. Since there is no enqueue mechanism for this resource, you can't just 'lock table ... in exclusive mode;' All that gives you is a DML lock. One way to avoid this is to write a PL/SQL routine that uses DBMS_SQL and spins trying to run the DDL, stopping only when it succeeds. This seems to work most of the time. Does anyone have a script for doing the above that they would like to share? Please don't tell me to just use dbms_redefinition. That is unnecessarily complex when the above technique can be used instead. Thanks -- Jeremiah Wilton ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------