Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: how can you protect read-only indexes?

RE: how can you protect read-only indexes?

From: Carmen Rusu <carmen.rusu_at_rrc.state.tx.us>
Date: Thu, 03 Mar 2005 11:35:23 -0600
Message-Id: <s226f68a.092@gwgate.rrc.state.tx.us>


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.

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_at_FMR.COM> 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_at_rrc.state.tx.us]=20 Sent: Thursday, March 03, 2005 11:03 AM
To: oracle-l_at_freelists.org
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?=20

Thanks,
-Carmen Rusu
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l 
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 03 2005 - 12:46:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US