一些存储过程

--查看表空间使用的统计信息（）

SET serveroutput ON

DECLARE
  TOTAL_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;
/