Solved

tablespace error

Posted on 2002-05-30
12
1,354 Views
Last Modified: 2007-11-27
Hi...i got below error;

*
ERROR at line 1:
ORA-01630: max # extents (100) reached in temp segment in tablespace TEMP
ORA-06512: at "NMSPROD.ARCHIVELDCOUTPUT", line 120
ORA-06512: at line 1

 when i add datafile to the  tablespace, let say 100M,
 what is actually change in dba_tablespaces info;

 INITIAL_EXTENT
 NEXT_EXTENT
 PCT_INCREASE
 MAX_EXTENTS


 tq
 
 
0
Comment
Question by:maliksl4141
[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
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 7044191
When you add new files to a tablespace you increase the number of extents of the tablespaceq as pointed below. So you have to increase MAX_EXTENTS parameter of the TS.

To change this parameter look at the example:

ALTER TABLESPACE app_data
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MAXEXTENTS 999 );

Segments
A segment is the space allocated for a specific logical storage structure within a
tablespace. For example, all of the storage allocated to a table is a segment.
• A tablespace may consist of one or more segments.
• A segment cannot span tablespaces; however, a segment can span multiple data
files that belong to the same tablespace.
• Each segment is made up of one or more extents.
Extents
Space is allocated to a segment by extents.
• One or more extents make up a segment.
– When a segment is created, it consists of at least one extent.
– As the segment grows, extents are added to the segment.
– The DBA can manually add extents to a segment.
• An extent is a set of contiguous Oracle blocks.
• An extent may not span a data file, but must exist in one data file.
0
 

Expert Comment

by:lcorbett
ID: 7044262
Nothing changes in the dba_tablespaces view, only in dba_data_files view where the new data_file is now available
0
 

Author Comment

by:maliksl4141
ID: 7046462
after add 100M of datafile, i still get the error..

do i really need to specify the extents
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 48

Expert Comment

by:schwertner
ID: 7046633
As mentioned you have to increase the MAXEXTENTS parameter of the tablespace TEMP:

ALTER TABLESPACE temp
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MAXEXTENTS 999 );

0
 

Expert Comment

by:lcorbett
ID: 7046719
Yes you have to increase the maxextents from 100 to 999
0
 

Author Comment

by:maliksl4141
ID: 7053293
thanks...for the suggessted answer.
as a new DBA, i wait until tomorrow
as Oracle experts will come to help
me tune up my db.
i will give points and answer grade as soon as
i resolve the problem.
0
 

Author Comment

by:maliksl4141
ID: 7058804
i cannot increase the maxextents to 999
because my db_blok_size is = 2048
and oracle suggest to increase it to 121.

after increase the maxextents to 121,
the problem is still happened.

my db is Oracle 7.3.4.0.1

maybe i should increase the db_blok_size
now.
any help??
0
 

Author Comment

by:maliksl4141
ID: 7058856
my db_files parameter is = 40
and my dbf file is 43.

is this parameter has something to do
with my problem currently.

thanks.
0
 

Accepted Solution

by:
lcorbett earned 50 total points
ID: 7058982
The DB_files is the amount of database files you are allowed. (*.dbf)

Maybe you could increase the size of the Next_Extent to 4M+ to allow for 121 extents, but bigger.

Maybe the Sql Statment could be looked at as well, to see if it could be modified not to use so much temp space.
0
 
LVL 1

Expert Comment

by:kouke
ID: 7076705
If your oracle is 7.3.4 or newer you CAN set maxextents over 121 even to 'unlimited':

alter tablespace temp default storage (maxextents 1000);
alter tablespace temp default storage (maxextents unlimited);
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7078657
Greetings.

This question has been locked with a Proposed Answer, and remains open today.  Questions in the LOCKED versus OPEN question queue draw little to zero attention.

If the Proposed Answer did not serve your needs, please reject it and comment with an update.  If the Proposed Answer helped you, please accept it to grade and close this question.  If you need help splitting points between multiple experts, please comment here with details so we can help you.

EXPERTS ->  Please guide me here in terms of closing recommendations if the Asker does not respond in 4 days.

Thanks to all,
Moondancer - EE Moderator
0
 

Author Comment

by:maliksl4141
ID: 7250373
i have reached max number of datafiles in my
db.
oracle suggested that i recreate my database
with bigger db_files
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
ODBC in excel 2016 in Windows 10 via VBA 16 387
SQL2016 to ORACLE11G linked-server 6 76
subtr returning incorrect value 8 93
pl/sql parameter is null sometimes 2 37
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

739 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