[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也太多了.
--//總之規避的方法就是在結尾寫分號之前不要留空格.
--//或者乾脆不寫分號,實際上對於這類語句寫分號是多餘的.