SET LINESIZE 140 SET TRIMSPOOL ON SET PAGESIZE 1000 SET AUTOTRACE TRACEONLY EXPLAIN select owner, view_name from (select v.owner, v.view_name from dba_views v where v.owner ='SYS' ) vv where dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%TEST%'; 11.2.0.2 @ Linux 64-bit dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%TEST%' * ERROR at line 12: ORA-31603: object "/1000323d_DelegateInvocationHa" of type VIEW not found in schema "SYS" ORA-06512: at "SYS.DBMS_METADATA", line 4018 ORA-06512: at "SYS.DBMS_METADATA", line 5843 ORA-06512: at line 1 Plan hash value: 739763936 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 1680 | 63 (2)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | HASH JOIN | | 20 | 1680 | 63 (2)| 00:00:01 | | 3 | NESTED LOOPS | | 20 | 1260 | 61 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 287 | 16646 | 61 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 18 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | I_OBJ5 | 287 | 11480 | 60 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | I_VIEW1 | 1 | 5 | 0 (0)| 00:00:01 | | 9 | INDEX FULL SCAN | I_USER2 | 52 | 1092 | 1 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 | |* 11 | INDEX SKIP SCAN | I_USER2 | 1 | 19 | 1 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- 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_i d'))))) 2 - access("O"."OWNER#"="U"."USER#") 6 - access("U"."NAME"='SYS') 7 - access("O"."SPARE3"="U"."USER#") filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%TEST%') 8 - access("O"."OBJ#"="V"."OBJ#") 11 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur rent_edition_id'))) filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur rent_edition_id'))) 12 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") Note ----- - automatic DOP: skipped because of IO calibrate statistics are missing 11.2.0.1 @ Linux - 64bit ORA-31603: object "/1000323d_DelegateInvocationHa" of type VIEW not found in schema "SYS" 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 Plan hash value: 1104226585 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 1680 | 62 (2)| 00:00:02 | |* 1 | FILTER | | | | | | |* 2 | HASH JOIN | | 20 | 1680 | 62 (2)| 00:00:02 | | 3 | NESTED LOOPS | | 20 | 1260 | 60 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 286 | 16588 | 60 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 18 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | I_OBJ5 | 286 | 11440 | 59 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | I_VIEW1 | 1 | 5 | 0 (0)| 00:00:01 | | 9 | INDEX FULL SCAN | I_USER2 | 52 | 1092 | 1 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 | |* 11 | INDEX FULL SCAN | I_USER2 | 1 | 19 | 1 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- 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_i d'))))) 2 - access("O"."OWNER#"="U"."USER#") 6 - access("U"."NAME"='SYS') 7 - access("O"."SPARE3"="U"."USER#") filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%TEST%') 8 - access("O"."OBJ#"="V"."OBJ#") 11 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur rent_edition_id'))) filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur rent_edition_id'))) 12 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 11.1.0.7 @ Sol - 64bit ORA-31603: object "/1000323d_DelegateInvocationHa" of type VIEW not found in schema "SYS" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 3241 ORA-06512: at "SYS.DBMS_METADATA", line 4812 ORA-06512: at line 1 Plan hash value: 946651586 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 924 | 38 (3)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | HASH JOIN | | 11 | 924 | 38 (3)| 00:00:01 | | 3 | NESTED LOOPS | | 11 | 671 | 36 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 163 | 9128 | 36 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 17 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | I_OBJ5 | 163 | 6357 | 35 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | I_VIEW1 | 1 | 5 | 0 (0)| 00:00:01 | | 9 | INDEX FULL SCAN | I_USER2 | 62 | 1426 | 1 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 | |* 11 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_OBJ4 | 1 | 8 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- 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_i d'))))) 2 - access("O"."OWNER#"="U"."USER#") 6 - access("U"."NAME"='SYS') 7 - access("O"."SPARE3"="U"."USER#") filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%TEST%') 8 - access("O"."OBJ#"="V"."OBJ#") 11 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur rent_edition_id'))) filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur rent_edition_id'))) 12 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 10.2.0.4 @ Solaris - 64bit ORA-31603: object "CON$" of type VIEW not found in schema "SYS" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 2806 ORA-06512: at "SYS.DBMS_METADATA", line 4333 ORA-06512: at line 1 Plan hash value: 544415489 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 12 | 696 | 83 (4)| 00:00:02 | | 1 | NESTED LOOPS OUTER | | 12 | 696 | 83 (4)| 00:00:02 | | 2 | NESTED LOOPS | | 12 | 648 | 83 (4)| 00:00:02 | | 3 | NESTED LOOPS | | 163 | 7987 | 83 (4)| 00:00:02 | | 4 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 16 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL | OBJ$ | 163 | 5379 | 82 (4)| 00:00:02 | |* 7 | INDEX UNIQUE SCAN | I_VIEW1 | 1 | 5 | 0 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | I_TYPED_VIEW1 | 1 | 4 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("U"."NAME"='SYS') 6 - filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%TEST%' AND "O"."OWNER#"="U"."USER#") 7 - access("O"."OBJ#"="V"."OBJ#") 8 - access("O"."OBJ#"="T"."OBJ#"(+)) 9.2.0.8 @ Solaris - 64bit ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 653 ORA-06512: at "SYS.DBMS_METADATA", line 1260 ORA-06512: at line 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 NESTED LOOPS (OUTER) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'USER$' 5 4 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE) 6 3 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 7 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE) 8 2 INDEX (UNIQUE SCAN) OF 'I_TYPED_VIEW1' (UNIQUE) 9 1 INDEX (UNIQUE SCAN) OF 'I_VIEW1' (UNIQUE)