There are a couple of ways to address your problems. First, you
could use automatic storage management for that tablespace. That
would allow Oracle to manage the space. You wouldn't have to deal
with it anymore. However, that does affect everything in the
tablespace. I should point out that this only applies to Oracle 9.x
and higher.
The second approach would be to resize the index to a more
appropriate extent size. In order to do that you need to know much
space the index is using:
Select sum(bytes)/1024 from dba_extents where object_name = 'xxx'
and object_type = 'INDEX';
You want to resize the index extents to fit into 5 to 10 extents.
Let's assume your index is currently using 10MB of space. You could
rebuild the index to use 2MB extents. That would fix your problem:
Alter index xxx rebuild storage (initial 2M next 2M) tablespace
xxxx;
Regards,
James E. Strange
Database Architect Wellpoint IT
Office (317) 287-5423 Fax (317) 287-5428
Home 317 253-6154 Cell (317) 402-1742
Oracle Certified Professional DBA
-----Original Message-----
From: luis
[mailto:oracledba-ezmlmshield-x89910070.[Email address protected]
Sent: Wednesday, July 06, 2005 7:08 AM
To: LazyDBA Discussion
Subject: MAX EXTENTS
Gurues,
I am getting MAX EXENTS reached once a week on a segment(INDEX), if
I
udnerstand correctly increasing the storage (MAXEXTENTS) may produce
fragmentation in the long run.
I am told I need to find a finer combination of storage values but
what I
do not know is number one how to recreate it and number to what do I
base
my judgement uppon.
Pointers advice and help will very much be appreciated.
Luis
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain confidential
and privileged information or otherwise protected by law. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not the
intended recipient, please contact the sender by reply e-mail and destroy
all copies of the original message.
Oracle LazyDBA home page