Error while creating temporary tablespace

Printable View

06-13-2002, 05:34 AM
SANJAY_G

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
06-13-2002, 05:47 AM
Orca777

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
06-13-2002, 05:58 AM
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
06-13-2002, 06:06 AM
ales

As I see it, you forgot to specify the size of the tempfile!
06-13-2002, 06:07 AM
kgb

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;
06-13-2002, 06:11 AM
Tarry

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
06-13-2002, 06:15 AM
SANJAY_G

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
06-13-2002, 06:18 AM
kgb

Parameters

autoextend on
next 20M

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

Quote:

Originally posted by SANJAY_G
Hi kgb, since I am using LMT with UNIFORM. autoextend and next is not required.
06-13-2002, 06:29 AM
SANJAY_G

You are right, kgb !

Thanks again
Sanjay
06-13-2002, 07:36 AM
Orca777

Quote:

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