From oracle-l-bounce@freelists.org Thu Mar 3 14:56:02 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j23Ku294032045 for ; Thu, 3 Mar 2005 14:56:02 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j23Ktxem032029 for ; Thu, 3 Mar 2005 14:55:59 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D6DD47DF81; Thu, 3 Mar 2005 14:54:08 -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 27327-01; Thu, 3 Mar 2005 14:54:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 584B47DECE; Thu, 3 Mar 2005 14:54:08 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: how can you protect read-only indexes? Date: Thu, 3 Mar 2005 14:52:15 -0500 Message-ID: <42BBD772AC30EA428B057864E203C99901233750@MSGBOSCLF2WIN.DMN1.FMR.COM> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: how can you protect read-only indexes? Thread-Index: AcUgGAfPpg68hXcRR4SGENBiLmedNQAEG7lQ From: "Khedr, Waleed" To: X-OriginalArrivalTime: 03 Mar 2005 19:52:22.0493 (UTC) FILETIME=[83FB58D0:01C5202A] X-archive-position: 16920 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Waleed.Khedr@FMR.COM Precedence: normal Reply-To: Waleed.Khedr@FMR.COM X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: I know I'm not answering your question, but I have to say that you need to fix the processes themselves that are causing these issues. Also why does the process drop the indexes if it failed truncating the table. You need to defined some dependencies and abort mechanisms. Regards, Waleed -----Original Message----- From: Carmen Rusu [mailto:carmen.rusu@rrc.state.tx.us]=20 Sent: Thursday, March 03, 2005 12:35 PM To: Khedr, Waleed; oracle-l@freelists.org Subject: RE: how can you protect read-only indexes? This is about a datawarehouse ETL job. It's scheduled via crontab and runs once a month. Before a good run, I move the affected tablespaces in read-write by running a script manually, not via crontab like the rest of the ETL. When the load finishes I move them back to read-only mode, same way as at the beginning. Last week the ETL just run second time due to a crontab schedule mistake. Obviously, in this case the tablespace alter to read-write didnt happen, saving the most time consuming part of the ETL, the table loads. The sql*loader part is protected by a sentinel file. The truncate table doesnt happen on a read-only tablespace. So I am left with the indexes - how do I protect them? Rebuilding the indexes is easy and takes about 4h.=20 The problem is the data warehouse slows to unusable until I do it so I want to foolproof it. Thanks, -Carmen Rusu >>> "Khedr, Waleed" 3/3/2005 11:03:17 AM >>> You need to configure accounts and security differently. Don't give owner id to developers. Waleed -----Original Message----- From: Carmen Rusu [mailto:carmen.rusu@rrc.state.tx.us]=3D20=20 Sent: Thursday, March 03, 2005 11:03 AM To: oracle-l@freelists.org=20 Cc: Carmen Rusu Subject: how can you protect read-only indexes? Oracle EE 9.2.0.4 64 bit SunOS 5.8 64bit data warehouse db ~100gb right now, growing every month no partitioning yet Just verified that you can drop an index when its underlying tablespace is in read-only mode. It happened when an ETL job ran second time, by mistake. The corresponding tables, also on read-only tablespaces, survived ok the truncate op. So, what can I do to foolproof my ETL, so that the indexes are not dropped by mistake next time?=3D20 Thanks, -Carmen Rusu Oracle DBA -- http://www.freelists.org/webpage/oracle-l=20 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l