drop table 引出的问题之 ORA-21700: object does not exist or is marked for delete

最新推荐文章于 2022-11-14 22:53:19 发布

c.a.p

最新推荐文章于 2022-11-14 22:53:19 发布

阅读量1.2k

点赞数

分类专栏： Oracle 文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/renyanjie123/article/details/121472601

版权

Oracle 专栏收录该内容

31 篇文章 4 订阅

订阅专栏

应客户要求对确认不用的表drop掉，释放存储。
看到这个要求首先想到的是风险，没有想到接下来遇到的棘手问题。经过一系列查询之后还是对表进行删除。

SQL> truncate table  GIS.ALL_STRUCT_L_1;

ORA-04063: table "GIS.ALL_STRUCT_L_1" has error

SQL> drop table GIS.ALL_STRUCT_L_1;

ORA-21700: object does not exist or is marked for delete

看着这个报错愣了一秒钟，因为正常对表的ddl不会出现这种报错，我有检查了一下这个表名，确定没问题后，select查询也同样的错误，简直诡异。
那么直接删除用户是什么样呢？

SQL> drop user GIS cascade;
drop user GIScascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21700: object does not exist or is marked for delete

接下来对drop user 做一个10046分析一下问题
trace：

PARSING IN CURSOR #139947798506528 len=63 dep=2 uid=0 oct=3 lid=0 tim=1631181842442906 hv=2020575198 ad='13f68b37c8' sqlid='8km6u41w6z1yy'
==select STATSTYPE# from USTATS$ where OBJ# = :1 and INTCOL# = :2==
END OF STMT
PARSE #139947798506528:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3059727152,tim=1631181842442906
BINDS #139947798506528:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f4822d123a8  bln=22  avl=06  flg=05
  value=181095743
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f4822d123c0  bln=22  avl=02  flg=01
  value=15
EXEC #139947798506528:c=30,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3059727152,tim=1631181842443021
WAIT #139947798506528: nam='cell single block physical read' ela= 197 cellhash#=1085199203 diskhash#=2092294982 bytes=8192 obj#=443 tim=1631181842443285
FETCH #139947798506528:c=295,e=296,p=1,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=3059727152,tim=1631181842443327
STAT #139947798506528 id=1 cnt=1 pid=0 pos=1 obj=443 op='TABLE ACCESS BY INDEX ROWID USTATS$ (cr=2 pr=1 pw=0 time=295 us cost=1 size=17 card=1)'
STAT #139947798506528 id=2 cnt=1 pid=1 pos=1 obj=444 op='INDEX UNIQUE SCAN USTATS1 (cr=1 pr=0 pw=0 time=3 us cost=0 size=0 card=1)'
CLOSE #139947798506528:c=10,e=10,dep=2,type=1,tim=1631181842443398
=====================
PARSING IN CURSOR #139947798598296 len=65 dep=2 uid=0 oct=3 lid=0 tim=1631181842443435 hv=4055509184 ad='13e52a93e8' sqlid='6qd6t9zsvna60'
==select interface_version# from association$ where statstype# = :1==
END OF STMT
PARSE #139947798598296:c=27,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=785096182,tim=1631181842443435
BINDS #139947798598296:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f4822d123c0  bln=22  avl=06  flg=05
  value=117350737
EXEC #139947798598296:c=45,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=785096182,tim=1631181842443519
WAIT #139947798598296: nam='gc current block 2-way' ela= 131 p1=1 p2=2984 p3=4 obj#=440 tim=1631181842443731
WAIT #139947798598296: nam='gc current block 2-way' ela= 76 p1=1 p2=2985 p3=1 obj#=440 tim=1631181842443876
FETCH #139947798598296:c=370,e=370,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=785096182,tim=1631181842443901
STAT #139947798598296 id=1 cnt=0 pid=0 pos=1 obj=440 op='TABLE ACCESS STORAGE FULL ASSOCIATION$ (cr=2 pr=0 pw=0 time=371 us cost=2 size=24 card=3)'
CLOSE #139947798598296:c=6,e=6,dep=2,type=1,tim=1631181842443939
WAIT #139947798315368: nam='gc cr grant 2-way' ela= 49 p1=1 p2=71212 p3=1 obj#=523 tim=1631181842444078
WAIT #139947798315368: nam='cell single block physical read' ela= 220 cellhash#=1210432303 diskhash#=639361597 bytes=8192 obj#=523 tim=1631181842444346
EXEC #139947798129368:c=60313,e=91925,p=54,cr=771,cu=17,mis=0,r=0,dep=1,og=4,plh=0,tim=1631181842444406
>>>>  ERROR #139947798129368:err=21700 tim=1631181842444419
>>>>  WAIT #139947796552792: nam='enq: IV -  contention' ela= 432 type|mode=1230372869 id1=1280262987 id2=38 obj#=40 tim=1631181842445037
WAIT #139947796552792: nam='enq: IV -  contention' ela= 191 type|mode=1230372869 id1=1398361667 id2=38 obj#=40 tim=1631181842445318
EXEC #139947796552792:c=96341,e=165166,p=198,cr=1590,cu=24,mis=0,r=0,dep=0,og=1,plh=0,tim=1631181842445372
>>>>  ERROR #139947796552792:err=604 tim=1631181842445382
>>>>  CLOSE #139947799136952:c=3,e=3,dep=0,type=0,tim=1631181842445404
STAT #139947798349856 id=1 cnt=1 pid=0 pos=1 obj=444 op='INDEX RANGE SCAN USTATS1 (cr=1 pr=1 pw=0 time=360 us cost=1 size=10 card=1)'
CLOSE #139947798349856:c=19,e=19,dep=2,type=0,tim=1631181842445441
CLOSE #139947799140400:c=2,e=2,dep=2,type=0,tim=1631181842445455
CLOSE #139947798129368:c=48,e=48,dep=1,type=0,tim=1631181842445465
CLOSE #139947796544784:c=11,e=11,dep=1,type=0,tim=1631181842445485
CLOSE #139947795681768:c=1,e=2,dep=1,type=0,tim=1631181842445499
WAIT #139947796552792: nam='log file sync' ela= 329 buffer#=26344 sync scn=574118505 p3=0 obj#=40 tim=1631181842445969
WAIT #139947796552792: nam='SQL*Net break/reset to client' ela= 2 driver id=1650815232 break?=1 p3=0 obj#=40 tim=1631181842446009
WAIT #139947796552792: nam='SQL*Net break/reset to client' ela= 46 driver id=1650815232 break?=0 p3=0 obj#=40 tim=1631181842446067
WAIT #139947796552792: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=40 tim=1631181842446076

通过以上trace error那一部分发现，SQL在执行select interface_version# from association$ where statstype# = :1一步报错，那么我从数据库查询gis用户下的association发现没有任何值，陷入沉思，，，往上翻trace 发现有一个这样的操作： select STATSTYPE# from USTATS where OBJ# = :1 and INTCOL# = :2
其中的字段STATSTYPE#正好是下一步select的谓词，查了一下ustats,居然有值，思路开始陷入清晰，要把上班查询的值带入下边的association基表中，发现association$基表没有值，然后报错。
查询表结构同样也发现一个问题：

SQL> desc "GIS"."RMS_NODEBPOINT"
Name            Type           Nullable Default Comments 
--------------- -------------- -------- ------- -------- 
OBJECTID        INTEGER                                  
UNIQUE_KEY      NVARCHAR2(255) Y                         
NODEBFUNCTIONID NVARCHAR2(255) Y                         
USERLABEL       NVARCHAR2(255) Y                         
LONGITUDE       NVARCHAR2(255) Y                         
LATITUDE        NVARCHAR2(255) Y                         
RNC_UNIQUE_KEY  NVARCHAR2(255) Y                         
RNC_USER_LABEL  NVARCHAR2(255) Y                         
CITY_ID         NVARCHAR2(255) Y                         
CITY_NAME       NVARCHAR2(255) Y                         
ZHLABEL         NVARCHAR2(255) Y                         
VENDOR_ID       NVARCHAR2(255) Y                         
VENDOR_NAME     NVARCHAR2(255) Y                         
NODEB_CLASS     NVARCHAR2(255) Y                         
SHAPE           ==(256)==          Y

SHAPE列没有数据类型，结合刚才的trace可以初步判断shape字段的数据类型应该是自定义的数据类型，这个类型删除的时候检查不严格，导致类型在依赖的表之前被删掉，数据库字典表信息不一致。
分析到这里我们做一个大胆的猜想判断，是不是吧ustats$字典表里的obj信息delete掉表就可以正常删掉了。
这个假设风险极大，由于这是一个废弃的表空间且通过object_id删除，生产环境谨慎操作，即使这样我们仍然做了备份。

SQL> create table text_151783858 as select * from sys.ustats$ where obj#=151783858;
 
Table created

SQL> delete     from sys.ustats$ where obj#=151783858;
SQL> commit;
SQL> drop table INSPUR_GIS.RMS_NODEBPOINT purge;
 
Table dropped

发现表已经被删掉了，生产环境谨慎操作，仅供学习交流！！！