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.
Posted by at
Labels: , ,

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

30. Dezember 2010 um 20:31
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.

30. Dezember 2010 um 22:18
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.

31. Dezember 2010 um 14:01

Kommentar veröffentlichen

Abonnieren Kommentare zum Post (Atom)