DATAFILE vs TEMPFILE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: DATAFILE vs TEMPFILE

Hybrid View

  1. #1
    Join Date
    Jun 2002
    Posts
    5

    Question

    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;

  2. #2
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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

  3. #3
    Join Date
    Jun 2002
    Posts
    5
    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.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    [QUOTE]Originally posted by nagarjuna
    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]

  5. #5
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    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
    "Greatest Rewards come only with Greatest Commitments!"

  6. #6
    Join Date
    Jun 2002
    Posts
    73

    Smile

    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".
    You cannot succeed if you fear to face challenges.
    MAS
    BE(CS) , OCP 8/8i.



  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    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
    "Greatest Rewards come only with Greatest Commitments!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width