Re: TEMP Tablespace problem

I have Oracle 9.2i
I already run the query and the tablespace TEMP have CONTENTS=TEMPORARY AND EXTENT_MAN=LOCAL Can I run this line to fix the size of my tablespace?

SQL> alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M;

I mean is the same or I have to change something?

>>> mgogala_at_adelphia.net 09/30/03 10:04PM >>>
Teresita, spelling the word "administrator" with the capital letter is a good thing. Furthermore, if you have version 9i, you can do the following, little known, thing:

SQL> alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M;

Database altered.

If it's Oracle 8i, you can actually have a permanent, dictionary based tablespace acting as a temporary tablespace. In version 9, however, you can't. Here is the query to find out what do you have:   1 select tablespace_name,contents,extent_management   2 from dba_tablespaces
  3* order by contents
SQL> /

TABLESPACE_NAME                CONTENTS  EXTENT_MAN
------------------------------ --------- ----------
SYSTEM                         PERMANENT LOCAL
DRSYS                          PERMANENT LOCAL
EXAMPLE                        PERMANENT LOCAL
INDX                           PERMANENT LOCAL
USERS                          PERMANENT LOCAL
XDB                            PERMANENT LOCAL
TOOLS                          PERMANENT LOCAL
TEMP                           TEMPORARY LOCAL
UNDOTBS1                       UNDO      LOCAL

9 rows selected.

SQL> If the column contents reads "TEMPORARY" for the given tablespace, you can drop it and recreate it without any harm. Note that in 8i you don't have "UNDO" tablespaces and your system tablespace cannot be LMT.

On 2003.09.30 22:24, Teresita Castro wrote:
> Hi!!
> I am trying to change the size of my tablespace TEMP, I am not an
> Administrator but we really need to make this tablespace smaller.
> Already the size is 13214 Mgs, and this tablaspace is on a disk that is
> full,
> so if we can not make it smaller we are going to be in a serius trouble
> ( our
> Administrator is not here until Monday).
>

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Teresita Castro
  INET: Teresita.Castro_at_s-martmx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).