oracle 删除全局临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7932977/
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
dropping a global temporary table
提问by x.509
2 Separate questions.
2 单独的问题。
I am using this script to drop a table [SOLVED]
BEGIN EXECUTE IMMEDIATE 'DROP TABLE_NAME'; DBMS_OUTPUT.PUT_LINE ('Global table TABLE_NAME Dropped'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Global table TABLE_NAME Doesn''t exist.'); END; /
我正在使用此脚本删除表 [已解决]
BEGIN EXECUTE IMMEDIATE 'DROP TABLE_NAME'; DBMS_OUTPUT.PUT_LINE ('Global table TABLE_NAME Dropped'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Global table TABLE_NAME Doesn''t exist.'); END; /
Is there anyway I can differentiate if table "doesn't exist" or it is being used in some other sessions (in that case it would locked and couldn't be deleted). I am not sure if I can see that table exists in user_tables. I am not fully aware of permissions.
无论如何我可以区分表是否“不存在”或它正在其他一些会话中使用（在这种情况下它会被锁定并且无法删除）。我不确定是否可以在 user_tables 中看到该表。我不完全了解权限。
I have added this code now
我现在已经添加了这个代码
WHEN OTHERS THEN
i_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 64);
if i_code = -942 THEN
DBMS_OUTPUT.PUT_LINE ('TABLE_NAME doesn''t exist. Script will continue to create it');
ELSE
DBMS_OUTPUT.PUT_LINE ('Error dropping temporary table. The error code is ' || i_code || '- ' || v_errm);
END IF ;
2. I see .at the end of each procedure like this
2. 我明白了。在这样的每个程序结束时
END PROCEDURE_NAME;
.
/
sho err;
I just don't understand why .is here. Is it syntax or what?
我只是不明白为什么。在这儿。是语法还是什么？
回答by Nader
-- First Truncate temporary table SQL> TRUNCATE TABLE test_temp1; -- Then Drop temporary table SQL> DROP TABLE test_temp1;
回答by Shannon Severance
Step 1. Figure out which errors you want to trap:
步骤 1. 找出要捕获的错误：
If the table does not exist:
如果表不存在：
SQL> drop table x;
drop table x
*
ERROR at line 1:
ORA-00942: table or view does not exist
If the table is in use:
如果表正在使用中：
SQL> create global temporary table t (data varchar2(4000));
Table created.
Use the table in another session. (Notice no commit or anything after the insert.)
在另一个会话中使用该表。（注意插入后没有提交或任何内容。）
SQL> insert into t values ('whatever');
1 row created.
Back in the first session, attempt to drop:
回到第一个会话，尝试删除：
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
So the two errors to trap:
所以要捕获两个错误：
- ORA-00942: table or view does not exist
- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
- ORA-00942: 表或视图不存在
- ORA-14452: 尝试在已使用的临时表上创建、更改或删除索引
See if the errors are predefined. They aren't. So they need to be defined like so:
查看错误是否已预定义。他们不是。所以他们需要像这样定义：
create or replace procedure p as
table_or_view_not_exist exception;
pragma exception_init(table_or_view_not_exist, -942);
attempted_ddl_on_in_use_GTT exception;
pragma exception_init(attempted_ddl_on_in_use_GTT, -14452);
begin
execute immediate 'drop table t';
exception
when table_or_view_not_exist then
dbms_output.put_line('Table t did not exist at time of drop. Continuing....');
when attempted_ddl_on_in_use_GTT then
dbms_output.put_line('Help!!!! Someone is keeping from doing my job!');
dbms_output.put_line('Please rescue me');
raise;
end p;
And results, first without t:
结果，首先没有t：
SQL> drop table t;
Table dropped.
SQL> exec p;
Table t did not exist at time of drop. Continuing....
PL/SQL procedure successfully completed.
And now, with tin use:
现在，t使用中：
SQL> create global temporary table t (data varchar2(4000));
Table created.
In another session:
在另一个会话中：
SQL> insert into t values (null);
1 row created.
And then in the first session:
然后在第一个会话中：
SQL> exec p;
Help!!!! Someone is keeping from doing my job!
Please rescue me
BEGIN p; END;
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
ORA-06512: at "SCHEMA_NAME.P", line 16
ORA-06512: at line 1
回答by Randy
yes - the engine will throw different exceptions for different conditions.
是的 - 引擎会针对不同的条件抛出不同的异常。
you will change this part to catch the exception and do something different
您将更改此部分以捕获异常并做一些不同的事情
EXCEPTION
WHEN OTHERS THEN
here is a reference
这是一个参考
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm
回答by PL SHARMA
The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current connection.
DECLARE GLOBAL TEMPORARY TABLE 语句为当前连接定义一个临时表。
These tables do not reside in the system catalogs and are not persistent.
这些表不驻留在系统目录中，也不是持久的。
Temporary tables exist only during the connection that declared them and cannot be referenced outside of that connection.
临时表仅在声明它们的连接期间存在，并且不能在该连接之外引用。
When the connection closes, the rows of the table are deleted, and the in-memory description of the temporary table is dropped.
当连接关闭时，表的行被删除，临时表的内存描述被删除。
For your reference http://docs.oracle.com/javadb/10.6.2.1/ref/rrefdeclaretemptable.html
供您参考http://docs.oracle.com/javadb/10.6.2.1/ref/rrefdeclaretemptable.html
回答by Suresh Kalvoj
- Down the apache server by running below in
puttycd $ADMIN_SCRIPTS_HOME./adstpall.sh - Drop the Global temporary tables
drop table t;
- 通过在下面运行来关闭 apache 服务器
puttycd $ADMIN_SCRIPTS_HOME./adstpall.sh - 删除全局临时表
drop table t;
This will workout..
这将锻炼..
