IT评测·应用市场-qidao123.com技术社区

标题: [20240818]测试21c下sqlplus show recyclebin的小问题2.txt [打印本页]

作者: 慢吞云雾缓吐愁 时间: 2024-8-20 23:33
标题: [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也太多了.
--//总之规避的方法就是在结尾写分号之前不要留空格.
--//或者干脆不写分号,实际上对于这类语句写分号是多余的.

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)