SQL0290N表空间状态问题：停顿的独占处理
|
今天一个同事报告一个问题，表都不能使用了,检查了一下，发现 问题 db2 => select * from test ACTNO ACTKWD ACTDESC ------ ------ -------------------- SQL0290N Table space access is not allowed. SQLSTATE=55039 其他表也不能使用 在db2cc里查看表空间状态为： 停顿的独占 解决方法： 到命令行状态，首先connect 到需要处理的数据库 ◆1、db2 => list tablespaces show detail 显示表空间状态 Tablespaces for Current Database Tablespace ID = 0 Name = SYSCATSPACE Type = System managed space Contents = Any data State = 0x0000 Detailed explanation: Normal Total pages = 2519 Useable pages = 2519 Used pages = 2519 Free pages = Not applicable High water mark (pages) = Not applicable Page size (bytes) = 4096 Extent size (pages) = 32 Prefetch size (pages) = 32 Number of containers = 1 Tablespace ID = 1 Name = TEMPSPACE1 Type = System managed space Contents = System Temporary data State = 0x0000 Detailed explanation: Normal Total pages = 1 Useable pages = 1 Used pages = 1 Free pages = Not applicable High water mark (pages) = Not applicable Page size (bytes) = 4096 Extent size (pages) = 32 Prefetch size (pages) = 32 Number of containers = 1 Tablespace ID = 2 Name = USERSPACE1 Type = System managed space Contents = Any data State = 0x0004 这个代码意义就是“停顿的独占”，正常状态为0x0000， 非0就是有问题，都可以用下面方法解决。 Detailed explanation: Quiesced: EXCLUSIVE Total pages = 687 Useable pages = 687 Used pages = 687 Free pages = Not applicable High water mark (pages) = Not applicable Page size (bytes) = 4096 Extent size (pages) = 32 Prefetch size (pages) = 32 Number of containers = 1 Number of quiescers = 1 注意下面几行 Quiescer 1: Tablespace ID = 2 Object ID = 50 “object id是造成死锁表的id” ◆2、db2 => select tabname from syscat.tables where tableid=50 通过所住的id号找出是哪个表。 TABNAME -------------------------------------------- SYSINDEXEXTENSIONPARMS TEST 就是这个表 2 record(s) selected. ◆3、db2 => quiesce tablespaces for table test reset 执行该命令清除错误的状态 DB20000I The QUIESCE TABLESPACES command completed successfully. ◆4、重新select，问题解决 ◆5、比较常见导致改种问题的原因是，非正常的中止正对表进行的操作，导致对表空间的使用没有正常的释放 |
