oracle type 强制删除,请教TYPE为啥删除不了？

最新推荐文章于 2023-11-28 15:05:33 发布

张清北

最新推荐文章于 2023-11-28 15:05:33 发布

阅读量941

点赞数

文章标签： oracle type 强制删除

traveler99 发表于 2010-12-15 14:08 thread-1342021-1-1.html

终于解决了，方法是：跟踪会话，查看跟踪文件，发现会话在删除一张关联表时数据没有删除，手动删除，OK。不 ...

正在处理同样的问题，也发起了对会话SQL的跟踪，但并没有发现多少异常，楼主提供多些分析信息，谢谢！

如下为trace的内容：

PARSING IN CURSOR #8 len=31 dep=0 uid=0 oct=78 lid=0 tim=25083390794340 hv=0 ad='bf39fc08'

drop type SDE.ST_GEOMETRY force

END OF STMT

PARSE #8:c=0,e=598,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=25083390794333

=====================

PARSING IN CURSOR #9 len=652 dep=1 uid=39 oct=47 lid=39 tim=25083390794602 hv=3055982128 ad='fd789f38'

BEGIN

IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_ow

ner)) THEN

xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);

END IF;

EXCEPTION

WHEN OTHERS THEN

null;

END;

BEGIN

IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_ow

ner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN

xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);

END IF;

EXCEPTION

WHEN OTHERS THEN

null;

END;

END OF STMT

PARSE #9:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=25083390794599

EXEC #4:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390795010

FETCH #4:c=0,e=215,p=0,cr=7,cu=0,mis=0,r=1,dep=2,og=1,tim=25083390795250

EXEC #5:c=0,e=152,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390795487

FETCH #5:c=0,e=709,p=0,cr=62,cu=0,mis=0,r=1,dep=2,og=1,tim=25083390796217

EXEC #6:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390796300

FETCH #6:c=0,e=40,p=0,cr=8,cu=0,mis=0,r=1,dep=2,og=1,tim=25083390796360

=====================

PARSING IN CURSOR #10 len=173 dep=2 uid=74 oct=3 lid=74 tim=25083390796504 hv=2978679347 ad='fe958938'

SELECT count(*) FROM user_policies o WHERE o.object_name = :tablename AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE

'%$xd_%') AND o.function='CHECKPRIVRLS_SELECTPF'

END OF STMT

PARSE #10:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390796502

EXEC #10:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390796798

FETCH #10:c=0,e=152,p=0,cr=23,cu=0,mis=0,r=1,dep=2,og=1,tim=25083390796985

STAT #10 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=23 pr=0 pw=0 time=182 us)'

STAT #10 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=23 pr=0 pw=0 time=175 us)'

STAT #10 id=3 cnt=8 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=5 pr=0 pw=0 time=89 us)'

STAT #10 id=4 cnt=1 pid=3 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=37 us)'

STAT #10 id=5 cnt=1 pid=4 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=32 us)'

STAT #10 id=6 cnt=8 pid=3 pos=2 obj=438 op='TABLE ACCESS FULL RLS$ (cr=3 pr=0 pw=0 time=49 us)'

STAT #10 id=7 cnt=0 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=18 pr=0 pw=0 time=72 us)'

STAT #10 id=8 cnt=8 pid=7 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=10 pr=0 pw=0 time=42 us)'

EXEC #4:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390797252

FETCH #4:c=0,e=24,p=0,cr=7,cu=0,mis=0,r=1,dep=2,og=1,tim=25083390797295

EXEC #5:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390797377

FETCH #5:c=0,e=214,p=0,cr=26,cu=0,mis=0,r=1,dep=2,og=1,tim=25083390797612

EXEC #6:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390797673

FETCH #6:c=0,e=26,p=0,cr=8,cu=0,mis=0,r=1,dep=2,og=1,tim=25083390797717

=====================

PARSING IN CURSOR #11 len=176 dep=2 uid=74 oct=3 lid=74 tim=25083390797794 hv=3744356981 ad='fe958398'

SELECT count(*) FROM user_policies o WHERE o.object_name = :tablename AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE

'%$xd_%') AND o.function='CHECKPRIVRLS_SELECTPROPF'

END OF STMT

PARSE #11:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390797791

EXEC #11:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=25083390798002

FETCH #11:c=0,e=35,p=0,cr=5,cu=0,mis=0,r=1,dep=2,og=1,tim=25083390798069

STAT #11 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=5 pr=0 pw=0 time=52 us)'

STAT #11 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=5 pr=0 pw=0 time=46 us)'

STAT #11 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=5 pr=0 pw=0 time=43 us)'

STAT #11 id=4 cnt=1 pid=3 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=23 us)'

STAT #11 id=5 cnt=1 pid=4 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=15 us)'

STAT #11 id=6 cnt=0 pid=3 pos=2 obj=438 op='TABLE ACCESS FULL RLS$ (cr=3 pr=0 pw=0 time=19 us)'

STAT #11 id=7 cnt=0 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'

STAT #11 id=8 cnt=0 pid=7 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'

EXEC #9:c=10000,e=3465,p=0,cr=146,cu=0,mis=0,r=1,dep=1,og=1,tim=25083390798249

=====================

PARSING IN CURSOR #3 len=523 dep=1 uid=47 oct=47 lid=47 tim=25083390798378 hv=2706012741 ad='fe957700'

declare

stmt varchar2(200);

rdf_exception EXCEPTION; pragma exception_init(rdf_exception, -20000);

BEGIN

if dictionary_obj_type = 'USER' THEN

BEGIN

EXECUTE IMMEDIATE

'begin ' ||

'mdsys.rdf_apis_internal.' ||

'notify_drop_user( :don ); ' ||

'end;'

USING dbms_assert.QUALIFIED_SQL_NAME(dictionary_obj_name);

EXCEPTION

WHEN rdf_exception THEN RAISE;

WHEN OTHERS THEN NULL;

END;

end if;

end;

END OF STMT

PARSE #3:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=25083390798375

EXEC #3:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=25083390798610

=====================

PARSING IN CURSOR #7 len=58 dep=1 uid=47 oct=3 lid=47 tim=25083390798707 hv=1385655277 ad='fe956a58'

select dummy from dual where ora_dict_obj_type = 'TABLE'

END OF STMT

PARSE #7:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=25083390798705

EXEC #7:c=0,e=132,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=25083390798894

FETCH #7:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=25083390798918

STAT #7 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=115 us)'

STAT #7 id=2 cnt=0 pid=1 pos=1 obj=258 op='TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us)'

=====================

PARSING IN CURSOR #2 len=49 dep=1 uid=0 oct=3 lid=0 tim=25083390808856 hv=1758550401 ad='fd80e728'

select count(*) from association$ where obj# = :1

END OF STMT

PARSE #2:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=25083390808853

EXEC #2:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=25083390808967

FETCH #2:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=25083390809009

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1 pr=0 pw=0 time=24 us)'

STAT #2 id=2 cnt=1 pid=1 pos=1 obj=387 op='INDEX RANGE SCAN ASSOC1 (cr=1 pr=0 pw=0 time=17 us)'

=====================

PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=169 lid=0 tim=25083390809357 hv=3922716985 ad='fe49ab38'

disassociate statistics from TYPES SDE.ST_GEOMETRY force

END OF STMT

PARSE #2:c=0,e=262,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=25083390809354

=====================

PARSING IN CURSOR #3 len=46 dep=2 uid=0 oct=3 lid=0 tim=25083390809493 hv=2695128284 ad='fd3e7318'

select STATSTYPE# from USTATS$ where OBJ# = :1

END OF STMT

PARSE #3:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=25083390809490

EXEC #3:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=25083390809579

FETCH #3:c=0,e=29,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,tim=25083390809626

STAT #3 id=1 cnt=0 pid=0 pos=1 obj=389 op='TABLE ACCESS FULL USTATS$ (cr=3 pr=0 pw=0 time=29 us)'

=====================

PARSING IN CURSOR #9 len=40 dep=2 uid=0 oct=7 lid=0 tim=25083390809718 hv=2197275288 ad='e6a059a0'

delete from association$ where obj# = :1

END OF STMT

PARSE #9:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=25083390809715

EXEC #9:c=0,e=342,p=0,cr=1,cu=6,mis=0,r=1,dep=2,og=4,tim=25083390810124

=====================

PARSING IN CURSOR #11 len=179 dep=2 uid=0 oct=3 lid=0 tim=25083390810190 hv=715816513 ad='e4e7f9f8'

select u.obj#, u.intcol# from ustats$ u, coltype$ c, obj$ o where bitand(u.property, :1) = :2 AND o.oid$ = c.toid AND c.o

bj# = u.obj# AND c.intcol# = u.intcol# AND o.obj# = :3

END OF STMT

PARSE #11:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=25083390810187

EXEC #11:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=25083390810298

FETCH #11:c=0,e=91,p=0,cr=11,cu=0,mis=0,r=1,dep=2,og=4,tim=25083390810412

EXEC #2:c=0,e=1158,p=0,cr=17,cu=6,mis=0,r=0,dep=1,og=4,tim=25083390810567

ERROR #2:err=21700 tim=2568539219

EXEC #8:c=10000,e=16394,p=1,cr=170,cu=15,mis=0,r=0,dep=0,og=1,tim=25083390810796

ERROR #8:err=604 tim=2568539219

STAT #11 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=11 pr=0 pw=0 time=97 us)'

STAT #11 id=2 cnt=2 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=6 pr=0 pw=0 time=48 us)'

STAT #11 id=3 cnt=1 pid=2 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=23 us)'

STAT #11 id=4 cnt=1 pid=3 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=15 us)'

STAT #11 id=5 cnt=2 pid=2 pos=2 obj=389 op='TABLE ACCESS FULL USTATS$ (cr=3 pr=0 pw=0 time=22 us)'

STAT #11 id=6 cnt=1 pid=1 pos=2 obj=168 op='TABLE ACCESS BY INDEX ROWID COLTYPE$ (cr=5 pr=0 pw=0 time=45 us)'

STAT #11 id=7 cnt=1 pid=6 pos=1 obj=170 op='INDEX UNIQUE SCAN I_COLTYPE2 (cr=4 pr=0 pw=0 time=35 us)'

STAT #9 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE ASSOCIATION$ (cr=1 pr=0 pw=0 time=300 us)'

STAT #9 id=2 cnt=1 pid=1 pos=1 obj=387 op='INDEX RANGE SCAN ASSOC1 (cr=1 pr=0 pw=0 time=12 us)'

=====================

PARSING IN CURSOR #11 len=52 dep=0 uid=0 oct=47 lid=0 tim=25083390826331 hv=1029988163 ad='fef43d40'

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

END OF STMT

PARSE #11:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=25083390826328

EXEC #11:c=0,e=213,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=25083390826640

张清北

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫