CODEWITHSUNDEEP
oracle-databasetruncate
Bates
Bates

Reputation: 141

Truncate table in Oracle

I need to truncate a table that has around 40 million records in it. i guess it is ok to REUSE STORAGE for the table since new data will be inserted after the truncate. I need to know

  1. approximately how much time the command TRUNCATE TABLE <tablename> REUSE STORAGE; would take.

  2. Can this be done offline, so that the operations/ commands on the DB don't get affected?

Upvotes: 2

Views: 10494

Answers (1)

KevinKirkpatrick
KevinKirkpatrick

Reputation: 1456

Based on "so that the operations/ commands don't get affected", it sounds like you'd like to replace the contents of a table with "good" values, without anyone seeing an empty table. If users/processes need to be able to continue making changes to the table while you switch out the contents, you'll need to research DBMS_REDEFINITION. If this is a read-only table (in other words, you can do this operation while nobody is inserting/updating/deleting its contents), there's a nice DIY approach that takes advantage of partition exchanging:

1) Create a table which has identical partitioning and indexing as original, and has the desired "good" rows.
2)
IF YOUR TABLE IS PARTITIONED: 

CREATE TABLE <tablename>_TMP AS SELECT * FROM <tablename> WHERE 1=0;

-- Add any local indexes on <tablename> as standard indexes on <tablename_tmp>

BEGIN 
    FOR R IN (SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = <tablename>)
    LOOP
        EXECUTE IMMEDIATE 
            'INSERT INTO <tablename>_TMP' 
            ||' SELECT * FROM <good_data_tablename> PARTITION ('||R.PARTITION_NAME||')';
        EXECUTE IMMEDIATE
            'ALTER TABLE <tablename>_TMP' 
            ||' EXCHANGE PARTITION WITH TABLE <tablename> PARTITION ' || R.PARTITION_NAME 
            ||' INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES';
        EXECUTE IMMEDIATE 
            'TRUNCATE TABLE <tablename>_TMP';
    END LOOP    
END;
/

DROP TABLE <tablename>_TMP ;

IF YOUR TABLE IS NOT PARTITIONED:

CREATE TABLE <tablename>_TMP 
PARTITION BY RANGE (<non-null-column>)
(PARTITION ALL_DATA values less than (maxvalue))
AS SELECT * FROM <good_data_tablename>;

-- Add any <tablename> indexes as local indexes on <tablename>_TMP 

ALTER TABLE <tablename> EXCHANGE PARTITION WITH TABLE <tablename>_TMP INCLUDING INDEXES WITHOUT VALIDATION;

DROP TABLE <tablename>_TMP ;

Upvotes: 4

Related Questions