在11g到19c的迁移过程中发现一个sql语法方面的问题：

11.2.0.2 和19c分别创建测试表:

create table tt as select * from dba_objects;

create table t1 as select * from dba_objects;

分别执行下面的sql：

select A.B.object_name   FROM TT A LEFT JOIN T1 B ON B.object_id=A.object_id;

select Z.B.object_name   FROM TT A LEFT JOIN T1 B ON B.object_id=A.object_id;

11g下（11.2.0.2和11.2.0.4效果一样）：

SQL> set autotrace trace

SQL> 

SQL> select A.B.object_name   FROM TT A LEFT JOIN T1 B ON B.object_id=A.object_id;

71895 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3382560155

--------------------------------------------------------------------------------

| Id  | Operation    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |   | 62167 |  5585K|   |   941   (1)| 00:00:12 |

|*  1 |  HASH JOIN OUTER   |   | 62167 |  5585K|  1520K|   941   (1)| 00:00:12 |

|   2 |   TABLE ACCESS FULL| TT   | 62167 |   789K|   |   287   (1)| 00:00:04 |

|   3 |   TABLE ACCESS FULL| T1   | 67933 |  5240K|   |   287   (1)| 00:00:04 |

SQL> select Z.B.object_name   FROM TT A LEFT JOIN T1 B ON B.object_id=A.object_id;

71895 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3382560155

--------------------------------------------------------------------------------

| Id  | Operation    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |   | 62167 |  5585K|   |   941   (1)| 00:00:12 |

|*  1 |  HASH JOIN OUTER   |   | 62167 |  5585K|  1520K|   941   (1)| 00:00:12 |

|   2 |   TABLE ACCESS FULL| TT   | 62167 |   789K|   |   287   (1)| 00:00:04 |

|   3 |   TABLE ACCESS FULL| T1   | 67933 |  5240K|   |   287   (1)| 00:00:04 |

19c下执行：

SQL> set autotrace trace

SQL> select Z.B.object_name   FROM TT A LEFT JOIN T1 B ON B.object_id=A.object_id;

select Z.B.object_name FROM TT A LEFT JOIN T1 B ON B.object_id=A.object_id

       *

ERROR at line 1:

ORA-00904: "Z"."B"."OBJECT_NAME": invalid identifier

SQL> select A.B.object_name   FROM TT A LEFT JOIN T1 B ON B.object_id=A.object_id;

select A.B.object_name FROM TT A LEFT JOIN T1 B ON B.object_id=A.object_id

       *

ERROR at line 1:

ORA-00904: "A"."B"."OBJECT_NAME": invalid identifier

很明显，19c的语法检查比11g要严格了多，重点还是写sql的时候要严谨，不然还会遇到很多奇奇怪怪的问题。

目前暂时没有想到什么办法解决：

测试将compatible 改为11.2.0.4 （compatible不能降级，11g的库升级为19c）然后再执行sql

SQL> show parameter com

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

cell_offload_compaction      string ADAPTIVE

commit_logging      string

commit_point_strength      integer 1

commit_wait      string

commit_write      string

common_user_prefix      string C##

compatible      string 11.2.0.4.0

db_index_compression_inheritance     string NONE

inmemory_prefer_xmem_memcompress     string

nls_comp      string BINARY

noncdb_compatible      boolean FALSE

测试发现 也不行。