« 年终难终 - 记年底前的几则数据恢复案例 | Blog首页 | 高Latch等待 - 'Object queue header operation' »

Delete sdo_geor_ddl__table$$ 的异常删除操作

在某客户的数据库跟踪中，发现如下程序块，对sdo_geor_ddl__table$$执行删除操作。

这是被Oracle Spatial 触发的一个后台操作，由触发器调用 SDO_GEOR_ERR_TRIGGER ，如果数据库不需要Spatial组件，建议不要安装，也可以禁用相关触发器或者写在Spatial选件。

********************************************************************************

declare
  m_stmt  varchar2(512);
begin
   m_stmt:='delete from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 52     (recursive depth: 1)
********************************************************************************

SQL ID: 3972rvxu3knn3

delete from sdo_geor_ddl__table$$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52     (recursive depth: 2)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   TABLE ACCESS FULL SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

SQL ID: cb21bacyh3c7d

select metadata
from
 kopm$  where name='DB_FDO'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=108 card=1)
         1          1          1   INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 540)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net break/reset to client                   2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       20.65         20.65
********************************************************************************

By eygle on 2011-12-27 08:00 | Comments (0) | Case | 2936 |

Case