***8i Temporary Tablespace -Tempfile * Locally Managed

From: Arun J Muthalaly <arunjacob_at_0racledba.com>
Date: Fri, 3 Nov 2000 10:27:28 -0800 (PST)
Message-Id: <10669.121101@fatcity.com>

Hi,

I cteated a Temporary Tablespace in 8.1.6.2 with the followinf statement.

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'/data/YIPEEIA/u08/p03_ss02_d06_2g_21' SIZE 2000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M and subsequently added more datafiles

ALTER TABLESPACE TEMP1 add TEMPFILE
'/data/YIPEEIA/u08/p03_ss02_d06_2g_25' SIZE 2000M
'/data/YIPEEIA/u08/p03_ss02_d06_2g_26' SIZE 2000M
'/data/YIPEEIA/u08/p03_ss02_d06_2g_27' SIZE 2000M
'/data/YIPEEIA/u08/p03_ss02_d06_2g_28' SIZE 2000M
'/data/YIPEEIA/u08/p03_ss02_d06_2g_29' SIZE 2000M
'/data/YIPEEIA/u08/p03_ss02_d06_2g_10' SIZE 2000M
/

When i query the datadictionary tables i get

SQL> select FILE_NAME from dba_data_files where TABLESPACE_NAME = 'TEMP1'; no rows selected

and

select TABLESPACE_NAME,EXTENT_MANAGEMENT,STATUS,CONTENTS from dba_tablespaces;

SAM_T_SPLY_TYPE_T        LOCAL      ONLINE    PERMANENT
SAM_T_SPLY_TYPE_I        LOCAL      ONLINE    PERMANENT
TEMP1                    LOCAL      ONLINE    TEMPORARY

which i think is as per the Docs

But the Strange part is that the datafiles doesnt consume any space in the filesystem unless i do some activity (Index builds) in the TEMP1 tablespace.

This Can become a problem if at a time of Temporary tablespace inactivity , other DBA's may try to add datafiles to other Tablespaces since there is Free (virtual) space available.

Please advise....

Cheers

Arun Jacob M       

---

Be someone_at_0racledba.com - get your free mail from Oriole Corporation  http://www.oriolecorp.com - Performance tools for Oracle DBAs

Get email for your site ---> http://www.everyone.net Received on Fri Nov 03 2000 - 12:27:28 CST