《Oracle中究竟能不能找到执行过的DDL?》曾尝试过找到刚执行过的DDL语句,其实还存在一个隐藏的问题,像这种create table建表语句,是可以在v$sql中找到,但是只存储了20个字符,如下所示,
SQL> select sql_text, length(sql_text) as total from v$sql where sql_text like '%create table%';
SQL_TEXT TOTAL
----------------------- -------
create table t01 as 20
这其实是Oracle的一个bug(17982832),而且是在11.2.0.4和12.1.0.1上出现的,可以通过patch或升级到12.2.0.1来解决,按照他所描述的,"CREATE or ALTER table commands became truncated to 20 bytes when viewed in some Vsql.sql_fulltext",create或alter子句,会自动截断到20个字符显示在v
另外,10046看到的SQL,都是截断的,
=====================
PARSING IN CURSOR #140563089936136 len=29 dep=0 uid=38 oct=1 lid=38 tim=1625731955432687 hv=1392146939 ad='6d1b60f8' sqlid='an553699gnygv'
create table t01 as
END OF STMT
PARSE #140563089936136:c=2000,e=11234,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1625731955432686
如果是sql_trace,
SQL> select 'TRACE FILE: '||VALUE FROM V$DIAG_INFO where NAME='Default Trace File';
'TRACEFILE:'||VALUE
--------------------------------------------------------------------------------
TRACE FILE: /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_ora_19062.trc
SQL> alter session set sql_trace =TRUE;
Session altered.
SQL> create table temp (id number);
Table created.
一样被截断了,
=====================
PARSING IN CURSOR #139769368366856 len=29 dep=0 uid=38 oct=1 lid=38 tim=1625732816772472 hv=1392146939 ad='6d1b60f8' sqlid='an553699gnygv'
create table temp (id number)
END OF STMT
PARSE #139769368366856:c=0,e=328,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1625732816772471
=====================
这是另外一个bug(18705302),同样可以通过patch或升级到12.2.0.1来解决,
下载并在线安装这两个patch,
opatch apply online -connectString TEST:sys:oracle
确认安装完成,
[oracle@bisal 17982832]$ opatch lsinv
...
Interim patches (1) :
Patch (online) 18705302: applied on Thu Jul 08 16:21:42 CST 2021
Unique Patch ID: 19102257
Created on 6 Jul 2015, 01:39:50 hrs PST8PDT
Bugs fixed:
18705302
Patch (online) 17982832: applied on Thu Jul 08 16:03:56 CST 2021
Unique Patch ID: 17805229
Created on 2 Jul 2014, 04:37:02 hrs PST8PDT
Bugs fixed:
17982832
此时无论是v$还是trace中记录的create/alter table就是准确的了。
其实除了以上两个场景,wrh$中同样可能出现截断的情况,还是通过patch或者升级到12.2.0.1解决,看来这种截断性的问题都在12.2中统一解决了,
近期更新的文章:
《感悟线上分享》
《小白学习MySQL - InnoDB支持optimize table?》
文章分类和索引: