你是不是在PL /SQL DEV里面执行的查询?是不是没有全部展示出来,呵呵你试试这个验证下SELECT count(*) FROM RMS_MGW WHERE STATEFLAG=0 AND IS_WRONG=1 AND MGW_IS_MSC_RIGHT(RELATED_MSC, ',')=1;
你检查一下用户对这个表RMS_MGW的权限,试试用拥有dba权限的用户执行同样的语句。 看是不是使用了安全策略啊什么的。PS:发现个无关紧要的问题,既然查询条件中IS_WRONG=1,为什么设置时还是set IS_WRONG=1呢?? UPDATE RMS_MGW SET IS_WRONG=1 WHERE STATEFLAG=0 AND IS_WRONG=1 AND MGW_IS_MSC_RIGHT(RELATED_MSC, ',')=1;
alter session set statistics_level=ALL set serveroutput off SELECT WRONG_INFO, RELATED_MSC, MGW_IS_MSC_RIGHT(RELATED_MSC, ',') FROM RMS_MGW WHERE STATEFLAG=0 AND IS_WRONG=1 AND MGW_IS_MSC_RIGHT(RELATED_MSC, ',')=1; SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last')); UPDATE RMS_MGW SET IS_WRONG=1 WHERE STATEFLAG=0 AND IS_WRONG=1 AND MGW_IS_MSC_RIGHT(RELATED_MSC, ',')=1; SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));
[Quote=引用 15 楼 viszl 的回复:]167 rows selected
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 9m7787camwh4m, child number 2 begin :id := sys.dbms_transaction.local_transaction_id; end; NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 2 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_p
8 rows selected
0 rows updated
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 9m7787camwh4m, child number 2 begin :id := sys.dbms_transaction.local_transaction_id; end; NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 2 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_p
你是不是在PL /SQL DEV里面执行的查询?是不是没有全部展示出来,呵呵你试试这个验证下SELECT count(*)
FROM RMS_MGW
WHERE STATEFLAG=0
AND IS_WRONG=1
AND MGW_IS_MSC_RIGHT(RELATED_MSC, ',')=1;
你检查一下用户对这个表RMS_MGW的权限,试试用拥有dba权限的用户执行同样的语句。
看是不是使用了安全策略啊什么的。PS:发现个无关紧要的问题,既然查询条件中IS_WRONG=1,为什么设置时还是set IS_WRONG=1呢??
UPDATE RMS_MGW
SET IS_WRONG=1
WHERE STATEFLAG=0
AND IS_WRONG=1
AND MGW_IS_MSC_RIGHT(RELATED_MSC, ',')=1;
该不是你第一给表的数据不对吧create table test2 as select * from RMS_MGW;然后对 test2 执行一下语句,看结果是不是对的
是再同一个PLSQL窗口中执行的,RMS_MGW不是临时表。
1、请问怎么查看安全策略对语句的影响。
2、条件中的那个IS_WRONG=1是我测试的时候加上去得,去掉后问题依然,可以忽略。
对TEST2 执行语句,结果正常。
alter session set statistics_level=ALL
set serveroutput off
SELECT WRONG_INFO, RELATED_MSC, MGW_IS_MSC_RIGHT(RELATED_MSC, ',')
FROM RMS_MGW
WHERE STATEFLAG=0
AND IS_WRONG=1
AND MGW_IS_MSC_RIGHT(RELATED_MSC, ',')=1;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));
UPDATE RMS_MGW
SET IS_WRONG=1
WHERE STATEFLAG=0
AND IS_WRONG=1
AND MGW_IS_MSC_RIGHT(RELATED_MSC, ',')=1;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 2
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 2
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
8 rows selected
0 rows updated
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 2
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 2
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
8 rows selected