2010-12-30

Invalid SQL if hint is missing?

Recently Charles Hooper posted a blog An Invalid, or Do You Just Not Want to Work. There he asked under which circumstances a SQL should be considered invalid? I tried to discuss this question in the comments there and I am still thinking about an other kind of answer. But today I'd like to show a statement, which is invalid without a hint!

here we go:
(everything 11.2.0.1EE 64-bit on Linux)
I was asked to provide a list of all views which have a hint (how ironic) in it. After some test I provided this one:

select /*+ no_merge(vv) */ owner, view_name
from (select v.owner, v.view_name
from dba_views v, dba_objects o
where v.owner = o.owner
AND v.view_name = o.object_name
AND o.object_type='VIEW'
AND o.status='VALID'
AND v.owner ='PSFT' ) vv
where dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'

without the no_merge hint I get this error stack:

ERROR at line 9:
ORA-31603: object "AAB_TEST" of type VIEW not found in schema "PSFT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1


I did some explain plan with and without hints, so I got these 2 outputs:

SQL_ID bwsx4fryyu6u9, child number 0
-------------------------------------
select /*+ no_merge(vv) */ owner, view_name from (select v.owner,
v.view_name from dba_views v, dba_objects o where v.owner =
o.owner AND v.view_name = o.object_name AND o.object_type='VIEW'
AND o.status='VALID' AND v.owner ='PSFT' ) vv where
dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'

Plan hash value: 3436948868

------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:01:33.65 | 1088K| | | |
|* 1 | VIEW | | 1 | 1 | 14 |00:01:33.65 | 1088K| | | |
|* 2 | FILTER | | 1 | | 8802 |00:00:00.28 | 7432 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 8802 |00:00:00.25 | 7432 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 8802 |00:00:00.21 | 2469 | | | |
|* 5 | HASH JOIN | | 1 | 1 | 8802 |00:00:00.15 | 2464 | 1152K| 1152K| 1616K (0)|
| 6 | VIEW | DBA_OBJECTS | 1 | 26 | 8802 |00:00:00.07 | 2239 | | | |
| 7 | UNION-ALL | | 1 | | 8802 |00:00:00.07 | 2239 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | SUM$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 9 | INDEX UNIQUE SCAN | I_SUM$_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 10 | FILTER | | 1 | | 8802 |00:00:00.06 | 2239 | | | |
| 11 | NESTED LOOPS | | 1 | 1 | 8802 |00:00:00.05 | 2239 | | | |
| 12 | NESTED LOOPS | | 1 | 1 | 8802 |00:00:00.03 | 2236 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 14 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 1 | 8802 |00:00:00.03 | 2234 | | | |
|* 16 | INDEX RANGE SCAN | I_OBJ5 | 1 | 25 | 8803 |00:00:00.01 | 222 | | | |
|* 17 | INDEX RANGE SCAN | I_USER2 | 8802 | 1 | 8802 |00:00:00.02 | 3 | | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | IND$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 19 | INDEX UNIQUE SCAN | I_IND1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 20 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 21 | INDEX FULL SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 22 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 23 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 24 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 26 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 27 | INDEX RANGE SCAN | I_LINK1 | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
| 28 | NESTED LOOPS | | 1 | 2546 | 25853 |00:00:00.03 | 225 | | | |
| 29 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 30 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
|* 31 | INDEX RANGE SCAN | I_OBJ5 | 1 | 2546 | 25853 |00:00:00.01 | 223 | | | |
|* 32 | INDEX RANGE SCAN | I_USER2 | 8802 | 1 | 8802 |00:00:00.05 | 5 | | | |
|* 33 | INDEX UNIQUE SCAN | I_VIEW1 | 8802 | 1 | 8802 |00:00:00.03 | 4963 | | | |
| 34 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 35 | INDEX FULL SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 36 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("DBMS_METADATA"."GET_DDL"('VIEW',"VV"."VIEW_NAME","VV"."OWNER") LIKE '%/*+%')
2 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10
AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND
((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
5 - access("O"."NAME"="O"."OBJECT_NAME")
8 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
9 - access("S"."OBJ#"=:B1)
10 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
"O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
(INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR
("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL)))))
14 - access("U"."NAME"='PSFT')
15 - filter((DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND BITAND("O"."FLAGS",128)=0))
16 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,
'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATIO
N CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE
GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD
PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW'))
17 - access("O"."OWNER#"="U"."USER#")
18 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9))
19 - access("I"."OBJ#"=:B1)
21 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
22 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
23 - filter(NULL IS NOT NULL)
26 - access("U"."NAME"='PSFT')
27 - access("L"."OWNER#"="U"."USER#")
30 - access("U"."NAME"='PSFT')
31 - access("O"."SPARE3"="U"."USER#")
32 - access("O"."OWNER#"="U"."USER#")
33 - access("O"."OBJ#"="V"."OBJ#")
35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")


100 rows selected.

and

Plan hash value: 739349040

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 66 (2)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 107 | 66 (2)| 00:00:02 |
| 3 | NESTED LOOPS | | 1 | 102 | 66 (2)| 00:00:02 |
|* 4 | HASH JOIN | | 1 | 80 | 65 (2)| 00:00:01 |
| 5 | VIEW | DBA_OBJECTS | 26 | 624 | 40 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 26 | 0 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
| 10 | NESTED LOOPS | | 1 | 121 | 40 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 99 | 39 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 82 | 38 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | I_OBJ5 | 25 | | 23 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 20 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
|* 22 | FILTER | | | | | |
| 23 | NESTED LOOPS | | 2 | 54 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | I_LINK1 | 2 | 20 | 1 (0)| 00:00:01 |
| 27 | NESTED LOOPS | | 127 | 7112 | 24 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | I_OBJ5 | 127 | 4953 | 23 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | I_VIEW1 | 1 | 5 | 0 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 34 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$5947ACE7
5 - SET$1 / O@SEL$2
6 - SET$1
7 - SEL$8 / S@SEL$8
8 - SEL$8 / S@SEL$8
9 - SEL$DA86A24D
12 - SEL$DA86A24D / U@SEL$7
13 - SEL$DA86A24D / U@SEL$7
14 - SEL$DA86A24D / O@SEL$9
15 - SEL$DA86A24D / O@SEL$9
16 - SEL$DA86A24D / U@SEL$9
17 - SEL$12 / I@SEL$12
18 - SEL$12 / I@SEL$12
19 - SEL$11
20 - SEL$11 / U2@SEL$11
21 - SEL$11 / O2@SEL$11
22 - SEL$13
24 - SEL$13 / U@SEL$13
25 - SEL$13 / U@SEL$13
26 - SEL$13 / L@SEL$13
28 - SEL$5947ACE7 / U@SEL$3
29 - SEL$5947ACE7 / U@SEL$3
30 - SEL$5947ACE7 / O@SEL$4
31 - SEL$5947ACE7 / U@SEL$4
32 - SEL$5947ACE7 / V@SEL$3
33 - SEL$6
34 - SEL$6 / U2@SEL$6
35 - SEL$6 / O2@SEL$6

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
"O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
"O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
"O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
"U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
"O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
4 - access("O"."NAME"="O"."OBJECT_NAME")
7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
8 - access("S"."OBJ#"=:B1)
9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
"O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
"O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
"O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
"U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
"O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
13 - access("U"."NAME"='PSFT')
14 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
BITAND("O"."FLAGS",128)=0)
15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
"O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
(SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
16 - access("O"."OWNER#"="U"."USER#")
17 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
18 - access("I"."OBJ#"=:B1)
20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
ion_id')))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
ion_id')))
21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
22 - filter(NULL IS NOT NULL)
25 - access("U"."NAME"='PSFT')
26 - access("L"."OWNER#"="U"."USER#")
29 - access("U"."NAME"='PSFT')
30 - access("O"."SPARE3"="U"."USER#")
filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%/*+%')
31 - access("O"."OWNER#"="U"."USER#")
32 - access("O"."OBJ#"="V"."OBJ#")
34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
ion_id')))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
ion_id')))
35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30]
2 - (#keys=0) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
"O"."TYPE#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
"U"."SPARE2"[NUMBER,22]
3 - (#keys=0) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
"O"."TYPE#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
"U"."SPARE2"[NUMBER,22]
4 - (#keys=1) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
"O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
5 - "O"."OBJECT_NAME"[VARCHAR2,128]
6 - STRDEF[30], STRDEF[128], STRDEF[19], STRDEF[7]
8 - "S".ROWID[ROWID,10]
9 - "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
"O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
10 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
"O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22], "U"."TYPE#"[NUMBER,22],
"U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
11 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
"O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
12 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
13 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
14 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
"O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
15 - "SYS_ALIAS_4".ROWID[ROWID,10], "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22],
"O"."OWNER#"[NUMBER,22], "O"."OBJ#"[NUMBER,22]
16 - "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
18 - "I".ROWID[ROWID,10]
19 - (#keys=0)
20 - "U2"."USER#"[NUMBER,22]
22 - "U"."NAME"[VARCHAR2,30], "L"."NAME"[VARCHAR2,128]
23 - (#keys=0) "U"."NAME"[VARCHAR2,30], "L"."NAME"[VARCHAR2,128]
24 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
25 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
26 - "L"."NAME"[VARCHAR2,128]
27 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
"O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22]
28 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
29 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
30 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
"O"."TYPE#"[NUMBER,22]
31 - "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
33 - (#keys=0)
34 - "U2"."USER#"[NUMBER,22]

195 rows selected.


I did not came down what's really wrong in the 2nd case. But it is some of these effects where I'd blame the database more than the SQL for invalidity.

3 Kommentare:

Joel Garry hat gesagt…

Does the error go away if you grant select catalog role to PSFT?

I'm speculating the no merge keeps the access in the procedure which has sufficient privilege.

word: arncemb

Martin Berger hat gesagt…

Joel,
thank you for your comment.
I did not care about any permission of user PSFT as I run the statement connected as "/ as sysdba"
Also no security feature to limit SYS is enabled there.
So it's not an issue of grants.

Anonym hat gesagt…

How many rows are returned if you query SYS.TAB_STATS$ ?

I was able to execute both the hinted and unhinted versions of the SQL statement on 11.2.0.1 on 64 bit Windows when SYS.TAB_STATS$ contained 0 rows, and also when it contained 798 rows - see my blog article.