PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Truncate Table
Version 11.1
 
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.

Note that by default, TRUNCATE drops storage even if DROP STORAGE is not specified.

Oracle Database SQL Reference documentation for versions 11.1, 10.2, 10.1 and 9.2 all state that "DROP STORAGE" is default option for TRUNCATE. That is:

"DROP STORAGE: Specify DROP STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default."
(Emphasis added)
 
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

Note: If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.

If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table. However, when the table is truncated, the index is also truncated, and a new high water mark is calculated for the index segment.

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.
The second parameter in this function, "p__storage BOOLEAN DEFAULT FALSE" was suggested by Mariusz Mroz, who indicated that it's important for huge tables and full table scans operations.
/*
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, p_storage BOOLEAN DEFAULT FALSE) 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;

  IF p_drop_storage THEN
      sql_stmt:='TRUNCATE TABLE ' || v_owner || '.' || p_tabname || ' REUSE STORAGE';
  ELSE
      sql_stmt:='TRUNCATE TABLE ' || v_owner || '.' || p_tabname;
  END IF;


  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 = '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;
 
Related Topics
Delete
Partitions
Tables
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [61 users online]    © 2010 psoug.org