DB2中select查询某表期间无法drop删除该表;Oracle不然
来源:互联网 发布:c语言基础入门知识 编辑:程序博客网 时间:2024/06/05 14:29
在session1中执行:
db2 => select * from db2admin.t_da_jkda_rkxzl --会执行很长时间
在session2中执行:
db2 => drop table db2admin.t_da_jkda_rkxzl --此时无法执行完成,hang住
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 40 *LOCAL.db2inst1.171019012914 TEST 1
DB2INST1 db2bp 33 *LOCAL.db2inst1.171019012907 TEST 1
[db2inst1@db ~]$ db2pd -d test -apinfo 40
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:37
Application :
Address : 0x0000000200DC65E0
AppHandl [nod-index] : 40 [000-00040]
TranHdl : 8
Application PID : 4806
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376551)Thu Oct 19 09:29:11 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 58
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 1
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012914
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
List of active statements :
UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H20
Package Version :
Section Number : 201
SQL Type : Dynamic
Isolation : CS
Statement Type : DML, Select (blockable)
Statement : select * from
db2admin.T_DA_JKDA_JKZK
--------------------------------------------------------------------------------
[db2inst1@db ~]$ db2pd -d test -apinfo 33
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:41
Application :
Address : 0x0000000200DA0080
AppHandl [nod-index] : 33 [000-00033]
TranHdl : 2
Application PID : 4963
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376547)Thu Oct 19 09:29:07 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 17
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : Lock-wait
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012907
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
List of active statements :
*UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H20
Package Version :
Section Number : 203
SQL Type : Dynamic
Isolation : CS
Statement Type : DDL, (not Set Constraints)
Statement : drop table
db2admin.T_DA_JKDA_JKZK
--------------------------------------------------------------------------------
[db2inst1@db ~]$ db2 "force application (40)" -->此时Kill掉select查询sessin1后,session2中的drop可以正常执行
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@db ~]$ db2pd -d test -apinfo 33
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:01:01
Application :
Address : 0x0000000200DA0080
AppHandl [nod-index] : 33 [000-00033]
TranHdl : 2
Application PID : 4963
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376547)Thu Oct 19 09:29:07 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 17
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012907
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
db2 => select * from db2admin.t_da_jkda_rkxzl --会执行很长时间
在session2中执行:
db2 => drop table db2admin.t_da_jkda_rkxzl --此时无法执行完成,hang住
以下操作步骤均在session3中执行:
[db2inst1@db ~]$ db2 list applicationAuth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 40 *LOCAL.db2inst1.171019012914 TEST 1
DB2INST1 db2bp 33 *LOCAL.db2inst1.171019012907 TEST 1
[db2inst1@db ~]$ db2pd -d test -apinfo 40
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:37
Application :
Address : 0x0000000200DC65E0
AppHandl [nod-index] : 40 [000-00040]
TranHdl : 8
Application PID : 4806
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376551)Thu Oct 19 09:29:11 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 58
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 1
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012914
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
List of active statements :
UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H20
Package Version :
Section Number : 201
SQL Type : Dynamic
Isolation : CS
Statement Type : DML, Select (blockable)
Statement : select * from
db2admin.T_DA_JKDA_JKZK
--------------------------------------------------------------------------------
[db2inst1@db ~]$ db2pd -d test -apinfo 33
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:41
Application :
Address : 0x0000000200DA0080
AppHandl [nod-index] : 33 [000-00033]
TranHdl : 2
Application PID : 4963
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376547)Thu Oct 19 09:29:07 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 17
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : Lock-wait
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012907
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
List of active statements :
*UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H20
Package Version :
Section Number : 203
SQL Type : Dynamic
Isolation : CS
Statement Type : DDL, (not Set Constraints)
Statement : drop table
db2admin.T_DA_JKDA_JKZK
--------------------------------------------------------------------------------
[db2inst1@db ~]$ db2 "force application (40)" -->此时Kill掉select查询sessin1后,session2中的drop可以正常执行
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@db ~]$ db2pd -d test -apinfo 33
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:01:01
Application :
Address : 0x0000000200DA0080
AppHandl [nod-index] : 33 [000-00033]
TranHdl : 2
Application PID : 4963
Application Node Name : db
IP Address: n/a
Connection Start Time : (1508376547)Thu Oct 19 09:29:07 2017
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 17
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.171019012907
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
--------------------------------------------------------------------------------
Oracle中实验如下:
session1中:
SQL> create table test as select * from dba_objects;
SQL> select * from dba_objects; --此处会执行很长时间
session2中
SQL> drop table test; -->在session1中的select执行期间,在session2中执行drop操作,此时可以正常删除test表
Table dropped.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
阅读全文
0 0
- DB2中select查询某表期间无法drop删除该表;Oracle不然
- oracle 中删除表 drop delete truncate
- oracle 中删除表 drop delete truncate
- 误删除 drop oracle表
- 恢复oracle中误删除drop掉的表
- 恢复oracle中误删除drop掉的表
- 【回收站】恢复oracle中误删除drop掉的表
- 恢复oracle中误删除drop掉的表
- 恢复oracle中误删除drop掉的表
- 恢复oracle中误删除drop掉的表
- 恢复oracle中误删除drop掉的表
- DROP TABLE 恢复oracle中误删除drop掉的表
- Postgresql之无法删除表(drop table)
- db2 误用drop删除表和数据恢复方法
- Oracle Drop表并未直接删除 drop table xx purge
- Oracle Drop表并未直接删除 drop table xx purge
- Oracle drop 表以后空间无法回收
- oracle找回drop删除的表
- kaggle比赛模型融合指南
- C++11/C++14 (四)THE NULLPTR AND STRONG TYPED ENUMERATIONS
- Tablayout与viewpager联动
- 编程检验ASCII码值与字符对应关系 P11
- Java接口样例(使用注解方式)
- DB2中select查询某表期间无法drop删除该表;Oracle不然
- PHP知识点总结
- 2014 ECCV 行人检测文献综述 学习笔记
- MyBatis mapper文件中的变量引用方式#{}与${}的差别
- 数据的安全
- TRACE,ASSERT,VERIFY
- 空指针on a null object reference问题
- Java 基础练习之方法定义
- expected at least 1 bean which qualifies as autowire candidate for this depe错误的简单分析