| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: moving temp datafile
Oh... I forgot to put the following output:
$ sqlplus scott/tiger
SQL*Plus: Release 8.1.5.0.0 - Production on Mon Jan 24 15:12:40 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> select segment_name,tablespace_name from user_segments;
SEGMENT_NAME
Eugene.
Jeremiah Wilton <jeremiah_at_wolfenet.com> wrote in message
news:388C9E2A.977FF7F3_at_wolfenet.com...
> 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 - 14:16:53 CST
 |
 |