Carmen, What if you change your shell script to test for the tablespace(s) being = in READ ONLY mode before you start: $ORACLE_HOME/bin/sqlplus -L /nolog <<eof connect / as sysdba whenever sqlerror exit fatal set head off feed off spool $TEMP/tablespace_status.txt SELECT DISTINCT tablespace_name || ':' || status FROM dba_tablespaces WHERE tablespace_name IN (<your list>); exit eof if [[ $? -ne 0 ]]; then echo "SQL*Plus failure" # or your own message and actions fi if [[ `/bin/grep -c "READ ONLY" $TEMP/tablespace_status.txt` -ne 0 ]]; = then # you have READ ONLY tablespaces else #load em up fi Note: The above is not tested. ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Carmen Rusu Sent: Thursday, March 03, 2005 10:35 AM To: Waleed.Khedr@xxxxxxx; oracle-l@xxxxxxxxxxxxx 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" <Waleed.Khedr@xxxxxxx> 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@xxxxxxxxxxxxxxx]=3D20=20 Sent: Thursday, March 03, 2005 11:03 AM To: oracle-l@xxxxxxxxxxxxx=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 -- //www.freelists.org/webpage/oracle-l=20 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l This electronic message transmission is a PRIVATE communication which = contains information which may be confidential or privileged. The information is = intended=20 to be for the use of the individual or entity named above. If you are = not the=20 intended recipient, please be aware that any disclosure, copying, = distribution=20 or use of the contents of this information is prohibited. Please notify = the sender of the delivery error by replying to this message, or notify us = by telephone (877-633-2436, ext. 0), and then delete it from your system. -- //www.freelists.org/webpage/oracle-l