How to create a locally management temp table space with multiple temp files?

I want to create a locally managed temporary table space with multiple temp files, the database is Oracle 8i. I searched online for a statement, and I was able to find one which is
create temporary tablespace "TEMP3"
tempfile '/ordata/DB_NAME/data/temp_01.dbf' size 100M  autoextend on  next 10M  maxsize 500M
extent management local
uniform size 64K

How I can modify it to allow two or more temp files instead of one?
alcsoftAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
simply list each file separated by commas

CREATE TEMPORARY TABLESPACE temp3 TEMPFILE
    '/oracle/DB_NAME/data/temp_01.dbf' SIZE 100M  AUTOEXTEND ON  NEXT 10M  MAXSIZE 500M,
   '/oracle/DB_NAME/data/temp_02.dbf' SIZE 100M  AUTOEXTEND ON  NEXT 10M  MAXSIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;
0
 
alcsoftAuthor Commented:
I am getting this error

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE
*
ERROR at line 1:
ORA-01119: error in creating database file '/ordata/DB_NAME /data/temp2_01.dbf'
ORA-27038: skgfrcre: file exists

I dont want to recreate the database.
0
 
sdstuberCommented:
the error explains the problem, albeit a little cryptically

the file already exists

you'll need to either delete that file (make sure it really is a temp file)
or pick a new file name/location
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
alcsoftAuthor Commented:
'/ordata/DB_NAME /data/temp2_01.dbf', is not there!!

I changed even the file name and it still giveing me the same error.

skgfrcre: file exists

I think the issue is with the control file skgfrcre!!
0
 
slightwv (䄆 Netminder) Commented:
I just noticed there is a space after DB_NAME and before the '/'.  That can be the issue.

If not:

Does the PATH exist and does oracle have read/write access?

Post the result from the following OS command:

ls -al /ordata/DB_NAME/data


I have to ask:  Are you replacing DB_NAME with the actual database name?
0
 
alcsoftAuthor Commented:
Yes,

Thank you I figured out why it has an issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.