asked on

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?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alcsoft

ASKER

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.

Sean Stuber

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

alcsoft

ASKER

'/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!!

slightwv (䄆 Netminder)

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?

alcsoft

ASKER

Yes,

Thank you I figured out why it has an issue.