system tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: system tablespace

  1. #1
    Join Date
    Jul 2002
    Posts
    335

    system tablespace

    Ok, this is a situation you should never get into, but we were constrained by a 3rd party application.

    10gR2 RAC database on 2 linux nodes.

    We literally have over half a million objects, mostly small tables and views. As such our system tablespace is around 11Gb.

    We will shortly be implementing an applocation upgrade to create one large partitioned table and migrate the data from the small tables to the large one. After which the small tables get dropped.

    The problem will be is that we'll be stuck with huge system datafiles that potentially (probably) we will not be able to significantly reduce the size of.

    Without resorting to an export import of the whole 500Gb database, does anyone have any ideas to reduce the size of the system tablespace?

    Thanks for your time.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    move the objects 'alter table xxx move' so that they are at the start, unlucky if you packages are at the end of the datafiles

  3. #3
    Join Date
    Jul 2002
    Posts
    335
    Thanks, not even thought of that. It may help, fingers crossed!

  4. #4
    Join Date
    Jul 2002
    Posts
    335
    Ok, just had a brainwave (well actually I was nosing around on AskTom) that uses transportable tablespaces...

    - generate a transportable tablespace set ( set tablespaces to read only and perform a special export)
    - shutdown database instances on both nodes
    - move/rename the old system/sysaux/undo datafiles
    - Create a new empty database with just the bare minimum i.e. system/sysaux/undo etc
    - Import the tablespace set
    - Set tablespaces in read/write

    Might have to create small undo datafiles (due to mountpoint constraints) and resize them up after we drop the old ones, but at first glance I think it would work.

    Anyone spot any flaws?

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by bazza
    Ok, just had a brainwave (well actually I was nosing around on AskTom) that uses transportable tablespaces...

    - generate a transportable tablespace set ( set tablespaces to read only and perform a special export)
    - shutdown database instances on both nodes
    - move/rename the old system/sysaux/undo datafiles
    - Create a new empty database with just the bare minimum i.e. system/sysaux/undo etc
    - Import the tablespace set
    - Set tablespaces in read/write

    Might have to create small undo datafiles (due to mountpoint constraints) and resize them up after we drop the old ones, but at first glance I think it would work.

    Anyone spot any flaws?
    How does this help to move half million objects from system tablespace to another tablespace?

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    As far as I understand the objects are not into the system tablespace. They just are half milion and their metadata is about 11G and when the objects gets dropped, the pace will be released but the data dictionary tables will not release extents nor the system datafile will be resized.
    First, I do not see such big problem in 11G system tablespace, but if you want to reduce the size, I would support the idea of the transportable tablespaces.
    The idea is:
    1) Change the schema ( partitions instead of many objects)
    2)Create a new database at the same machine
    3) Generate a transportable tablespace set at the original database
    4) Attach it to the newly createtd database
    5) Test what have happen
    6) Drop the original database

    That's just and idea, I have not tested that, but seems the only aproach that could work

    Regards
    Boris

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Bore
    ...partitions instead of many objects...
    ...last time I checked it out "partitions" were "objects" too.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Jul 2002
    Posts
    335
    yes sorry should have made it crystal clear, the objects are all in data tablespaces and its purely the metadata information for all those objects that is making the system tablespace so large.

    Partitions are only part of a table of course but 60 odd partitions are better than 500000 objects.

    I think we're going to try the move within the system tablespace first (including the new 10g shrink segment commands) and if we can't reduce it significantly we'll see if we can live with it performance wise, if not we'll test the transportable tablespace idea.

    Thanks for your input peeps.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    When you move the system tables, you're aware that the indexes on them will then be unusable and that you might crash your database? If you move the segment table, which presumably itself has a lot of segments itself, and invalidate an index on it that then prevents it from being maintained then will your database even work?

    I'd counsel you to check with Oracle support on this one -- you might not be able to do this without breaking something.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width