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

Thread: How can I merge two system tablespace datafile?

  1. #1
    Join Date
    Sep 2000
    Posts
    14
    the database have three datafiles of system tablespace,
    /u01/..../system01.dbf (30MB,)
    /u02/..../system02.dbf (10MB,4.5MB used)
    /u02/..../system03.dbf (40MB)

    I want to drop datafile /u02/.../system02.dbf,
    how can I do?

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You CANNOT do that. The only way you can do is:

    Export all the objects of system tablespace and blow the tablespace and recreate the tablespace with one datafile. This is how you do for your database tablespaces. As it is SYSTEM tablespace there is inherent risk of this procedure might not work.

    Better leave it the way it is.
    Reddy,Sam

  3. #3
    Join Date
    Sep 2000
    Posts
    14
    thanks

  4. #4
    Join Date
    Jul 2000
    Posts
    37
    Sam,

    Is there ANY possibilty that this procedure would work at all?
    I don't see how it could. Has anyone ever done this?

    cheers

    chris.

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You can try if you have a test database. You can try on even production if you have solid recovery plan if something goes wrong.

    Other alternative is build the tablespace on a different server with the same OS and Patch level and Oracle version and plug it in with current production. I did once for data tablespace like this not for system tablespace.

    It scares me to give advise to play with system tablespace. What if when I attempt to do it on production. I can't think about it.
    Reddy,Sam

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Mind twister for me, as I never thought about this procedure for system tablespace.

    There is Enterprose Manager REORG utility, one can try what it does is move all the current objects into a temp tablespaces and then put back to the tablespace with the newly defined storage clause.
    Reddy,Sam

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Best and SAFE is Recreate the database by full export and import if its not very big. Blow the original one.

    I would advise you not to give a try with above procedures you use usually in handling data and index tablespaces.
    Reddy,Sam

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Recreating the whole database is the only option here. Usualy in system TS there are only SYS's objects, and you can not possibly export those. So the only option is to:

    1. do a full export (not because of the SYSTEM tablespace's objects, but because of all the other objects),
    2. drop the whole database,
    3. freshly create the new database with appropriate system's datafile
    4. perform a full import to restore non-system TS objects
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Feb 2001
    Posts
    389
    Even if it is possible by any manipulations etc, it is of no use, as Oracle does not support dropping of datafiles, if use offline drop command and remove files from OS, next time if u start oracle, it shall not .

    Take Care
    GP

  10. #10
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Dropping datafile is OUT OF CONTEXT here, as one shouldn't attempt to do it no matter what the situation. Anybody could tell me the reason why Oracle provided that stupid option for DBA's. One always tempted to that and endup in soup.

    Oracle says, drop datafile if you have the intention of dropping tablespace. CRAZY ! If I have the intention of dropping tablspace, I will drop tablespace and then remove files at OS level, why do I go and drop datafiles one by one and blow tablespace at the end... Doesn't make sense to have this option at all and the reason Oracle giving to provide that option...

    I have seen co-dba burning his hands by doing this once and we end up staying at work with no sleep for 36 hours.
    Reddy,Sam

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