One big tablespace for all the segments

Hi gurus,

I've come across 2 interesting scenarios, need your help/guidance:

1. there are other users objects exist in system tablespace, other then sys & system, and same for sysaux, I think it should be used by OEM/dbconsole (DBSNMP & SYSMAN), please correct me if this is not right understanding, and which user's objects should exist in these 2 tablespaces (system & sysaux).

2. And 2nd: there is one BIG tablespace for all the segments, related to one user, where as whatever I've been using, it should be segregated on the basis of segments, but need some proof ... what do you think, is this good, correct, ok or ?

Please throw your experts thoughts, suggestions and advices, and if possible to provide some reference doc/links, that'll be of great great favor.

Because it is really impossible to convenience, the existing DBA, here.

Thanks and very best regards.

Existing segments within the db:
SEGMENT_TYPE         COUNT(*)         MB
------------------ ---------- ----------
CLUSTER                    10       51.5
INDEX                    2874     488.19
INDEX PARTITION           141          9
LOB PARTITION               1        .06
LOBINDEX                  676      48.19
LOBSEGMENT                676   10468.63
NESTED TABLE               25       1.56
ROLLBACK                    1        .38
TABLE                    1979     861.69
TABLE PARTITION           123       8.25
TYPE2 UNDO                 10      20.25
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1. I avoid to place user objects in system and sysaux tablespaces. When you create user you can assign default tablespace to him/her.
2. Your db is small enough to hold all the data in one datafile (34Gb for a db_block_size=8k, default), so it is suitable for you to put all user objects in single datafile in one tablespace.
slightwv (䄆 Netminder) Commented:
1: agreed.  This is a bad practice in general.
      I'm not sure if this has changed with 11g but SYSTEM used to be dictionary managed by default.  Locally managed tablespaces have advantages.

2: Separating everything out is a good practice to get into and allows for more flexibility in the future.


I can see both sides.  From a straight I/O perspective:  If everything is on one set of disks does it really matter if everything is in it's own tablespaces/datafiles?  Probably not.

From a common sense/organization point:  It just makes sense.

You have a better argument for separation for tablespace fragmentation purposes and if you might ever need different block sizes across tablespaces.

You also have transportable tablespaces available if you might ever need to move data around.

If you ever get additional disks and want to think about separation it's more of a hassle to move objects as opposed to moving datafiles.

I'm sorry I can't provide definitive proof the current DBA is wrong.

I'm afraid in this situation Seniority wins.  If the current DBA out-ranks you, he wins.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
As soon as I clicked submit, I think I might have the winning argument for you:

Oracle Optimal Flexible Architecture (OFA)

2.      Separate groups of segments (data objects) with different behavior into different tablespaces.

Granted you will want to go to Oracle and get an 'official' document but that link should get you started.

Mark GeerlingsDatabase AdministratorCommented:
1. The SYS and SYSAUX tablespaces should contain only the objects that the Oracle installer puts there.  You should *NOT* allow any user-created objects to go to those two tablespaces.

2. Whether you should have one big tablespace for all user-created objects or not is not so easy to say.  There are advantages and disadvantages either way.  I prefer the flexibility and manageablility that separate tablespaces offer.  But, if your database is small enough to fit into one tablespace, and if you don't plant to ever split indexes (for example) unto a separate disk from table data, then having just one large tablespace for the user data can be an easy way to go.

3. You didn't mention TEMP or UNDO, but these need to each be in their own tablespaces independent of SYS, SYSAUX and whatever tablespace(s) you use for user data.
I agree with others above, if your data volumes are small then a single tablespace is sufficient.

Small comment about the link above, there's generally no need to separate data from indexes.
The link above does suggest it, but it's not stated in the source article from Millsap.

Also note, with modern SAN storage systems and virtualized mount points,
splitting your tablespaces so that table segments are in one set of files on and index segments are in another set of files
does NOT mean your data and indexes will be physically separated and not have io contention for the same physical devices.
Conversely, putting them all together in a single tablespace, and even a single file also does not mean you will create contention.

The segments are accessed in blocks, and those blocks could be anywhere and that "anywhere" is largely invisible to the database.

So, beyond any technical ramifications, go for simple (i.e. fewer pieces to monitor and maintain) until you have a reason to add complexity.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux Distributions

From novice to tech pro — start learning today.