DECLARE

wMB integer  := 0;
wEX integer  := 0;

wOwner VARCHAR2(255) := 'CINEMA';

tablename VARCHAR2(255);
indexname VARCHAR2(255);

CURSOR cTables (pOwner VARCHAR2) is select owner || '.' || segment_name from dba_segments where segment_type in ('TABLE') and  owner=pOwner order by 1;
CURSOR cIndexes  (pOwner VARCHAR2) is select owner || '.' || segment_name from dba_segments where segment_type in ('INDEX') and  owner=pOwner order by 1;

BEGIN
    
    select sum(bytes/1024) mb, SUM(EXTENTS) EXTENTS into wMB,wEX  from dba_segments where segment_type in ('TABLE','INDEX') and  owner = wOwner;

    DBMS_OUTPUT.PUT_LINE('Spazio MB :  ' || to_char(wMB) || '  EXTENTS :  ' || to_char(wEX));

    OPEN cTables(wOwner);
    LOOP
        FETCH cTables INTO tablename;
        EXIT WHEN cTables%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('shrink table ' || tablename);

        BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE ' ||  tablename || ' enable ROW movement';
            EXECUTE IMMEDIATE 'ALTER TABLE ' ||  tablename || ' shrink SPACE';
            EXECUTE IMMEDIATE 'ALTER TABLE ' ||  tablename || ' disable ROW movement';
        EXCEPTION
            WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE('ERRORE shrink table ' || tablename);
        END;
        
    END LOOP;
    CLOSE cTables;
    
    OPEN cIndexes(wOwner);
    LOOP
        FETCH cIndexes INTO indexname;
        EXIT WHEN cIndexes%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('REBUILD INDEX ' || indexname);

        BEGIN
            EXECUTE IMMEDIATE 'ALTER INDEX ' ||  indexname || ' REBUILD';
        EXCEPTION
            WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE('ERRORE REBUILD INDEX ' || indexname);
        END;

    END LOOP;
    CLOSE cIndexes;

    select sum(bytes/1024) mb, SUM(EXTENTS) EXTENTS into wMB,wEX  from dba_segments where segment_type in ('TABLE','INDEX') and  owner = wOwner;

    DBMS_OUTPUT.PUT_LINE('Spazio MB :  ' || to_char(wMB) || '  EXTENTS :  ' || to_char(wEX));

    
END;