error while truncating tables from oracle db
35,768
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.
Author by
Admin
Updated on July 18, 2021Comments
-
Admin almost 3 years
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.
- Why is a storage keyword mandatory? I thought
DROP STORAGE
was the default. Even specifying storage close, as in,
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || t.table_name || 'DROP STORAGE';
doesn't help. The error is the same.
- I thought it might have something to do with foreign constraints on some tables. Hence, the 'disable constraint' earlier in the script
- Why is a storage keyword mandatory? I thought