How can I merge two system tablespace datafile?

[marist89]

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)

[pando]

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

[rsuri]

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

[LND]

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