Saturday, March 08, 2008

Archival of tables and reclaim spaces in Oracle, PL/SQL

DECLARE
from_table VARCHAR2 (30) := ' ';
fromkey1 VARCHAR2 (1000) := ' ';
v_stmt VARCHAR2 (500);
ERRORS NUMBER;
l_cnt NUMBER := 0;
l_bulk_limit NUMBER := 1000;
bulk_error EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_error, -24381);

TYPE ref_cur IS REF CURSOR;

c_rowids ref_cur;

TYPE rowid_array IS TABLE OF VARCHAR2 (10000)
INDEX BY BINARY_INTEGER;

t_rowids_arr rowid_array;

CURSOR cur_del_tab
IS
SELECT from_table, fromkey1
FROM del_stag_data_rules;
BEGIN
OPEN cur_del_tab;

LOOP
FETCH cur_del_tab
INTO from_table, fromkey1;
EXIT WHEN cur_del_tab%NOTFOUND;
OPEN c_rowids FOR 'SELECT ROWID FROM ' || from_table || ' ' || fromkey1;

LOOP
FETCH c_rowids
BULK COLLECT INTO t_rowids_arr LIMIT 100;


BEGIN
FORALL i IN 1 .. t_rowids_arr.COUNT SAVE EXCEPTIONS

EXECUTE IMMEDIATE 'DELETE FROM '
|| from_table
|| ' WHERE ROWID = :rowarr LOG ERRORS INTO ERROR_LOG REJECT LIMIT UNLIMITED '
USING t_rowids_arr (i);
DBMS_ERRLOG.create_error_log (dml_table_name => from_table,
err_log_table_name => from_table
|| '_R'
);

DBMS_ERRLOG.create_error_log (SUBSTR (from_table, 30));
EXCEPTION
WHEN bulk_error
THEN

ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
l_cnt := l_cnt + ERRORS;

FOR i IN 1 .. ERRORS
LOOP
DBMS_OUTPUT.put_line
( 'error iteration '
|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
|| ' oracle error IS '
|| SQL%BULK_EXCEPTIONS (i).ERROR_CODE
);
END LOOP;
END;

COMMIT;
DBMS_STATS.gather_table_stats (ownname => 'SCOTT',
tabname => from_table,
CASCADE => TRUE
);
EXIT WHEN c_rowids%NOTFOUND;
END LOOP;

CLOSE c_rowids;
END LOOP;

CLOSE cur_del_tab;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
END;
/

No comments: