加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle 了解 DDL 操作与 REDO 的关系

发布时间：2020-12-12 13:30:59 所属栏目：百科 来源：网络整理
导读：目录 了解 DDL 操作与 REDO 的关系 DDL是否会产生REDO 通过 10046 trace 来分析create 和drop 如果drop失败，redo的变化 了解 DDL 操作与 REDO 的关系 DDL是否会产生REDO 用到的SQL： ---查看redo的大小SQL select b.name,a.value from v$mystat a,v$statnam

目录

  • 了解 DDL 操作与 REDO 的关系
    • DDL是否会产生REDO
    • 通过 10046 trace 来分析create 和drop
    • 如果drop失败，redo的变化

了解 DDL 操作与 REDO 的关系

DDL是否会产生REDO

用到的SQL：

---查看redo的大小
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                                      VALUE
-------------------------------------- ----------
redo size                                   0

---创建一个表，查看产生的redo大小

SQL> create table kyeup_tb1 as select * from v$datafile;

Table created.

SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                    VALUE
------------------ ----------
redo size               61072

---从上面看出创建表的时候redo大小为61072字节，那么删除这个表会产生redo多大呢？

SQL> drop table kyeup_tb1;

Table dropped.

SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                    VALUE
-----------------   ----------
redo size              101420

---drop表产生的redo大小：101420-61072= 40348

drop table 语句产生 bytes 的 redo 数据，少于 create table；

这里我们需要查看 DDL 语句执行过程。

通过 10046 trace 来分析create 和drop

可能是 create table 时 Oracle 需要向基表中 insert 数据，而 drop table时则需要delete/update 数据

我们下面用 10046 来跟踪一下 create table 与 drop table 到底做了哪些操作？

SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                                      VALUE
---------------------------------------------------------------- ----------
redo size                                 0

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55251.trc
SQL> oradebug event 10046 trace name context forever,level 1;
Statement processed.
SQL> create table kyeuptb1(id int,name varchar2(12));

Table created.

SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                                      VALUE
---------------------------------------------------------------- ----------
redo size                                  8880

---分析trace

[[email?protected] ~]# cat /oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55251.trc |egrep ‘insert|update|delete|create‘
create table kyeuptb1(id 
   m_stmt:=‘insert into sdo_geor_ddl__table$$ values (1)‘;
   m_stmt:=‘insert into sdo_geor_ddl__table$$ values (2)‘;
     m_stmt:=‘call mderr.raise_md_error(‘‘MD‘‘,‘‘SDO‘‘,-13391,‘‘GeoRaster reserved names cannot be used to create regular triggers.‘‘)‘;
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)
insert into seg$ (file#,block#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr,scanhint,bitmapranges) values (:1,DECODE(:17,NULL,:17),:18,:19)
insert into tab$(obj#,file#,bobj#,tab#,intcols,kernelcols,clucols,audit$,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,avgspc_flb,flbcnt,trigflag,spare6)values(:1,decode(:5,null,:5),decode(:6,:6),decode(:9,:9),:19,:20,:21,:22,:23,:24,:25,decode(:26,1,:26),decode(:27,:27),:28,:29,:30,:31,:32,:33)
insert into col$(obj#,intcol#,segcol#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,charsetid,charsetform,spare3)values(:1,182/*DTYIYM*/,183/*DTYIDS*/,decode(:7,:7)),2,decode(:8,-127/*MAXSB1MINAL*/,:8),178,179,180,181,182,183,231,null),decode(:12,:12),:20)
     m_stmt:=‘begin SDO_GEOR_UTL.createDMLTrigger(:1,:2); end;‘;
   m_stmt:=‘delete from sdo_geor_ddl__table$$ where id=2‘;
   m_stmt:=‘delete from sdo_geor_ddl__table$$‘;
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13,65535,:13),groups=decode(:14,:14),cachehint=:15,hwmincr=:16,spare1=DECODE(:17,scanhint=:18,bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3

---create表的时候进行了insert，update等操作，现在开始跟踪下drop表(退出来重新做)

SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                                      VALUE
---------------------------------------------------------------- ----------
redo size                                 0

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55296.trc
SQL> oradebug event 10046 trace name context forever,level 1;
Statement processed.
SQL> drop table kyeuptb1;

Table dropped.

SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                                      VALUE
---------------------------------------------------------------- ----------
redo size                                  8552

---drop产生的redo要比create产生的要少；分析trace

[[email?protected] ~]# cat /oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55296.trc |egrep ‘insert|update|delete|create‘
         ‘Need use delete_topo_geometry_layer() to deregister table ‘
   m_stmt:=‘insert into sdo_geor_ddl__table$$ values (1)‘;
   m_stmt:=‘insert into sdo_geor_ddl__table$$ values (2)‘;
     m_stmt:=‘call mderr.raise_md_error(‘‘MD‘‘,‘‘GeoRaster reserved names cannot be used to create regular triggers.‘‘)‘;
insert into sdo_geor_ddl__table$$ values (2)
select decode(u.type#,u.ext_username,u.name),o.name,t.update$,t.insert$,t.delete$,t.enabled,decode(bitand(t.property,8192),8192,0),65536),65536,131072),131072,(select o.name from obj$ o          where o.obj# = u.spare2 and o.type# =57)  from sys.obj$ o,sys.user$ u,sys.trigger$ t,sys.obj$ bo where t.baSEObject=bo.obj# and bo.name = :1 and bo.spare3 = :2  and bo.namespace = 1  and t.obj#=o.obj# and o.owner#=u.user#  and o.type# = 12 and bitand(property,16)=0 and bitand(property,8)=0  order by o.obj#
delete from object_usage where obj# in  (select a.obj# from object_usage a,ind$ b where  a.obj# = b.obj# and b.bo# = :1)
delete from sys.cache_stats_1$ where dataobj# = :1
delete com$ where obj#=:1
delete from hist_head$ where obj# = :1
delete from compression$ where obj#=:1
     m_stmt:=‘begin SDO_GEOR_UTL.createDMLTrigger(:1,:2); end;‘;
   m_stmt:=‘delete from sdo_geor_ddl__table$$ where id=2‘;
   m_stmt:=‘delete from sdo_geor_ddl__table$$‘;
delete from sdo_geor_ddl__table$$ where id=2
delete from col$ where obj#=:1
delete from icol$ where bo#=:1
delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)
delete from jijoin$ where obj# in ( select obj# from jijoin$ where tab1obj# = :1 or tab2obj# = :1)
delete from jirefreshsql$ where iobj# in ( select iobj# from jirefreshsql$ where tobj# = :1)
delete from ccol$ where obj#=:1
delete from ind$ where bo#=:1
delete from cdef$ where obj#=:1
delete ecol$ where tabobj# = :1
delete from tab$ where obj#=:1
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete coltype$ where obj#=:1
delete from subcoltype$ where obj#=:1
delete ntab$ where obj#=:1
delete lob$ where obj#=:1
delete refcon$ where obj#=:1
delete from opqtype$ where obj#=:1
delete from cdef$ where obj#=:1
delete from objauth$ where obj#=:1
delete from obj$ where obj# = :1
update seg$ set type#=:4,bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
delete from seg$ where ts#=:1 and file#=:2 and block#=:3

如果drop失败，redo的变化

SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                                      VALUE
---------------------------------------------------------------- ----------
redo size                                 0

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55343.trc
SQL> oradebug event 10046 trace name context forever,level 1;
Statement processed.
SQL> drop table kyeuptb111;
drop table kyeuptb111
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                                      VALUE
---------------------------------------------------------------- ----------
redo size                               384

SQL> create table aa;
create table aa
              *
ERROR at line 1:
ORA-00906: missing left parenthesis


SQL> select b.name,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;

NAME                                      VALUE
---------------------------------------------------------------- ----------
redo size                               384

SQL>

在create失败的时候不会产生redo，但是drop失败的时候是产生redo的(在删除的时候有insert into发生；

---分析如下

[[email?protected] ~]# cat /oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55343.trc |egrep ‘insert|update|delete|create‘
         ‘Need use delete_topo_geometry_layer() to deregister table ‘
   m_stmt:=‘insert into sdo_geor_ddl__table$$ values (1)‘;
   m_stmt:=‘insert into sdo_geor_ddl__table$$ values (2)‘;
     m_stmt:=‘call mderr.raise_md_error(‘‘MD‘‘,‘‘GeoRaster reserved names cannot be used to create regular triggers.‘‘)‘;
insert into sdo_geor_ddl__table$$ values (2)
create table aa 
[[email?protected] ~]#

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同