Sure, you could do that. It just doesn't seem like a good way to deal with the possibility of an index tablespace possibly having data segments in it when backing up only data segment tablespaces.
Unless you have *really* large databases with very generous restore time requirements , I don't see the point in trying to do this anyway. Jared On Wed, 2003-10-01 at 05:14, Mark Leith wrote: > Couldn't you do this with a simple: > > select owner, table_name > from all_tables > where tablespace_name = 'index_tbs'; > > ? > > Or of course use IN for a list of tablespaces? > > Or am I missing something? > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > [EMAIL PROTECTED] > Sent: 30 September 2003 22:45 > To: Multiple recipients of list ORACLE-L > Subject: RE: Separate Indexes and Data > > > > Good question Ian. If anyone does have a different backup schedule for > index tbs , I > would be interested to know how they ensure that the index TBS do not > have any > data segments in them. > > Jared > > > > "MacGregor, Ian A." <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 09/30/2003 10:34 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: RE: Separate Indexes and Data > > > > I'd be very interested to know how many people have their index > tablespaces on a different backup schedule from their data tablespaces. If > so how different? What happens when a media failure occurs and you must > restore from backup? You would need to have on hand and apply more redo > logs to make the database current. > > I understand the argument proffered is separating indexes and data can > mean that when physical corruption of the file happens to an index > tablespace then all one needs do is to offline, drop, drop and rebuild the > index tablespace. I admit I have not tried off-lining the tablespace first, > but you cannot normally drop a tablespace which is being used to enforce > referential integrity. If off-lining the tablespace first does work, I can > see someone trying to do the rebuild with the database available and having > duplicate records in the parent tables and records without parents in the > child tables. > > On the size of the segments: The paper entitled "How To Start > Defragmenting and Start Living" or something like that strongly advocated > uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I > recall. However the paper > Never mentioned what to do when an object that used to fit nicely into > the 128k extent category now more properly belongs to the 4M category. If > you move the data, large holes are left in the other tablespace, and while > this does not impact Oracle performance, it does mean that your physical > backups are larger than necessary. I am in the process of migrating from > uniform to autoallocated extents. This means extents of different sizes > share the same tablespace. The extent sizes being multiples of each other. > This removes the argument about not having indexes and data in the same > tablespaces due to their different sizes. > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > -----Original Message----- > Sent: Monday, September 29, 2003 8:10 AM > To: Multiple recipients of list ORACLE-L > > > Thomas, > > It *is* a good idea to separate index data from heap data into different > tablespaces. But the reason isn't solely to eliminate I/O competition. Even > if I/O competition isn't an issue for you (and the OFA Standard doesn't say > that it will be), then it's *still* a good idea to separate your index data > from your heap data, for reasons including: > > * Index segments have different backup and recovery requirements than > their corresponding heap segments. For example, as Peter mentioned, if you > have an index block corruption event, then it's convenient to just offline, > kill, and rebuild an index tablespace. If the indexes and data are mixed up > in a single tablespace, this is not an option. Another > example: If you construct your backup schedule to make media recovery time > a constant, then you probably don't need to back up your indexes on the same > schedule as you back up your heaps. But unless they're in different > tablespaces, this isn't an option either. > > * Index segments are usually smaller than their corresponding heap > segments. Using separate tablespaces allows you to use a smaller extent size > to conserve disk storage capacity. > > I don't think I ever wrote that you need to put indexes and their > corresponding tables/clusters on separate disks, but you do need to be > *able* to do that if your I/O rates indicate that you should. > > For the original OFA Standard definition, please see section 3 of the > document called "The OFA Standard--Oracle for Open Systems," and section 5 > of "Configuring Oracle Server for VLDB," both available for free at > www.hotsos.com. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > Thomas Day > Sent: Monday, September 29, 2003 9:05 AM > To: Multiple recipients of list ORACLE-L > > > My struggle is not with the directory layout OFA. > > It is with the "mythical" OFA that every DBA that I have talked to knows > all about. Where ORACLE says that if you are a good and competent DBA you > will separate your table data and your index data into two separate > tablespaces so that one disk head can be reading index entries while another > disk head is reading the table data. You've never run into that? > > > > > > Tim Gorman <tim > > @sagelogix.com> To: Multiple > recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > > ml-errors Subject: Re: BAARF > > > > > > 09/28/2003 09:44 > > PM > > Please respond > > to ORACLE-L > > > > > > > > > > Thomas, > > Please pardon me, but you are off-target in your criticisms of OFA. > > It has never advocated separating tables from indexes for performance > purposes. Ironically, your email starts to touch on the real reason for > separating them (i.e. different types of I/O, different recovery > requirements, etc). Tables and indexes do belong in different tablespaces, > but not for reasons of performance. > > Cary first designed and implemented OFA in the early 90s and formalized it > into a paper in 1995. Quite frankly, it is a brilliant set of rules of how > Oracle-based systems should be structured, and a breath of fresh air from > the simplistic way that Oracle installers laid things out at the time. It > took several years for Oracle Development to see the light and become > OFA-compliant, and not a moment too soon either. Just imagine if everything > were still installed into a single directory tree under ORACLE_HOME? All of > things you mention here have nothing to do with OFA. > > Please read the paper. > > Hope this helps... > > -Tim > > P.S. By the way, multiple block sizes are not intended for > performance > optimization; they merely enable transportable tablespaces between > databases with different block sizes. > > > on 9/25/03 11:04 AM, Thomas Day at [EMAIL PROTECTED] wrote: > > > > > I would love to have a definitive site that I could send all RAID-F > > advocates to where it would be laid out clearly, unambiguously, and > > definitively what storage types should be used for what purpose. > > > > Redo logs on RAID 0 with Oracle duplexing (y/n)? > > Rollback (or undo) ditto? > > Write intensive tablespaces on RAID 1+0 (or should that be 0+1)? Read > > intensive tablespaces on RAID ? (I guess 5 is OK since it's > cheaper > > than 1+0 and you won't have the write penalty) > > > > While we're at it could we blow up the OFA myth? Since you're > tablespaces > > are on datafiles that are on logical volumns that are on physical > devices > > which may contain one or many actual disks, does it really make sense > to > > worry (from a performance standpoint) about separating tables and > indexes > > into different tablespaces? > > > > We have killed the "everything in one extent" myth haven't we? > Everybody's > > comfortable with tables that have 100's of extents? > > > > And while we're at it, could we include the Oracle 9 multiple > blocksizes > > and how to use them. The best that I've seen is indexes in big > blocks, > > tables in small blocks --- uh, oh, time to separate tables and > indexes. > > > > Maybe we will never get rid of the OFA myth. > > > > Just venting. > > > > Tired of arguing in front of management with Oracle certified DBAs > that > > RAID 5 is not good, OFA is unnecessary, and uniform extents is the > only > way > > to go. Looking for a big stick to catch their attention with. > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tim Gorman > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the > message BODY, include a line containing: UNSUB ORACLE-L (or the name of > mailing list you want to be removed from). You may also send the HELP > command for other information (like subscribing). > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Thomas Day > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the > message BODY, include a line containing: UNSUB ORACLE-L (or the name of > mailing list you want to be removed from). You may also send the HELP > command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the > message BODY, include a line containing: UNSUB ORACLE-L (or the name of > mailing list you want to be removed from). You may also send the HELP > command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: MacGregor, Ian A. > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.521 / Virus Database: 319 - Release Date: 23/09/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
