create table truncatedelete
as select * from emp;
/* Formatted on 2011/12/17 00:36 (Formatter Plus v4.8.8) */
SET SERVEROUTPUT ON;
DECLARE
v_count NUMBER := 1;
BEGIN
INSERT INTO truncatedelete
SELECT *
FROM scott.emp;
FOR i IN 1 .. 31
LOOP
INSERT INTO truncatedelete
SELECT *
FROM truncatedelete;
COMMIT;
DBMS_OUTPUT.put_line ('v_count' || v_count);
v_count := v_count + 1;
IF MOD (v_count, 10) = 0
THEN
DELETE FROM truncatedelete
WHERE ROWNUM < 1000;
DBMS_OUTPUT.put_line ('Deleted 9 rows');
COMMIT;
END IF;
IF v_count > 15
THEN
EXIT;
DELETE FROM truncatedelete
WHERE ROWNUM < 10000;
INSERT INTO truncatedelete
SELECT *
FROM truncatedelete;
END IF;
END LOOP;
END;
select sum(bytes/1024/1024) from dba_extents where segment_name='TRUNCATEDELETE';
You need to have access to the dba_views (use system schema for this purpose)
delete from truncatedelete;
select sum(bytes/1024/1024) from dba_extents where segment_name='TRUNCATEDELETE';
truncate table truncatedelete;
select sum(bytes/1024/1024) from dba_extents where segment_name='TRUNCATEDELETE';
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)