OGG —— flush sequence
一、背景
在OGG迁移工作中经常出现sequence不一致的情况，在官方文档中发现与sequence同步相关的命令：flush sequence；本文旨在测试OGG命令：flush sequence的作用与命令实际执行逻辑。
二、flush sequence测试
Flush sequence 使用的先决条件：
在初始化或者重新同步期间首次启动Extract 后立刻使用flush sequence；（flush sequence owner.sequence）
dblogin ；GLOBALS文件中使用GGSCHEMA参数指定安装sequence.sql 过程的模式；用于DDL支持;抽取、投递、应用进程中支持sequence同步
关闭抽取进程或者新配置进程链路：
源端：
1、创建sequence
DANA@hmb> create sequence sequencetest1
2 increment by 1
3 start with 1
4 maxvalue 99999
5 minvalue 1
6 cache 3;
Sequence created.
2、检查：
DANA@hmb> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST1’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------------------------------------------------------------------ ---------- -----------
SEQUENCETEST1 3 1
3、开启抽取进程
Dblogin
先不flush sequence dana.“SEQUENCETEST1”
（易错点： flush sequence dana.sequence1 虽然显示成功，但是是无效操作，bug：当序列名称指定为较低值时，序列不会更新，详细可见Doc ID 2654809.1，必须用双引号括起来且大写，即序列名称被指定为上限值并且用“”括起来，序列在数据库上更新。）
4、目标端：
检查：
DANA@pdb2> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST1’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
-------------------- ---------- -----------
SEQUENCETEST1 3 1
即sequence已经同步；且与源端一致；
5、源与目标执行nextval
DANA@hmb> select sequencetest1.nextval from dual;
NEXTVAL
----------
1
DANA@hmb> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST1’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER ------------------------------------------------------------------------------------------ ----------
SEQUENCETEST1 3 4
即执行一次nextval，直接获取cache三个值，last_number加了一个cache；
DANA@pdb2> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST1’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
-------------------- ---------- -----------
SEQUENCETEST1 3 5
此时目标端因为源端变化而同步，且以last_number改变为条件而同步，以
源库+cache=目标库 而目标库N last_number=目标库+1；
DANA@pdb2> select sequencetest1.nextval from dual;
NEXTVAL
----------
5
DANA@pdb2> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST1’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
-------------------- ---------- -----------
SEQUENCETEST1 3 8
控制变量发现，sequence同步的原理：以源库的last_number变化为标准，以cache为单位变化，源库+cache=目标库 而目标库N last_number=目标库+1；若源库的last_number没有变化，则目标库的last_number也不变化，但若在目标库查询一次nextval则last_number直接加cache；
小结：本意设计对比实验：没有执行flush sequence ：在目标端能看到表同步，但sequenc不存在，但是发现其实sequence是同步的，而查询MOS（MOS ID 1477974.1； ID 2654809.1）发现flush sequence的作用在于初始化或者开启抽取进程后没有同步sequence时使用，但是为什么这样子做以及作用如何未知。
为了探究flush sequence是否有刷新ogg两边sequence不一致的作用（本文的sequence一致并非强一致，只有目标端不小于源端即可），进一步测试；
为了比较和发现规律，新建了sequencetest2
多次执行flush sequence ；
GGSCI (hmb as goldengate@hmb) 18> flush sequence dana.“SEQUENCETEST2”
2021-12-30 11:21:15 INFO OGG-15311 Successfully flushed 1 sequence(s) dana.SEQUENCETEST2.
GGSCI (hmb as goldengate@hmb) 19> flush sequence dana.“SEQUENCETEST2”
2021-12-30 11:22:42 INFO OGG-15311 Successfully flushed 1 sequence(s) dana.SEQUENCETEST2.
GGSCI (hmb as goldengate@hmb) 20> flush sequence dana.“SEQUENCETEST2”
2021-12-30 11:23:00 INFO OGG-15311 Successfully flushed 1 sequence(s) dana.SEQUENCETEST2.
观察源库和目标库：
目标库：
DANA@hmb> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST2’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------------------------------------------------------------------ ---------- -----------
SEQUENCETEST2 3 9
DANA@hmb> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST2’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------------------------------------------------------------------ ---------- -----------
SEQUENCETEST2 3 10
DANA@hmb> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST2’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------------------------------------------------------------------ ---------- -----------
SEQUENCETEST2 3 11
目标库：
DANA@pdb2> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST2’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQUENCETEST2 3 12
DANA@pdb2>
DANA@pdb2>
DANA@pdb2> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST2’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQUENCETEST2 3 12
DANA@pdb2>
DANA@pdb2> select SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from user_sequences where SEQUENCE_NAME=‘SEQUENCETEST2’;
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQUENCETEST2 3 12
由于比较实验比较繁琐，不详细展示；直接得出结论：
Flush sequence 可以刷新源库的sequence，执行一次刷新一下，且将last_number值自动调大一个值，但这种设置调整的last_number的并不会触发sequence的同步，所以目标库没有变化；
三、定位flush sequence的本质操作
使用方法：oradebug event 10046
3.1关闭进程，flush sequence对源端影响
关闭进程，dblogin定位用户goldengate
详细操作如下：
1、关联 v$process和v$session 确定SPID
SYS@hmb> select P.PID,P.SPID,S.SID from v$process p,v$session s where P.ADDR=S.PADDR and S.username like ‘%GOL%’;
PID SPID SID ---------- ------------------------------------------------------------------------ ----------
30 99430 40
2、oradebug setospid
SYS@hmb> oradebug setospid 99430
Oracle pid: 30, Unix process pid: 99430, image: oracle@hmb
3、开启oradebug event 10046
SYS@hmb> oradebug event 10046 trace name context forever, level 12
Statement processed.
4、flush sequence
GGSCI (hmb as goldengate@hmb) 26> flush sequence dana.“SEQUENCETEST2”
2021-12-30 12:59:12 INFO OGG-15311 Successfully flushed 1 sequence(s) dana.SEQUENCETEST2.
5、关闭oradebug event 10046
SYS@hmb> oradebug event 10046 trace name context off
Statement processed.
6、获取trance文件
SYS@hmb> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/hmb/hmb/trace/hmb_ora_99430.trc
7、读取分析trance文件
[oracle@hmb ~]$ cat /u01/app/oracle/diag/rdbms/hmb/hmb/trace/hmb_ora_99430.trc
Trace file /u01/app/oracle/diag/rdbms/hmb/hmb/trace/hmb_ora_99430.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: hmb
Release: 2.6.32-696.el6.x86_64
Version: #1 SMP Tue Feb 21 00:53:17 EST 2017
Machine: x86_64
VM name: VMWare Version: 6
Instance name: hmb
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 99430, image: oracle@hmb
*** 2021-12-30 12:58:14.705
*** SESSION ID:(40.825) 2021-12-30 12:58:14.705
*** CLIENT ID:() 2021-12-30 12:58:14.705
*** SERVICE NAME:(hmb) 2021-12-30 12:58:14.705
*** MODULE NAME:(OGG-ORA_ADMIN) 2021-12-30 12:58:14.705
*** ACTION NAME:() 2021-12-30 12:58:14.705
Received ORADEBUG command (#1) ‘event 10046 trace name context forever, level 12’ from process 'Unix process pid: 102520, image: ’
*** 2021-12-30 12:58:14.776
Finished processing ORADEBUG command (#1) ‘event 10046 trace name context forever, level 12’
*** 2021-12-30 12:59:12.032
WAIT #139970243151920: nam=‘SQL*Net message from client’ ela= 5771524132 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640840352032716
=====================
PARSING IN CURSOR #139970242840784 len=59 dep=0 uid=89 oct=3 lid=89 tim=1640840352033907 hv=2303204956 ad=‘890e50b0’ sqlid=‘0arp5pf4nh7kw’
SELECT sys.dbms_assert.SCHEMA_NAME(UPPER(‘dana’)) FROM dual
END OF STMT
EXEC #139970242840784:c=743,e=743,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1640840352033905
=====================
PARSING IN CURSOR #139970242992656 len=112 dep=1 uid=89 oct=3 lid=89 tim=1640840352035181 hv=1811829377 ad=‘8b26a6c0’ sqlid=‘6f48339pzwnn1’
SELECT COUNT(*) FROM SYS.ALL_USERS WHERE NLSSORT(USERNAME, ‘NLS_SORT=BINARY’) = NLSSORT(:B1 , ‘NLS_SORT=BINARY’)
END OF STMT
BINDS #139970242992656:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=7f4d5c7f96a8 bln=32 avl=04 flg=05
value=“DANA”
EXEC #139970242992656:c=2302,e=55754,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1011760737,tim=1640840352090751
FETCH #139970242992656:c=844,e=877,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=1,plh=1011760737,tim=1640840352091784
CLOSE #139970242992656:c=2,e=2,dep=1,type=3,tim=1640840352091994
WAIT #139970242840784: nam=‘SQL*Net message to client’ ela= 40 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640840352092095
FETCH #139970242840784:c=4339,e=57823,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1640840352092122
WAIT #139970242840784: nam=‘SQL*Net message from client’ ela= 721 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640840352092899
=====================
PARSING IN CURSOR #139970242974048 len=88 dep=0 uid=89 oct=3 lid=89 tim=1640840352093063 hv=1701954558 ad=‘8913ab58’ sqlid=‘g1qqsxpkr3hzy’
SELECT COUNT(*) FROM dba_sequences WHERE sequence_owner = :1 and sequence_name = :2
END OF STMT
BINDS #139970242974048:
Bind#0
oacdty=96 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=64 off=0
kxsbbbfp=7f4d5c8264c8 bln=32 avl=04 flg=05
value=“DANA”
Bind#1
oacdty=96 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=32
kxsbbbfp=7f4d5c8264e8 bln=32 avl=13 flg=01
value=“SEQUENCETEST2”
EXEC #139970242974048:c=300,e=300,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3841320250,tim=1640840352093236
WAIT #139970242974048: nam=‘SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640840352093426
FETCH #139970242974048:c=173,e=173,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=3841320250,tim=1640840352093448
WAIT #139970242974048: nam=‘SQL*Net message from client’ ela= 1264 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640840352094783
=====================
PARSING IN CURSOR #139970243151920 len=59 dep=0 uid=89 oct=47 lid=89 tim=1640840352094939 hv=129564125 ad=‘8905cbd8’ sqlid=‘8jd6pd83vjzfx’
BEGIN goldengate .updateSequence (:1, :2, :3, :4, :5); END;
END OF STMT
BINDS #139970243151920:
Bind#0
oacdty=96 mxl=04(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=80 off=0
kxsbbbfp=7f4d5c8264b8 bln=04 avl=04 flg=05
value=“DANA”
Bind#1
oacdty=96 mxl=22(13) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=8
kxsbbbfp=7f4d5c8264c0 bln=22 avl=13 flg=01
value=“SEQUENCETEST2”
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=32
kxsbbbfp=7f4d5c8264d8 bln=22 avl=01 flg=01
value=0
Bind#3
oacdty=96 mxl=02(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=56
kxsbbbfp=7f4d5c8264f0 bln=02 avl=02 flg=01
value="’’"
Bind#4
oacdty=96 mxl=10(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=64
kxsbbbfp=7f4d5c8264f8 bln=10 avl=10 flg=01
value=“GOLDENGATE”
=====================
PARSING IN CURSOR #139970243136096 len=54 dep=1 uid=89 oct=3 lid=89 tim=1640840352096151 hv=3774346158 ad=‘852e2f90’ sqlid=‘05y50bvhggwxf’
SELECT COUNT(*) FROM SYS.DBA_USERS WHERE USERNAME=:B1
END OF STMT
BINDS #139970243136096:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=7f4d5c9c8d10 bln=32 avl=04 flg=05
value=“DANA”
EXEC #139970243136096:c=515,e=515,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=497759109,tim=1640840352096360
FETCH #139970243136096:c=3375,e=3821,p=0,cr=16,cu=0,mis=0,r=1,dep=1,og=1,plh=497759109,tim=1640840352100287
CLOSE #139970243136096:c=1,e=1,dep=1,type=3,tim=1640840352100407
=====================
PARSING IN CURSOR #139970241001792 len=135 dep=1 uid=89 oct=3 lid=89 tim=1640840352100651 hv=807489267 ad=‘892c2ed8’ sqlid=‘8r4hgg4s22mrm’
SELECT SEQUENCE_OWNER, SEQUENCE_NAME, LAST_NUMBER, CYCLE_FLAG FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER = :B2 AND SEQUENCE_NAME LIKE :B1
END OF STMT
BINDS #139970241001792:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=160 off=0
kxsbbbfp=7f4d5c9c8c90 bln=32 avl=04 flg=05
value=“DANA”
Bind#1
oacdty=01 mxl=128(100) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=0 off=32
kxsbbbfp=7f4d5c9c8cb0 bln=128 avl=13 flg=01
value=“SEQUENCETEST2”
EXEC #139970241001792:c=225,e=225,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=265299978,tim=1640840352100753
FETCH #139970241001792:c=89,e=89,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=1,plh=265299978,tim=1640840352100871
=====================
PARSING IN CURSOR #139970241132864 len=39 dep=1 uid=89 oct=42 lid=89 tim=1640840352101173 hv=1648071303 ad=‘0’ sqlid=‘958zjsxj3r4n7’
ALTER SESSION SET CURRENT_SCHEMA=“DANA”
END OF STMT
PARSE #139970241132864:c=116,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1640840352101173
EXEC #139970241132864:c=27,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1640840352101250
CLOSE #139970241132864:c=18,e=41,dep=1,type=0,tim=1640840352101310
XCTEND rlbk=0, rd_only=1, tim=1640840352102592
=====================
PARSING IN CURSOR #139970241132864 len=38 dep=1 uid=92 oct=14 lid=89 tim=1640840352103884 hv=2606497137 ad=‘8b3840f0’ sqlid=‘grpmnaudprzbj’
ALTER SEQUENCE “SEQUENCETEST2” NOCYCLE
END OF STMT
PARSE #139970241132864:c=2925,e=2493,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1640840352103883
=====================
PARSING IN CURSOR #139970243099976 len=5746 dep=2 uid=57 oct=47 lid=57 tim=1640840352104855 hv=127875610 ad=‘72ef7460’ sqlid=‘13t5kwh3tyfhu’
declare
TYPE attrs_cur IS REF CURSOR;
m_cur attrs_cur;
m_event varchar2(512);
m_user varchar2(512);
m_owner varchar2(512);
m_user1 varchar2(512);
m_type varchar2(512);
m_stmt varchar2(512);
m_name varchar2(5120);
m_column varchar2(5120);
m_cnt NUMBER;
m_stmt1 varchar2(512);
m_var varchar2(512);
m_o_stmt VARCHAR2(5120);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
m_stmt:=‘select sys.dbms_standard.dictionary_obj_type from dual’;
execute immediate m_stmt into m_type;
if(not (m_type=‘TABLE’ or m_type=‘TRIGGER’ or m_type=‘USER’ or m_type=‘TABLESPACE’))
then
return;
end if;
m_stmt:=‘select sys.dbms_standard.sysevent from dual’;
execute immediate m_stmt into m_event;
m_stmt:=‘select SYS_CONTEXT(’‘USERENV’’,’‘SESSION_USER’’) from dual’;
execute immediate m_stmt into m_user;
m_stmt:=‘select SYS_CONTEXT(’‘USERENV’’,’‘CURRENT_USER’’) from dual’;
execute immediate m_stmt into m_user1;
m_stmt:=‘select sys.dbms_standard.dictionary_obj_owner from dual’;
execute immediate m_stmt into m_owner;
m_stmt:=‘select sys.dbms_standard.dictionary_obj_name from dual’;
execute immediate m_stmt into m_name;
m_stmt:=‘select sdo_geor_def.getSqlText from dual’;
execute immediate m_stmt into m_o_stmt;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘ALTER’)
then
m_stmt:=‘select column_name from dba_tab_columns where owner=:1 and table_name=:2’;
open m_cur for m_stmt using m_owner,m_name;
loop
fetch m_cur into m_column;
exit when m_cur%NOTFOUND;
m_stmt:=‘select sdo_geor_def.isDropColumn(:1) from dual’;
execute immediate m_stmt into m_stmt1 using SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
if (trim(m_stmt1)=‘TRUE’)
then
m_stmt:=‘begin sdo_geor_def.doAlterDropColumn(:1,:2,:3); end;’;
execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
end if;
end loop;
end if;
if ((m_event=‘DROP’ and m_type=‘USER’) or (m_event=‘DROP’ and m_type=‘TABLESPACE’))
then
m_stmt:=‘insert into sdo_geor_ddl__table$$ values (1)’;
EXECUTE IMMEDIATE m_stmt;
commit;
end if;
if (m_event=‘DROP’ and m_type=‘TABLE’)
then
m_stmt:=‘select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2’;
EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;
if(m_cnt!=0)
then
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if((m_cnt=0)and (m_user!=‘SYS’ and m_user!=‘SYSTEM’ and m_user!='MDSYS’and m_owner!=‘MDSYS’ and m_owner!=‘SYS’))
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘The referenced raster data table(RDT) cannot be dropped.’’)’;
execute immediate m_stmt;
end if;
end if;
m_stmt:=‘insert into sdo_geor_ddl__table$$ values (2)’;
EXECUTE IMMEDIATE m_stmt;
commit;
end if;
if(m_user=‘SYS’ or m_user=‘SYSTEM’ or m_user=‘MDSYS’
or m_owner=‘MDSYS’ or m_owner=‘SYS’)
then
return;
end if;
if (m_event=‘RENAME’ and m_type=‘TABLE’)
then
m_stmt:=‘select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2’;
EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;
if(m_cnt!=0)
then
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘The referenced raster data table(RDT) cannot be renamed directly.’’)’;
execute immediate m_stmt;
end if;
end if;
end if;
if (m_type=‘TRIGGER’ and m_event=‘DROP’)
then
m_stmt:=‘select REGEXP_SUBSTR(:1,’‘GRDMLTR_.+’’,1,1,’‘i’’) from dual’;
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘GeoRaster DML triggers cannot be dropped.’’)’;
execute immediate m_stmt;
end if;
end if;
if (m_type=‘TRIGGER’ and m_event=‘ALTER’)
then
m_o_stmt:=upper(trim(m_o_stmt));
if(instr(m_o_stmt,’ COMPILE ‘)>0 or instr(m_o_stmt,’ ENABLE ')>0
or substr(m_o_stmt,length(m_o_stmt)-8,8)=’ COMPILE’ or substr(m_o_stmt,length(m_o_stmt)-7,7)=’ ENABLE’ )
then
return;
end if;
m_stmt:=‘select REGEXP_SUBSTR(:1,’‘GRDMLTR_.+’’,1,1,’‘i’’) from dual’;
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘GeoRaster DML triggers cannot be altered.’’)’;
execute immediate m_stmt;
end if;
end if;
if (m_type=‘TRIGGER’ and m_event=‘CREATE’)
then
m_stmt:=‘select REGEXP_SUBSTR(:1,’‘GRDMLTR_.+’’,1,1,’‘i’’) from dual’;
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘GeoRaster reserved names cannot be used to create regular triggers.’’)’;
execute immediate m_stmt;
end if;
end if;
Exception
when others then
if(sqlcode=-13391)
then
m_stmt:=sqlerrm;
m_stmt:=substr(m_stmt,11);
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391,’’’||m_stmt||’’’)’;
execute immediate m_stmt;
end if;
end;
END OF STMT
PARSE #139970243099976:c=98,e=99,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1640840352104855
=====================
PARSING IN CURSOR #139970241080208 len=54 dep=3 uid=57 oct=3 lid=57 tim=1640840352180187 hv=664851305 ad=‘8c1f0ff0’ sqlid=‘bcv9qynmu1nv9’
select sys.dbms_standard.dictionary_obj_type from dual
END OF STMT
PARSE #139970241080208:c=11826,e=74391,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=1,plh=1388734953,tim=1640840352180186
EXEC #139970241080208:c=24,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1388734953,tim=1640840352180284
FETCH #139970241080208:c=334,e=334,p=0,cr=0,cu=0,mis=0,r=1,dep=3,og=1,plh=1388734953,tim=1640840352180646
STAT #139970241080208 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)’
CLOSE #139970241080208:c=1,e=1,dep=3,type=3,tim=1640840352180815
EXEC #139970243099976:c=12645,e=75210,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=0,tim=1640840352180831
CLOSE #139970243099976:c=4,e=4,dep=2,type=3,tim=1640840352180859
=====================
PARSING IN CURSOR #139970241123232 len=129 dep=2 uid=0 oct=6 lid=0 tim=1640840352181356 hv=2635489469 ad=‘8c2ac468’ sqlid=‘4m7m0t6fjcs5x’
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
BINDS #139970241123232:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac800 bln=22 avl=02 flg=09
value=1
Bind#1
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac812 bln=22 avl=02 flg=09
value=1
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac824 bln=22 avl=04 flg=09
value=99999
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f4d5c9fa0d0 bln=22 avl=01 flg=05
value=0
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5c9fa0e8 bln=22 avl=01 flg=01
value=0
Bind#5
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac836 bln=22 avl=02 flg=09
value=3
Bind#6
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac848 bln=22 avl=02 flg=09
value=10
Bind#7
oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=8c2ac85a bln=32 avl=32 flg=09
value="--------------------------------"
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f4d5c9fa088 bln=22 avl=02 flg=05
value=8
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5c9fa0a0 bln=22 avl=04 flg=01
value=89877
WAIT #139970241123232: nam=‘Disk file operations I/O’ ela= 34 FileOperation=2 fileno=3 filetype=2 obj#=-1 tim=1640840352192251
EXEC #139970241123232:c=1322,e=11199,p=0,cr=1,cu=3,mis=0,r=1,dep=2,og=4,plh=1935744642,tim=1640840352192517
CLOSE #139970241123232:c=1,e=0,dep=2,type=3,tim=1640840352192575
BINDS #139970241123232:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac800 bln=22 avl=02 flg=09
value=1
Bind#1
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac812 bln=22 avl=02 flg=09
value=1
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac824 bln=22 avl=04 flg=09
value=99999
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f4d5c9fa0d0 bln=22 avl=01 flg=05
value=0
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5c9fa0e8 bln=22 avl=01 flg=01
value=0
Bind#5
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac836 bln=22 avl=02 flg=09
value=3
Bind#6
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac848 bln=22 avl=02 flg=09
value=10
Bind#7
oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=8c2ac85a bln=32 avl=32 flg=09
value="--------------------------------"
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f4d5c9fa088 bln=22 avl=02 flg=05
value=8
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5c9fa0a0 bln=22 avl=04 flg=01
value=89877
EXEC #139970241123232:c=204,e=204,p=0,cr=1,cu=2,mis=0,r=1,dep=2,og=4,plh=1935744642,tim=1640840352192846
CLOSE #139970241123232:c=0,e=0,dep=2,type=3,tim=1640840352192857
=====================
PARSING IN CURSOR #139970241118360 len=235 dep=2 uid=0 oct=6 lid=0 tim=1640840352192912 hv=159997841 ad=‘893df3f8’ sqlid=‘4yyb4104skrwj’
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
BINDS #139970241118360:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f4d5c9fa0d0 bln=22 avl=04 flg=05
value=89877
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5c9fa0e8 bln=22 avl=02 flg=01
value=6
Bind#2
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=8b2441c1 bln=07 avl=07 flg=09
value=“12/30/2021 10:53:14”
Bind#3
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=8b2441c8 bln=07 avl=07 flg=09
value=“12/30/2021 12:59:12”
Bind#4
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=8b2441cf bln=07 avl=07 flg=09
value=“12/30/2021 10:53:14”
Bind#5
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=72 off=0
kxsbbbfp=7f4d5c9fa070 bln=22 avl=02 flg=05
value=1
Bind#6
oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5c9fa088 bln=22 avl=00 flg=01
Bind#7
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=7f4d5c9fa0a0 bln=22 avl=01 flg=01
value=0
Bind#8
oacdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=32 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=72 off=0
kxsbbbfp=7f4d5c9fa010 bln=22 avl=02 flg=05
value=6
Bind#10
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5c9fa028 bln=22 avl=04 flg=01
value=65535
Bind#11
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=7f4d5c9fa040 bln=22 avl=02 flg=01
value=92
Bind#12
oacdty=01 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=8b2440ce bln=32 avl=13 flg=09
value=“SEQUENCETEST2”
Bind#13
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f4d5c9f9fe0 bln=22 avl=02 flg=05
value=1
EXEC #139970241118360:c=358,e=358,p=0,cr=3,cu=1,mis=0,r=1,dep=2,og=4,plh=2683643009,tim=1640840352193228
CLOSE #139970241118360:c=1,e=1,dep=2,type=3,tim=1640840352193246
=====================
PARSING IN CURSOR #139970241111984 len=3509 dep=2 uid=57 oct=47 lid=57 tim=1640840352193647 hv=1920321438 ad=‘893f0658’ sqlid=‘38spc81t7bjwy’
declare
TYPE attrs_cur IS REF CURSOR;
m_cur attrs_cur;
m_event varchar2(512);
m_user varchar2(512);
m_owner varchar2(512);
m_user1 varchar2(512);
m_type varchar2(512);
m_name varchar2(5120);
m_column varchar2(5120);
m_cnt NUMBER;
m_stmt varchar2(512);
m_ret varchar2(3000);
m_ret1 varchar2(512);
m_o_stmt VARCHAR2(5120);
begin
m_stmt:=‘select sys.dbms_standard.dictionary_obj_type from dual’;
execute immediate m_stmt into m_type;
if(not (m_type=‘TABLE’ or m_type=‘TRIGGER’ or m_type=‘USER’ or m_type=‘TABLESPACE’))
then
return;
end if;
m_stmt:=‘select sys.dbms_standard.sysevent from dual’;
execute immediate m_stmt into m_event;
m_stmt:=‘select SYS_CONTEXT(’‘USERENV’’,’‘SESSION_USER’’) from dual’;
execute immediate m_stmt into m_user;
m_stmt:=‘select sys.dbms_standard.login_user from dual’;
execute immediate m_stmt into m_user1;
m_stmt:=‘select sys.dbms_standard.dictionary_obj_owner from dual’;
execute immediate m_stmt into m_owner;
m_stmt:=‘select sys.dbms_standard.dictionary_obj_name from dual’;
execute immediate m_stmt into m_name;
if((instr(upper(m_name),‘MDRT_’)>0) and m_event=‘DROP’)
then
return;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘CREATE’)
then
m_stmt:=‘select sdo_geor_def.listAllGeoRasterFieldsStr(:1,:2) from dual’;
execute immediate m_stmt into m_ret using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name);
m_ret:=trim(m_ret);
while (length(m_ret)!=0) loop
if (instr(m_ret,’ $$__## ')!=0)
then
m_ret1:=trim(substr(m_ret,1,instr(m_ret,’ $$__## ')-1));
m_ret:=trim(substr(m_ret,instr(m_ret,’ $$__## ')+8));
else
m_ret1:=trim(m_ret);
m_ret:=’’;
end if;
m_stmt:=‘begin SDO_GEOR_UTL.createDMLTrigger(:1,:2); end;’;
execute immediate m_stmt using m_owner||’.’||m_name,SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME(m_ret1);
end loop;
return;
end if;
if (m_name!=‘MDSYS’ and m_type=‘USER’ and m_event=‘DROP’)
then
m_stmt:=‘call sdo_geor_def.doDropUserAndTable()’;
execute immediate m_stmt;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘DROP’)
then
m_stmt:=‘call sdo_geor_def.doDropUserAndTable()’;
execute immediate m_stmt;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘TRUNCATE’)
then
m_stmt:=‘call sdo_geor_def.doTruncateTable()’;
execute immediate m_stmt;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘ALTER’)
then
m_stmt:=‘call sdo_geor_def.doAlterRenameTable()’;
execute immediate m_stmt;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘RENAME’)
then
m_stmt:=‘call sdo_geor_def.doRenameTable()’;
execute immediate m_stmt;
end if;
if (m_event=‘DROP’ and m_type=‘TABLE’)
then
m_stmt:=‘delete from sdo_geor_ddl__table$$ where id=2’;
EXECUTE IMMEDIATE m_stmt;
end if;
if ((m_type=‘USER’ and m_event=‘DROP’) or (m_type=‘TABLESPACE’ and m_event=‘DROP’))
then
m_stmt:=‘delete from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt;
end if;
Exception
when others then
if(sqlcode=-13391)
then
m_stmt:=sqlerrm;
m_stmt:=substr(m_stmt,11);
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391,’’’||m_stmt||’’’)’;
execute immediate m_stmt;
end if;
end;
END OF STMT
PARSE #139970241111984:c=34,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1640840352193646
PARSE #139970241080208:c=4,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1388734953,tim=1640840352193999
EXEC #139970241080208:c=9,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1388734953,tim=1640840352194019
FETCH #139970241080208:c=59,e=59,p=0,cr=0,cu=0,mis=0,r=1,dep=3,og=1,plh=1388734953,tim=1640840352194097
CLOSE #139970241080208:c=1,e=1,dep=3,type=3,tim=1640840352194122
EXEC #139970241111984:c=240,e=240,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=0,tim=1640840352194129
CLOSE #139970241111984:c=3,e=3,dep=2,type=3,tim=1640840352194144
XCTEND rlbk=0, rd_only=1, tim=1640840352194159
EXEC #139970241132864:c=17512,e=90247,p=0,cr=5,cu=10,mis=0,r=0,dep=1,og=1,plh=0,tim=1640840352194177
CLOSE #139970241132864:c=1,e=1,dep=1,type=0,tim=1640840352194342
=====================
PARSING IN CURSOR #139970241110584 len=102 dep=2 uid=0 oct=3 lid=0 tim=1640840352194910 hv=3967354608 ad=‘8c3b87e0’ sqlid=‘axmdf8vq7k1rh’
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1
END OF STMT
BINDS #139970241110584:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f4d5c9c8d18 bln=22 avl=04 flg=05
value=89877
EXEC #139970241110584:c=115,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2203911306,tim=1640840352194977
FETCH #139970241110584:c=9,e=8,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=2203911306,tim=1640840352194997
CLOSE #139970241110584:c=1,e=1,dep=2,type=3,tim=1640840352195010
=====================
PARSING IN CURSOR #139970243093176 len=151 dep=2 uid=0 oct=3 lid=0 tim=1640840352195251 hv=4139184264 ad=‘8c3d5a00’ sqlid=‘2q93zsrvbdw48’
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
END OF STMT
BINDS #139970243093176:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f4d5c9c8d18 bln=22 avl=04 flg=05
value=89877
EXEC #139970243093176:c=150,e=149,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2874733959,tim=1640840352195332
FETCH #139970243093176:c=92,e=92,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=2874733959,tim=1640840352195434
CLOSE #139970243093176:c=1,e=1,dep=2,type=3,tim=1640840352195453
=====================
PARSING IN CURSOR #139970241082056 len=40 dep=1 uid=92 oct=3 lid=89 tim=1640840352195876 hv=1347272314 ad=‘891cc018’ sqlid=‘3tgyqw184vgmu’
SELECT “SEQUENCETEST2”.nextval FROM DUAL
END OF STMT
PARSE #139970241082056:c=1247,e=1508,p=0,cr=4,cu=0,mis=1,r=0,dep=1,og=1,plh=2297760174,tim=1640840352195875
EXEC #139970241082056:c=52,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2297760174,tim=1640840352195953
BINDS #139970241123232:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac800 bln=22 avl=02 flg=09
value=1
Bind#1
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac812 bln=22 avl=02 flg=09
value=1
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac824 bln=22 avl=04 flg=09
value=99999
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f4d5cb24630 bln=22 avl=01 flg=05
value=0
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5cb24648 bln=22 avl=01 flg=01
value=0
Bind#5
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac836 bln=22 avl=02 flg=09
value=3
Bind#6
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac848 bln=22 avl=02 flg=09
value=13
Bind#7
oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=8c2ac85a bln=32 avl=32 flg=09
value="--------------------------------"
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f4d5cb245e8 bln=22 avl=02 flg=05
value=8
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f4d5cb24600 bln=22 avl=04 flg=01
value=89877
EXEC #139970241123232:c=219,e=219,p=0,cr=1,cu=2,mis=0,r=1,dep=2,og=4,plh=1935744642,tim=1640840352196250
CLOSE #139970241123232:c=0,e=0,dep=2,type=3,tim=1640840352196262
FETCH #139970241082056:c=333,e=333,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=1,plh=2297760174,tim=1640840352196307
STAT #139970241082056 id=1 cnt=1 pid=0 pos=1 obj=89877 op=‘SEQUENCE SEQUENCETEST2 (cr=1 pr=0 pw=0 time=374 us)’
STAT #139970241082056 id=2 cnt=1 pid=1 pos=1 obj=0 op=‘FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)’
CLOSE #139970241082056:c=0,e=0,dep=1,type=3,tim=1640840352196390
=====================
PARSING IN CURSOR #139970241086296 len=85 dep=1 uid=92 oct=3 lid=89 tim=1640840352196516 hv=1743359171 ad=‘8530ff80’ sqlid=‘c00hfj5mym363’
SELECT LAST_NUMBER FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER=:B2 AND SEQUENCE_NAME=:B1
END OF STMT
BINDS #139970241086296:
Bind#0
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=7f4d5cb73e00 bln=32 avl=04 flg=09
value=“DANA”
Bind#1
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=7f4d5cb73e20 bln=32 avl=13 flg=09
value=“SEQUENCETEST2”
EXEC #139970241086296:c=162,e=162,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=265299978,tim=1640840352196566
FETCH #139970241086296:c=42,e=42,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=1,plh=265299978,tim=1640840352196622
CLOSE #139970241086296:c=1,e=1,dep=1,type=3,tim=1640840352196642
=====================
PARSING IN CURSOR #139970241132864 len=45 dep=1 uid=92 oct=42 lid=89 tim=1640840352196689 hv=4112858304 ad=‘0’ sqlid=‘653dv1mukag60’
ALTER SESSION SET CURRENT_SCHEMA=“GOLDENGATE”
END OF STMT
PARSE #139970241132864:c=32,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1640840352196689
EXEC #139970241132864:c=12,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1640840352196723
CLOSE #139970241132864:c=1,e=0,dep=1,type=0,tim=1640840352196732
CLOSE #139970241001792:c=1,e=1,dep=1,type=3,tim=1640840352196745
EXEC #139970243151920:c=28871,e=101933,p=0,cr=40,cu=13,mis=0,r=1,dep=0,og=1,plh=0,tim=1640840352196756
WAIT #139970243151920: nam=‘log file sync’ ela= 24333 buffer#=2717 sync scn=3870585 p3=0 obj#=118 tim=1640840352221105
WAIT #139970243151920: nam=‘SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640840352221188
*** 2021-12-30 12:59:44.797
Received ORADEBUG command (#2) ‘event 10046 trace name context off’ from process 'Unix process pid: 102520, image: ’
*** 2021-12-30 12:59:44.798
Finished processing ORADEBUG command (#2) ‘event 10046 trace name context off’
*** 2021-12-30 13:00:13.460
Received ORADEBUG command (#3) ‘tracefile_name’ from process 'Unix process pid: 102520, image: ’
*** 2021-12-30 13:00:13.460
Finished processing ORADEBUG command (#3) ‘tracefile_name’
3.2开启进程，flush sequence对目标端****影响
同里为了探索flush sequence对于开启抽取进程时对目标库的影响
先关闭进程，dblogin，确定psid，然后再开启进程
其余操作同上：
1、先dblogin 定位SPID
SYS@hmb> select P.PID,P.SPID,S.SID from v$process p,v$session s where P.ADDR=S.PADDR and S.username like ‘%GOL%’;
PID SPID SID
------------------------------------------------------------------------ ----------
21 102606 158
2、 oradebug setospid
SYS@hmb> oradebug setospid 102606
Oracle pid: 21, Unix process pid: 102606, image: oracle@hmb
3、开启抽取进程
start EXTDANA1
4、开启oradebug event 10046
SYS@hmb> oradebug event 10046 trace name context forever, level 12
Statement processed.
5、 flush sequence
GGSCI (hmb as goldengate@hmb) 9> flush sequence dana.“SEQUENCETEST2”
2021-12-30 13:19:53 INFO OGG-15311 Successfully flushed 1 sequence(s) dana.SEQUENCETEST2.
6、关闭oradebug event 10046
SYS@hmb> oradebug event 10046 trace name context off
Statement processed.
7、获取trace文件
SYS@hmb> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/hmb/hmb/trace/hmb_ora_102606.trc
8、读取并分析trace文件
[oracle@hmb ~]$ cat /u01/app/oracle/diag/rdbms/hmb/hmb/trace/hmb_ora_102606.trc
Trace file /u01/app/oracle/diag/rdbms/hmb/hmb/trace/hmb_ora_102606.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: hmb
Release: 2.6.32-696.el6.x86_64
Version: #1 SMP Tue Feb 21 00:53:17 EST 2017
Machine: x86_64
VM name: VMWare Version: 6
Instance name: hmb
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 102606, image: oracle@hmb
*** 2021-12-30 13:19:19.107
*** SESSION ID:(158.15157) 2021-12-30 13:19:19.107
*** CLIENT ID:() 2021-12-30 13:19:19.107
*** SERVICE NAME:(hmb) 2021-12-30 13:19:19.107
*** MODULE NAME:(OGG-ORA_ADMIN) 2021-12-30 13:19:19.107
*** ACTION NAME:() 2021-12-30 13:19:19.107
Received ORADEBUG command (#1) ‘event 10046 trace name context forever, level 12’ from process 'Unix process pid: 102598, image: ’
*** 2021-12-30 13:19:19.108
Finished processing ORADEBUG command (#1) ‘event 10046 trace name context forever, level 12’
*** 2021-12-30 13:19:53.805
WAIT #139847449049992: nam=‘SQL*Net message from client’ ela= 227434320 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640841593805050
=====================
PARSING IN CURSOR #139847449106632 len=59 dep=0 uid=89 oct=3 lid=89 tim=1640841593805381 hv=2303204956 ad=‘890e50b0’ sqlid=‘0arp5pf4nh7kw’
SELECT sys.dbms_assert.SCHEMA_NAME(UPPER(‘dana’)) FROM dual
END OF STMT
PARSE #139847449106632:c=150,e=150,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1640841593805381
EXEC #139847449106632:c=114,e=113,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1640841593805570
WAIT #139847449106632: nam=‘SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640841593805686
=====================
PARSING IN CURSOR #139847449251528 len=112 dep=1 uid=89 oct=3 lid=89 tim=1640841593806386 hv=1811829377 ad=‘8b26a6c0’ sqlid=‘6f48339pzwnn1’
SELECT COUNT(*) FROM SYS.ALL_USERS WHERE NLSSORT(USERNAME, ‘NLS_SORT=BINARY’) = NLSSORT(:B1 , ‘NLS_SORT=BINARY’)
END OF STMT
PARSE #139847449251528:c=119,e=119,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1011760737,tim=1640841593806385
BINDS #139847449251528:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=7f30c588c7f0 bln=32 avl=04 flg=05
value=“DANA”
EXEC #139847449251528:c=289,e=289,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1011760737,tim=1640841593806732
FETCH #139847449251528:c=195,e=195,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=1,plh=1011760737,tim=1640841593806961
STAT #139847449251528 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘SORT AGGREGATE (cr=6 pr=0 pw=0 time=206 us)’
STAT #139847449251528 id=2 cnt=1 pid=1 pos=1 obj=0 op=‘NESTED LOOPS (cr=6 pr=0 pw=0 time=154 us cost=3 size=29 card=1)’
STAT #139847449251528 id=3 cnt=1 pid=2 pos=1 obj=0 op=‘NESTED LOOPS (cr=4 pr=0 pw=0 time=135 us cost=2 size=26 card=1)’
STAT #139847449251528 id=4 cnt=1 pid=3 pos=1 obj=22 op=‘TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=31 us cost=1 size=23 card=1)’
STAT #139847449251528 id=5 cnt=1 pid=4 pos=1 obj=46 op=‘INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=17 us cost=0 size=0 card=1)’
STAT #139847449251528 id=6 cnt=1 pid=3 pos=2 obj=16 op=‘TABLE ACCESS CLUSTER TS$ (cr=2 pr=0 pw=0 time=101 us cost=1 size=3 card=1)’
STAT #139847449251528 id=7 cnt=1 pid=6 pos=1 obj=7 op=‘INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=9 us cost=0 size=0 card=1)’
STAT #139847449251528 id=8 cnt=1 pid=2 pos=2 obj=16 op=‘TABLE ACCESS CLUSTER TS$ (cr=2 pr=0 pw=0 time=11 us cost=1 size=3 card=1)’
STAT #139847449251528 id=9 cnt=1 pid=8 pos=1 obj=7 op=‘INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=1 us cost=0 size=0 card=1)’
CLOSE #139847449251528:c=1,e=1,dep=1,type=3,tim=1640841593807227
FETCH #139847449106632:c=1443,e=1540,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1640841593807253
STAT #139847449106632 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘FAST DUAL (cr=0 pr=0 pw=0 time=5 us cost=2 size=0 card=1)’
WAIT #139847449106632: nam=‘SQL*Net message from client’ ela= 671 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640841593807989
=====================
PARSING IN CURSOR #139847449232496 len=88 dep=0 uid=89 oct=3 lid=89 tim=1640841593808213 hv=1701954558 ad=‘8913ab58’ sqlid=‘g1qqsxpkr3hzy’
SELECT COUNT(*) FROM dba_sequences WHERE sequence_owner = :1 and sequence_name = :2
END OF STMT
PARSE #139847449232496:c=147,e=147,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3841320250,tim=1640841593808213
BINDS #139847449232496:
Bind#0
oacdty=96 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=64 off=0
kxsbbbfp=7f30c5897940 bln=32 avl=04 flg=05
value=“DANA”
Bind#1
oacdty=96 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=32
kxsbbbfp=7f30c5897960 bln=32 avl=13 flg=01
value=“SEQUENCETEST2”
EXEC #139847449232496:c=193,e=192,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3841320250,tim=1640841593808463
WAIT #139847449232496: nam=‘SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640841593808527
FETCH #139847449232496:c=73,e=73,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=3841320250,tim=1640841593808621
STAT #139847449232496 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘SORT AGGREGATE (cr=6 pr=0 pw=0 time=160 us)’
STAT #139847449232496 id=2 cnt=1 pid=1 pos=1 obj=0 op=‘NESTED LOOPS (cr=6 pr=0 pw=0 time=149 us cost=3 size=56 card=1)’
STAT #139847449232496 id=3 cnt=1 pid=2 pos=1 obj=0 op=‘NESTED LOOPS (cr=5 pr=0 pw=0 time=138 us cost=3 size=51 card=1)’
STAT #139847449232496 id=4 cnt=1 pid=3 pos=1 obj=22 op=‘TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=20 us cost=1 size=18 card=1)’
STAT #139847449232496 id=5 cnt=1 pid=4 pos=1 obj=46 op=‘INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=11 us cost=0 size=0 card=1)’
STAT #139847449232496 id=6 cnt=1 pid=3 pos=2 obj=37 op=‘INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=36 us cost=2 size=33 card=1)’
STAT #139847449232496 id=7 cnt=1 pid=2 pos=2 obj=79 op=‘INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=7 us cost=0 size=5 card=1)’
WAIT #139847449232496: nam=‘SQL*Net message from client’ ela= 559 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640841593809308
=====================
PARSING IN CURSOR #139847449275800 len=59 dep=0 uid=89 oct=47 lid=89 tim=1640841593809607 hv=129564125 ad=‘8905cbd8’ sqlid=‘8jd6pd83vjzfx’
BEGIN goldengate .updateSequence (:1, :2, :3, :4, :5); END;
END OF STMT
PARSE #139847449275800:c=245,e=245,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1640841593809606
BINDS #139847449275800:
Bind#0
oacdty=96 mxl=04(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=80 off=0
kxsbbbfp=7f30c58baf78 bln=04 avl=04 flg=05
value=“DANA”
Bind#1
oacdty=96 mxl=22(13) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=8
kxsbbbfp=7f30c58baf80 bln=22 avl=13 flg=01
value=“SEQUENCETEST2”
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=32
kxsbbbfp=7f30c58baf98 bln=22 avl=01 flg=01
value=0
Bind#3
oacdty=96 mxl=02(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=56
kxsbbbfp=7f30c58bafb0 bln=02 avl=02 flg=01
value="’’"
Bind#4
oacdty=96 mxl=10(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=64
kxsbbbfp=7f30c58bafb8 bln=10 avl=10 flg=01
value=“GOLDENGATE”
=====================
PARSING IN CURSOR #139847449411568 len=54 dep=1 uid=89 oct=3 lid=89 tim=1640841593810370 hv=3774346158 ad=‘852e2f90’ sqlid=‘05y50bvhggwxf’
SELECT COUNT(*) FROM SYS.DBA_USERS WHERE USERNAME=:B1
END OF STMT
PARSE #139847449411568:c=1,e=148,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=497759109,tim=1640841593810369
BINDS #139847449411568:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=7f30c58b1600 bln=32 avl=04 flg=05
value=“DANA”
EXEC #139847449411568:c=128,e=432,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=497759109,tim=1640841593810857
FETCH #139847449411568:c=4955,e=5596,p=0,cr=16,cu=0,mis=0,r=1,dep=1,og=1,plh=497759109,tim=1640841593816497
STAT #139847449411568 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘SORT AGGREGATE (cr=16 pr=0 pw=0 time=5691 us)’
STAT #139847449411568 id=2 cnt=1 pid=1 pos=1 obj=0 op=‘MERGE JOIN CARTESIAN (cr=16 pr=0 pw=0 time=5633 us cost=11 size=87 card=1)’
STAT #139847449411568 id=3 cnt=1 pid=2 pos=1 obj=0 op=‘HASH JOIN OUTER (cr=14 pr=0 pw=0 time=5584 us cost=10 size=78 card=1)’
STAT #139847449411568 id=4 cnt=1 pid=3 pos=1 obj=0 op=‘HASH JOIN (cr=12 pr=0 pw=0 time=3508 us cost=8 size=49 card=1)’
STAT #139847449411568 id=5 cnt=1 pid=4 pos=1 obj=0 op=‘NESTED LOOPS (cr=10 pr=0 pw=0 time=466 us cost=6 size=47 card=1)’
STAT #139847449411568 id=6 cnt=17 pid=5 pos=1 obj=0 op=‘NESTED LOOPS (cr=9 pr=0 pw=0 time=407 us cost=6 size=47 card=17)’
STAT #139847449411568 id=7 cnt=1 pid=6 pos=1 obj=0 op=‘NESTED LOOPS (cr=8 pr=0 pw=0 time=378 us cost=5 size=38 card=1)’
STAT #139847449411568 id=8 cnt=1 pid=7 pos=1 obj=0 op=‘NESTED LOOPS (cr=6 pr=0 pw=0 time=366 us cost=4 size=35 card=1)’
STAT #139847449411568 id=9 cnt=1 pid=8 pos=1 obj=0 op=‘NESTED LOOPS (cr=4 pr=0 pw=0 time=354 us cost=3 size=32 card=1)’
STAT #139847449411568 id=10 cnt=1 pid=9 pos=1 obj=22 op=‘TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=69 us cost=1 size=29 card=1)’
STAT #139847449411568 id=11 cnt=1 pid=10 pos=1 obj=46 op=‘INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=56 us cost=0 size=0 card=1)’
STAT #139847449411568 id=12 cnt=1 pid=9 pos=2 obj=293 op=‘TABLE ACCESS FULL USER_ASTATUS_MAP (cr=2 pr=0 pw=0 time=282 us cost=2 size=3 card=1)’
STAT #139847449411568 id=13 cnt=1 pid=8 pos=2 obj=16 op=‘TABLE ACCESS CLUSTER TS$ (cr=2 pr=0 pw=0 time=12 us cost=1 size=3 card=1)’
STAT #139847449411568 id=14 cnt=1 pid=13 pos=1 obj=7 op=‘INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=6 us cost=0 size=0 card=1)’
STAT #139847449411568 id=15 cnt=1 pid=7 pos=2 obj=16 op=‘TABLE ACCESS CLUSTER TS$ (cr=2 pr=0 pw=0 time=8 us cost=1 size=3 card=1)’
STAT #139847449411568 id=16 cnt=1 pid=15 pos=1 obj=7 op=‘INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=3 us cost=0 size=0 card=1)’
STAT #139847449411568 id=17 cnt=17 pid=6 pos=2 obj=285 op=‘INDEX RANGE SCAN I_PROFILE (cr=1 pr=0 pw=0 time=31 us cost=0 size=0 card=17)’
STAT #139847449411568 id=18 cnt=1 pid=5 pos=2 obj=281 op=‘TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=1 pr=0 pw=0 time=50 us cost=1 size=9 card=1)’
STAT #139847449411568 id=19 cnt=2 pid=4 pos=2 obj=282 op=‘TABLE ACCESS FULL PROFNAME$ (cr=2 pr=0 pw=0 time=90 us cost=2 size=2 card=1)’
STAT #139847449411568 id=20 cnt=0 pid=3 pos=2 obj=298 op=‘TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=2 pr=0 pw=0 time=51 us cost=2 size=29 card=1)’
STAT #139847449411568 id=21 cnt=1 pid=2 pos=2 obj=0 op=‘BUFFER SORT (cr=2 pr=0 pw=0 time=43 us cost=9 size=9 card=1)’
STAT #139847449411568 id=22 cnt=1 pid=21 pos=1 obj=281 op=‘TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=2 pr=0 pw=0 time=20 us cost=1 size=9 card=1)’
STAT #139847449411568 id=23 cnt=17 pid=22 pos=1 obj=285 op=‘INDEX RANGE SCAN I_PROFILE (cr=1 pr=0 pw=0 time=24 us cost=0 size=0 card=17)’
CLOSE #139847449411568:c=1,e=1,dep=1,type=3,tim=1640841593817049
=====================
PARSING IN CURSOR #139847447271744 len=135 dep=1 uid=89 oct=3 lid=89 tim=1640841593817380 hv=807489267 ad=‘892c2ed8’ sqlid=‘8r4hgg4s22mrm’
SELECT SEQUENCE_OWNER, SEQUENCE_NAME, LAST_NUMBER, CYCLE_FLAG FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER = :B2 AND SEQUENCE_NAME LIKE :B1
END OF STMT
PARSE #139847447271744:c=228,e=229,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=265299978,tim=1640841593817379
BINDS #139847447271744:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=160 off=0
kxsbbbfp=7f30c58af0b0 bln=32 avl=04 flg=05
value=“DANA”
Bind#1
oacdty=01 mxl=128(100) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=0 off=32
kxsbbbfp=7f30c58af0d0 bln=128 avl=13 flg=01
value=“SEQUENCETEST2”
EXEC #139847447271744:c=1065,e=203,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=265299978,tim=1640841593817636
FETCH #139847447271744:c=154,e=153,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=1,plh=265299978,tim=1640841593817824
STAT #139847447271744 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘NESTED LOOPS (cr=7 pr=0 pw=0 time=64 us cost=4 size=63 card=1)’
STAT #139847447271744 id=2 cnt=1 pid=1 pos=1 obj=0 op=‘NESTED LOOPS (cr=6 pr=0 pw=0 time=52 us cost=4 size=63 card=1)’
STAT #139847447271744 id=3 cnt=1 pid=2 pos=1 obj=0 op=‘NESTED LOOPS (cr=5 pr=0 pw=0 time=45 us cost=3 size=51 card=1)’
STAT #139847447271744 id=4 cnt=1 pid=3 pos=1 obj=22 op=‘TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=14 us cost=1 size=18 card=1)’
STAT #139847447271744 id=5 cnt=1 pid=4 pos=1 obj=46 op=‘INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=6 us cost=0 size=0 card=1)’
STAT #139847447271744 id=6 cnt=1 pid=3 pos=2 obj=37 op=‘INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=30 us cost=2 size=33 card=1)’
STAT #139847447271744 id=7 cnt=1 pid=2 pos=2 obj=79 op=‘INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=5 us cost=0 size=0 card=1)’
STAT #139847447271744 id=8 cnt=1 pid=1 pos=2 obj=74 op=‘TABLE ACCESS BY INDEX ROWID SEQ$ (cr=1 pr=0 pw=0 time=8 us cost=1 size=12 card=1)’
=====================
PARSING IN CURSOR #139847449371888 len=39 dep=1 uid=89 oct=42 lid=89 tim=1640841593818119 hv=1648071303 ad=‘0’ sqlid=‘958zjsxj3r4n7’
ALTER SESSION SET CURRENT_SCHEMA=“DANA”
END OF STMT
PARSE #139847449371888:c=39,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1640841593818119
EXEC #139847449371888:c=25,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1640841593818180
CLOSE #139847449371888:c=4,e=4,dep=1,type=0,tim=1640841593818200
XCTEND rlbk=0, rd_only=1, tim=1640841593818670
=====================
PARSING IN CURSOR #139847449371888 len=38 dep=1 uid=92 oct=14 lid=89 tim=1640841593819068 hv=2606497137 ad=‘8b3840f0’ sqlid=‘grpmnaudprzbj’
ALTER SEQUENCE “SEQUENCETEST2” NOCYCLE
END OF STMT
PARSE #139847449371888:c=419,e=845,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1640841593819067
=====================
PARSING IN CURSOR #139847449364936 len=70 dep=2 uid=0 oct=3 lid=0 tim=1640841593821522 hv=1853064805 ad=‘8c290d68’ sqlid=‘5hrvvu1r771m5’
SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME = ‘OGG_TRIGGER_OPTIMIZATION’
END OF STMT
PARSE #139847449364936:c=1873,e=2326,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=415205717,tim=1640841593821521
EXEC #139847449364936:c=17,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=415205717,tim=1640841593821637
FETCH #139847449364936:c=37,e=37,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=415205717,tim=1640841593821687
STAT #139847449364936 id=1 cnt=0 pid=0 pos=1 obj=98 op=‘TABLE ACCESS FULL PROPS$ (cr=2 pr=0 pw=0 time=37 us cost=2 size=28 card=1)’
=====================
PARSING IN CURSOR #139847447393328 len=5746 dep=2 uid=57 oct=47 lid=57 tim=1640841593822199 hv=127875610 ad=‘72ef7460’ sqlid=‘13t5kwh3tyfhu’
declare
TYPE attrs_cur IS REF CURSOR;
m_cur attrs_cur;
m_event varchar2(512);
m_user varchar2(512);
m_owner varchar2(512);
m_user1 varchar2(512);
m_type varchar2(512);
m_stmt varchar2(512);
m_name varchar2(5120);
m_column varchar2(5120);
m_cnt NUMBER;
m_stmt1 varchar2(512);
m_var varchar2(512);
m_o_stmt VARCHAR2(5120);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
m_stmt:=‘select sys.dbms_standard.dictionary_obj_type from dual’;
execute immediate m_stmt into m_type;
if(not (m_type=‘TABLE’ or m_type=‘TRIGGER’ or m_type=‘USER’ or m_type=‘TABLESPACE’))
then
return;
end if;
m_stmt:=‘select sys.dbms_standard.sysevent from dual’;
execute immediate m_stmt into m_event;
m_stmt:=‘select SYS_CONTEXT(’‘USERENV’’,’‘SESSION_USER’’) from dual’;
execute immediate m_stmt into m_user;
m_stmt:=‘select SYS_CONTEXT(’‘USERENV’’,’‘CURRENT_USER’’) from dual’;
execute immediate m_stmt into m_user1;
m_stmt:=‘select sys.dbms_standard.dictionary_obj_owner from dual’;
execute immediate m_stmt into m_owner;
m_stmt:=‘select sys.dbms_standard.dictionary_obj_name from dual’;
execute immediate m_stmt into m_name;
m_stmt:=‘select sdo_geor_def.getSqlText from dual’;
execute immediate m_stmt into m_o_stmt;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘ALTER’)
then
m_stmt:=‘select column_name from dba_tab_columns where owner=:1 and table_name=:2’;
open m_cur for m_stmt using m_owner,m_name;
loop
fetch m_cur into m_column;
exit when m_cur%NOTFOUND;
m_stmt:=‘select sdo_geor_def.isDropColumn(:1) from dual’;
execute immediate m_stmt into m_stmt1 using SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
if (trim(m_stmt1)=‘TRUE’)
then
m_stmt:=‘begin sdo_geor_def.doAlterDropColumn(:1,:2,:3); end;’;
execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
end if;
end loop;
end if;
if ((m_event=‘DROP’ and m_type=‘USER’) or (m_event=‘DROP’ and m_type=‘TABLESPACE’))
then
m_stmt:=‘insert into sdo_geor_ddl__table$$ values (1)’;
EXECUTE IMMEDIATE m_stmt;
commit;
end if;
if (m_event=‘DROP’ and m_type=‘TABLE’)
then
m_stmt:=‘select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2’;
EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;
if(m_cnt!=0)
then
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if((m_cnt=0)and (m_user!=‘SYS’ and m_user!=‘SYSTEM’ and m_user!='MDSYS’and m_owner!=‘MDSYS’ and m_owner!=‘SYS’))
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘The referenced raster data table(RDT) cannot be dropped.’’)’;
execute immediate m_stmt;
end if;
end if;
m_stmt:=‘insert into sdo_geor_ddl__table$$ values (2)’;
EXECUTE IMMEDIATE m_stmt;
commit;
end if;
if(m_user=‘SYS’ or m_user=‘SYSTEM’ or m_user=‘MDSYS’
or m_owner=‘MDSYS’ or m_owner=‘SYS’)
then
return;
end if;
if (m_event=‘RENAME’ and m_type=‘TABLE’)
then
m_stmt:=‘select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2’;
EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;
if(m_cnt!=0)
then
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘The referenced raster data table(RDT) cannot be renamed directly.’’)’;
execute immediate m_stmt;
end if;
end if;
end if;
if (m_type=‘TRIGGER’ and m_event=‘DROP’)
then
m_stmt:=‘select REGEXP_SUBSTR(:1,’‘GRDMLTR_.+’’,1,1,’‘i’’) from dual’;
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘GeoRaster DML triggers cannot be dropped.’’)’;
execute immediate m_stmt;
end if;
end if;
if (m_type=‘TRIGGER’ and m_event=‘ALTER’)
then
m_o_stmt:=upper(trim(m_o_stmt));
if(instr(m_o_stmt,’ COMPILE ‘)>0 or instr(m_o_stmt,’ ENABLE ')>0
or substr(m_o_stmt,length(m_o_stmt)-8,8)=’ COMPILE’ or substr(m_o_stmt,length(m_o_stmt)-7,7)=’ ENABLE’ )
then
return;
end if;
m_stmt:=‘select REGEXP_SUBSTR(:1,’‘GRDMLTR_.+’’,1,1,’‘i’’) from dual’;
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘GeoRaster DML triggers cannot be altered.’’)’;
execute immediate m_stmt;
end if;
end if;
if (m_type=‘TRIGGER’ and m_event=‘CREATE’)
then
m_stmt:=‘select REGEXP_SUBSTR(:1,’‘GRDMLTR_.+’’,1,1,’‘i’’) from dual’;
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:=‘select count(*) from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391, ‘‘GeoRaster reserved names cannot be used to create regular triggers.’’)’;
execute immediate m_stmt;
end if;
end if;
Exception
when others then
if(sqlcode=-13391)
then
m_stmt:=sqlerrm;
m_stmt:=substr(m_stmt,11);
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391,’’’||m_stmt||’’’)’;
execute immediate m_stmt;
end if;
end;
END OF STMT
PARSE #139847447393328:c=97,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1640841593822199
=====================
PARSING IN CURSOR #139847447368232 len=54 dep=3 uid=57 oct=3 lid=57 tim=1640841593823282 hv=664851305 ad=‘8c1f0ff0’ sqlid=‘bcv9qynmu1nv9’
select sys.dbms_standard.dictionary_obj_type from dual
END OF STMT
PARSE #139847447368232:c=147,e=148,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1388734953,tim=1640841593823282
EXEC #139847447368232:c=18,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1388734953,tim=1640841593823344
FETCH #139847447368232:c=286,e=285,p=0,cr=0,cu=0,mis=0,r=1,dep=3,og=1,plh=1388734953,tim=1640841593823680
STAT #139847447368232 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)’
CLOSE #139847447368232:c=1,e=1,dep=3,type=3,tim=1640841593823760
EXEC #139847447393328:c=751,e=751,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=0,tim=1640841593823786
CLOSE #139847447393328:c=11,e=11,dep=2,type=1,tim=1640841593824622
=====================
PARSING IN CURSOR #139847447352632 len=129 dep=2 uid=0 oct=6 lid=0 tim=1640841593898659 hv=2635489469 ad=‘8c2ac468’ sqlid=‘4m7m0t6fjcs5x’
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
PARSE #139847447352632:c=29004,e=38404,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=1640841593898658
BINDS #139847447352632:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac800 bln=22 avl=02 flg=09
value=1
Bind#1
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac812 bln=22 avl=02 flg=09
value=1
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac824 bln=22 avl=04 flg=09
value=99999
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f30c5751e98 bln=22 avl=01 flg=05
value=0
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c5751eb0 bln=22 avl=01 flg=01
value=0
Bind#5
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac836 bln=22 avl=02 flg=09
value=3
Bind#6
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac848 bln=22 avl=02 flg=09
value=11
Bind#7
oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=8c2ac85a bln=32 avl=32 flg=09
value="--------------------------------"
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f30c5751e50 bln=22 avl=02 flg=05
value=8
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c5751e68 bln=22 avl=04 flg=01
value=89877
WAIT #139847447352632: nam=‘Disk file operations I/O’ ela= 38 FileOperation=2 fileno=3 filetype=2 obj#=0 tim=1640841593901221
WAIT #139847447352632: nam=‘db file sequential read’ ela= 14 file#=3 block#=2111 blocks=1 obj#=0 tim=1640841593901280
EXEC #139847447352632:c=1887,e=2687,p=1,cr=1,cu=3,mis=1,r=1,dep=2,og=4,plh=1935744642,tim=1640841593901451
STAT #139847447352632 id=1 cnt=0 pid=0 pos=1 obj=0 op=‘UPDATE SEQ$ (cr=1 pr=1 pw=0 time=404 us)’
STAT #139847447352632 id=2 cnt=1 pid=1 pos=1 obj=79 op=‘INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=11 us cost=0 size=70 card=1)’
CLOSE #139847447352632:c=1,e=1,dep=2,type=3,tim=1640841593901531
BINDS #139847447352632:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac800 bln=22 avl=02 flg=09
value=1
Bind#1
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac812 bln=22 avl=02 flg=09
value=1
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac824 bln=22 avl=04 flg=09
value=99999
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f30c5751dc8 bln=22 avl=01 flg=05
value=0
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c5751de0 bln=22 avl=01 flg=01
value=0
Bind#5
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac836 bln=22 avl=02 flg=09
value=3
Bind#6
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac848 bln=22 avl=02 flg=09
value=11
Bind#7
oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=8c2ac85a bln=32 avl=32 flg=09
value="--------------------------------"
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f30c5751d80 bln=22 avl=02 flg=05
value=8
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c5751d98 bln=22 avl=04 flg=01
value=89877
EXEC #139847447352632:c=351,e=351,p=0,cr=1,cu=2,mis=0,r=1,dep=2,og=4,plh=1935744642,tim=1640841593902086
CLOSE #139847447352632:c=1,e=1,dep=2,type=3,tim=1640841593902113
=====================
PARSING IN CURSOR #139847447361840 len=235 dep=2 uid=0 oct=6 lid=0 tim=1640841593903605 hv=159997841 ad=‘893df3f8’ sqlid=‘4yyb4104skrwj’
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #139847447361840:c=2311,e=1460,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=1640841593903604
BINDS #139847447361840:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f30c5751dc8 bln=22 avl=04 flg=05
value=89877
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c5751de0 bln=22 avl=02 flg=01
value=6
Bind#2
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=8b2441c1 bln=07 avl=07 flg=09
value=“12/30/2021 10:53:14”
Bind#3
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=8b2441c8 bln=07 avl=07 flg=09
value=“12/30/2021 13:19:53”
Bind#4
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=8b2441cf bln=07 avl=07 flg=09
value=“12/30/2021 10:53:14”
Bind#5
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=72 off=0
kxsbbbfp=7f30c5751d68 bln=22 avl=02 flg=05
value=1
Bind#6
oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c5751d80 bln=22 avl=00 flg=01
Bind#7
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=7f30c5751d98 bln=22 avl=01 flg=01
value=0
Bind#8
oacdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=32 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=72 off=0
kxsbbbfp=7f30c5751d08 bln=22 avl=02 flg=05
value=6
Bind#10
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c5751d20 bln=22 avl=04 flg=01
value=65535
Bind#11
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=7f30c5751d38 bln=22 avl=02 flg=01
value=92
Bind#12
oacdty=01 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=8b2440ce bln=32 avl=13 flg=09
value=“SEQUENCETEST2”
Bind#13
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f30c5751cd8 bln=22 avl=02 flg=05
value=1
EXEC #139847447361840:c=2899,e=3402,p=0,cr=3,cu=1,mis=1,r=1,dep=2,og=4,plh=2683643009,tim=1640841593907102
STAT #139847447361840 id=1 cnt=0 pid=0 pos=1 obj=0 op=‘UPDATE OBJ$ (cr=3 pr=0 pw=0 time=180 us)’
STAT #139847447361840 id=2 cnt=1 pid=1 pos=1 obj=37 op=‘INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=23 us cost=3 size=84 card=1)’
CLOSE #139847447361840:c=2,e=2,dep=2,type=3,tim=1640841593907200
=====================
PARSING IN CURSOR #139847447339664 len=3509 dep=2 uid=57 oct=47 lid=57 tim=1640841593907737 hv=1920321438 ad=‘893f0658’ sqlid=‘38spc81t7bjwy’
declare
TYPE attrs_cur IS REF CURSOR;
m_cur attrs_cur;
m_event varchar2(512);
m_user varchar2(512);
m_owner varchar2(512);
m_user1 varchar2(512);
m_type varchar2(512);
m_name varchar2(5120);
m_column varchar2(5120);
m_cnt NUMBER;
m_stmt varchar2(512);
m_ret varchar2(3000);
m_ret1 varchar2(512);
m_o_stmt VARCHAR2(5120);
begin
m_stmt:=‘select sys.dbms_standard.dictionary_obj_type from dual’;
execute immediate m_stmt into m_type;
if(not (m_type=‘TABLE’ or m_type=‘TRIGGER’ or m_type=‘USER’ or m_type=‘TABLESPACE’))
then
return;
end if;
m_stmt:=‘select sys.dbms_standard.sysevent from dual’;
execute immediate m_stmt into m_event;
m_stmt:=‘select SYS_CONTEXT(’‘USERENV’’,’‘SESSION_USER’’) from dual’;
execute immediate m_stmt into m_user;
m_stmt:=‘select sys.dbms_standard.login_user from dual’;
execute immediate m_stmt into m_user1;
m_stmt:=‘select sys.dbms_standard.dictionary_obj_owner from dual’;
execute immediate m_stmt into m_owner;
m_stmt:=‘select sys.dbms_standard.dictionary_obj_name from dual’;
execute immediate m_stmt into m_name;
if((instr(upper(m_name),‘MDRT_’)>0) and m_event=‘DROP’)
then
return;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘CREATE’)
then
m_stmt:=‘select sdo_geor_def.listAllGeoRasterFieldsStr(:1,:2) from dual’;
execute immediate m_stmt into m_ret using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name);
m_ret:=trim(m_ret);
while (length(m_ret)!=0) loop
if (instr(m_ret,’ $$__## ')!=0)
then
m_ret1:=trim(substr(m_ret,1,instr(m_ret,’ $$__## ')-1));
m_ret:=trim(substr(m_ret,instr(m_ret,’ $$__## ')+8));
else
m_ret1:=trim(m_ret);
m_ret:=’’;
end if;
m_stmt:=‘begin SDO_GEOR_UTL.createDMLTrigger(:1,:2); end;’;
execute immediate m_stmt using m_owner||’.’||m_name,SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME(m_ret1);
end loop;
return;
end if;
if (m_name!=‘MDSYS’ and m_type=‘USER’ and m_event=‘DROP’)
then
m_stmt:=‘call sdo_geor_def.doDropUserAndTable()’;
execute immediate m_stmt;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘DROP’)
then
m_stmt:=‘call sdo_geor_def.doDropUserAndTable()’;
execute immediate m_stmt;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘TRUNCATE’)
then
m_stmt:=‘call sdo_geor_def.doTruncateTable()’;
execute immediate m_stmt;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘ALTER’)
then
m_stmt:=‘call sdo_geor_def.doAlterRenameTable()’;
execute immediate m_stmt;
end if;
if (m_owner!=‘MDSYS’ and m_owner!=‘SYS’ and m_type=‘TABLE’ and m_event=‘RENAME’)
then
m_stmt:=‘call sdo_geor_def.doRenameTable()’;
execute immediate m_stmt;
end if;
if (m_event=‘DROP’ and m_type=‘TABLE’)
then
m_stmt:=‘delete from sdo_geor_ddl__table$$ where id=2’;
EXECUTE IMMEDIATE m_stmt;
end if;
if ((m_type=‘USER’ and m_event=‘DROP’) or (m_type=‘TABLESPACE’ and m_event=‘DROP’))
then
m_stmt:=‘delete from sdo_geor_ddl__table$$’;
EXECUTE IMMEDIATE m_stmt;
end if;
Exception
when others then
if(sqlcode=-13391)
then
m_stmt:=sqlerrm;
m_stmt:=substr(m_stmt,11);
m_stmt:=‘call mderr.raise_md_error(’‘MD’’, ‘‘SDO’’, -13391,’’’||m_stmt||’’’)’;
execute immediate m_stmt;
end if;
end;
END OF STMT
PARSE #139847447339664:c=101,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1640841593907736
PARSE #139847447368232:c=8,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1388734953,tim=1640841593908320
EXEC #139847447368232:c=67,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1388734953,tim=1640841593908405
FETCH #139847447368232:c=95,e=95,p=0,cr=0,cu=0,mis=0,r=1,dep=3,og=1,plh=1388734953,tim=1640841593908542
CLOSE #139847447368232:c=1,e=1,dep=3,type=3,tim=1640841593908584
EXEC #139847447339664:c=397,e=398,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=0,tim=1640841593908597
CLOSE #139847447339664:c=8,e=8,dep=2,type=1,tim=1640841593908629
XCTEND rlbk=0, rd_only=1, tim=1640841593908664
EXEC #139847449371888:c=44456,e=89595,p=1,cr=7,cu=10,mis=0,r=0,dep=1,og=1,plh=0,tim=1640841593908712
CLOSE #139847449371888:c=2,e=2,dep=1,type=0,tim=1640841593908934
=====================
PARSING IN CURSOR #139847447270344 len=102 dep=2 uid=0 oct=3 lid=0 tim=1640841593910173 hv=3967354608 ad=‘8c3b87e0’ sqlid=‘axmdf8vq7k1rh’
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1
END OF STMT
PARSE #139847447270344:c=584,e=584,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=1640841593910172
BINDS #139847447270344:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f30c56bd4e8 bln=22 avl=04 flg=05
value=89877
EXEC #139847447270344:c=1285,e=1324,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=2203911306,tim=1640841593911627
FETCH #139847447270344:c=17,e=18,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=2203911306,tim=1640841593911662
STAT #139847447270344 id=1 cnt=1 pid=0 pos=1 obj=74 op=‘TABLE ACCESS BY INDEX ROWID SEQ$ (cr=2 pr=0 pw=0 time=19 us cost=1 size=70 card=1)’
STAT #139847447270344 id=2 cnt=1 pid=1 pos=1 obj=79 op=‘INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=11 us cost=0 size=0 card=1)’
CLOSE #139847447270344:c=1,e=1,dep=2,type=3,tim=1640841593911731
=====================
PARSING IN CURSOR #139847447335120 len=151 dep=2 uid=0 oct=3 lid=0 tim=1640841593912105 hv=4139184264 ad=‘8c3d5a00’ sqlid=‘2q93zsrvbdw48’
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
END OF STMT
PARSE #139847447335120:c=178,e=178,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2874733959,tim=1640841593912105
BINDS #139847447335120:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f30c56c9588 bln=22 avl=04 flg=05
value=89877
EXEC #139847447335120:c=143,e=143,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2874733959,tim=1640841593912310
FETCH #139847447335120:c=36,e=36,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=2874733959,tim=1640841593912360
STAT #139847447335120 id=1 cnt=0 pid=0 pos=1 obj=0 op=‘SORT GROUP BY (cr=2 pr=0 pw=0 time=47 us cost=4 size=15 card=1)’
STAT #139847447335120 id=2 cnt=0 pid=1 pos=1 obj=61 op=‘TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=32 us cost=3 size=15 card=1)’
STAT #139847447335120 id=3 cnt=0 pid=2 pos=1 obj=62 op=‘INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=30 us cost=2 size=0 card=1)’
CLOSE #139847447335120:c=5,e=6,dep=2,type=3,tim=1640841593940697
=====================
PARSING IN CURSOR #139847449371888 len=40 dep=1 uid=92 oct=3 lid=89 tim=1640841593941492 hv=1347272314 ad=‘891cc018’ sqlid=‘3tgyqw184vgmu’
SELECT “SEQUENCETEST2”.nextval FROM DUAL
END OF STMT
PARSE #139847449371888:c=33021,e=32523,p=0,cr=4,cu=0,mis=1,r=0,dep=1,og=1,plh=2297760174,tim=1640841593941491
EXEC #139847449371888:c=48,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2297760174,tim=1640841593941671
BINDS #139847447352632:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac800 bln=22 avl=02 flg=09
value=1
Bind#1
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac812 bln=22 avl=02 flg=09
value=1
Bind#2
oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac824 bln=22 avl=04 flg=09
value=99999
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f30c573fd60 bln=22 avl=01 flg=05
value=0
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c573fd78 bln=22 avl=01 flg=01
value=0
Bind#5
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac836 bln=22 avl=02 flg=09
value=3
Bind#6
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8c2ac848 bln=22 avl=02 flg=09
value=14
Bind#7
oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=8c2ac85a bln=32 avl=32 flg=09
value="--------------------------------"
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f30c573fd18 bln=22 avl=02 flg=05
value=8
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f30c573fd30 bln=22 avl=04 flg=01
value=89877
EXEC #139847447352632:c=423,e=423,p=0,cr=1,cu=2,mis=0,r=1,dep=2,og=4,plh=1935744642,tim=1640841593942240
CLOSE #139847447352632:c=1,e=1,dep=2,type=3,tim=1640841593942270
FETCH #139847449371888:c=666,e=666,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=1,plh=2297760174,tim=1640841593942387
STAT #139847449371888 id=1 cnt=1 pid=0 pos=1 obj=89877 op=‘SEQUENCE SEQUENCETEST2 (cr=1 pr=0 pw=0 time=686 us)’
STAT #139847449371888 id=2 cnt=1 pid=1 pos=1 obj=0 op=‘FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)’
CLOSE #139847449371888:c=1,e=1,dep=1,type=3,tim=1640841593942478
=====================
PARSING IN CURSOR #139847447383384 len=85 dep=1 uid=92 oct=3 lid=89 tim=1640841593943131 hv=1743359171 ad=‘8530ff80’ sqlid=‘c00hfj5mym363’
SELECT LAST_NUMBER FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER=:B2 AND SEQUENCE_NAME=:B1
END OF STMT
PARSE #139847447383384:c=1,e=610,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1640841593943131
=====================
PARSING IN CURSOR #139847447381384 len=37 dep=2 uid=0 oct=3 lid=0 tim=1640841593943633 hv=1398610540 ad=‘8c3c6c48’ sqlid=‘grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #139847447381384:c=115,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3684871272,tim=1640841593943632
BINDS #139847447381384:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=7f30c56c7930 bln=16 avl=16 flg=05
value=000022F6.0006.0001
EXEC #139847447381384:c=281,e=281,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3684871272,tim=1640841593943983
FETCH #139847447381384:c=32,e=31,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=3684871272,tim=1640841593944040
STAT #139847447381384 id=1 cnt=1 pid=0 pos=1 obj=69 op=‘TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=64 us cost=1 size=15 card=1)’
CLOSE #139847447381384:c=33,e=33,dep=2,type=1,tim=1640841593944088
BINDS #139847447383384:
Bind#0
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=7f30c5a3b298 bln=32 avl=04 flg=09
value=“DANA”
Bind#1
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=7f30c5a3b2b8 bln=32 avl=13 flg=09
value=“SEQUENCETEST2”
EXEC #139847447383384:c=5198,e=17336,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,plh=265299978,tim=1640841593960521
FETCH #139847447383384:c=64,e=64,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=1,plh=265299978,tim=1640841593960646
STAT #139847447383384 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘NESTED LOOPS (cr=7 pr=0 pw=0 time=63 us cost=4 size=60 card=1)’
STAT #139847447383384 id=2 cnt=1 pid=1 pos=1 obj=0 op=‘NESTED LOOPS (cr=6 pr=0 pw=0 time=53 us cost=4 size=60 card=1)’
STAT #139847447383384 id=3 cnt=1 pid=2 pos=1 obj=0 op=‘NESTED LOOPS (cr=5 pr=0 pw=0 time=49 us cost=3 size=51 card=1)’
STAT #139847447383384 id=4 cnt=1 pid=3 pos=1 obj=22 op=‘TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=30 us cost=1 size=18 card=1)’
STAT #139847447383384 id=5 cnt=1 pid=4 pos=1 obj=46 op=‘INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=14 us cost=0 size=0 card=1)’
STAT #139847447383384 id=6 cnt=1 pid=3 pos=2 obj=37 op=‘INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=15 us cost=2 size=33 card=1)’
STAT #139847447383384 id=7 cnt=1 pid=2 pos=2 obj=79 op=‘INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=3 us cost=0 size=0 card=1)’
STAT #139847447383384 id=8 cnt=1 pid=1 pos=2 obj=74 op=‘TABLE ACCESS BY INDEX ROWID SEQ$ (cr=1 pr=0 pw=0 time=2 us cost=1 size=9 card=1)’
CLOSE #139847447383384:c=1,e=1,dep=1,type=3,tim=1640841593960814
=====================
PARSING IN CURSOR #139847447371696 len=45 dep=1 uid=92 oct=42 lid=89 tim=1640841593960929 hv=4112858304 ad=‘0’ sqlid=‘653dv1mukag60’
ALTER SESSION SET CURRENT_SCHEMA=“GOLDENGATE”
END OF STMT
PARSE #139847447371696:c=78,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1640841593960928
EXEC #139847447371696:c=23,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1640841593960998
CLOSE #139847447371696:c=3,e=3,dep=1,type=0,tim=1640841593961018
CLOSE #139847447271744:c=0,e=1,dep=1,type=3,tim=1640841593961058
WAIT #139847449275800: nam=‘SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=118 tim=1640841593961080
EXEC #139847449275800:c=94246,e=151433,p=1,cr=44,cu=13,mis=0,r=1,dep=0,og=1,plh=0,tim=1640841593961094
*** 2021-12-30 13:20:21.802
Received ORADEBUG command (#2) ‘event 10046 trace name context off’ from process 'Unix process pid: 102598, image: ’
*** 2021-12-30 13:20:21.802
Finished processing ORADEBUG command (#2) ‘event 10046 trace name context off’
*** 2021-12-30 13:20:47.975
Received ORADEBUG command (#3) ‘tracefile_name’ from process 'Unix process pid: 102598, image: ’
*** 2021-12-30 13:20:47.975
Finished processing ORADEBUG command (#3) ‘tracefile_name’
总结：3.1与3.2对比发现flush sequence只对源库操作，对于目标库无影响；且会更改last_number值，与之前观察的现象结论一致；trace文件中的flush sequence操作涉及到一个存储过程updatesequence，再次获取存储过程分析内容
3.3 获取存储过程updatesequence
1、查询存储过程
SYS@hmb> select OWNER,OBJECT_NAME ,status from dba_objects where OBJECT_TYPE=‘PROCEDURE’ and owner=‘GOLDENGATE’;
OWNER OBJECT_NAME STATUS
------------------------------------------------- ---------------------------------------
GOLDENGATE SEQTRACE VALID
GOLDENGATE GETSEQFLUSH VALID
GOLDENGATE REPLICATESEQUENCE VALID
GOLDENGATE UPDATESEQUENCE VALID
2、获取存储过程的具体内容
SELECT text
FROM dba_source
WHERE NAME = ‘UPDATESEQUENCE’
ORDER BY line;
SYS@hmb> SELECT text
2 FROM dba_source
3 WHERE NAME = ‘UPDATESEQUENCE’
4 ORDER BY line;
TEXT
------------------------------------------------------------------------------------------------------------------------
PROCEDURE updateSequence (
schemaName IN VARCHAR2,
seqName IN VARCHAR2,
isTrace IN NUMBER,
traceUser IN VARCHAR2,
sessUser IN VARCHAR2)
AUTHID current_user
IS
seqVal NUMBER;
cyc VARCHAR2(100);
lastN NUMBER;
userN NUMBER;
oraSeqName VARCHAR2(100);
BEGIN
IF isTrace = 1 THEN
“GOLDENGATE” .seqTrace (‘BEGIN "’ || traceUser || ‘".trace_put_line(’‘SEQUENCEUPD’’, ‘’################ BEGIN bu
mping up HWM, schema=’’ || ‘’’ || schemaName || ‘’’ || ‘’ seq ‘’ || ‘’’ || seqName || ‘’’); END;’, traceUser);
END IF;
– replace OGG wildcards with SQLPLUS (do not change the meaning of % and _)
– we don’t replace % with \% and _ with \_. We could but it’s not done anywhere in OGG
oraSeqName := seqName;
IF oraSeqName IS NOT NULL THEN
oraSeqName := REPLACE (oraSeqName, ‘*’, ‘%’);
oraSeqName := REPLACE (oraSeqName, ‘?’, ‘_’);
END IF;
IF oraSeqName = ‘’ OR oraSeqName IS NULL THEN
oraSeqName := ‘%’;
END IF;
IF isTrace = 1 THEN
“GOLDENGATE” .seqTrace (‘BEGIN "’ || traceUser || ‘".trace_put_line(’‘SEQUENCEUPD’’, '‘new seqname ‘’ || ‘’’ ||
oraSeqName || ‘’’); END;’, traceUser);
END IF;
– check if schema exists
SELECT COUNT(*) INTO userN FROM SYS.DBA_USERS where USERNAME=schemaName;
IF userN = 0 THEN
IF isTrace = 1 THEN
“GOLDENGATE” .seqTrace (‘BEGIN "’ || traceUser || ‘".trace_put_line(’‘SEQUENCEUPD’’, ''schema does not exist
=’’ || ‘’’ || schemaName || ‘’’); END;’, traceUser);
END IF;
RETURN;
END IF;
– look for all sequences in schema in bring them to be last_number (HWM). This will work because
– if nextval reaches it, it will force an update. This update will move to the target system
– and bring sequences in “sync”
FOR seq IN (SELECT sequence_owner, sequence_name, last_number, cycle_flag FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER = sch
emaName AND SEQUENCE_NAME LIKE oraSeqName) LOOP
IF seq.cycle_flag = ‘Y’ THEN
cyc := ‘CYCLE’;
ELSE
cyc := ‘NOCYCLE’;
END IF;
– use setting schema to avoid problems with table names equal to that of schema
EXECUTE IMMEDIATE (‘ALTER SESSION SET CURRENT_SCHEMA="’||schemaName||’"’);
IF isTrace = 1 THEN
“GOLDENGATE” .seqTrace (‘BEGIN "’ || traceUser || ‘".trace_put_line(’‘SEQUENCEUPD’’, ‘‘set session schema=’’
|| ‘’’ || seq.sequence_owner || ‘’’); END;’, traceUser);
END IF;
EXECUTE IMMEDIATE ‘ALTER SEQUENCE "’|| seq.sequence_name || '" ’ || cyc;
BEGIN
EXECUTE IMMEDIATE ‘SELECT "’|| seq.sequence_name || ‘".nextval FROM DUAL’ INTO seqVal;
EXCEPTION
WHEN OTHERS THEN IF SQLCODE = -8004 THEN NULL; END IF; – ignore going over MAXVALUE for NOCYCLE
END;
SELECT last_number INTO lastN FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER=seq.sequence_owner AND
SEQUENCE_NAME=seq.sequence_name;
IF isTrace = 1 THEN
“GOLDENGATE” .seqTrace (‘BEGIN "’ || traceUser || ‘".trace_put_line(’‘SEQUENCEUPD’’, ‘‘seqName=’’ || ‘’’ ||
seq.sequence_name || ‘’’ || ‘’ old HWM = ‘’ ||’ || seq.last_number || ’ || ‘’ new HWM = ‘’ ||’ ||
lastN || ‘); END;’, traceUser);
END IF;
EXECUTE IMMEDIATE (‘ALTER SESSION SET CURRENT_SCHEMA="’||sessUser||’"’);
IF isTrace = 1 THEN
“GOLDENGATE” .seqTrace (‘BEGIN "’ || traceUser || ‘".trace_put_line(’‘SEQUENCEUPD’’, ''restore session sche
ma to ‘’ || ‘’’ || sessUser || ‘’’); END;’, traceUser);
END IF;
END LOOP;
IF isTrace = 1 THEN
“GOLDENGATE” .seqTrace (‘BEGIN "’ || traceUser || ‘".trace_put_line(’‘SEQUENCEUPD’’, ‘’################ END sche
ma=’’ || ‘’’ || schemaName || ‘’’); END;’, traceUser);
END IF;
END;
78 rows selected.
总结：flush sequence只对源库操作，对于目标库无影响；每一次flush sequence调用一次存储过程，即nextval一次，且会更改last_number值使其加1，但次过程的last_number变化并不会使其通过ogg同步；所以ogg的flush sequence并不能起到两边sequence不一致而刷新的作用，只能在初始化并未同步sequence使起到一个select nextval的作用，触发初始化时的sequence同步。
