How can I merge two system tablespace datafile?

[cookye]

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?

[sreddy]

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.

[cookye]

thanks

[Chris]

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.

[sreddy]

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.

[sreddy]

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.

[sreddy]

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.

[jmodic]

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

[gpsingh]

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

[sreddy]

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.