Re: Error when renaming tempfile

Sorry I was a little bit off the mark with my initial statment. It's not because it's a temporary file that you can't rename it, rather it's because it's both locally managed and a temporary file! Sorry to have muddied the waters like that. With regards to Oracle documentation look at the last paragraph of the third chapter, named "Tablespace and Datafiles", in the Oracle 8i Concepts manual. Which for your convenience I have pasted below.

Temporary Datafiles
Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that:

Tempfiles are always set to NOLOGGING mode.

You cannot make a tempfile read-only.

You cannot rename a tempfile.

You cannot create a tempfile with the ALTER DATABASE statement.

Media recovery does not recognize tempfiles:

BACKUP CONTROLFILE does not generate any information for tempfiles.

CREATE CONTROLFILE cannot specify any information about tempfiles.

Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.

Kind Regards,

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"BEDick" <bdick_at_cox.net> wrote in message news:YRMj8.14589$E_3.1305677_at_news2.east.cox.net...
> "Fraser McCallum" <fmcc_at_removetoemail_odbaguru.com> wrote in message
> news:Jgwj8.10101$uR5.18716_at_newsfeeds.bigpond.com...
> >
> > I'm assuming you have used the 8i temporary files, i.e. this file shows
up
> > when you query DBA_TEMP_FILES. You can't rename locally managed
temporary
> > files, just as you can't recover them. However this is not a problem as
> > being a temporary file means that no data is stored in them. So for your
> > move just drop and recreate the tablespace.
>
> The following is from the 8.1.6 ALTER DATABASE docs. Given you statement,
I
> find the mention of tempfiles confusing
>
> ---
> RENAME FILE
>
> renames datafiles, tempfiles, or redo log file members. This clause
renames
> only files in the control file. It does not actually rename them on your
> operating system. You must specify each filename using the conventions for
> filenames on your operating system before specifying this clause.
> ---
>
> Is it documented anywhere that tempfiles cannot be renamed?
>
> BTW, my code for handling tempfiles does an ALTER TABLESPACE ADD TEMPFILE
> ... and an ALTER DATABASE TEMPFILE ... DROP. Total lack of symmetry, but
it
> works.
>
> Thanks for assuring my solution. I feel a bit less stupid, now.
>
> --
> Later,
> BEDick
>
>
>
Received on Wed Mar 13 2002 - 18:35:24 CST