索引和表不在一个表空间导致无法删除表空间SQL0282N 错误

字数 19608阅读 3337评论 1赞 0

索引和表不在一个表空间导致无法删除表空间SQL0282N 错误
 一、故障现象 在删除老经分数据库的表空间时，删除表空间时报错： [DWE3:/db2home/db2inst1/fengsh]db2 "drop tablespaces tbs_ods" DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0282N  Table space "TBS_ODS" cannot be dropped because at least one of the tables in it, "PODS.T_ODS_MRK_TOTAL_M1110", has one or more of its parts in another table space.  SQLSTATE=55024   意思是PODS.T_ODS_MRK_TOTAL_M1110这个表在此表空间，而他至少有一或多个部分在其他表空间。   [DWE3:/db2home/db2inst1/fengsh]db2 ? SQL0282N 
SQL0282N Table space "<tablespace-name>" cannot be dropped           because at least one of the tables in it,           "<table-name>", has one or more of its parts in another           table space.  Explanation: A table in the specified table space does not contain all of its parts in that table space.  If more than one table spaces are specified, then a table in one of the specified table spaces does not contain all of its parts in the list.  The base table, indexes, or long data may be in another table space, so dropping the table space(s) will not completely drop the table.  This would leave the table in an inconsistent state and therefore the table space(s) cannot be dropped.  User Response: Ensure that all objects contained in table space "<tablespace-name>" contain all their parts in this table space before attempting to drop it, or include those table spaces containing the parts in the list to be dropped.   This may require dropping the table "<table-name>" before dropping the table space.   sqlcode :  -282  sqlstate :  55024 
检查确认： db2 "select tabschema,tabname,indschema,indname,a.TBSPACEID,TBSPACE from syscat.indexes a,syscat.tablespaces b where a.tbspaceid=b.tbspaceid and tabname='T_ODS_MRK_TOTAL_M1110'"  TABSCHEMA                                                                                                                        TABNAME                                                                                                                          INDSCHEMA                                                                                                                        INDNAME                                                                                                                          TBSPACEID   TBSPACE                                                                                                                         -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- PODS                                                                                                                             T_ODS_MRK_TOTAL_M1110                                                                                                            PODS                                                                                                                             SQL111103090750936                                                                                                                         6 TBS_IDX                                                                                                                           1 record(s) selected. 此表确实有一个索引建在TBS_IDX表空间。  
二、处理过程 捞出此表空间上所有表，将表删除，索引自动删除后，直接删除成功 select substr(tabschema,1,20) as tabschema,substr(tabname,1,60) as tabname,'db2 drop table'||rtrim(tabschema)||'.'||rtrim(tabname)||';' as sql from syscat.tables t1,syscat.tablespaces t2 where t1.tbspaceid=t2.tbspaceid and t2.tbspace='TBS_ODS' TABSCHEMA            TABNAME                                                      SQL                                                                                                                                                                                                                                                                             -------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PODS                 T_ODS_STL_SMS                                                db2 drop tablePODS.T_ODS_STL_SMS;                                                                                                                                                                                                                                               PODS                 T_ODS_STL_VOC                                                db2 drop tablePODS.T_ODS_STL_VOC;                                                                                                                                                                                                                                               PODS                 T_ODS_BAL_CDMA1X                                             db2 drop tablePODS.T_ODS_BAL_CDMA1X;                                                                                                                                                                                                                                             [DWE3:/db2home/db2inst1/fengsh]db2 "drop tablespace tbs_ods" DB20000I  The SQL command completed successfully. [DWE3:/db2home/db2inst1/fengsh]db2 commit DB20000I  The SQL command completed successfully.
 三、总结 此问题主要是由于删除的表空间上表和表创建的索引不在同一表空间，解决的方法主要有两种： 1）删除表空间上所有表，再删除表空间 2）同时删除数据表空间和索引所在表空间（我们的环境由于索引表空间共用，还有其他表空间上表也在使用此索引表空间，无法删除）
例如Salesidx 和user 表空间。 db2 "drop tablespaces Salesidx , user"
 删除 Salesidx <a href="mailto:e91q3a@TSGDB2:/home/db2users/e91q3a" target="_blank">e91q3a@TSGDB2:/home/db2users/e91q3a</A>> db2 drop tablespaces Salesidx DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0282N Table space "SALESIDX" cannot be dropped because at least one of the tables in it, "E91Q3A.AABB", has one or more of its parts in another table space. SQLSTATE=55024
 删除user 表空间 <a href="mailto:e91q3a@TSGDB2:/home/db2users/e91q3a" target="_blank">e91q3a@TSGDB2:/home/db2users/e91q3a</A>> db2 drop tablespaces user DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0282N Table space "USER" cannot be dropped because at least one of the tables in it, "E91Q3A.AABB", has one or more of its parts in another table space. SQLSTATE=55024
 
 <a href="mailto:e91q3a@TSGDB2:/home/db2users/e91q3a" target="_blank">e91q3a@TSGDB2:/home/db2users/e91q3a</A>> db2 "drop tablespaces Salesidx , user" DB20000I The SQL command completed successfully.

表

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论1 条评论

dengrp软件开发工程师Truly
2012-09-19 14:54

学习中！

Ctrl+Enter 发表

匿名评论

索引和表不在一个表空间导致无法删除表空间SQL0282N 错误

添加新评论1 条评论

作者其他文章

相关问题