oracle 流技术（一）

最新推荐文章于 2021-04-13 00:01:42 发布

zistxym

最新推荐文章于 2021-04-13 00:01:42 发布

阅读量2.5k

点赞数

分类专栏： oracle 文章标签： oracle database instantiation object command include

本文链接：https://blog.csdn.net/zistxym/article/details/5193004

版权

oracle 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

  1 oracle数据流实验：
  2
  3 一、先找出常规用户表空间位置
  4 1.以sysdba方式来打开sqlplus，命令如下： sqlplus "/as sysdba"
  5 2.查看我们常规将用户表空间放置位置：执行如下sql：
  6 select name from v$datafile;
  7 上边的sql一般就将你的用户表空间文件位置查出来了。
  8
  9 二、创建表空间
10 create tablespace streams_tbs datafile '/oradata/stream_tbs.dbf' size 25M
11 reuse autoextend on maxsize unlimited;
12
13 创建用户，指定密码和上边创建的用户表空间
14 create user strmadmin identified by strmadmin
15 default tablespace streams_tbs
16 quota unlimited on streams_tbs;
17
18 赋予权限
19 grant dba to strmadmin;
20
21 三、创建表
22 conn lltrade/lltrade
23 CREATE TABLE employee(
24 employee_id    NUMBER(6),
25 first_name     VARCHAR2(20),
26 last_name      VARCHAR2(25),
27 email          VARCHAR2(25),
28 phone_number   VARCHAR2(20),
29 hire_date      DATE,
30 job_id         VARCHAR2(10),
31 salary         NUMBER(8,2),
32 commission_pct NUMBER(2,2),
33 manager_id     NUMBER(6),
34 department_id  NUMBER(4) );
35
36 grant all on lltrade.employee to strmadmin;
37
38 CREATE TABLE employee_audit(
39 employee_id    NUMBER(6),
40 first_name     VARCHAR2(20),
41 last_name      VARCHAR2(25),
42 email          VARCHAR2(25),
43 phone_number   VARCHAR2(20),
44 hire_date      DATE,
45 job_id         VARCHAR2(10),
46 salary         NUMBER(8,2),
47 commission_pct NUMBER(2,2),
48 manager_id     NUMBER(6),
49 department_id  NUMBER(4),
50 upd_date      DATE,
51 user_name VARCHAR2(30),
52 action VARCHAR2(30));
53
54 grant all on lltrade.employee_audit to strmadmin;
55
56 四、  conn strmadmin/strmadmin
57 创建日志表
58 CREATE TABLE streams_monitor (
59 date_and_time TIMESTAMP(6) DEFAULT systimestamp,
60 txt_msg CLOB );
61
62 创建队列
63 BEGIN
64 DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table  => 'strmadmin.streams_queue_table', queue_name   => 'strmadmin.streams_queue');
65 END;
66 /
67
68 下面只定义了DML（数据操作语言），没有定义DDL（数据定义语言）。
69 BEGIN
70 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
71 table_name     => 'lltrade.employee',
72 streams_type   => 'capture',
73 streams_name   => 'capture_emp',
74 queue_name     => 'strmadmin.streams_queue',
75 include_dml    =>  true,
76 include_ddl    =>  false,
77 inclusion_rule =>  true);
78 END;
79 /
80
81
82 下面定义捕捉进程
83 BEGIN
84 DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
85 capture_name   => 'capture_emp',
86 attribute_name => 'username',
87 include        => true);
88 END;
89 /
90
91 通知oracle在那里开始捕捉。
92 DECLARE
93 iscn  NUMBER;
94 BEGIN
95 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
96 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
97 source_object_name    => 'lltrade.employee',
98 source_database_name  => 'oradf4',
99 instantiation_scn     => iscn);
100 END;
101 /
102
103 下面是自己定义的捕捉过程。
104 CREATE OR REPLACE PROCEDURE emp_dml_handler(in_any IN ANYDATA) IS
105 lcr          SYS.LCR$_ROW_RECORD;
106 rc           PLS_INTEGER ;
107 command      VARCHAR2(30);
108 old_values   SYS.LCR$_ROW_LIST;
109 BEGIN
110 -- Access the LCR
111 rc := in_any.GETOBJECT(lcr);
112 -- 获取目标命令类型
113 command := lcr.GET_COMMAND_TYPE();
114 -- I am inserting the XML equivalent of the LCR into the monitoring table.
115 -- 插入XML格式到监测表中
116 insert into streams_monitor (txt_msg)
117 values (command ||
118 DBMS_STREAMS.CONVERT_LCR_TO_XML(in_any) );
119 -- Set the command_type in the row LCR to INSERT
120 lcr.SET_COMMAND_TYPE('INSERT');
121 -- Set the object_name in the row LCR to EMP_DEL
122 lcr.SET_OBJECT_NAME('EMPLOYEE_AUDIT');
123 -- Set the new values to the old values for update and delete
124 IF command IN ('DELETE', 'UPDATE') THEN
125 -- Get the old values in the row LCR
126 old_values := lcr.GET_VALUES('old');
127 -- Set the old values in the row LCR to the new values in the row LCR
128 lcr.SET_VALUES('new', old_values);
129 -- Set the old values in the row LCR to NULL
130 lcr.SET_VALUES('old', NULL);
131 END IF;
132 -- Add a SYSDATE for upd_date
133 lcr.ADD_COLUMN('new', 'UPD_DATE', ANYDATA.ConvertDate(SYSDATE));
134 -- Add a user column
135 lcr.ADD_COLUMN('new', 'user_name',
136 lcr.GET_EXTRA_ATTRIBUTE('USERNAME') );
137 -- Add an action column
138 lcr.ADD_COLUMN('new', 'ACTION', ANYDATA.ConvertVarChar2(command));
139
140 -- Make the changes
141 lcr.EXECUTE(true);
142 commit;
143 END;
144 /
145
146 创建DML句柄。
147 BEGIN
148 DBMS_APPLY_ADM.SET_DML_HANDLER(
149 object_name         => 'lltrade.employee',
150 object_type         => 'TABLE',
151 operation_name      => 'INSERT',
152 error_handler       => false,
153 user_procedure      => 'strmadmin.emp_dml_handler',
154 apply_database_link => NULL,
155 apply_name          => NULL);
156 END;
157 /
158 BEGIN
159 DBMS_APPLY_ADM.SET_DML_HANDLER(
160 object_name         => 'lltrade.employee',
161 object_type         => 'TABLE',
162 operation_name      => 'UPDATE',
163 error_handler       => false,
164 user_procedure      => 'strmadmin.emp_dml_handler',
165 apply_database_link => NULL,
166 apply_name          => NULL);
167 END;
168 /
169
170
171 DECLARE
172 emp_rule_name_dml  VARCHAR2(30);
173 emp_rule_name_ddl  VARCHAR2(30);
174 BEGIN
175 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
176 table_name      => 'lltrade.employee',
177 streams_type    => 'apply',
178 streams_name    => 'apply_emp',
179 queue_name      => 'strmadmin.streams_queue',
180 include_dml     =>  true,
181 include_ddl     =>  false,
182 source_database => 'oradf4',
183 dml_rule_name   => emp_rule_name_dml,
184 ddl_rule_name  => emp_rule_name_ddl);
185
186 DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
187 rule_name               =>  emp_rule_name_dml,
188 destination_queue_name  =>  'strmadmin.streams_queue');
189 END;
190 /
191
192 BEGIN
193 DBMS_APPLY_ADM.SET_PARAMETER(
194 apply_name  => 'apply_emp',
195 parameter   => 'disable_on_error',
196 value       => 'n');
197 END;
198 /
199
200 打开应该进程：
201 BEGIN
202 DBMS_APPLY_ADM.START_APPLY(
203 apply_name  => 'apply_emp');
204 END;
205 /
206
207 打开捕捉进程：
208 BEGIN
209 DBMS_CAPTURE_ADM.START_CAPTURE(
210 capture_name  => 'capture_emp');
211 END;
212 /
213
214 开始测试：
215
216 sqlplus lltrade/lltrade
217
218 INSERT INTO lltrade.employee VALUES(207, 'JOHN', 'SMITH', 'JSMITH@MYCOMPANY.COM', NULL, sysdate, 'AC_ACCOUNT', 777, NULL, NULL, 110);
219 COMMIT;
220 UPDATE lltrade.employee SET salary=5999 WHERE employee_id=207;
221 COMMIT;
222 DELETE FROM lltrade.employee WHERE employee_id=207;
223 COMMIT;
224
225 SELECT employee_id, first_name, last_name, upd_Date, action
226 FROM lltrade.employee_audit
227 ORDER BY employee_id;
228 结果：
229
230 207 JOHN                 SMITH                     15-JAN-10    UPDATE
231 207 JOHN                 SMITH                     15-JAN-10    INSERT
232
233
234 查看输出结果：
235 conn strmadmin/strmadmin
236 set long 9999
237 set pagesize 0
238 select * from streams_monitor;
239
240 输出内容：
241
242 从下面可以看出UPDATE， INSERT 的内容。
243
244 15-JAN-10 09.37.15.400922 AM
245 INSERT<ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr" xmlns:xsi="ht
246 tp://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle
247 .com/streams/schemas/lcr  http://xmlns.oracle.com/streams/schemas/lcr/streamslcr
248 .xsd">
249   <source_database_name>ORADF4.LIANLIAN</source_database_name>
250   <command_type>INSERT</command_type>
251   <object_owner>LLTRADE</object_owner>
252   <object_name>EMPLOYEE</object_name>
253   <transaction_id>11.25.1031</transaction_id>
254   <scn>3269687255</scn>
255   <new_values>
256     <new_value>
257       <column_name>EMPLOYEE_ID</column_name>
258       <data>
259         <number>207</number>
260       </data>
261     </new_value>
262     <new_value>
263       <column_name>FIRST_NAME</column_name>
264       <data>
265         <varchar2>JOHN</varchar2>
266       </data>
267     </new_value>
268     <new_value>
269       <column_name>LAST_NAME</column_name>
270       <data>
271         <varchar2>SMITH</varchar2>
272       </data>
273     </new_value>
274     <new_value>
275       <column_name>EMAIL</column_name>
276       <data>
277         <varchar2>JSMITH@MYCOMPANY.COM</varchar2>
278       </data>
279     </new_value>
280     <new_value>
281       <column_name>PHONE_NUMBER</column_name>
282       <data>
283         <varchar2 xsi:nil="true"/>
284       </data>
285     </new_value>
286     <new_value>
287       <column_name>HIRE_DATE</column_name>
288       <data>
289         <date>
290           <value> 2010/01/15 09:37:14</value>
291           <format>SYYYY/MM/DD HH24:MI:SS</format>
292         </date>
293       </data>
294     </new_value>
295     <new_value>
296       <column_name>JOB_ID</column_name>
297       <data>
298         <varchar2>AC_ACCOUNT</varchar2>
299       </data>
300     </new_value>
301     <new_value>
302       <column_name>SALARY</column_name>
303       <data>
304         <number>777</number>
305       </data>
306     </new_value>
307     <new_value>
308       <column_name>COMMISSION_PCT</column_name>
309       <data>
310         <number xsi:nil="true"/>
311       </data>
312     </new_value>
313     <new_value>
314       <column_name>MANAGER_ID</column_name>
315       <data>
316         <number xsi:nil="true"/>
317       </data>
318     </new_value>
319     <new_value>
320       <column_name>DEPARTMENT_ID</column_name>
321       <data>
322         <number>110</number>
323       </data>
324     </new_value>
325   </new_values>
326   <extra_attribute_values>
327     <extra_attribute_value>
328       <attribute_name>USERNAME</attribute_name>
329       <attribute_value>
330         <varchar2>LLTRADE</varchar2>
331       </attribute_value>
332     </extra_attribute_value>
333   </extra_attribute_values>
334 </ROW_LCR>
335
336
337 15-JAN-10 09.37.15.424642 AM
338 UPDATE<ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr" xmlns:xsi="ht
339 tp://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle
340 .com/streams/schemas/lcr  http://xmlns.oracle.com/streams/schemas/lcr/streamslcr
341 .xsd">
342   <source_database_name>ORADF4.LIANLIAN</source_database_name>
343   <command_type>UPDATE</command_type>
344   <object_owner>LLTRADE</object_owner>
345   <object_name>EMPLOYEE</object_name>
346   <transaction_id>7.45.103812</transaction_id>
347   <scn>3269687258</scn>
348   <old_values>
349     <old_value>
350       <column_name>EMPLOYEE_ID</column_name>
351       <data>
352         <number>207</number>
353       </data>
354     </old_value>
355     <old_value>
356       <column_name>FIRST_NAME</column_name>
357       <data>
358         <varchar2>JOHN</varchar2>
359       </data>
360     </old_value>
361     <old_value>
362       <column_name>LAST_NAME</column_name>
363       <data>
364         <varchar2>SMITH</varchar2>
365       </data>
366     </old_value>
367     <old_value>
368       <column_name>EMAIL</column_name>
369       <data>
370         <varchar2>JSMITH@MYCOMPANY.COM</varchar2>
371       </data>
372     </old_value>
373     <old_value>
374       <column_name>PHONE_NUMBER</column_name>
375       <data>
376         <varchar2 xsi:nil="true"/>
377       </data>
378     </old_value>
379     <old_value>
380       <column_name>HIRE_DATE</column_name>
381       <data>
382         <date>
383           <value> 2010/01/15 09:37:14</value>
384           <format>SYYYY/MM/DD HH24:MI:SS</format>
385         </date>
386       </data>
387     </old_value>
388     <old_value>
389       <column_name>JOB_ID</column_name>
390       <data>
391         <varchar2>AC_ACCOUNT</varchar2>
392       </data>
393     </old_value>
394     <old_value>
395       <column_name>SALARY</column_name>
396       <data>
397         <number>777</number>
398       </data>
399     </old_value>
400     <old_value>
401       <column_name>COMMISSION_PCT</column_name>
402       <data>
403         <number xsi:nil="true"/>
404       </data>
405     </old_value>
406     <old_value>
407       <column_name>MANAGER_ID</column_name>
408       <data>
409         <number xsi:nil="true"/>
410       </data>
411     </old_value>
412     <old_value>
413       <column_name>DEPARTMENT_ID</column_name>
414       <data>
415         <number>110</number>
416       </data>
417     </old_value>
418   </old_values>
419   <new_values>
420     <new_value>
421       <column_name>SALARY</column_name>
422       <data>
423         <number>5999</number>
424       </data>
425     </new_value>
426   </new_values>
427   <extra_attribute_values>
428     <extra_attribute_value>
429       <attribute_name>USERNAME</attribute_name>
430       <attribute_value>
431         <varchar2>LLTRADE</varchar2>
432       </attribute_value>
433     </extra_attribute_value>
434   </extra_attribute_values>
435 </ROW_LCR>
436