Shrink a Table

CREATE OR REPLACE PROCEDURE  dpr_table_shrink
IS
CURSOR c_table_name
IS
SELECT table_name
FROM user_tables
WHERE TEMPORARY = 'N' AND row_movement = 'DISABLED';

CURSOR c_table_shrink
IS
SELECT table_name
FROM user_tables
WHERE TEMPORARY = 'N' AND row_movement = 'ENABLED';

table_id INTEGER;
table_status INTEGER;
owner_name VARCHAR2 (200);
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
INTO owner_name
FROM DUAL;

FOR i IN c_table_name LOOP
table_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse (table_id,
'ALTER TABLE '
|| i.table_name
|| ' ENABLE ROW MOVEMENT',
DBMS_SQL.native
);
table_status := DBMS_SQL.EXECUTE (table_id);
DBMS_SQL.close_cursor (table_id);
END LOOP;

FOR i IN c_table_shrink LOOP
BEGIN
table_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse (table_id,
'ALTER TABLE ' || i.table_name || ' SHRINK SPACE',
DBMS_SQL.native
);
table_status := DBMS_SQL.EXECUTE (table_id);
DBMS_SQL.close_cursor (table_id);
DBMS_OUTPUT.put_line ('Table shrink completed: ' || i.table_name);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.is_open (table_id) THEN
DBMS_SQL.close_cursor (table_id);
END IF;

DBMS_OUTPUT.put_line ('Table can not shrink: ' || i.table_name);
END;
END LOOP;
END;
/