Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 27, 2001 - 11:10 am UTC

Last updated: June 10, 2004 - 4:58 pm UTC

Version: 8.1.7.1

Viewed 1000+ times

You Asked

Hi

I wonder if the number of extents of segments in a Locally Maaged Tablespace (with uniform size) matters? I have to create several tablespaces for an application, there are 700 tables and 1900 indexes. The size of these segments sizes from several KB to GB, I am planning to create just two tablespaces, one for data and the other for index (with 10 datafiles each), if I use 128K extent size the big GB tables would have tons of extents and the small tables probbaly just one extent, is this feasible? I understand that the number of extents does not affect the perfomance of database so I guess this shouldnt matter?

Or I have to used the old space management strategy, create one tablespace for very big segments, one for big segments, one for mid-size segments, one for small segments and one for micro-sized segments?

What would happen if one sgement has 10000 extents in a locally managed tablespace???

and Tom said...

No, you want to use 6 tablespaces (if you want to segregate indexes and tables)

You want to get in the habit of creating small, medium and large.

Small with 64k extents
Medium with 1m extents
Large with 128m extents

for example.

I wish that was the "old space mgmt" way -- we would not need LMTs if people did that all of the time ;)


10,000 extents -- probably too many. hundreds, low thousands -- just fine.

Rating

  (2 ratings)

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

Comments

Number of Extents

Dee, July 06, 2001 - 3:03 pm UTC

Nice succinct answer for those of us who aren't into doing all the math. Thanks!

How about going for very small extents

Manish, June 10, 2004 - 9:13 am UTC

I have similar situations. I am using an ERP application with around 3000+ tables with 0 rows. If I use small table space with 64K extent, there may be wastage of space.
Can I go for very small with say 8K extents?

Thank you



Tom Kyte
June 10, 2004 - 4:58 pm UTC

even if you have 1000 objects that each waste 56k -- you would have 56m -- my pendrive is bigger than that.

I would use system allocated LMTs....

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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.