This discussion is archived
5 Replies Latest reply: Sep 22, 2013 6:36 PM by Hemant K Chitale RSS

Negatives to 11gR2 SYSTEM tbs being dictionary-managed?

raindog Newbie
Currently Being Moderated

Long story short, one of our DBAs screwed up when creating a database and made the SYSTEM tablespace dictionary-managed.  This is 11.2.0.3.

 

All the data tablespaces, SYSAUX, etc. are locally-managed, but the SYSTEM tablespace is dictionary-managed.

 

Someone posed the question: ideally, SYSTEM would be LMT, but what's the real-world negative?  It'd be a fair amount of work to get the downtime, do the testing, etc. to convert to LMT (via the procedure in the manual) so why not just leave it?  What is it going to harm?

 

I was trying to think of actual negatives.  It's a big DB (9TB) but all of that is in the data tablespaces.  I'm thinking the SYSTEM tablespace doesn't really get much space management - we are not adding and removing tons of users, there's some PL/SQL code and such but not huge amounts...and even if it was, it'd take a lot of PL/SQL to consume a significant amount of storage.

 

Curious if anyone has any opinions about things I might be overlooking?

 

Thanks.

  • 1. Re: Negatives to 11gR2 SYSTEM tbs being dictionary-managed?
    rp0428 Guru
    Currently Being Moderated

    raindog wrote:

     

    Long story short, one of our DBAs screwed up when creating a database and made the SYSTEM tablespace dictionary-managed.  This is 11.2.0.3.

     

    All the data tablespaces, SYSAUX, etc. are locally-managed, but the SYSTEM tablespace is dictionary-managed.

     

    Someone posed the question: ideally, SYSTEM would be LMT, but what's the real-world negative?  It'd be a fair amount of work to get the downtime, do the testing, etc. to convert to LMT (via the procedure in the manual) so why not just leave it?  What is it going to harm?

     

    I was trying to think of actual negatives.  It's a big DB (9TB) but all of that is in the data tablespaces.  I'm thinking the SYSTEM tablespace doesn't really get much space management - we are not adding and removing tons of users, there's some PL/SQL code and such but not huge amounts...and even if it was, it'd take a lot of PL/SQL to consume a significant amount of storage.

     

    Curious if anyone has any opinions about things I might be overlooking?

     

    Thanks.

    So migrate the SYSTEM tablespace from dictionary managed to LMT.

     

    See the DBA doc. It has a section that tells you EXACTLY how to do that.

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces012.htm

    Migrating the SYSTEM Tablespace to a Locally Managed Tablespace

    Use the DBMS_SPACE_ADMIN package to migrate the SYSTEM tablespace from dictionary-managed to locally managed. The following statement performs the migration:

    SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');  

    Before performing the migration the following conditions must be met:

    •   The database has a default temporary tablespace that is not SYSTEM.
    •   There are no rollback segments in the dictionary-managed tablespace.
    •   There is at least one online rollback segment in a locally managed tablespace, or if using automatic undo management, an undo tablespace is online.
    •   All tablespaces other than the tablespace containing the undo space (that is, the tablespace containing the rollback segment or the undo tablespace) are in read-only mode.
    •   The system is in restricted mode.
    •   There is a cold backup of the database.

    All of these conditions, except for the cold backup, are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure.

    That same doc has a section that tells you the benefits of using LMT

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#i1013496

    Locally Managed Tablespaces

    Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:

    •   Fast, concurrent space operations. Space allocations and deallocations modify locally managed resources (bitmaps stored in header files).
    •   Enhanced performance
    •   Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo.
    •   Space allocation is simplified, because when the AUTOALLOCATE clause is specified, the database automatically selects the appropriate extent size.
    •   User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
    •   Coalescing free extents is unnecessary for locally managed tablespaces.

    All tablespaces, including the SYSTEM tablespace, can be locally managed.

    The DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.

  • 2. Re: Negatives to 11gR2 SYSTEM tbs being dictionary-managed?
    raindog Newbie
    Currently Being Moderated

    Yes, I know EXACTLY how to do it...that is not the question I was asking.

  • 3. Re: Negatives to 11gR2 SYSTEM tbs being dictionary-managed?
    sb92075 Guru
    Currently Being Moderated

    > so why not just leave it?  What is it going to harm?

    For how long has this DB been in Production?

  • 4. Re: Negatives to 11gR2 SYSTEM tbs being dictionary-managed?
    rp0428 Guru
    Currently Being Moderated

    raindog wrote:

     

    Yes, I know EXACTLY how to do it...that is not the question I was asking.

    The entire second half of my reply answers the question you were asking. The benefits of LMT are as I posted from the doc.

     

    Oracle worked just fine for many, many years before they ever introduced LMT.

  • 5. Re: Negatives to 11gR2 SYSTEM tbs being dictionary-managed?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    > I'm thinking the SYSTEM tablespace doesn't really get much space management

    This means that you consider the SYSTEM tablespace to be generally static in size.  There won't be frequent allocation / deallocation of extents in the SYSTEM tablespace  (although other tablespaces for your [user data]  tables and indexes are likely to have such behaviour).

    As long as you follow the cardinal rule "Never create user objects in the SYSTEM tablespace" ....

    There is no overwhelming reason to convert SYSTEM to LMT.  It would be nice to do if you can get the downtime but doesn't seem mandatory.

     

     

    Hemant K Chitale

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points