maliksl4141
asked on
tablespace error
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
*
ERROR at line 1:
ORA-01630: max # extents (100) reached in temp segment in tablespace TEMP
ORA-06512: at "NMSPROD.ARCHIVELDCOUTPUT"
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
Nothing changes in the dba_tablespaces view, only in dba_data_files view where the new data_file is now available
ASKER
after add 100M of datafile, i still get the error..
do i really need to specify the extents
do i really need to specify the extents
As mentioned you have to increase the MAXEXTENTS parameter of the tablespace TEMP:
ALTER TABLESPACE temp
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MAXEXTENTS 999 );
ALTER TABLESPACE temp
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MAXEXTENTS 999 );
Yes you have to increase the maxextents from 100 to 999
ASKER
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.
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.
ASKER
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??
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??
ASKER
my db_files parameter is = 40
and my dbf file is 43.
is this parameter has something to do
with my problem currently.
thanks.
and my dbf file is 43.
is this parameter has something to do
with my problem currently.
thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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);
alter tablespace temp default storage (maxextents 1000);
alter tablespace temp default storage (maxextents unlimited);
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
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
ASKER
i have reached max number of datafiles in my
db.
oracle suggested that i recreate my database
with bigger db_files
db.
oracle suggested that i recreate my database
with bigger db_files
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.