从 oracle db 截断表时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/657639/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
error while truncating tables from oracle db
提问by
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 STORAGEwas 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
- 为什么存储关键字是必需的？我以为
DROP STORAGE是默认的。 即使指定存储关闭，如，
立即执行'截断表' || t.table_name || '丢弃存储';
没有帮助。错误是一样的。
- 我认为这可能与某些表上的外部约束有关。因此，脚本中前面的“禁用约束”
回答by Dave Costa
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.
我建议您在字符串变量中构建正在执行的命令，使用 dbms_output 输出它，然后执行它。通过这种方式，您将准确地看到它正在尝试执行的正在生成错误的内容。
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.
回答by DCookie
Change your program:
改变你的程序：
- put your truncate command in a PL/SQL variable prior to execution
- add an exception handler that outputs the truncate statements via dbms_output or utl_file (fflush after each one) when you encounter an exception:
- 在执行之前将 truncate 命令放入 PL/SQL 变量中
- 添加一个异常处理程序，当您遇到异常时，该处理程序通过 dbms_output 或 utl_file（在每个语句之后都刷新）输出 truncate 语句：
LOOP BEGIN ... v_sql := 'TRUNCATE TABLE ' || t.table_name ; EXECUTE IMMEDIATE v_sql; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); dbms_output.put_line(v_sql); END; END LOOP;
LOOP BEGIN ... v_sql := 'TRUNCATE TABLE ' || t.table_name ; EXECUTE IMMEDIATE v_sql; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); dbms_output.put_line(v_sql); END; END LOOP;
This should show you the statement causing the issue.
这应该向您显示导致问题的声明。
回答by R Tiwari
truncate table Table_NAME drop storage;
截断表 Table_NAME 删除存储；
