Posted on 2011-10-25
Last Modified: 2012-05-12
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.
Question by:sam15
    LVL 76

    Accepted Solution

    >>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.

    Author Comment

    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Author Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now