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