[20241014]记录工作的一个错误--sql语句忘记写分号.txt

--//记录工作的一个错误--sql语句忘记写分号.txt.

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
$ cat a1.txt
                SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
                E.table_name as table_ref, f.column_name as column_ref,
                C.table_name
        FROM ALL_CONS_COLUMNS C
        inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
        left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
        left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position
        WHERE C.OWNER = 'SCOTT'
           and C.table_name = 'EMP'
           and D.constraint_type <> 'P'
        order by d.constraint_name, c.position
--//sql语句忘记写最后的分号.

SCOTT@book01p> @ a1.txt
SCOTT@book01p> @ a1.txt
SCOTT@book01p> @ a1.txt
--//感觉已经执行完成。
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  d675kjgr4d8p4, child number 0
-------------------------------------
select     to_char(sysdate, 'YYYYMMDD-HH24MISS') seminar_logfile   ,
instance_name||'-'||to_char(sysdate, 'YYYYMMDD-HH24MISS') tpt_tempfile
from v$instance

Plan hash value: 4175978637

----------------------------------------------------------------------------
| Id  | Operation              | Name        | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |        |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN  |             |      1 |    21 |     0   (0)|
|   2 |   MERGE JOIN CARTESIAN |             |      1 |    10 |     0   (0)|
|   3 |    MERGE JOIN CARTESIAN|             |      1 |       |     0   (0)|
|   4 |     FIXED TABLE FULL   | X$QUIESCE   |      1 |       |     0   (0)|
|   5 |     BUFFER SORT        |             |      1 |       |     0   (0)|
|   6 |      FIXED TABLE FULL  | X$KJIDT     |      1 |       |     0   (0)|
|   7 |    BUFFER SORT         |             |      1 |    10 |     0   (0)|
|*  8 |     FIXED TABLE FULL   | X$KSUXSINST |      1 |    10 |     0   (0)|
|   9 |   BUFFER SORT          |             |      1 |    11 |     0   (0)|
|* 10 |    FIXED TABLE FULL    | X$KVIT      |      1 |    11 |     0   (0)|
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$AF73C875
   4 - SEL$AF73C875 / "QU"@"SEL$5"
   6 - SEL$AF73C875 / "X$KJIDT"@"SEL$5"
   8 - SEL$AF73C875 / "KS"@"SEL$5"
  10 - SEL$AF73C875 / "KV"@"SEL$5"

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - filter((INTERNAL_FUNCTION("KS"."CON_ID") AND
              "KS"."INST_ID"=USERENV('INSTANCE')))
  10 - filter("KVITTAG"='kcbwst')

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
46 rows selected.
--//很明显执行内容来源tpt 的init.sql脚本。
--//当使用edit打开时，看到的情况：
                SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
                E.table_name as table_ref, f.column_name as column_ref,
                C.table_name
        FROM ALL_CONS_COLUMNS C
        inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
        left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
        left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position
        WHERE C.OWNER = 'SCOTT'
           and C.table_name = 'EMP'
           and D.constraint_type <> 'P'
        order by d.constraint_name, c.position
/

--//edit打开后自动在结尾加了/,在这里迷糊一下，感觉没有问题。保存后，要打入/才会执行。再次查看a1.txt文本才发现问题所在。
--//还有1个小技巧输入;可以看到前面执行的sql语句。
SCOTT@book01p> ;
  1                  SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
  2                  E.table_name as table_ref, f.column_name as column_ref,
  3                  C.table_name
  4          FROM ALL_CONS_COLUMNS C
  5          inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
  6          left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
  7          left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position
  8          WHERE C.OWNER = 'SCOTT'
  9             and C.table_name = 'EMP'
 10             and D.constraint_type <> 'P'
 11*         order by d.constraint_name, c.position
--//注：即使后面补了分号，打入;看到的内容没有后面的分号。