--查看表空间使用的统计信息()
SET serveroutput ON
DECLARETOTAL_BLOCKS NUMBER;
TOTAL_BYTES NUMBER;
UNUSED_BLOCKS NUMBER;
UNUSED_BYTES NUMBER;
LAST_USED_EXTENT_FILE_ID NUMBER;
LAST_USED_EXTENT_BLOCK_ID NUMBER;
LAST_USED_BLOCK NUMBER;
BEGIN
dbms_space.unused_space('TEST', 'T1', 'TABLE', TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES, LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID, LAST_USED_BLOCK);
dbms_output.put_line('OBJECT_NAME = T1');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('TOTAL BLOCKS = ' || TOTAL_BLOCKS);
dbms_output.put_line('TOTAL SIZE(KByte) = ' || TOTAL_BYTES / 1024);
dbms_output.put_line('UNUSED BLOCKS = ' || UNUSED_BLOCKS);
dbms_output.put_line('UNUSED SIZE(KByte) = ' || UNUSED_BYTES / 1024);
END;
/
--通过rowid删除指定数据
DECLARE
CURSOR table_name_cur
IS
SELECT
/*+ FULL(a) */
a.rowid
FROM table_name a
WHERE time_column<required_date table_name_rec table_name_cur%ROWTYPE;
row_number NUMBER;
BEGIN
row_number :=0;
OPEN table_name_cur;
LOOP
FETCH table_name_cur INTO table_name_rec;
IF table_name_cur%NOTFOUND THEN
COMMIT;
EXIT;
END IF;
DELETE FROM table_name WHERE rowid = table_name_rec.rowid;
row_number := row_number + 1;
IF (mod (row_number,1000) =0) THEN
INSERT INTO delete_rows VALUES
(row_number
);
COMMIT;
END IF;
END LOOP;
INSERT INTO delete_rows VALUES
(row_number
);
COMMIT;
CLOSE table_name_cur;
END;
/