Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:


I have am 11g database with 5 tablespaces and all set to locally managed tablespaces.

I noticed SYSTEM, TEMP and UNDOTBS1 ASSM are set to MANUAL. Is it recommended to set these to AUTO? I have been reading different things about this an that this may improve and may not improve performance depending on the applicatin.

The applications is a reporting analysis COTS package that purges data nighly and loads new data. It runs about 10 hours every night..

I also noticed that SYSTEM is sied at 3.5 GB abd 98% full and does not see to extends until it hits 100%. is this normal? is this filesize small.
  • 2
  • 2
1 Solution
slightwv (䄆 Netminder) Commented:
>>and does not see to extends until it hits 100%

It will extend when there isn't an extent available for the NEXT size.

>>Is it recommended to set these to AUTO?

Depends.  AUTO is the 'new' way.  Manual is still valid.
sam15Author Commented:
Is not this the default for all tablespaces with 11gR2?

I think oracle recommends that too for locally managed spaces.


is it recommended for data warehouse kind of database that get bulk loads at night.
slightwv (䄆 Netminder) Commented:
I believe it is the default since 10g.  I'm not sure about older databases that have been upgraded.

If this has been manually changed it might have been for a reason.

Check the docs and online papers that talk about this topic and determine if locally managed makes sense for your database.  Every database is different.

For me, I love locally managed.  I can't suggest what works for you.
sam15Author Commented:
from what i read dictionary managed tbalespaces is the old stuff and getting phased out.
everything should be locally manged now.

but locally managed tablespace is for extents and we already have that setting.

i was asking about "Segment Space management" for segments. it is different feature.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now