Note: Deletes perform
normal DML. That is, they take locks on rows, they generate redo (lots of it), and they
require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If
a mistake is made a rollback can be issued to restore the records prior to a commit. A
delete does not relinquish segment space thus a table in which all records have been
deleted retains all of its original blocks.
Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the
table back to zero. No row-level locks are taken, no redo or rollback is generated.
All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to
anything other than 1, then that number of extents is retained rather than just the
initial). By re-positioning the high water mark, they prevent reading of any table data,
so they have the same effect as a delete, but without all the overhead. Just one slight
problem: a truncate is a DDL command, so you can't roll it back if you decide you made a
mistake. (It's also true that you can't selectively truncate -no "WHERE" clause
is permitted, unlike with deletes, of course).
By resetting the High Water Mark, the truncate prevents reading of any table's data, so
they it has the same effect as a delete, but without the overhead. There is, however, one
aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a
COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is
possible. |
| |
| Truncate Table |
Simple Truncate |
TRUNCATE TABLE <table_name>
[<PRESERVE | PURGE>] [MATERIALIZED VIEW LOG]
[<DROP | REUSE> STORAGE];
By default drops storage even if DROP STORAGE is not specified.
The PURGE option is for truncating materialized views and purges the
log. |
CREATE TABLE test AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
SELECT COUNT(*) FROM test;
TRUNCATE TABLE test;
or more explicitly
TRUNCATE TABLE test
DROP STORAGE;
SELECT COUNT(*) FROM test; |
Empty Table And Move Highwater Mark |
CREATE TABLE test (
testcol VARCHAR2(20));
BEGIN
FOR i IN 1..10000
LOOP
INSERT INTO test
(testcol)
VALUES
('ABCDEFGHIJKLMNOPQRST');
END LOOP;
COMMIT;
END;
/
exec dbms_stats.gather_table_stats(OWNNAME=>'UWCLASS', TABNAME=>'TEST');
SELECT SUM(blocks)
FROM user_extents
WHERE segment_name = 'TEST';
TRUNCATE TABLE test;
SELECT SUM(blocks)
FROM user_extents
WHERE segment_name = 'TEST'; |
Empty Table And Do Not Move Highwater Mark |
TRUNCATE TABLE <table_name>
REUSE STORAGE; |
BEGIN
FOR i IN 1..10000
LOOP
INSERT INTO test
(testcol)
VALUES
('ABCDEFGHIJKLMNOPQRST');
END LOOP;
COMMIT;
END;
/
exec dbms_stats.gather_table_stats(OWNNAME=>'UWCLASS', TABNAME=>'TEST');
SELECT SUM(blocks)
FROM user_extents
WHERE segment_name = 'TEST';
TRUNCATE TABLE test REUSE STORAGE;
SELECT SUM(blocks)
FROM user_extents
WHERE segment_name = 'TEST'; |
| |
| Truncate Partition |
Truncate A Single Partition In A Partitioned Table |
ALTER TABLE <table_name>
TRUNCATE PARTITION <partition_name>; |
CREATE TABLE parttab (
state VARCHAR2(2),
sales NUMBER(10,2))
PARTITION BY LIST (state) (
PARTITION northwest VALUES ('OR', 'WA') TABLESPACE uwdata,
PARTITION southwest VALUES ('AZ', 'CA') TABLESPACE uwdata);
INSERT INTO parttab VALUES ('OR', 100000);
INSERT INTO parttab VALUES ('WA', 200000);
INSERT INTO parttab VALUES ('AZ', 300000);
INSERT INTO parttab VALUES ('CA', 400000);
COMMIT;
SELECT * FROM parttab PARTITION(northwest);
SELECT * FROM parttab PARTITION(southwest);
ALTER TABLE parttab
TRUNCATE PARTITION southwest;
SELECT * FROM parttab PARTITION(northwest);
SELECT * FROM parttab PARTITION(southwest); |
| |
| Truncate In Other
Schemas |
This procedure was provided to the library by JP Vijaykumar |
/*
In applications, users need truncate privileges on third party tables. The owner of the table can truncate his/her own tables.
A user with DBA role granted can truncate a third party table. Any user with "drop any table" system privilege can truncate a third
party table.
It is not recommended in Production environments to grant powerful roles like DBA role or privileges like "drop any table" privilege
to accomplish this purpose.
This procedure need to be created in each schema. The schema owner need to grant execute permission to the user,
and the delete permission on specified tables.
Then the user can execute the procedure to truncate tables User A owns the table
temp_jp.
conn uwclass/uwclass
CREATE TABLE t1 AS
SELECT * FROM all_tables;
CREATE OR REPLACE PROCEDURE trunc_tab(p_tabname IN VARCHAR2) AUTHID
DEFINER AS
/**********************************
AUTHOR JP Vijaykumar
ORACLE DBA
**********************************/
v_num NUMBER(10):=0;
v_owner VARCHAR2(30);
v_user VARCHAR2(30);
sql_stmt VARCHAR2(2000);
BEGIN
SELECT username
INTO v_owner
FROM user_users;
SELECT sys_context('USERENV','SESSION_USER')
INTO v_user
FROM dual;
sql_stmt:='TRUNCATE TABLE ' || v_owner || '.' || p_tabname;
IF (v_owner = v_user) THEN
execute immediate sql_stmt;
ELSE
SELECT COUNT(*)
INTO v_num
FROM all_tab_privs
WHERE table_name = UPPER(p_tabname)
AND table_schema = v_owner
AND grantee = v_user
AND privilege in 'DELETE';
IF (v_num > 0) THEN
execute immediate sql_stmt;
ELSE
RAISE_APPLICATION_ERROR(-20001,'Insufficient privileges.');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Insufficient privileges');
END trunc_tab;
/
GRANT execute ON t1 TO abc;
conn abc/abc
exec uwclass.trunc_tab('T1');
conn uwclass/uwclass
GRANT delete ON t1 TO abc;
conn abc/abc
exec uwclass.trunc_tab('T1');
conn uwclass/uwclass
SELECT * FROM t1; |