如果dual中的记录超过1条,就无法drop 其它表,为什么?
一下是本机测试数据
SQL> create table tb(id int);表已创建。SQL> drop table tb;表已删除。SQL> insert into dual values('a');已创建 1 行。提交完成。
SQL> create table tb(id int);表已创建。SQL> drop table tb;
drop table tb
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01422: 实际返回的行数超出请求的行数
SQL> delete from dual;已删除 1 行。提交完成。
SQL> drop table tb;表已删除。
一下是本机测试数据
SQL> create table tb(id int);表已创建。SQL> drop table tb;表已删除。SQL> insert into dual values('a');已创建 1 行。提交完成。
SQL> create table tb(id int);表已创建。SQL> drop table tb;
drop table tb
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01422: 实际返回的行数超出请求的行数
SQL> delete from dual;已删除 1 行。提交完成。
SQL> drop table tb;表已删除。
There is internalized code that makes this happen. Code checks that ensurethat a table scan of SYS.DUAL only returns one row. Svrmgrl behaviour is incorrect but this is now an obsolete product.
The base issue you should always remember and keep is: DUAL table should always have 1 ROW. Dual is a normal table with one dummy column of varchar2(1).
This is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other
prebuilt or application functions. If DUAL has no rows at all some applications (that use DUAL) may fail with NO_DATA_FOUND exception. If DUAL has more than 1 row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception.
So DUAL should ALWAYS have 1 and only 1 row
select dummy from dual where ora_dict_obj_type = 'TABLE';
多插入一条数据后,返回值就是
dummy
dummy
两行了。再看drop的后续操作,要受这个返回行数影响,所以报错。
不要随便更改DUAL表中的数据或结构
Sort options: default********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executingdisk = 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
********************************************************************************drop table tb
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.02 0 0 0 0Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
********************************************************************************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.03 0.02 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.02 0 0 0 1Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38 (recursive depth: 1)
********************************************************************************SELECT USER_ID
FROM
ALL_USERS WHERE USERNAME = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 14 0 2Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38 (recursive depth: 2)
********************************************************************************SELECT /*+ ALL_ROWS */ COUNT(*)
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 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 66 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 66 0 2Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38 (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.01 0 0 0 0
Fetch 1 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 6 0 1Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 2)Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=409 us)
0 TABLE ACCESS BY INDEX ROWID RLS$ (cr=6 pr=0 pw=0 time=352 us)
2 NESTED LOOPS (cr=6 pr=0 pw=0 time=236 us)
1 NESTED LOOPS (cr=5 pr=0 pw=0 time=238 us)
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=82 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=43 us)(object id 44)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=107 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=34 us)(object id 37)
0 INDEX RANGE SCAN I_RLS2 (cr=1 pr=0 pw=0 time=21 us)(object id 440)********************************************************************************select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.01 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.01 0.00 0 8 0 4Misses 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=37 us)********************************************************************************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.01 0 0 0 0
Fetch 1 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 6 0 1Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 2)Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=396 us)
0 TABLE ACCESS BY INDEX ROWID RLS$ (cr=6 pr=0 pw=0 time=339 us)
2 NESTED LOOPS (cr=6 pr=0 pw=0 time=225 us)
1 NESTED LOOPS (cr=5 pr=0 pw=0 time=230 us)
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=87 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=40 us)(object id 44)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=107 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=35 us)(object id 37)
0 INDEX RANGE SCAN I_RLS2 (cr=1 pr=0 pw=0 time=21 us)(object id 440)********************************************************************************
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: 46 (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: 46 (recursive depth: 1)Rows Row Source Operation
------- ---------------------------------------------------
2 FILTER (cr=3 pr=0 pw=0 time=168 us)
2 TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=56 us)********************************************************************************BEGIN dbms_system.set_sql_trace_in_session(134,1543,false); 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: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.01 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 0 0 0 1Misses in library cache during parse: 2
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 0.00 0.00 0 0 0 0
Execute 13 0.04 0.05 0 0 0 2
Fetch 11 0.00 0.00 0 103 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.04 0.06 0 103 0 13Misses in library cache during parse: 2
Misses in library cache during execute: 3 7 user SQL statements in session.
6 internal SQL statements in session.
13 SQL statements in session.
********************************************************************************
Trace file: test_ora_5860.trc
Trace file compatibility: 10.01.00
Sort options: default 1 session in tracefile.
7 user SQL statements in trace file.
6 internal SQL statements in trace file.
13 SQL statements in trace file.
10 unique SQL statements in trace file.
173 lines in trace file.
13 elapsed seconds in trace file.