| Index Extents [message #57798] |
Tue, 08 July 2003 09:08  |
Regen
Messages: 3
Registered: February 2003
|
Junior Member |
|
|
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production on Windows NT 4.0
I am running this script:
SELECT owner,
segment_name,
segment_type,
tablespace_name,
max_extents,
extents
FROM dba_segments
WHERE owner = 'OPS$ALGOMA'
AND extents > =10;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MAX_EXTENT EXTENTS
------------------------------ -------------------------------------------------------------------------------- ----------------- ------------------------------ ---------- ----------
OPS$ALGOMA U_HEAT_CONTENTS INDEX ALGOMA 121 118
1 row selected.
As you can see segment_name U_HEAT_CONTENTS has max_extents of 121 and extents of 118.
When the extents reach the max_extents one of our application starts having major problems.
I am correcting this problem by:
ALTER TABLE HEAT_CONTENTS DROP PRIMARY KEY;
ALTER TABLE HEAT_CONTENTS ADD CONSTRAINT U_HEAT_CONTENTS
PRIMARY_KEY (
VERSION_NO,
HEAT_ID,
CASTER_ID,
STAND_ID,
SEQUENCE_NO) USING INDEX STORAGE (PCTINCREASE 0 INITIAL 110K NEXT 10K);
This will bring the extents down to 0 but in a day or two the extents reach 121
and we have problems again.
Any ideas on how to get this under control.?
|
|
|
|
| Re: Index Extents [message #57845 is a reply to message #57798] |
Thu, 10 July 2003 14:28  |
kochunni
Messages: 17
Registered: May 2003
|
Junior Member |
|
|
This is an issue with the lack of proper space management.
Give bigger INITIAL extent, Make the NEXT same as the INITIAL, make MAXEXTENTS something like 1024.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
