DATAFILE vs TEMPFILE

My shop has the same problem as most of the posts I've read regarding TEMP tablespace filling up. So, I am investigating. I noticed that my TEMP tablespace is allocated as a DATAFILE, not a TEMPFILE. This confuses me....was this created incorrectly? Also, I believe my extents are not coded correctly for our environment, please advise. Thank you for your time.

SORT_AREA_SIZE 2927641
DB_BLOCK_SIZE 8192

CREATE TABLESPACE "TEMP" DATAFILE '/data/SRMDEVA/temp1.dbf' SIZE 104857600 REUS
E DEFAULT STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 1000 PCTINCREASE 0) ONLINE TEMPORARY;

Quote:

---

Originally posted by MDSDBA
My shop has the same problem as most of the posts I've read regarding TEMP tablespace filling up. So, I am investigating. I noticed that my TEMP tablespace is allocated as a DATAFILE, not a TEMPFILE. This confuses me....was this created incorrectly? Also, I believe my extents are not coded correctly for our environment, please advise. Thank you for your time.

SORT_AREA_SIZE 2927641
DB_BLOCK_SIZE 8192

CREATE TABLESPACE "TEMP" DATAFILE '/data/SRMDEVA/temp1.dbf' SIZE 104857600 REUS
E DEFAULT STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 1000 PCTINCREASE 0) ONLINE TEMPORARY;

---

Your syntex is wrong I guess. you can not use the reuse option when you specify the size for datafile.

coming to the qn...
What ever you are doing is right. You are creating a disctionary managed temporary tablespace. Tempfile is used only for locally managed temporary tablespace. The systex for the same will look like..

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/data/SRMDEVA/temp1.dbf' SIZE 104857600 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288;

You can use 1m extents for your environment.

-nagarjuna

Thank you for responding. I read that the initial & next extent should be sized 1/20 to 1/50 of the size of the tablespace and that you chould use PCTINCREASE of 0 so that the segments will be identically sized.

[QUOTE]Originally posted by nagarjuna

Quote:

---

Your syntex is wrong I guess. you can not use the reuse option when you specify the size for datafile.

coming to the qn...
What ever you are doing is right. You are creating a disctionary managed temporary tablespace. Tempfile is used only for locally managed temporary tablespace. The systex for the same will look like..

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/data/SRMDEVA/temp1.dbf' SIZE 104857600 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288;

You can use 1m extents for your environment.

-nagarjuna

---

1) It was correct syntax, u can write reuse parameter with size and
with/without change of size of file.

SQL> create tablespace abc datafile '/opt/db/oradata/lab/abc.dbf' size 1M reuse temporary;

Tablespace created.

SQL> alter tablespace abc_1 offline;

Tablespace altered.

SQL> drop tablespace abc_1 ;

Tablespace dropped.

SQL> create tablespace abc_2 datafile '/opt/db/oradata/lab/abc.dbf' size 2M reuse temporary;

Tablespace created.

SQL> alter tablespace abc_1 offline;

Tablespace altered.

SQL> drop tablespace abc_1 ;

Tablespace dropped.

SQL> create tablespace abc_2 datafile '/opt/db/oradata/lab/abc.dbf' size 2M reuse temporary;

Tablespace created.

=================================================
about type of temporary tablespaces in 8i/9i:

8i+DMT (default type of ts managment):
file type --> datafile, syntax like :
CREATE TABLESPACE "TEMP" DATAFILE '/data/SRMDEVA/temp1.dbf' SIZE 104857600 REUS
E DEFAULT STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 1000 PCTINCREASE 0) ONLINE TEMPORARY;

8i+LMT
filetype --> tempfile, syntax:
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/data/SRMDEVA/temp1.dbf' SIZE 104857600 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288;

in 9i was changing default type of ts managment:
from DMT to LMT and add db object "system" temporary tablespace
(when u execute CREATE DATABASE command )




[Edited by Shestakov on 09-03-2002 at 04:07 PM]

Thanks Shestakov and Nagarjuna for useful information...
One more thing when we a temporary tablespace using tempfile clause (as Nagaajuna and Shestakov has written) we can't see the file while querying V$datafile. this file be seen only if you query V$tempfile. With this you can identify if your file is datafile or tempfile.

Sandy

Quote:

---

Originally posted by sandycrab
Thanks Shestakov and Nagarjuna for useful information...
One more thing when we a temporary tablespace using tempfile clause (as Nagaajuna and Shestakov has written) we can't see the file while querying V$datafile. this file be seen only if you query V$tempfile. With this you can identify if your file is datafile or tempfile.

Sandy

---

You hit the nail right sandy...the "tempfiles" are realted to Locally managed tablespace while "datafiles" are related to Dictionary managed tablespace. There are seperate views for tempfiles. Just remember that there are all views that belong to datafiles, u just remove "data" with "temp" from all views which contain "data".

Quote:

---

Originally posted by asharsidd
You hit the nail right sandy...the "tempfiles" are realted to Locally managed tablespace while "datafiles" are related to Dictionary managed tablespace. There are seperate views for tempfiles. Just remember that there are all views that belong to datafiles, u just remove "data" with "temp" from all views which contain "data".

---

No, this is all wrong. "Tempfiles" are not related to LMT and "datafiles" are not related to dictionary managed tablespaces. "Tempfiles" are related to tempfiles, "datafiles" are related to datafiles, that's all. The only relation between file types and tablespace types is that tempfiles can only belong to LMT. But on the other hand datafiles can belong to either LMT or DMT.

And there are exacttly two (2) views that are related to tempfiles: V$TEMPFILE and DBA_TEMP_FILES.

[Edited by jmodic on 09-04-2002 at 04:02 AM]

you are abs right Jurij Modic...
two views for each type of file...
1. v$datafile
dba_data_files
2. v$tempfile
dba_temp_files

Sandy