原理: 写系统触发器,在修改数据库结构的时候,把DDL写入表中
create sequence A_Ver_Control_seq
minvalue 1
nomaxvalue
start with 1
increment by 1
nocache;
/
--
--
--drop trigger tri_A_Ver_Control_ID
--/
--
--drop table A_Ver_Control cascade co
nstraints
/
create table A_Ver_Control
(
iAutoID INTEGER not null,
event VARCHAR2(20),
user_name VARCHAR2(10),
owner VARCHAR2(10),
obj_name VARCHAR2(500),
obj_ddl clob,
obj_type VARCHAR2(10),
constraint PK_A_Ver_Control primary key (iAutoID)
)
/
create trigger tri_A_Ver_Control_ID before insert
on A_Ver_Control for each row
begin
select A_Ver_Control_SEQ.NEXTVAL INTO :new.iAutoID from dual;
end;
/
comment on table A_Ver_Control is
'系统版本控制';
comment on column A_Ver_Control.event is
'事件';
comment on column A_Ver_Control.user_name is
'用户';
comment on column A_Ver_Control.owner is
'所有者';
comment on column A_Ver_Control.obj_name is
'对象名';
comment on column A_Ver_Control.obj_ddl is
'对象ddl';
comment on column A_Ver_Control.obj_type is
'对象类型';
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON fspcsbby.schema
BEGIN
INSERT INTO A_Ver_Control(event,user_name,owner,obj_name,obj_type,Obj_Ddl)
select ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,dbms_metadata.get_ddl(ora_dict_obj_type,ora_dict_obj_name) from dual;
exception
when others then null;
END;
/