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

来源：这里教程网时间：2026-03-03 20:42:03 作者：

[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 --//注：即使后面补了分号，打入;看到的内容没有后面的分号。