Re: Undo ALTER TABLESPACE ADD DATAFILE

In article sul_at_panix.com, rryan_at_panix.com (Rob Ryan) writes:
> On Oracle7 on a Sun, we've just added a datafile to a tablespace with the
> ALTER TABLESPACE ADD DATAFILE command. We discovered that we put the file
> on the wrong file system and want to move it. But it doesn't appear to
> be that easy. I assume that I could detach that tablespace, move the
> datafile to where it belongs, and then define a symbolic link, but that's
> a little cumbersome.
>
> Clearly, we could also EXPort the data, drop the tablespace, recreate
> it as we wanted it to be, and then IMPort the data back in, but again,
> that's cumbersome.
>
> So, are we overlooking some obvious alternative? I could see that a ALTER
> TABLESPACE DELETE DATAFILE command might be difficult for Oracle to
> implement if there's data on it already, but this is a case where we are
> practically guaranteed that the datafile hasn't yet been used. So *surely*
> there's some easy way to undo a mistyped ALTER TABLESPACE ADD DATAFILE
> command.
>
> Any help would be greatly appreciated. Thanks.
>
> -- Rob
>

You should be able to fix this fairly easily. Take the tablespace offline, move the file where you want it be (at the OS level), issue the 'alter tablespace rename datafile' command, and then bring the tablespace back online.

Beverly Zygmunt
Oak Ridge National Lab Received on Thu Nov 18 1993 - 13:36:59 CET