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.

GUI criticism

As a DBA, I'm not a big fan of Graphical User Interfaces.
From my point of view they too often hiding more information than they visualize.
As I am no GUI developer, please excuse me if I name some things not correct.

I'll show an example. Please take this screenshot. I do not own the tool myself but got this via email from a developer.


What can I see?
In the top left corner I see 3 tabs, the active one is Sessions. This shows me a table with one line highlighted (sid 160). Below that table, there is something I would assume as a sub-tab. Especially the borders of that tab makes me think so, as it's 'above' the 'Sessions' tab.
The sub-tab Waits is the active on. In it I see 2 Tables, Current Waits and Total Waits.
At the bottom I can find a line Last refresh. I'm glad I have any timestamp anywhere.

This is all really fine. But I'm still missing something:

• In general, I'd like to know the statement which generates the data I can see here. In the topmost table, I assume it will query v$session, v$process and some others to get the informations. But what's the query exactly? Even small changes can mean something different. Do you see the column CPU in the first table? I can not even imagine it's origin.


• Many tools provide the ability to spool all SQLs they generate into a file. But as they do not associate the SQL to the visual representation, it's still guesswork.


• I can not see which columns I do not see! Is the AUDSID important? Most of the time I'd say no. Until I have to deal with auditing
  


• I can not adapt the query according to my needs. In the Current Waits table - I assume based on v$session_wait I am often interested in the P1, P2 and P3 values (sometimes even raw - it depends). Sometimes also the change of SEQ# is of some interest. This rapidly forces me to go to the command line, or SQL-window if you want.
  

But there are also clever examples of some GUIs. The one I like most (as it helps both blind people and others) is Providing Textual Descriptions of Charts. It's still not the SQL, but at least the content of a graph in re-useable form.

I have only considered of any kind of query, yet. no DML or DDL operations at the moment. Maybe it's worth another post in the future ;-)

create database might fail with 11.2.0.2

I hit a nice bug at a test migration from 10.2.0.5 to 11.2.0.2.
As it's easier to convince Oracle Support of a problem if the testcase is just simple, a colleague reduced the testcase to


cretate a small pfile:

db_name=test
db_block_size=2048
db_create_file_dest=/tmp

and run

create database character set al32utf8;

the result is:

create database character set al32utf8
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/doptim.bsq' near line 416
ORA-00604: error occurred at recursive SQL level 1
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
Process ID: 16723
Session ID: 47 Serial number: 1

For Orale Support this really was enough to file Bug:10410249 towards development.

If you have a Database with blocksize of 2k and a multibyte characterset, beware!

So far enough for Oracle Support. Just to provide some more insights:
the upgrade failed at
@catupgrd.sql
with

SQL> begin
 2    dbms_stats.delete_table_stats('SYS', 'OBJ$MIG');
 3    dbms_stats.delete_table_stats('SYS', 'USER$MIG');
 4    dbms_Stats.gather_table_stats('SYS', 'OBJ$MIG',  estimate_percent => 100,
 5                                   method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
 6    dbms_Stats.gather_table_stats('SYS', 'USER$MIG', estimate_percent => 100,
 7                                   method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
 8  end;
 9  /
begin
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 2

I did not know why dbms_stats seems to be broken. So I tried to run catupgrd.sql again - same problem.

Next step: recompile dbms_stats manually.
at running

@prvtstas.plb

it showed me there was an error at line 2175.
If you are curious what's going on there:

2175 CURSOR GET_COL_GROUP_USAGE(OWNER VARCHAR2, TABNAME VARCHAR2) IS
2176 SELECT CU.OBJ# OBJN, CU.COLS,
2177    (CASE WHEN BITAND(CU.FLAGS, 1) = 1 THEN 'FILTER ' ELSE '' END) ||
2178    (CASE WHEN BITAND(CU.FLAGS, 2) = 2 THEN 'JOIN ' ELSE '' END) ||
2179    (CASE WHEN BITAND(CU.FLAGS, 4) = 4 THEN 'GROUP_BY ' ELSE '' END) USAGE,
2180    CU.FLAGS USAGEFLG
2181 FROM SYS.COL_GROUP_USAGE$ CU
2182 WHERE CU.OBJ# = (SELECT O.OBJ# FROM SYS.OBJ$ O, SYS.USER$ U
2183                     WHERE O.OWNER# = U.USER#
2184                       AND U.NAME = OWNER
2185                       AND O.NAMESPACE = 1
2186                       AND O.REMOTEOWNER IS NULL
2187                       AND O.LINKNAME IS NULL
2188                       AND O.SUBNAME IS NULL
2189                       AND O.TYPE# = 2
2190                       AND O.NAME = TABNAME)
2191   ORDER BY ...

I checked COL_GROUP_USAGE$ - but it did not exist!

Some grep in $ORACLE_HOME/rdbms/admin brought me to c1102000.sql
There I can read:

-- #(9577300) Column group usage
create table col_group_usage$
(
 obj#              number,                                 /* object number */
 /*
  * We store intcol# separated by comma in the following column.
  * We allow upto 32 (CKYMAX) columns in the group. intcol# can be
  * upto 1000 (or can be 64K in future or with some xml virtual columns?).
  * Assume 5 digits for intcol# and one byte for comma.
  * So max length would be 32 * (5+1) = 192
  */
 cols              varchar2(192 char),              /* columns in the group */
 timestamp         date,     /* timestamp of last time this row was changed */
 flags             number,                                 /* various flags */
 constraint        pk_col_group_usage$
 primary key       (obj#, cols))
 organization index
 storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/

As I tried to create that table manually, I recieved my ORA-01429.

We discussed some workarounds internally.
I created the IOT (without approval from Oracle Support) with cols varchar2(192 byte). Guess what, it worked! Also @catupgrd.sql was fine afterwards.
As I'm sure in this particular DB there are only column names with plain ASCII, this will not be an issue in this particular DB. But some other suggestions like creating a heap table or IOT without the cols as part of the index definition. This might result in performance problems of dbms_stats, but does not affect any logic (as my WA does).

UPDATE:
Patch:10410249 is provided for this issue now. It's labeled generic, as it contains only some sql files for $ORACLE_HOME/rdbms/admin and the proper patch (and rollback) scripts.

11.2.0.2 multicast fails totally

Recently Oracle released a new Patch Set: 11g Release 2 (11.2.0.2) Patch Set 1

In its README there is no word about multicast or the IP 230.0.1.0. It just contains a link to the Known Issues specific to the 11.2.0.2 Patch Set. Still no word there about multicast or the IP 230.0.1.0.
But why is it so important for me, more than it seems to be for Oracle?

Oracle has introduced a new feature in 11.2.0.2 Grid Infrastructure: It uses multicast communication on the cluster interlink for some purposes.
To be more precise, it uses the IP 230.0.1.0 by default for this purpose.

Multicast per se is not a bad thing at all, but it should be done right, not the Oracle way:

The IANA has defined a set of IP adresses to be used for multicast communication. All together this is the range from 224.0.0.0 to 239.255.255.255.
As 230.0.1.0 is within this range - everything fine? NO!
The block 225.0.0.0-231.255.255.255 is marked as RESERVED. And the IETF has a clear wording about these addresses:

Applications MUST NOT use addressing in the IANA reserved blocks.

It seems I'm not the first person who found out this issue. If you read
11.2.0.2 Grid Infrastructure Install or Upgrade may fail due to Multicasting Requirement [ID 1212703.1] you see the

Cause

The cause of this issue is CSSD being unable to establish network communication on the private interconnect network.  Assuming that Cluster Verify (cluvfy) has succeeded on all network checks or you are upgrading to 11.2.0.2 whereas the previous release was not experiencing communication issues, multicast not being enabled on the private network is a potential cause.

Oracle Grid Infrastructure 11.2.0.2 introduces new feature called Redundant Interconnect allowing for Oracle Supplied redundancy for the cluster interconnect.  With this new feature, multicast network communication on the private interconnect network is utilized on bootstrap to establish communication with peer nodes in the cluster, once communication is established network communication is then switched to unicast.  This mulitcast communication utilizes the 230.0.1.0 address (port 42424) on the private interconnect network.  Therefore multicast on the private interconnect network must be enabled and properly functioning on all cluster nodes for the mulitcast address 230.0.1.0 (port 42424).  Should multicast communication fail, the end result will be the inability for the node to join the cluster (as shown above in the symptoms). 

and a suggested

Solution

It has been found that the functionality of multicast on the 230.0.1.0 (port 42424) network address has been problematic with some network environments resulting in the issue stated above.  To address this issue Oracle has released Patch: 9974223 on top of 11.2.0.2 .  This patch makes use of the 224.0.0.251 (port 42424) multicast network address in addition to the 230.0.1.0 (port 42424) multicast address.  Multicast must be enabled on one of these two addresses to allow for Oracle Grid Infrastructure to successfully start on all cluster nodes.

Patch 9974223 has a size of 153M for Linux x86-64. but that's not a problem per se.

The real problem is the spirit of the patch: Instead of making the multicast IP configureable (by that it would be possible to use a dedicated IP address which was assigned for exactly that purpose) Oracle decided to use another IP: 224.0.0.251. So let's look for that IP in more detail: In the IANAs Document for multicast IPs 224.0.0.251 is defined for the use with mDNS - Multicast DNS.
Ok. Now we have at least an IP which is defined to be used with a protocol to announce available resources within a local network. But still, Oracle failed:
Now 224.0.0.251 (port 42424) is used. But this conflicts with the IETFs Document for Multicast DNS: There you can read:

When this document uses the term "Multicast DNS", it should be taken
to mean: "Clients performing DNS-like queries for DNS-like resource
records by sending DNS-like UDP query and response packets over IP
Multicast to UDP port 5353."

5353 != 42424

At the end my plea to Oracle is: Make the Multicast IP of a Cluster configurable by the customer! Follow existing standards! Many companies has different requirements regarding the usage of IPs - so any hard coded value will have disadvantages.

Update: I've just created SR:3-2347539801 for this - just out of curiosity.

Oracle joke

Sometimes it's really funny to read logfiles (and yes, some sarcasm helps a lot!)

my todays joke is this one:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
Fri Oct 29 11:28:04 2010
Block change tracking file is current.
Fri Oct 29 11:28:30 2010
Starting background process CTWR
Fri Oct 29 11:28:30 2010
CTWR started with pid=58, OS id=9457
Fri Oct 29 11:28:55 2010
ORA-1013 signalled during: ALTER DATABASE ENABLE BLOCK CHANGE TRACKING...
Stopping background process CTWR
Fri Oct 29 11:29:26 2010
Background process CTWR not dead after 30 seconds
Killing background process CTWR
Fri Oct 29 11:29:26 2010
PMON (ospid: 4322): terminating the instance due to error 487
Fri Oct 29 11:29:26 2010
ORA-1092 : opitsk aborting process
Fri Oct 29 11:29:26 2010

What was going here?

• Someone tried to enable Block Change Tracking.


• The process CTWR started.


• (for some reason) the DBA hit ^C before the operation finished.


• The instance tried to stop the CTWR.


• CTWR did not stop within 30 seconds - therefore the instance tried to kill it.


• PMON found CTWR dead (ORA-487 "CTWR process terminated with error"), considered this a critical issue and terminated the instance. (ORA-1092 "ORACLE instance terminated. Disconnection forced")

For me this is one if these bugs which are not worth investigating.

back to the future

Last month I walked through some boxes full of my old books at my parents house. There I also found my first school book about information technology. It was printed in 1990. So for me it covers almost the beginning of my life with IT.

To my surprise, the first page is not about bits, bytes or ICs. It's about communication:

With my todays background I scanned through the book and searched if there is anything about SQL or at least databases:
3 types of databases where mentioned there:

• hierarchical databases
• network databases
• relational databases

Especially relational databases where described as slow, but flexible ;-)

SQL was not mentioned within this book, but available database systems where listed:

dBASE III PLUS, Everyman, MS-R:Base, Informix, Dataflex (no Oracle or DB2, sorry).

As the first pichture in the book is still valueable, so is also the last:

Sometimes it's worth to look into his own history, and books I've read seem to be a valuable method. Many things have changed, some expectations did not come true. But it's worth to know about the own foundation.

debugging MOS

If you ever wanted to know a little bit more about the Flash Client of My Oracle Support, you can enable it's debug mode:

Hold down the CTRL key and click to Oracle logo.

I have limited use of these informations, but maybe someone with some more knowledge of Flash can use them?

are my resources in favored place?

On a 11gR1 Oracle Cluster I had an issue with some resources not in their favored place.
While investigating, I had to get the affected resources fast (at least faster than reading a huge list of resources manually and think if they match the rules).

This brought me to a small script:

crs_stat -f | awk ' BEGIN { FS="=" 
                            c=0}
/^NAME=/{n=$2}
/TYPE=/{t=$2}
/HOSTING_MEMBERS=/{hm=$2}
/PLACEMENT=/{p=$2}
/TARGET=/{g=$2}
/STATE=/{s=$2; 
  ch=s
  gsub("ONLINE on ", "",ch)
  if( (p=="favored") && !( index(hm,ch)>0 ) ) {
    printf("%-30s%-20s%-17s%-15s%-30s\n", n, " should be located on ", hm, " but has STATE: ", s) ;
    c+=1 ;
    }
} 
END { exit c }'

It prints out a line for every resource which has a favored placement, but currently is not on any HOSTING_MEMBERS.

I'm sure it's not the best awk-code, but it's fine for me.

To use it in other scripts (e.g. monitoring) I can use | wc -l to get the number of suboptimal located resources or just get the return value $?

developers joke

Yesterday evening I had to deal with the product of a really funny Oracle developer:
I had requested a CRS patch for Bug:8328904 under really high preassure (Management involved). With a target date of 3rd week of March 2010, I got the patch on 19-Mar-2010 about 2pm local time. Just in time!
So I had the fun to patch a test system on Sunday evening. As allways, it's not that easy as it sounds:
after a slow, but successful preparation of one node and a really easy patch, at restart of the CRS I got

/etc/init.d/init.crs: [[: not found

Ok. I knew this Patch was knitted with really hot needles, so after some checking we applied this diff:

 72c72
< if [[ "$CMD" = "init.crs" && "$PARENT_CMD" != "startpar" ]]; then
---
> if [ "$CMD" = "init.crs" ] && [ "$PARENT_CMD" != "startpar" ]; then

All the patching ended at Monday, 2am.

Of course, I updated my SR on Monday morning with this slightly detail.
The answer was overwhelming:

/etc/init.d/init.crs: [[: not found encountered while startup is a known issue and bug is already filed.( bug# 9131555).

The problem is with the 11.1.0.7. bundle 2 . Since the patch was created on top of 11.1.0.7 bundle 2 hence the same error got passed on. The workaround is correct and is same as mentioned in the above mentioned bug. So no problem it is a supported workaround.

What a funny developer!
delivering a patch for a escalated but just in time, delivering another known within it.

Who can tell me why i was not laughing?

Job, hobby or passion?

In my last post I summarized the answers I got on a mailing list for the short question:

what ist the fastest SQL you can create?

maybe the most remarkable answer was

I think y'all need a hobby- and one that doesn't include oracle!

Well, maybe I have to answer. Which is not that easy.
Let's try to confine a little bit: Let's start with Job. I see a job as a work which I get paid for. So i need a job to make a living.
At first view a hobby is a totally contradiction to a Job: I do it at my own will and possible even spend money to do it. A hobby is something I do to have fun.
But sometimes it's not that easy.
Even if job and hobby are a contradiction in money and choice, they are not imperatively a contradiction in fun.
Assume, you have a job which at the same time is fun. Why not enjoy this: instead of paying for fun, you get paid for! Maybe it's ok if I call it an avocation?
Of course, it's not that easy all the time, but nothing is that easy, not even a hobby ;-)
I see one more bonus in this situation: I assume I'm quite good in the tasks I get paid for. And at least at the moment I get a fair salary. I hope, both will at least stay at this level for a long time.

This all leads me to an answer:

I have a job in IT, especially Oracle. Why do I need a hobby?

fastest SQL?

Recently I asked what ist the fastest SQL you can create? on oracle-l mailing list.
It was a kind of challenge. But not a real clever one.
I was more interrested in the way people think and react to this question than in a particular SQL or result.

Here are some summaries for those who are interrested:
There where 33 replies (until now), these can be grouped by some categories (some mails belong to more than one):

select 1 from dual; was suggested by Niall Litchfield and Kevin Lidh.
Don Granaman showed an imprived version with a view on sys.v_dual, but Niall and Norman Dunbar argued, that's what fast_dual was introduced for.

Niall and Wolfgang Breitling asked for better specifications for the quest, which I left open by intention.

Another approach started with exit; by Daniel Fink. Robert Freeman called this not a SQL, but Grant Allen (--) and Jaromir D.B. Nemec ("empty statement") tried the same direction.
Job Miller suggested the qhole quest as a kind of trick question With the answer You look at the statement/business logic and decide that you don't need to run that statement at all. and Daniel gave a real world example for this effect.


Toon Koppelaars and Andre van Winssen both suggested query result cache. I tried to test this but could not find a suficient measurement. Brandon Allen raised the method by checking elapsed_time down to microseconds from v$sql/v$sqlstats.

Wolfgang did one step further with his client side cache.

A discussion about other products like purple and Mauve by Jack C. Applewhite, Christopher Boyle, John Piwowar and Jason Heinrich.

I think y'all need a hobby- and one that doesn't include oracle! by Kellyn Pedersen and Kathy Duret seems to be worth a seperate blog!

NOTE:Unident of disk

I just wondered where the lines

*** 2010-03-12 10:25:13.862
NOTE:Unident of disk:/appl/oracle/asm_disks/c3t60014380024D39280000A000039B0000d0s0_eva
NOTE:Unident of disk:/appl/oracle/asm_disks/c3t60014380024D39280000A00003A10000d0s0_eva

came from in my +ASMS744_rbal_3119.trc trace file every minute.

As i did not find any matches for NOTE:Unident of disk in MOS, I tried to open a SR there. Just in the preperation, I checked the status of this particular Disk:

select header_status, path 
from v$asm_disk 
where path like '%c3t60014380024D39280000A000039B0000d0s0%';

HEADER_STATUS PATH
------------- ------------------------------------------------------------------
CANDIDATE     /appl/oracle/asm_disks/c3t60014380024D39280000A000039B0000d0s0_eva

So I added these disks to a DiskGroup and the trace file did not grow any further.

I didn't found anything about Unident of disk in the docu, MOS or google. sadly.