Re: moving temp datafile

You could carefully try the following without bringing the database down assuming that your temporary tablespace TEMP consists of file_old1, file_old2,..., file_oldN files and in the new location it will be file_new1,file_new2,...,file_newM files for the TEMP:

1. Mke a full database backup (including controlfile backup) before you change anything.
2. Add of new files to the TEMP in new location:

alter tablespace TEMP add datafile 'file_new1' size ...; alter tablespace TEMP add datafile 'file_new2' size ...; ...
alter tablespace TEMP add datafile 'file_newM' size ...;

3. Take the files in old location offline:

alter database datafile 'file_old1' offline [DROP]; alter database datafile 'file_old2' offline [DROP]; ...
alter database datafile 'file_oldN' offline [DROP];

Use DROP option in case your database is running in NOARCHIVELOG mode.

Because after (2) Oracle stops checkpointing the offline datafiles file_old1, file_old2, file_oldN and they will never be really turned back online (so they don't need to be recovered) they can be removed from disks.

I have tested this trick in my Oracle 8.1.5 on SPARC Solaris7 environment. It works there.

Also I would like to hear from gurus any issues related to that approach.

Good luck,
Eugene.

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:948630952.3061.0.nnrp-12.9e984b29_at_news.demon.co.uk...
>
> If you mean TEMP as the tablespace to be
> used for sorting then you do not need to grant
> quotas on it.
>
> There are some issues with 'moving' the TEMP
> data base on a production system, especially
> if it is supposed to run 24 x 7.
>
> If you are trying to avoid having any moment in
> time when a user could require TEMP for sorting
> when TEMP does not exist, there is really only
> one option - have two temporary tablespaces.
>
> In fact, prior to 8.1 and 'create temporary tablespace'
> I always used to advise people to have two online
> temporary tablespaces if they wanted to avoid down-time
> due to losing and rebuilding temp.
>
> In your case you may take the strategy:
> create tablespace TEMP2 .... on new area.
>
> For each user
> alter user XXX temporary tablespace TEMP2;
> -- I assume this is what you actually meant by
> -- regranting the quota.
>
> Drop tablespace TEMP1
>
>
> If you do not consider downtime to be an issue, then
> pre-8.1 you can move the datafile just as you would
> any other.
>
> 8.1 and later -
> drop tablespace temp;
> create temporary tablespace temp .......
>
> A TEMPORARY tablespace /tempfile takes only a few
> seconds to build in 8.1, so you can afford to do it this way,
>
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Doug Cowles wrote in message <388950b5.23879061_at_news.remarq.com>...
> >Is there a way to move temp to another disk (datafile), without
> >re-creating it and regranting quota? i.e. on live database?
>
>
Received on Mon Jan 24 2000 - 11:31:41 CST