Re: moving temp datafile

Eugene,

This procedure should not work. If you offline drop datafiles that are part of an existing tablespace, it will make that tablespace unusable.

If you need continuous 7x24 operation, just create a second temporary tablespace called TEMP2 or something, alter all the users to use that as their temporary tablespace, and drop the old temporary tablespace once nobody is using it.

That's basically what John suggested, and I can't see any reason to try offline dropping datafiles instead.

--
Jeremiah

Eugene Firyago wrote:
>
> 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.
>
> 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 - 12:47:06 CST