Re: moving temp datafile

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 Sun Jan 23 2000 - 06:33:11 CST