1.创建表
-- Create table
create table T_OBJECT
(
ID NUMBER not null,
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_OBJECT
add constraint PK_T_OBJECT primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table
create table T_COUNT
(
TNAME VARCHAR2(100),
O VARCHAR2(20),
C NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
2.创建日志表
-- Create table
create table LOG_T_OBJECT
(
N_ID NUMBER(23) not null,
ID NUMBER(23) not null,
O VARCHAR2(20) not null,
CHANGE_DATE TIMESTAMP(6) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
#p#副标题#e#
-- Create/Recreate primary, unique and foreign key constraints
alter table LOG_T_OBJECT
add constraint PK_LOG_T_OBJECT primary key (N_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
3.创建存储
create or replace procedure pr_t_OBJECT(
i_rows number :=150 --每秒跑业务条数
) is
v_rand number;
v_rand2 number;
v_count number;
v_start number :=1;
v_end number :=1000000;
begin
for i in 1..i_rows
loop
select TRUNC(dbms_random.value(v_start,v_end)) into v_rand from dual;
select count(*) into v_count from t_OBJECT where id=v_rand;
if v_count=0 then
insert into t_OBJECT select v_rand, t.* from all_OBJECTs t where rownum=1;
else
select TRUNC(dbms_random.value(v_start,v_end)) into v_rand2 from dual;
if mod(v_rand2,2)=0 then --删除操作
delete t_OBJECT where id=v_rand;
else --update操作
update t_OBJECT set OBJECT_name = 'update后的值' where id = v_rand;
end if;
end if;
--commit;
--dbms_lock.sleep(1/i_rows);
end loop;
commit;
end pr_t_OBJECT;
4.创建触发器
create or replace trigger tr_count_t_object
after insert or update or delete on t_object
for each row
declare
-- local variables here
begin
IF INSERTING THEN
insert into log_t_object(n_id, id, o, change_date)
values (SEQ_T_OBJECT.nextval, :new.id, 'I', systimestamp);
ELSIF DELETING THEN
insert into log_t_object(n_id, id, o, change_date)
values (SEQ_T_OBJECT.nextval, :old.id, 'D', systimestamp);
else
insert into log_t_object(n_id, id, o, change_date)
values (SEQ_T_OBJECT.nextval, :new.id, 'U', systimestamp);
end if;
end tr_count_t_object;
5.创建序列
-- Create sequence
create sequence SEQ_T_OBJECT
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 100;
6.创建JOB
declare
v_job int;
begin
dbms_job.submit(job => v_job,
what => 'PR_T_OBJECT();',
next_date => sysdate,
interval => 'sysdate + (1/(24*60*60))'); --设置间隔为1秒
commit;
end;
/
关键词标签：ORACLE同步
相关阅读
热门文章
Oracle中使用alter table来增加,删除,修改列
oracle中使用SQL语句修改字段类型-oracle修
使用低权限Oracle数据库账户得到管理员权限
Oracle对user的访问控制
人气排行 ORACLE SQL 判断字符串是否为数字的语句Oracle中使用alter table来增加,删除,修改列的语法ORACLE和SQL语法区别归纳(1)oracle grant 授权语句如何加速Oracle大批量数据处理Oracle删除表的几种方法ORACLE修改IP地址后如何能够使用Oracle 10g创建表空间和用户并指定权限
查看所有0条评论>>