Re: Delete datafile(s) - 9.2.0.4 - RHEL3

"Tom" <tomNOSPAM_at_teameazyriders.com> wrote in message news:1101123863.1079.0_at_ersa.uk.clara.net...
> Hi
>
> During a brain lockup moment this morning i have created 3 datafiles with
> the
> incorect PATH - One path represents a testing location and the other a
> live
> environment
>
> I have created datafiles to do with a testing instance within the PATH of
> the
> live environment -
>
> How can i delete these datafiles without dropping the tablespace on the
> testing
> instance - My basic problem is that
>
> ALTER TABLESPACE USERS ADD DATAFILE '/u02/oradata/SID/users06.dbf' SIZE
> 10M
> AUTOEXTEND ON NEXT 10M MAXSIZE 1024M;
>
> should have read
>
> ALTER TABLESPACE USERS ADD DATAFILE
> '/u02/oradata/SOMEOTHERSID/users06.dbf' SIZE
> 10M AUTOEXTEND ON NEXT 10M MAXSIZE 1024M;
>
> and i need to remove them.
>

Hi Tom

Sounds like you need to move and rename the datafile rather than remove the datafile (which you can't really do without dropping the tablespace).

Try taking the tablespace offline (alter tablespace users offline;), move the datafile to the correct location using appropriate O/S commands, rename the datafile on the database (alter tablespace users rename datafile '/u02/oradata/SID/users06.dbf' to
'/u02/oradata/SOMEOTHERSID/users06.dbf'; ) and finally put the tablespace back online (alter tablespace user online;).

Good luck

Richard Received on Mon Nov 22 2004 - 06:56:30 CST