Oracle 数据库数据结构（包括存储过程，函数，表，触发器等）版本控制器

 原理： 写系统触发器，在修改数据库结构的时候，把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;

/

 

 

转载于:https://www.cnblogs.com/KevinMO/p/10287171.html