I have some indexes that have very high initial extents and little data. Is it possible at all to shrink them (I want to give a smaller initial extent size) online/offline? Or the only way is to drop and recreate? I tried the "alter index deallocate unused" without success. Thanks.

tom,

i have job that runs for every 10 minutes that does:

delete from t;
insert into t from select * from t@remotedb;

i think it will be a problem with the high water mark.

there are other reports accessing this table parelly, so i can't issue truncate the table before inserting.

how can i handle this?

Dear Tom

So many time i heard about high water mark. But I saw some documenation. But no clear. Actually what is that hiwater mark, why it is calling like that, and how to find it, with this any performace issuses will arise.

Thanks

Reddy

Hi Tom,

In your reply above you have said there are three ways to reduce HWM. Any other additions to the list in 9i.

Regards,
B

declare
i number;
begin
i:=0;
loop
insert into t1(name) values('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
commit;
delete from t1;
commit;
i:=i+1;
exit when i=9999999999;
end loop;
commit;
end;
/

Is there any probability that above procedure increase the HWM for t1 beyond control.

In your reply above you have said there are three ways to reduce HWM. Any other additions to the list in 10g or 11g?

Thanks!

Thanks Tom,

What I want to say here is that
If we continuously insert records by using a procedure P1 and another procedure P2 simultaneously process and deletes same records from same table T1.
Both procedure runs for long time say 1 month or 1 year. And during this period maximum records in table(T1) stays always less than 200 (For example suppose
200 was the maximum records in table T1 during this period)
So according to theory of HWM what I read and understand; HWM should be set according to 200 records and not more than that am I right ?
Or it can go higher than that ?

One more thing
When I execute following mentioned PL/SQL procedure It took whole 1 day to finish.

declare
i number;
begin
i:=0;
loop
insert into T1(Name) values('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||i);
commit;
delete from T1;
commit;
i:=i+1;
exit when i=9999999999;
end loop;
commit;
end;
/

Every time it inserts a different value in T1 and also the 'NAME' column of table T1 was primary key (So using Unique Index)
For first couple of hours after analyzing the table DB shows Total 3 BLOCKS uses by the table T1.
But After 8-9 hours analyzing the table it starts showing 13 BLOCK is being used by the table T1.

What can be the reason ?

Is insert statement not re-using the Blocks freed by delete statement and causing HWM to go up ?