Re: Junk Datafile

Hi There,

Not sure what version of Oracle you're running and I'm not sure whether the naughty temp file is on it's own or one of a number of tempfiles in the tablespace.

If the temp file is on it's own then simply dropping the tablespace and re-creating is the way to go. With 9i you can DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES and Oracle will automatically remove the O/S file for you (else you manually remove the files)

If the temp file is but one and you want to keep the others within the temp tablespace then ALTER DATABASE TEMPFILE DROP 'tempfile' INCLUDING CONTENTS is the way to go (with 9i) or forget the including contents portion and manually remove (8i)

With regard to the standby database, if 8i then (I think) you will need to manually recreate the controlfile or use the alter database command on the standby database. If 9i, if the STANDBY_FILE_MANAGEMENT parameter is set to AUTO on the standby database, these changes will automatically be applied.

Hope this helps

Cheers

Richard

(PS. Go Spain and England in the World Cup. Pity about Australia though ...) "markag" <member_at_dbforums.com> wrote in message news:3cf71522$3_at_usenetgateway.com...
> I have a datafile in a tablespace TEMP marked as temporary that was
> placed incorrectly on a small drive. I now want to rid myself of this
> datafile from the database. I want to remove this datafile from oracle's
> use, then physically delete it from the OS.
>
> Can I do that?
>
> What are the steps?
>
> I think I need to
>
> 1) shutdown DB
> 2) Copy DataFile in OS
> 3) Tell Oracle to rename file: ALTER DATABASE RENAME FILE
> 'C:\TEMP01.DBF' to 'D:\TEMP01.DBF'
>
> This makes the file C:\TEMP01.DBF invisible to Oracle.
>
> Can I then safely delete it?
>
> Also, I ned the same to happen to by standby database.
> 4) shutdown DB
> 5) Copy DataFile in OS
> 6) startup nomount
> 7) mount standby database
> 8) Tell Oracle to rename file:
>
> ALTER DATABASE RENAME FILE 'C:\TEMP01.DBF' to 'D:\TEMP01.DBF'
>
> 9) recover standby database
>
> Help Really Appreciated!
>
>
>
> --
> Posted via dBforums
> http://dbforums.com
Received on Fri May 31 2002 - 04:14:20 CDT