How can I merge two system tablespace datafile? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: How can I merge two system tablespace datafile?

  1. #11
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Just thinking in theory, couldn't you
    1. create a tablespace (SYSTEM_NEW) with the correct sized datafiles
    2. move all objects in SYSTEM to to SYSTEM_NEW
    3. drop SYSTEM tablespace
    4. recreate SYSTEM tablespace
    5. Move objects from SYSTEM_NEW to SYSTEM
    6. Drop SYSTEM_NEW

    ??

    (Of course, the database should probably be in restricted mode)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hm during this moving how can data dictionary be updated? For example all sys object would be in another tablespace therefore the data dictionary has to be updated, just one example

    may be someone can try it

  3. #13
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Pando has a good point. This could be really tricky.
    What happens when you are moving the objects that MAKE the data dictionary...do the objects move first and then update themselves for the new pointer (new tablespace) or the pointer is modifed first and then an attempt is made to move the objects.
    I think the easiest way would be to recreate the database but it really would be a fun excercise to see what happens to what Jeff suggested.
    I am a poor chap; don't have the luxury of many test workstations to attempt this...but I would be watching this thread if somebody could try this.

    - Rajeev
    Rajeev Suri

  4. #14
    Join Date
    Nov 2000
    Posts
    212
    system tablespace and datafiles are created when 'create database ...'
    So you can not take system tbs offline, drop, etc, as oracle will reference dictionary.

    What you can try to do is to hack database: if that new datafile is not used yet, then may be it is possible to modify data dictionary to get rid of it. But that datafile data can be denormalized in several tables, so you are never sure...

    You have to remove this datafile from controlfile too. May be while databse is mounted, it is possible to use
    alter database datafile '....' offline or drop

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