Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: moving temp datafile

Re: moving temp datafile

From: Eugene Firyago <efiryago_at_bisys.com>
Date: Mon, 24 Jan 2000 15:09:00 -0500
Message-ID: <86ibi5$l98$1@bob.news.rcn.net> 





Jeremiah,



I said, that I had tested it on SPARC Solaris7 with Oracle8.1.5.  It works
perfectly.


Here is the evidence from my REAL environment:



SVRMGR> alter tablespace users add datafile '/u02/oracle/db/A815/users_02.f'
size 5m;


Statement processed.


SVRMGR> alter tablespace users add datafile '/u02/oracle/db/A815/users_03.f'
size 5m;


Statement processed.


SVRMGR> alter database datafile '/u02/oracle/db/A815/users_02.f' offline;
Statement processed.


SVRMGR>



SVRMGR> select name,status from v$datafile;
NAME



STATUS







So even though the /u02/oracle/db/A815/users_02.f datafile had been taken
offline and gotten RECOVER status (we don't care) the USERS tablespace is
still ONLINE and functioning (same things for the TEMP I had experimented
with before this).



Moreover:



SVRMGR> create user scott identified by tiger default tablespace users


     2> temporary tablespace temp;


Statement processed.


SVRMGR> grant connect,resource to scott;
Statement processed.


SVRMGR> revoke unlimited tablespace from scott;
Statement processed.


SVRMGR> alter user scott qouta unlimited on users;
Statement processed.


SVRMGR> connect scott/tiger


Connected.


SVRMGR> @/u01/oracle/product/8.1.5/sqlplus/demo/demobld.sql

Termout                         ON




prompt Building demonstration tables.  Please wait.
*


ORA-00900: invalid SQL statement


Statement processed.


DROP TABLE EMP



           *


ORA-00942: table or view does not exist
DROP TABLE DEPT



           *


ORA-00942: table or view does not exist
DROP TABLE BONUS



           *


ORA-00942: table or view does not exist
DROP TABLE SALGRADE



           *


ORA-00942: table or view does not exist
DROP TABLE DUMMY



           *


ORA-00942: table or view does not exist
Statement processed.

1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.




Statement processed.

1 row processed.
1 row processed.
1 row processed.
1 row processed.




Statement processed.


Statement processed.

1 row processed.
1 row processed.
1 row processed.
1 row processed.
1 row processed.




Statement processed.


1 row processed.


Statement processed.


Server Manager complete.

$
$
$ sqlplus scott/tiger





SQL*Plus: Release 8.1.5.0.0 - Production on Mon Jan 24 13:56:38 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>



SQL> select * from emp;



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------


    DEPTNO




      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM


---------- ---------- --------- ---------- --------- ---------- ----------


    DEPTNO




      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM


---------- ---------- --------- ---------- --------- ---------- ----------


    DEPTNO




      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM


---------- ---------- --------- ---------- --------- ---------- ----------


    DEPTNO




      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 12-JAN-83       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM


---------- ---------- --------- ---------- --------- ---------- ----------


    DEPTNO




      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10





14 rows selected.



So everithing went fine and all the tablespace are still usable!
This because Oracle just assigned the RECOVER status to datafiles which have
been taken offline.


And since we are not gonna really recover those datafiles and take them
online in the future they could be physically deleted so their RECOVER
status never hurts the database.



The only issue related to that approach that it must be tested for the
specific working environment before use.



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:09:00 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US