Thanks for the question, hongyu.

Asked: November 06, 2012 - 9:05 pm UTC

Last updated: November 08, 2012 - 8:03 am UTC

Version: 10.2.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have a table named TB_MONITOR which store the table_name,owner ,and SGM_SPACE_MANAGEMENT for the tables which there are a lot of waste space, and needed to move .
I want to move table and rebuild the indexes one by one, not move together or rebuild index together, I have write a procedure name rebuild_table ,but there are some error ,pls help me to have a check.

here is the details:

SQL> desc TB_MONITOR
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(200 CHAR)
OWNER NOT NULL VARCHAR2(20 CHAR)
SIZE_THRESHOLD NUMBER
WASTE_THRESHOLD NUMBER
SGM_SPACE_MANAGEMENT VARCHAR2(6 CHAR)
CURRENT_SIZE NUMBER
CURRENT_WASTE NUMBER

create or replace procedure perfstat.rebuild_table is
cursor c_tab is select table_name,owner,sgm_space_management from perfstat.TB_MONITOR where SGM_SPACE_MANAGEMENT='AUTO';
v_tab_mon_aut c_tab%rowtype;
string_table_move varchar2(200);
string_index_rebuild varchar2(200);

err_num varchar2(2000);

begin
open c_tab;
loop
fetch c_tab into v_tab_mon_aut;
if v_tab_mon_aut.sgm_space_management='AUTO' then
string_table_move := CONCAT( 'alter table ',v_tab_mon_aut.owner);
string_table_move := CONCAT( string_table_move ,'.');
string_table_move := CONCAT( string_table_move,v_tab_mon_aut.table_name);
string_table_move := CONCAT( string_table_move,' move ; ');
-- string_index_rebuild = select 'alter index '||a.owner||' '||a.index_name||' rebuild;' from dba_indexes where table_name=v_tab_mon_aut.table;
dbms_output.put_line(string_table_move);
cursor c_index is select index_name,owner from dba_indexes where table_name=v_tab_mon_aut.table_name and owner= v_tab_mon_aut.owner;
v_index c_index%rowtype;
open c_index;
loop
fetch c_index into v_index;
string_index_rebuild:=concat('alter index '||v_index.owner);
string_index_rebuild := CONCAT( string_index_rebuild ,'.');
string_index_rebuild:= CONCAT( string_index_rebuild,v_index.index_name);
string_index_rebuild := CONCAT( string_index_rebuild,' rebuild ; ');
dbms_output.put_line(string_index_rebuild);
end loop;
close c_index;

end if;
exit when c_tab%NOTFOUND;
end loop;
close c_tab;

exception
when others then
err_num := SQLCODE;
if err_num = 1 then
dbms_output.put_line(SQLERRM(err_num));
end if;

end;
/

and Tom said...

I hate your code:

exception 
when others then 
err_num := SQLCODE; 
if err_num = 1 then 
dbms_output.put_line(SQLERRM(err_num)); 
end if;

why do you do that????? why?

you remove useful information.
you turn an error into "not an error" as far as the database goes.

This is the single worst practice in software development you can do (I am serious, dead serious, there is no reason for this). It is called swallowing an exception and it is the worst practice you can do.

You do not need/want to move a table to reclaim space.

Just

alter table t shrink space;
alter index i shrink space;

that is all you need to do.

and stop doing when others like that. It adds NOTHING to the code. It REMOVES many things - like line numbers from the error message, like the fact an error occurred at all...

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

pls be patient.！

hongyu ma, November 07, 2012 - 6:31 pm UTC

pls don't talk about the result, just write the program how to realise it.

I rewrite the the procedure, now it is working:)

here is it,for you conference:)

create or replace procedure perfstat.rebuild_table01 is
cursor cur_test2 is select owner, table_name ,cursor(select owner,index_name from sys.dba_indexes where table_name= tb_monitor.table_name ) from perfstat.tb_monitor;
rec_test2 perfstat.TB_MONITOR%rowtype;
rec_test1 sys.dba_indexes%rowtype;
cur_test1 sys_refcursor;

begin
open cur_test2;
loop
fetch cur_test2 into rec_test2.owner, rec_test2.table_name, cur_test1;
exit when cur_test2%notfound;
dbms_output.put_line('alter table ' || rec_test2.owner ||'.'||rec_test2.table_name||' move;');
loop
fetch cur_test1 into rec_test1.owner,rec_test1.index_name;
exit when cur_test1%notfound;
dbms_output.put_line( 'alter index ' || rec_test1.owner ||'.'||rec_test1.index_name||' rebuild;');
end loop;
end loop;
close cur_test2;
end;
/

November 08, 2012 - 8:03 am UTC

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>

wow!

I bet this is what many readers want to say!, November 08, 2012 - 3:44 pm UTC

tom,

I am amazed on why you would spent so much of your valuable time for answering such a disrespectful, outrages and rude comment! seeing how this person completely ignored your advice, its even more astonishing that you continued to advice him and you not only answered him, but also went back and added such comprehensive code!

I bet many readers were embarrassed by seeing his comments, and just when fuming from those arrogant words, seeing your comprehensive reply was just unbelievable! Don't know how you cope and how you keep cool ! Fair play for being so tolerant and so patient!

If everyone would put so much love and effort into what they do and into their jobs, world would have been a much better place.

Tom, you are a great man!

hongyu ma, November 08, 2012 - 6:56 pm UTC

thanks a lot for your great work!
You are Great because you can keep cool and patient from the disrespectful, outrages and rude comments. I am so sorry for what I have said to you.
In the world,there always have many toadys who flatters or defers to others for self-serving reasons ,just let them crazy:) just a jok, thank you again.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

pls be patient.！

wow!

Tom, you are a great man!

More to Explore

PL/SQL demos

PL/SQL docs

how can I move table and rebuid index one by one

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

pls be patient.！

wow!

Tom, you are a great man!

More to Explore

PL/SQL demos

PL/SQL docs