[20240818]测试21c下sqlplus show recyclebin的小问题2.txt

[20240818]测试21c下sqlplus show recyclebin的小问题2.txt --//以前测试过,链接[20210722]sqlplus下show recycebin的小问题.txt --//注:recycebin 拼写错误 应该是 recyclebin. --//这个问题当时也是浪费了大量实际,我记忆遇到问题时是上午,执行show recyclebin ;[注空格+;],linux下sqlplus进程会直接崩溃. --//当时手头有其他事情,第二天测试一切正常,好在我使用rlwrap会写.sqlplus_history,这才发现问题. --//当时测试11g,看看21c情况如下. 1.环境: SCOTT@book01p> @pr ============================== 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.测试: SCOTT@book01p> create table tx as select * from dept ; Table created. SCOTT@book01p> create table ty as select * from dept ; Table created. SCOTT@book01p> show recyclebin SCOTT@book01p> drop table tx ; Table dropped. SCOTT@book01p> drop table ty ; Table dropped. SCOTT@book01p> show recyclebin ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME ---------------- ------------------------------ ------------ ------------------- TX               BIN$H8nC3h4CElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:43 TY               BIN$H8nC3h4DElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:46 --//但是后面测试出现的问题更加奇葩. 3.测试结果： $ rlsql scott/book@book01p SQL*Plus: Release 21.0.0.0.0 - Production on Sun Aug 18 09:37:20 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle.  All rights reserved. Last Successful login time: Sun Aug 18 2024 09:07:02 +08:00 Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SCOTT@book01p> show linesize linesize 269 SCOTT@book01p> show recyclebin    ; SCOTT@book01p> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------  551811298 b3vdwbwhf7y72            0     129250       360870971  20e3f8e2  2024-08-18 09:37:29    16777220 SCOTT@book01p> show recyclebin    ; ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME ---------------- ------------------------------ ------------ ------------------- TX               BIN$H8nC3h4CElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:43 TY               BIN$H8nC3h4DElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:46 SCOTT@book01p> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2786599706 c380v8fm1h8su            0       8986      1501318174  a618231a  2024-08-18 09:38:16    16777216 --//第1次显示没有输出,第2次显示有输出!!注意show recyclebin执行后面有空格+分号. --//注意看每次执行的sql_id不同. SCOTT@book01p> @ sql_id b3vdwbwhf7y72 --SQL_ID = b3vdwbwhf7y72 SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,        OBJECT_NAME OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,        DROPTIME DROPTIME_PLUS_SHOW_RECYC   FROM USER_RECYCLEBIN  WHERE CAN_UNDROP    = 'YES'    AND ORIGINAL_NAME = UPPER(:NMBIND_SHOW_OBJ)    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  ORDER BY ORIGINAL_NAME,DROPTIME DESC,OBJECT_NAME; SCOTT@book01p> @ sql_id c380v8fm1h8su --SQL_ID = c380v8fm1h8su SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,        OBJECT_NAME OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,        DROPTIME DROPTIME_PLUS_SHOW_RECYC   FROM USER_RECYCLEBIN  WHERE CAN_UNDROP = 'YES'  ORDER BY ORIGINAL_NAME,DROPTIME DESC,OBJECT_NAME; --//注意看下划线,第1次有带入参数:NMBIND_SHOW_OBJ,而第2次没有带入参数. SCOTT@book01p> @ bind_cap b3vdwbwhf7y72 '' SQL_ID        CHILD_NUMBER WAS NAME                             POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING INST_ID ------------- ------------ --- ------------------------------ ---------- ---------- ------------------- --------------- ------------ ------- b3vdwbwhf7y72            0 YES :NMBIND_SHOW_OBJ                        1         32 2024-08-18 09:37:29 VARCHAR2(32)    С?                1 --//注意看带入的值存在一个乱码.查询肯定没有这个表,自然第1次查询没有输出,而第2次没有带入参数.自然是全部输出. --//如果以前在11g是crash的化,21c问题视乎更严重,第1次不知道那里取了值,第2次不知道为什么选择没有参数的执行. SCOTT@book01p> show recyclebin  ; SCOTT@book01p> show recyclebin  ; ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME ---------------- ------------------------------ ------------ ------------------- TX               BIN$H8nC3h4CElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:43 TY               BIN$H8nC3h4DElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:46 SCOTT@book01p> show recyclebin  ; ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME ---------------- ------------------------------ ------------ ------------------- TX               BIN$H8nC3h4CElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:43 TY               BIN$H8nC3h4DElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:46 SCOTT@book01p> show recyclebin  ; ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME ---------------- ------------------------------ ------------ ------------------- TX               BIN$H8nC3h4CElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:43 TY               BIN$H8nC3h4DElHgY2U4qMBHSA==$0 TABLE        2024-08-16:16:29:46 SCOTT@book01p> show recyclebin  ; --//测试时有时候正常有时候异常,相同的执行方法结果可以完成不同. --//真是见鬼了.放弃探究. --//我使用windows 21c的sqlplus客户端测试: SCOTT@192.168.56.101:1521/book01p> show recyclebin ; --//每次都是没有输出!! SCOTT@192.168.56.101:1521/book01p> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PL ---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------  551811298 b3vdwbwhf7y72            0     129250       360870971  20e3f8e2  2024-08-18 11:38:39    16777220 SCOTT@192.168.56.101:1521/book01p> @ bind_cap b3vdwbwhf7y72 '' SQL_ID        CHILD_NUMBER WAS NAME                             POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING INST_ID ------------- ------------ --- ------------------------------ ---------- ---------- ------------------- --------------- ------------ ------- b3vdwbwhf7y72            0 YES :NMBIND_SHOW_OBJ                        1         32 2024-08-18 11:36:25 VARCHAR2(32)    ||to_char          1 --//但是抓取的绑定变量值明显存在问题. 5.总结: --//不想在上面在浪费时间,怎么感觉oracel的bug也太多了. --//总之规避的方法就是在结尾写分号之前不要留空格. --//或者干脆不写分号,实际上对于这类语句写分号是多余的.