Re: Drop file from SYSTEM tablespace

> >To drop this file from SYSTEM tablespace I must
> >turn SYSTEM tablespace OFFLINE, but this tablespace
> >must always be ONLINE.
>
> >How to do this operation?
> >How to ensure that this file doesn't contain any important
> >information and file dropping will not impact to the database?
>

I don't know if you still need any assistance, but just in case....

I believe that you cannot simply drop ans SYSTEM datafiles without recreating the database, but you can move them without too much effort.

Firstly: "alter database backup controlfile to trace;"

-o- now locate the control file. It contains the commands needed to recreate the control files should they get lost/destroyed etc. It also allows you to edit what they will contain when they are recreated and therefore change the location of the datafiles. So now edit this file and change the location of the SYSTEM datafile(s).

-o- shutdown the database

-o- copy / move the SYSTEM datafile(s) to the new location.

-o- follow the instructions in the trace file from the earier stage. I think that you can just execute the file with the database open nomount, but if you read the trace file it is very clear.

-o- I cant remember if this procedure leaves the open, but if not, alter database open.

-o- now check that you SYSTEM datafiles are where you expect them; "select * from dba_data_files where tablespace_name='SYSTEM';"

That's it for moving the files, but to delete I "think" that you have to create a new database. The steps involved a little bit more complicated, but do-able, so if you need them post a reply.

I hope the info is helpful and no too late

Regards
Tony Adolph

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 10 1999 - 03:45:13 CDT