Skip to Main Content
  • Questions

  • Tablespace , DF configuration in RAC

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jalpan.

Asked: April 17, 2009 - 8:10 am UTC

Last updated: April 23, 2009 - 12:36 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I am look for some constructive advice for tablespace/datafile configurations in Oracle 10.2.0.3 RAC database. We have a fairly large database in 8.1.6.3 version, and as such it has various tablespaces along the line of small_size objects, medium_size objects, large_size objects, and very_large_size objects. As you know, these are purely for performance issue as each tablespace's extent is sized accordingly. However, what I don't know is whether this concept is still relevent in 10gR2 database running on ASM? Do we have any guidelines relating to how tablespaces/datafiles are sized in relation to objects, as we need to migrate this 8.1.6.3 db to 10gR2?


Thanks

and Tom said...

... As you know, these are purely for performance issue ...

tablespaces are not about performance, they are about book-keeping, they are an organization tool you, the dba, can use to make your life more enjoyable.

I would recommend

a) ASSM (automatic segement space management)
b) using ASM (to manage the disks)
c) in a locally managed tablespace using SYSTEM ALLOCATED extents

since you will likely be copying the data from 8iR2 to 10gr2 (no direct upgrade path and you are moving to ASM). When you create the tables/indexes initially use "initial 1k next 1k pctincrease 0 minextents 1" to effectively "wipe out any legacy storage clauses" - we don't want them anymore, all you might do by having them would be to over allocate the new segments.

If you do upgrade (upgrade to 817, then to 10g), you'll be creating new tablespaces in ASM and alter table move/alter index rebuild - use that storage clause on those commands....

The only storage related attribute you need to think about would be pctfree (and maybe initrans).



And put things in tablespaces according to what makes you feel good, what makes your life better (yes, you can and in some cases absolutely should put tables and the indexes on those tables in the same tablespace - it is not bad, it can in fact be very good)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ZZRMan, April 22, 2009 - 4:45 am UTC

Dear Tom,

For the first time, i'm not fully agree with you. ASSM is a hudge feature. But i will not use this feature in production. Try "ASSM" in metalink's search engine ( too many bugs ). At least upgrade to 10.2.0.4 before using it.

If the database is really large. May be you should consider changing ASM Allocation Unit ( contact Oracle support ).

Best Regards.
Tom Kyte
April 23, 2009 - 12:36 pm UTC

try select in metalink - do you know that select has bugs? probably more than ASSM.

did you know that freelist groups have bugs? (type freelist groups into metalink...)



The last paragraph does not compute - maybe you should consider changing ASM allocation unit? How did we get from ASSM to ASM - two entirely and completely different things?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.