[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也太多了.
--//总之规避的方法就是在结尾写分号之前不要留空格.
--//或者干脆不写分号,实际上对于这类语句写分号是多余的.
--//以前测试过,链接[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也太多了.
--//总之规避的方法就是在结尾写分号之前不要留空格.
--//或者干脆不写分号,实际上对于这类语句写分号是多余的.
