declare
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;

v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;

v_free_blocks number;

cursor SSI1 is
        select *
        from SSI_ETAT
        where partition_name is null and partitioned ='NO';

cursor SSI2 is
        select *
        from SSI_ETAT
        where partition_name is not null;

tab     SSI_ETAT%ROWTYPE;
begin

for tab in SSI1
loop
        dbms_space.unused_space (tab.table_owner,tab.table_name,'INDEX',v_total_blocks,v_total_bytes, v_unused_blocks, v_unused_bytes,v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block);

        update SSI_ETAT
        set     total_blocks=v_total_blocks,
                unused_blocks=v_unused_blocks
        where table_name = tab.table_name and table_owner = tab.table_owner;

        if tab.segment_space_management='AUTO' then
                dbms_space.space_usage(tab.table_owner,tab.table_name,'INDEX',v_unformatted_blocks,v_unformatted_bytes,v_fs1_blocks,v_fs1_bytes,v_fs2_blocks,v_fs2_bytes,v_fs3_blocks,v_fs3_bytes,v_fs4_blocks,v_fs4_bytes,v_full_blocks,v_full_bytes);

                update SSI_ETAT
                set     unformatted_blocks=v_unformatted_blocks,
                        fs1_blocks=v_fs1_blocks,
                        fs2_blocks=v_fs2_blocks,
                        fs3_blocks=v_fs3_blocks,
                        fs4_blocks=v_fs4_blocks,
                        full_blocks=v_full_blocks
                where table_name = tab.table_name and table_owner = tab.table_owner;
        else
                dbms_space.free_blocks(tab.table_owner,tab.table_name,'INDEX',0,v_free_blocks);

                update SSI_ETAT
                set     free_blocks=v_free_blocks
                where   table_name = tab.table_name and table_owner = tab.table_owner;
        end if;

        commit;
end loop;

for tab in SSI2
loop
        dbms_space.unused_space (tab.table_owner,tab.table_name,'INDEX PARTITION',v_total_blocks,v_total_bytes, v_unused_blocks, v_unused_bytes,v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block, tab.partition_name);

        update SSI_ETAT
        set     total_blocks=v_total_blocks,
                unused_blocks=v_unused_blocks
        where partition_name = tab.partition_name and table_owner = tab.table_owner and table_name = tab.table_name;

        if tab.segment_space_management='AUTO' then
                dbms_space.space_usage(tab.table_owner,tab.table_name,'INDEX PARTITION',v_unformatted_blocks,v_unformatted_bytes,v_fs1_blocks, v_fs1_bytes,v_fs2_blocks,v_fs2_bytes,v_fs3_blocks,v_fs3_bytes,v_fs4_blocks,v_fs4_bytes,v_full_blocks,v_full_bytes, tab.partition_name);

                update SSI_ETAT
                set     unformatted_blocks=v_unformatted_blocks,
                        fs1_blocks=v_fs1_blocks,
                        fs2_blocks=v_fs2_blocks,
                        fs3_blocks=v_fs3_blocks,
                        fs4_blocks=v_fs4_blocks,
                        full_blocks=v_full_blocks
                where partition_name = tab.partition_name and table_owner = tab.table_owner and table_name = tab.table_name;
        else
                dbms_space.free_blocks(tab.table_owner,tab.table_name,'INDEX PARTITION',0,v_free_blocks,null,tab.partition_name);

                update SSI_ETAT
                set     free_blocks=v_free_blocks
                where partition_name = tab.partition_name and table_owner = tab.table_owner and table_name = tab.table_name;
        end if;
        commit;
end loop;

END;
/
