Oracle – error while truncating tables from oracle db

oraclestoragetruncate

I am doing something like this in a procedure to clear all data from all tables in my database.

LOOP
    dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
 .
 .
 .

LOOP
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || t.table_name ;
  END LOOP;

Now , this throws the following error :

ORA-03291: Invalid truncate option - missing STORAGE keyword
ORA-06512: at "MYSCHEMA.CLEAR_DATA", line 15
ORA-06512: at line 2
Process exited.
Disconnecting from the database MYDB.
  1. Why is a storage keyword mandatory? I thought DROP STORAGE was the default.

  2. Even specifying storage close, as in,

    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || t.table_name || 'DROP STORAGE';

    doesn't help. The error is the same.

  3. I thought it might have something to do with foreign constraints on some tables. Hence, the 'disable constraint' earlier in the script

Best Solution

I would suggest that you build the command you are executing in a string variable, output it using dbms_output, then execute it. This way you will see exactly what it is trying to execute that is generating the error.

One thing that could be causing this error is if you have a table name with a space in it (yes, it's possible). The solution if that is the case is to wrap the table name in double quotes.

dev> create table "dave exp" (x number);

Table created.

dev> truncate table dave exp;
truncate table dave exp
                    *
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword

dev> truncate table "dave exp";

Table truncated.
Related Question