How can I merge two system tablespace datafile?

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?

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.

thanks

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.

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.

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.

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.

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

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

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.

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)

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 :D

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

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