Re: using DBMS_SPACE for multiple inst.

From: Michael Spellbrink <michaelspellbrink_at_yahoo.de>
Date: 14 Apr 2002 10:22:58 -0700
Message-ID: <638121fa.0204140922.73e5403@posting.google.com>

Dear Frank, I am no PL/SQL guru at all, but perhaps this will suit for you, too.

begin

for cur_inst (select inst from table_of_inst) loop

       for cur_table (select tables from tables_to_get_info) loop                   

       
       execute dbms_space@||cur_inst.inst||(
v_segment_owner,cur.table_tables,'TABLE',
       v_total_blocks,v_total_bytes,v_unused_blocks,v_unused_bytes,v_last_used_efid,
       v_last_used_ebid,v_last_used_blocks);


       -- now the variables are inserted

       insert into statistics_for_hist  values (
       
       cur_inst.inst, 
cur_table.tables,v_total_blocks,v_total_bytes,v_unused_blocks,v_unused_bytes,
       v_last_used_efid,v_last_used_ebid,v_last_used_blocks,(v_total_blocks
- v_unused_blocks -1),
       trunc(sysdate)
       
       									)
       end loop;

end loop;

of course you have to declare variables, and i am not sure if the execution of dbms_space works this way, but the idea should be right (as far as i can think of on sundays)
perhaps someone could help me with the right way of how to execute dbms_space
for that purpose; maybe one can just define v_sql_string = 'dbms_space like in code above'
and then go like execute immediate v_sql_string?

tia, regards, Michael Spellbrink Received on Sun Apr 14 2002 - 12:22:58 CDT