Oracle Truncate
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Usage 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
 Basic Truncate TRUNCATE TABLE <table_name> [<PRESERVE | PURGE>] [<DROP  [ALL] | REUSE> STORAGE];

By default drops storage even if DROP STORAGE is not specified. The PURGE option is for truncating materialized views and purges the MV log.
conn uwclass/uwclass@pdbdev

CREATE TABLE test AS
SELECT * FROM all_objects;

SELECT COUNT(*) FROM test;

TRUNCATE TABLE
test;

or more explicitly

TRUNCATE TABLE test DROP ALL 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 Partitions & Subpartitions
Truncate A Single Partition In A Partitioned Table ALTER TABLE <table_name>
TRUNCATE PARTITION <partition_name>;
conn uwclass/uwclass@pdbdev

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 A Subpartition In A Composite Partitioned Table ALTER TABLE <table_name>
TRUNCATE PARTITION <partition_name>;
conn uwclass/uwclass@pdbdev

CREATE TABLE demo_list_list (
cust_id     NUMBER(5),
dotw        NUMBER(1),
testdata    VARCHAR2(20))
PARTITION BY LIST(cust_id)
SUBPARTITION BY LIST (dotw)
SUBPARTITION TEMPLATE(
SUBPARTITION sunday VALUES (1),
SUBPARTITION monday VALUES(2),
SUBPARTITION tuesday VALUES(3),
SUBPARTITION wednesday VALUES(4),
SUBPARTITION thursday VALUES(5),
SUBPARTITION friday VALUES(6),
SUBPARTITION saturday VALUES (7)) (
PARTITION airbus VALUES (2),
PARTITION boeing VALUES (1));

INSERT INTO demo_list_list VALUES (1, 1, 'Test Data1');
INSERT INTO demo_list_list VALUES (2, 1, 'Test Data2');
INSERT INTO demo_list_list VALUES (1, 1, 'Test Data3');
INSERT INTO demo_list_list VALUES (2, 2, 'Test Data4');
INSERT INTO demo_list_list VALUES (1, 1, 'Test Data5');

col high_value format a50

SELECT partition_name, subpartition_name, high_value
FROM user_tab_subpartitions
WHERE table_name = 'DEMO_LIST_LIST';

SELECT COUNT(*) FROM demo_list_list SUBPARTITION(BOEING_SUNDAY);

ALTER TABLE demo_list_list TRUNCATE SUBPARTITION boeing_sunday;

SELECT COUNT(*) FROM demo_list_list SUBPARTITION(BOEING_SUNDAY);
Truncating a parent table partition truncates the corresponding child table partition ALTER TABLE <parent_table_name> TRUNCATE PARTITION <partition_name> CASCADE;
CREATE TABLE ref_parent (
table_name   VARCHAR2(30),
order_date   DATE,
num_rows     NUMBER)
PARTITION BY RANGE(num_rows) (
PARTITION num_rows1 VALUES LESS THAN (100),
PARTITION num_rows2 VALUES LESS THAN (1000),
PARTITION num_rows3 VALUES LESS THAN (MAXVALUE));

ALTER TABLE ref_parent
ADD CONSTRAINT pk_ref_parent
PRIMARY KEY (table_name)
USING INDEX;

CREATE TABLE ref_child (
table_name VARCHAR2(30) NOT NULL,
index_name VARCHAR2(30) NOT NULL,
CONSTRAINT fk_ref_child_parent
FOREIGN KEY(table_name) REFERENCES ref_parent(table_name) ON DELETE CASCADE)
PARTITION BY REFERENCE(fk_ref_child_parent);

INSERT INTO ref_parent VALUES ('OBJ$', SYSDATE-10, 42);
INSERT INTO ref_parent VALUES ('TAB$', SYSDATE-5, 200);
INSERT INTO ref_parent VALUES ('COL$', SYSDATE+5, 1111);
INSERT INTO ref_parent VALUES ('IND$', SYSDATE+10, 9999);
INSERT INTO ref_child VALUES ('OBJ$', 'PK_OBJ$');
INSERT INTO ref_child VALUES ('TAB$', 'PK_TAB$');
INSERT INTO ref_child VALUES ('COL$', 'PK_COL$');
INSERT INTO ref_child VALUES ('COL$', 'UI_COL$');
INSERT INTO ref_child VALUES ('IND$', 'PK_IND$');
COMMIT;

SELECT * FROM ref_parent;

SELECT * FROM ref_child;

ALTER TABLE ref_parent TRUNCATE PARTITION num_rows2 CASCADE;

SELECT * FROM ref_parent;

SELECT * FROM ref_child;
 
Truncate Cluster
Demo with Hash and Index Clusters TRUNCATE CLUSTER <schema_name.cluster_name> [<DROP [ALL] | REUSE> STORAGE];
conn uwclass/uwclass@pdbdev

CREATE CLUSTER uw_cluster (
rid NUMBER(10))
PCTFREE 0
TABLESPACE uwdata
HASHKEYS 5
ROWDEPENDENCIES;

CREATE TABLE cservers (
srvr_id    NUMBER(10),
network_id NUMBER(10))
CLUSTER uw_cluster (srvr_id);

CREATE TABLE cserv_inst (
custacct_id   VARCHAR2(10),
srvr_id       NUMBER(10),
ws_id         NUMBER(10))
CLUSTER uw_cluster (srvr_id);

INSERT INTO cservers (srvr_id, network_id) VALUES (1,2);
INSERT INTO cserv_inst (custacct_id, srvr_id, ws_id) VALUES ('TEST', 1, 4);

-- this does not work
TRUNCATE CLUSTER
uw_cluster;

DROP CLUSTER uw_cluster INCLUDING TABLES;

CREATE CLUSTER uw_cluster (
srvr_id NUMBER(10))
SIZE 1024;

CREATE TABLE cservers (
srvr_id    NUMBER(10),
network_id NUMBER(10))
CLUSTER uw_cluster (srvr_id);

CREATE TABLE cserv_inst (
custacct_id   VARCHAR2(10),
srvr_id       NUMBER(10),
ws_id         NUMBER(10))
CLUSTER uw_cluster (srvr_id);

CREATE INDEX idx_sc_srvr_id ON CLUSTER uw_cluster;

INSERT INTO cservers (srvr_id, network_id) VALUES (1,2);
INSERT INTO cserv_inst (custacct_id, srvr_id, ws_id) VALUES ('TEST', 1, 4);

TRUNCATE CLUSTER uw_cluster;
 
Truncate Materialized View Log
Truncating materialized view and materialized view logs. Read the docs before doing this as there are restrictions on what can and can not be preserved. TRUNCATE TABLE <table_name> [<PRESERVE | PURGE>] [MATERIALIZED VIEW LOG]
[<DROP  [ALL] | REUSE> STORAGE];
conn uwclass/uwclass@pdbdev

CREATE MATERIALIZED VIEW LOG ON servers
PCTFREE 0
PCTUSED 99
TABLESPACE uwdata
WITH PRIMARY KEY, ROWID;

CREATE MATERIALIZED VIEW mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT * FROM servers;

SELECT COUNT(*) FROM mv_simple;

INSERT INTO servers (srvr_id) VALUES (9999);
COMMIT;

SELECT COUNT(*) FROM mv_simple;

TRUNCATE TABLE mv_simple PRESERVE MATERIALIZED VIEW LOG;
 
Truncate In Other Schemas
This procedure was provided to the library by JP Vijaykumar /* Table owners can always truncate their own tables but for a third party to truncate a table that username must be granted escallated privileges such as the DROP ANY TABLE system privilege or a role such as DBA that posses that privilege.

Granting escallated privileges should never be done for routine activities so it is recommended that a procedure such as this one be placed into schemas where truncates may be required and EXECUTE privileges on the procedure be granted as is appropriate. */


conn uwclass/uwclass@pdbdev

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;

Related Topics
Clusters
Delete Statement
Partitions
Tables

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved