Error while creating temporary tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Error while creating temporary tablespace

  1. #1
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Hi,

    I am getting this error while creating temporary tablespace.
    Code:
    SQL> CREATE TEMPORARY TABLESPACE TEST_TEMP1 TEMPFILE 'E:\ORADATA\ORAM\TMP_DATA.DBF'
      2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
      3  /
    CREATE TEMPORARY TABLESPACE TEST_TEMP1 TEMPFILE 'E:\ORADATA\ORAM\TMP_DATA.DBF'
    *
    ERROR at line 1:
    ORA-01119: error in creating database file 'E:\ORADATA\ORAM\TMP_DATA.DBF'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.
    The path specified for the tempfile is correct. What could be the reason?

    Sanjay

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    The TempFile-Clause works together with the Parameter DB_CREATE_FILE_DEST ( Oracle-managed Files ). If so, you need not use the path but the filename only;

    This should work :

    CREATE TEMPORARY TABLESPACE TEST_TEMP1 TEMPFILE 'TMP_DATA.DBF'
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

    Orca

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Hi orca777

    Does this parameter exist in 8i ? I am unable to find any reference for this parameter in 8i documentetion.

    BTW, I tried without the path and the error still exists. I queried v$parameter for db_create_file_dest but it isn't there.

    Sanjay

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    As I see it, you forgot to specify the size of the tempfile!

  5. #5
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    When you create any tablespace, try ALWAYS to specify file SIZE.

    CREATE TEMPORARY TABLESPACE TEMP
    TEMPFILE 'E:\ORADATA\ORAM\TMP_DATA.DBF' SIZE 200M
    autoextend on
    next 20M
    maxsize 1024M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    Best wishes!
    Dmitri

  6. #6
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    was about to say that(what kgb just said)
    Code:
    SQL> CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE 'd:\orant\oradata\nick\lmtemp01.dbf'
      2  SIZE 10M
      3  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    
    Tablespace created.
    Cheers!!

    Tarry
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Thanks ales.
    Code:
    SQL> CREATE TEMPORARY TABLESPACE TEST_TEMP1 TEMPFILE 'e:\oradata\oram\TMP_DATA.DBF' size 10m
      2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    
    Tablespace created.
    Hi kgb, since I am using LMT with UNIFORM. autoextend and next is not required.

    Thanks all.

    Sanjay


  8. #8
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Parameters

    autoextend on
    next 20M

    related only to DATAFILE they are not effect on TABLESPACE STORAGE parameters.

    Originally posted by SANJAY_G
    Hi kgb, since I am using LMT with UNIFORM. autoextend and next is not required.
    Best wishes!
    Dmitri

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You are right, kgb !

    Thanks again
    Sanjay

  10. #10
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Originally posted by SANJAY_G
    Hi orca777

    Does this parameter exist in 8i ? I am unable to find any reference for this parameter in 8i documentetion.

    BTW, I tried without the path and the error still exists. I queried v$parameter for db_create_file_dest but it isn't there.

    Sanjay
    Sorry this was 9i-Syntax ( OK without size it would not work either )
    Orca

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