hah, please don't tell me what to talk about.
when I see horrible code, dangerous code, using the worst practices on the programming planet - I am *compelled* to comment on it. I cannot help it. It is just a fact.
and really, don't tell someone "just write the program how to realise it.", that is what we call presumptuous
besides, since you didn't follow directions:
http://www.flickr.com/photos/tkyte/4033155188/sizes/o/ (see #3), I find it hard to write code - I don't have your table...
Now, I already told you how I would do this, it would not involve a single move or rebuild, just shrink.
If you don't like my advice, feel free to do it yourself how you see fit.
but here you go anyway...
ops$tkyte%ORA11GR2> create table t
2 (
3 TABLE_NAME VARCHAR2(30 CHAR) ,
4 OWNER VARCHAR2(20 CHAR) ,
5 SIZE_THRESHOLD NUMBER ,
6 WASTE_THRESHOLD NUMBER ,
7 SGM_SPACE_MANAGEMENT VARCHAR2(6 CHAR) ,
8 CURRENT_SIZE NUMBER ,
9 CURRENT_WASTE NUMBER
10 );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table test as select * from all_objects;
Table created.
ops$tkyte%ORA11GR2> create index test_idx1 on test(object_name);
Index created.
ops$tkyte%ORA11GR2> create index test_idx2 on test(object_id);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> delete from test where mod(object_id,2) = 0;
36513 rows deleted.
ops$tkyte%ORA11GR2> insert into t values ( 'TEST', user, 0, 0, 'AUTO', 0, 0 );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure shrink_all
2 as
3 l_sql varchar2(4000);
4 l_sql2 varchar2(4000);
5 row_movement exception;
6 pragma exception_init( row_movement, -10636 );
7 begin
8 for x in (select table_name, owner
9 from t
10 where sgm_space_management = 'AUTO')
11 loop
12 l_sql := 'alter table "' || x.owner || '"."' || x.table_name || '" shrink space';
13 dbms_output.put_line( l_sql );
14
15 begin
16 execute immediate l_sql;
17 exception
18 when row_movement
19 then
20 dbms_output.put_line( 'failed due to row movement...' );
21 l_sql2 := 'alter table "' || x.owner || '"."' || x.table_name || '" enable row movement';
22 dbms_output.put_line( l_sql2 );
23 execute immediate l_sql2;
24 dbms_output.put_line( l_sql );
25 execute immediate l_sql;
26 end;
27
28
29 for y in (select owner, index_name
30 from dba_indexes
31 where table_owner = x.owner
32 and table_name = x.table_name )
33 loop
34 l_sql := 'alter index "' || y.owner || '"."' || y.index_name || '" shrink space';
35 dbms_output.put_line( l_sql );
36 execute immediate l_sql;
37 end loop;
38 end loop;
39 end;
40 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select segment_name, segment_type, blocks
2 from user_segments
3 where segment_name in ( 'TEST', 'TEST_IDX1', 'TEST_IDX2' );
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------------ ------------------ ----------
TEST TABLE 1152
TEST_IDX1 INDEX 384
TEST_IDX2 INDEX 256
ops$tkyte%ORA11GR2> exec shrink_all
alter table "OPS$TKYTE"."TEST" shrink space
failed due to row movement...
alter table "OPS$TKYTE"."TEST" enable row movement
alter table "OPS$TKYTE"."TEST" shrink space
alter index "OPS$TKYTE"."TEST_IDX1" shrink space
alter index "OPS$TKYTE"."TEST_IDX2" shrink space
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select segment_name, segment_type, blocks
2 from user_segments
3 where segment_name in ( 'TEST', 'TEST_IDX1', 'TEST_IDX2' );
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------------ ------------------ ----------
TEST TABLE 544
TEST_IDX1 INDEX 200
TEST_IDX2 INDEX 96
ops$tkyte%ORA11GR2>