streams trigger

panys 2008-12-31 03:56:09
把db1.A表的数据通过streams同步到db2.A表中,db2.A中有一个trigger用于记录一些log信息,

问题一是:当通过streams同步过来的数据,不会触发db2.A上的触发器,解决方法是在db2.A设置一下参数:
exec dbms_ddl.set_trigger_firing_property ('testuser', 'TR1_table_A', FALSE);
这个参数默认是true。设置好了,就可以触发db2.A上的触发器了。

但问题二是:这个触发器里面的user值无法得到,但其它的如sysdate则正常。而且如果是手工地在db2.A中插入一条数据,则user值则能得到。请问各位,通过streams同步过来的数据,触发器里不能得到这个user值吗?

这是db2.A上的触发器的SQL:
CREATE OR REPLACE TRIGGER TR1_table_A BEFORE UPDATE OR INSERT ON A FOR EACH ROW
BEGIN

IF dbms_ddl.is_trigger_fire_once('dms', 'TR1_table_A') THEN
insert into test1 values('true', to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
ELSE
insert into test1 values('false', to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
END IF;

IF USER IS NULL THEN
insert into test2 values('USER IS NULL', to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
ELSE
insert into test2 values(USER, to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
END IF;
END;
...全文
182 17 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
yun222222 2009-01-22
  • 打赏
  • 举报
回复
学习了没用过STREAM
watson110 2009-01-22
  • 打赏
  • 举报
回复
这样是取不到数据的,除非你自己建立规则
panys 2009-01-14
  • 打赏
  • 举报
回复
还是没有答案,好吧,我把需求在说清楚点,现有三个DB(db_A,db_B,db_C),各有一个表T1。db_A和_dbC中的数据可以通过Streams同步到db_B中,此时数据同步到db_B后是不用做任何处理的。

但如果在db_B端手工地插入一条数据时,我们则需要根据一些业务分类,分别把这样的数据传回到db_A或db_C中。所以此时在db_B的T1中要能够区分这条数据的来源是:1 Streams同步过来的,还是2 db_B来身产生的。(db_B相当是一个所有DB数据的全集)

目前,我们能根据user为空认定它是1 Streams同步过来的,否则是2 db_B来身产生的。但这种判断为空的作法不严谨,只是试验出来的,我是想确切地知道,是不是对于1就是不能得到user的值,还是说某个地方设的不对?
BlueskyWide 2009-01-14
  • 打赏
  • 举报
回复
Streams以前没用到过。

能否在所有的T1表中增加一个字段User_From,
当update,insert t1表(使用Trigger)时,自动写入User_From字段,以此可以判断来自于何User。

关键是:1.没有解决根本问题;2.T1此类的表多时,以上方法并不合适。

是否可以考虑使用Package。
panys 2009-01-13
  • 打赏
  • 举报
回复
谢谢,BlueskyWide,你可能有些误会了。
为了让TRIGGER TR1_table_A BEFORE能够多次被触发,需要set_trigger_firing_property=false。
IF dbms_ddl.is_trigger_fire_once这句话在这里主要是做检测用的。关键点不在这里,可以忽略这段IF ...END IF。(dms是用户名)


重点是:
insert into test2 values(ora_login_user, to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')); 

这句话里ora_login_user或者user无法得到当前用户值,目前总是null值。
BlueskyWide 2009-01-10
  • 打赏
  • 举报
回复
查了一下:
IS_TRIGGER_FIRE_ONCE:检测特定的DML或DDL触发器是否只触发一次
语法:DBMS_DDL.IS_TRIGGER_FIRE_ONCE( trigger_owner IN VARCHAR2,--触发器所有者
trigger_name IN VARCHAR2--触发器名);
RETURN BOOLEAN;--返回为TRUE则表示触发器只被触发一次

LZ在这里的引用{dbms_ddl.is_trigger_fire_once('dms', 'TR1_table_A')}可能有点问题:
dms是Oracle用户名吗?

如果不是,那么引用错了;
如果是的话,可以试一下:
CREATE OR REPLACE TRIGGER TR1_table_A BEFORE UPDATE OR INSERT ON A FOR EACH ROW 
BEGIN
IF dbms_ddl.is_trigger_fire_once('dms', 'TR1_table_A') THEN
insert into test1 values('dms', to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
ELSE
insert into test1 values('', to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
END IF;
END;


zengxiaodonghc 2009-01-07
  • 打赏
  • 举报
回复
飘过
Andy__Huang 2009-01-07
  • 打赏
  • 举报
回复
黄别好久才出来一次...
panys 2009-01-07
  • 打赏
  • 举报
回复
试过了,ora_login_user同USER一样都为空,同时试了ora_client_ip_address也为空。
但ora_database_name和ora_instance_num都是能得到的。

难道streams过来的数据,都通过自已的渠道,而不产生user,ora_login_user,ora_client_ip_address吗?
ab5669 2009-01-07
  • 打赏
  • 举报
回复
改成这样试下:
CREATE OR REPLACE TRIGGER TR1_table_A BEFORE UPDATE OR INSERT ON A FOR EACH ROW
BEGIN

IF dbms_ddl.is_trigger_fire_once('dms', 'TR1_table_A') THEN
insert into test1 values('true', to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
ELSE
insert into test1 values('false', to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
END IF;

insert into test2 values(ora_login_user, to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));

END;

关键是用ora_login_user系统函数
oraclelogan 2009-01-05
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 panys 的回复:]
谢谢,BlueskyWide。

哦,情况是这样的,由于对系统的高可用性要求比较高,我们用streams是必须的,是基本前提条件,所有的业务都是基于streams基础上来开展的,现在遇到的是其中的一个技术问题:
通过streams同步过来的数据,为何在目标端db的表trigger中无法读取到user变量的值?
[/Quote]

学习了,以前没有碰到过类似问题!
oraclelogan 2009-01-05
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 panys 的回复:]
谢谢,BlueskyWide。

哦,情况是这样的,由于对系统的高可用性要求比较高,我们用streams是必须的,是基本前提条件,所有的业务都是基于streams基础上来开展的,现在遇到的是其中的一个技术问题:
通过streams同步过来的数据,为何在目标端db的表trigger中无法读取到user变量的值?
[/Quote]

学习了,以前没有碰到过类似问题!
panys 2009-01-04
  • 打赏
  • 举报
回复
谢谢,BlueskyWide。

哦,情况是这样的,由于对系统的高可用性要求比较高,我们用streams是必须的,是基本前提条件,所有的业务都是基于streams基础上来开展的,现在遇到的是其中的一个技术问题:
通过streams同步过来的数据,为何在目标端db的表trigger中无法读取到user变量的值?

BlueskyWide 2009-01-04
  • 打赏
  • 举报
回复
如果db1.A和db2.A在相同的服务器上,仅需要在user2下:select * from user1.A;
如果db1.A和db2.A在不同的服务器上,则建立db_link1后,在user2下:select * from A@db_link1;
BlueskyWide 2009-01-04
  • 打赏
  • 举报
回复
可以换一种思维来实现:
1.以sysdba用户创建同义词(synonym);
2.conn user2/pwd2@str2;
create table test as select * from user1.test where 1=1;
3.如果实时性较强,可以制作一下脚本,将上述过程写入一个存贮过程中。使用JOB,每5分钟执行一次。
Hardware Video Encoding on iPhone — RTSP Server example On iOS, the only way to use hardware acceleration when encoding video is to use AVAssetWriter, and that means writing the compressed video to file. If you want to stream that video over the network, for example, it needs to be read back out of the file. I’ve written an example application that demonstrates how to do this, as part of an RTSP server that streams H264 video from the iPhone or iPad camera to remote clients. The end-to-end latency, measured using a low-latency DirectShow client, is under a second. Latency with VLC and QuickTime playback is a few seconds, since these clients buffer somewhat more data at the client side. The whole example app is available in source form here under an attribution license. It’s a very basic app, but is fully functional. Build and run the app on an iPhone or iPad, then use Quicktime Player or VLC to play back the URL that is displayed in the app. Details, Details When the compressed video data is written to a MOV or MP4 file, it is written to an mdat atom and indexed in the moov atom. However, the moov atom is not written out until the file is closed, and without that index, the data in mdat is not easily accessible. There are no boundary markers or sub-atoms, just raw elementary stream. Moreover, the data in the mdat cannot be extracted or used without the data from the moov atom (specifically the lengthSize and SPS and PPS param sets). My example code takes the following approach to this problem: Only video is written using the AVAssetWriter instance, or it would be impossible to distinguish video from audio in the mdat atom. Initially, I create two AVAssetWriter instances. The first frame is written to both, and then one instance is closed. Once the moov atom has been written to that file, I parse the file and assume that the parameters apply to both instances, since the initial conditions were the same. Once I have the parameters, I use a dispatch_source object to trigger reads from the file whenever new data is written. The body of the mdat chunk consists of H264 NALUs, each preceded by a length field. Although the length of the mdat chunk is not known, we can safely assume that it will continue to the end of the file (until we finish the output file and the moov is added). For RTP delivery of the data, we group the NALUs into frames by parsing the NALU headers. Since there are no AUDs marking the frame boundaries, this requires looking at several different elements of the NALU header. Timestamps arrive with the uncompressed frames from the camera and are stored in a FIFO. These timestamps are applied to the compressed frames in the same order. Fortunately, the AVAssetWriter live encoder does not require re-ordering of frames. When the file gets too large, a new instance of AVAssetWriter is used, so that the old temporary file can be deleted. Transition code must then wait for the old instance to be closed so that the remaining NALUs can be read from the mdat atom without reading past the end of that atom into the subsequent metadata. Finally, the new file is opened and timestamps are adjusted. The resulting compressed output is seamless. A little experimentation suggests that we are able to read compressed frames from file about 500ms or so after they are captured, and these frames then arrive around 200ms after that at the client app. Rotation For modern graphics hardware, it is very straightforward to rotate an image when displaying it, and this is the method used by AVFoundation to handle rotation of the camera. The buffers are captured, encoded and written to file in landscape orientation. If the device is rotated to portrait mode, a transform matrix is written out to the file to indicate that the video should be rotated for playback. At the same time, the preview layer is also rotated to match the device orientation. This is efficient and works in most cases. However, there isn’t a way to pass this transform matrix to an RTP client, so the view on a remote player will not match the preview on the device if it is rotated away from the base camera orientation. The solution is to rotate the pixel buffers after receiving them from the capture output and before delivering them to the encoder. There is a cost to this processing, and this example code does not include this extra step.

3,494

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧