Solved

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

Posted on 2013-06-14
6
793 Views
Last Modified: 2013-06-17
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?
0
Comment
Question by:alcsoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39248074
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
 

Author Comment

by:alcsoft
ID: 39248333
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39248346
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:alcsoft
ID: 39253760
'/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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39253958
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
 

Author Comment

by:alcsoft
ID: 39254065
Yes,

Thank you I figured out why it has an issue.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Fine Tune your automatic Updates for Ubuntu / Debian
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question