澳门新葡8455最新网站此刻在此外二个Session试行重新建立Procedure的操作,假如是合营到作者鲜明的20170901的表就记录下来

[oracle@jumper oracle]$ sqlplus eygle/eygle SQL*Plus: Release
9.2.0.4.0 – ProdUCtion on Sat Mar 31 17:52:55 2007 Copyright (c) 1982,
2002, Oracle Corporation. All rights reserved. Connected to:Oracle9i
Enterprise Edition Release 9.2.0.4.0 – ProductionWith the Partitioning
optionJServer Release 9.2.0.4.0 – Production SQL create or replace
PROCEDURE pining2 IS3 BEGIN4 NULL;5 END;6 / Procedure created. SQL
select object_name,last_ddl_time from dba_objects where
object_name=PINING; OBJECT_NAME
LAST_DDL_TIME—————————— ——————-PINING
2007-03-31 17:52:58 SQL create or replace PROCEDURE pining2 IS3 BEGIN4
NULL;5 END;6 / Procedure created. SQL select
object_name,last_ddl_time from dba_objects where
object_name=PINING; OBJECT_NAME
LAST_DDL_TIME—————————— ——————-PINING
2007-03-31 17:54:35

09:40:18 SQL exec calling; PL/SQL procedure successfully completed.
09:41:18 SQL 09:41:18 SQL select object_name,last_澳门新葡8455最新网站,ddl_time from
dba_objects where object_name in (PINING,CALLING); OBJECT_NAME
LAST_DDL_TIME—————————— ——————-CALLING
2007-04-02 09:40:18PINING 2007-04-02 09:40:22

关于ddl触发器,在匹配触发条件的时候总是报错,有没有大佬可以帮忙解答一下呢,求教
数据库是oracle,配置一个ddl触发器,针对direct用户,一旦有删表的操作,如果是匹配到我规定的20170901的表就记录下来
create table ddl_log(create_time date,op_table varchar2,op_user
varchar2,op_type varchar2,is_send number default
‘0’);–新建一张记录事件的表。
CREATE OR REPLACE TRIGGER DROP_DDL_TRIGGER AFTER DROP
ON DIRECT.SCHEMA
declare
col number;
begin
select count into col from dba_objects WHERE owner=’DIRECT’ AND
LAST_DDL_TIME=SYSDATE and OBJECT_NAME=’test_20170901′;
if col>0 then
insert into ddl_log(create_time,op_table,op_type) values((select
sysdate from dual),’test_20170901′,’drop’);
END IF;
END;
然后ddl触发器报错在select那一行说表或视图不存在(其实dba_objects这个表是肯定存在的),接着我有用另一种方法写触发器。
CREATE OR REPLACE TRIGGER DROP_DDL_TRIGGER AFTER DROP
ON DIRECT.SCHEMA
begin
if EXISTS(SELECT 1 FROM dba_objects WHERE owner=’DIRECT’ AND
LAST_DDL_TIME=(SELECT SYSDATE FROM DUAL) AND
OBJECT_NAME=’test_20170901′) THEN
insert into ddl_log(create_time,op_table,op_type)
values(sysdate,’test_20170901′,’drop’);
END IF;
END;
然后又报错说exists只能由于sql语句,请问怎么解决!求教

然而这个变化是否有效呢?请看我接下来的另外一个测试… -to be continued
…. —–

这个操作将一直挂起,直到第一个session的操作完成,此时在第三个session可以观察到Library
Cache Pin的竞争:

$ sqlplus eygle/eygle SQL*Plus: Release 10.2.0.1.0 – Production on Sat
Mar 31 17:44:46 2007 Copyright (c) 1982, 2005, Oracle. All rights
reserved. Connected to:Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 – 64bit ProductionWith the Partitioning, OLAP and Data Mining
options SQL create or replace PROCEDURE pining2 IS3 BEGIN4 NULL;5 END;6
/ Procedure created. SQL alter session set nls_date_format=yyyy-mm-dd
hh24:mi:ss; Session altered. SQL col object_name for a30SQL select
object_name,last_ddl_time from dba_objects where
object_name=PINING; OBJECT_NAME
LAST_DDL_TIME—————————— ——————-PINING
2007-03-31 17:45:25 SQL create or replace PROCEDURE pining2 IS3 BEGIN4
NULL;5 END;6 / Procedure created. SQL select
object_name,last_ddl_time from dba_objects where
object_name=PINING; OBJECT_NAME
LAST_DDL_TIME—————————— ——————-PINING
2007-03-31 17:45:25

我们看到对象PINING的LAST_DDL_TIME已经变化。

在Oracle10g中,这个LAST_DDL_TIME不再变化,这说明在10g中,当我们执行create
or replace PROCEDURE
时,Oracle现在先尝试进行过程检查,假如内容没有变化,则不需要对过程进行重新编译,这可以减少Cache中的Invalidation,
从而可以减少竞争:

前面提到,Oracle10g重建Procedure的处理有所增强,最初看到这个增强的时候,我想这个增强是否可以减少困扰已久的Library
Cache的竞争呢?

在Oracle9i中,即使一个完全相同的过程的重建,Oracle也需要重新编译过程,这个可以从LAST_DDL_TIME看出:

2 IS3 BEGIN4 NULL;5 END;6 /

相关文章

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图