是这样
我drop table 的时候发现报
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01422: 实际返回的行数超出请求的行数所有的drop table 都报错然后
SQL> alter session set events '10046 trace name context forever,level 12';会话已更改。SQL> drop table test;
drop table test
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01422: 实际返回的行数超出请求的行数
SQL> alter session set events '10046 trace name context off';
tkprof后的trace文件是
TKPROF: Release 10.2.0.1.0 - Production on 星期五 7月 6 11:20:11 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.Trace file: C:\oraclexe\app\oracle\admin\XE\udump\xe_ora_7008.trc
Sort options: default********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************alter session set events '10046 trace name context forever,level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 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 1 0.00 0.00 0 0 0 0Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYSElapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************drop table test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.03 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.03 0 0 0 0Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYSElapsed 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 0.00 0.00
********************************************************************************BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) 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_owner, 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;call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.02 0 0 0 1Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27 (recursive depth: 1)
********************************************************************************SELECT USER_ID
FROM
ALL_USERS WHERE USERNAME = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.02 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.02 0 14 0 2Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27 (recursive depth: 2)
********************************************************************************select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 17 0.00 0.00 0 0 0 0
Execute 17 0.04 0.06 0 0 0 0
Fetch 17 0.00 0.00 0 34 0 17
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 51 0.04 0.06 0 34 0 17Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=43 us)********************************************************************************select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.03 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.03 0 4 0 0Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 4)Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=46 us)
0 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=39 us)(object id 257)********************************************************************************
我drop table 的时候发现报
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01422: 实际返回的行数超出请求的行数所有的drop table 都报错然后
SQL> alter session set events '10046 trace name context forever,level 12';会话已更改。SQL> drop table test;
drop table test
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01422: 实际返回的行数超出请求的行数
SQL> alter session set events '10046 trace name context off';
tkprof后的trace文件是
TKPROF: Release 10.2.0.1.0 - Production on 星期五 7月 6 11:20:11 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.Trace file: C:\oraclexe\app\oracle\admin\XE\udump\xe_ora_7008.trc
Sort options: default********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************alter session set events '10046 trace name context forever,level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 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 1 0.00 0.00 0 0 0 0Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYSElapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************drop table test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.03 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.03 0 0 0 0Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYSElapsed 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 0.00 0.00
********************************************************************************BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) 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_owner, 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;call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.02 0 0 0 1Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27 (recursive depth: 1)
********************************************************************************SELECT USER_ID
FROM
ALL_USERS WHERE USERNAME = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.02 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.02 0 14 0 2Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27 (recursive depth: 2)
********************************************************************************select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 17 0.00 0.00 0 0 0 0
Execute 17 0.04 0.06 0 0 0 0
Fetch 17 0.00 0.00 0 34 0 17
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 51 0.04 0.06 0 34 0 17Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=43 us)********************************************************************************select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.03 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.03 0 4 0 0Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 4)Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=46 us)
0 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=39 us)(object id 257)********************************************************************************
FROM
ALL_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND
(POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND V.FUNCTION =
:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.06 0.08 0 0 0 0
Fetch 2 0.00 0.00 0 64 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.06 0.08 0 64 0 2Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27 (recursive depth: 2)
********************************************************************************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'
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 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 6 0 1Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40 (recursive depth: 2)Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=133 us)
0 TABLE ACCESS BY INDEX ROWID RLS$ (cr=6 pr=0 pw=0 time=115 us)
2 NESTED LOOPS (cr=6 pr=0 pw=0 time=88 us)
1 NESTED LOOPS (cr=5 pr=0 pw=0 time=84 us)
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=42 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=31 us)(object id 44)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=33 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=14 us)(object id 37)
0 INDEX RANGE SCAN I_RLS2 (cr=1 pr=0 pw=0 time=7 us)(object id 440)********************************************************************************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'
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 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 6 0 1Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40 (recursive depth: 2)Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=113 us)
0 TABLE ACCESS BY INDEX ROWID RLS$ (cr=6 pr=0 pw=0 time=95 us)
2 NESTED LOOPS (cr=6 pr=0 pw=0 time=69 us)
1 NESTED LOOPS (cr=5 pr=0 pw=0 time=66 us)
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=28 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=16 us)(object id 44)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=32 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=13 us)(object id 37)
0 INDEX RANGE SCAN I_RLS2 (cr=1 pr=0 pw=0 time=7 us)(object id 440)********************************************************************************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(''' || dictionary_obj_name || '''); ' ||
'end;';
EXCEPTION
WHEN rdf_exception THEN RAISE;
WHEN OTHERS THEN NULL;
END;
end if;
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 1Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 32 (recursive depth: 1)
********************************************************************************select dummy
from
dual where ora_dict_obj_type = '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 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 32 (recursive depth: 1)Rows Row Source Operation
------- ---------------------------------------------------
2 FILTER (cr=3 pr=0 pw=0 time=113 us)
2 TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=40 us)********************************************************************************alter session set events '10046 trace name context off'
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 0Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.01 0.03 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.03 0 0 0 0Misses in library cache during parse: 2
Misses in library cache during execute: 1Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 43.07 55.53
SQL*Net break/reset to client 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 25 0.00 0.00 0 0 0 0
Execute 28 0.10 0.23 0 0 0 2
Fetch 26 0.00 0.00 0 131 0 24
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 79 0.10 0.23 0 131 0 26Misses in library cache during parse: 5
Misses in library cache during execute: 6 10 user SQL statements in session.
19 internal SQL statements in session.
29 SQL statements in session.
********************************************************************************
Trace file: C:\oraclexe\app\oracle\admin\XE\udump\xe_ora_7008.trc
Trace file compatibility: 10.01.00
Sort options: default 1 session in tracefile.
10 user SQL statements in trace file.
19 internal SQL statements in trace file.
29 SQL statements in trace file.
12 unique SQL statements in trace file.
519 lines in trace file.
55 elapsed seconds in trace file.
请问能看出来是什么问题吗