在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
测试发现 也不行。
