時(shí)間：2015/6/28來源：IT貓撲網(wǎng)作者：網(wǎng)管聯(lián)盟我要評(píng)論(0)
1.創(chuàng)建表
-- 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.創(chuàng)建日志表
-- 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#副標(biāo)題#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.創(chuàng)建存儲(chǔ)
create or replace procedure pr_t_OBJECT(
i_rows number :=150 --每秒跑業(yè)務(wù)條數(shù)
) 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.創(chuàng)建觸發(fā)器
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.創(chuàng)建序列
-- Create sequence
create sequence SEQ_T_OBJECT
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 100;
6.創(chuàng)建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))'); --設(shè)置間隔為1秒
commit;
end;
/
關(guān)鍵詞標(biāo)簽：ORACLE同步
相關(guān)閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列
oracle中使用SQL語句修改字段類型-oracle修
使用低權(quán)限Oracle數(shù)據(jù)庫賬戶得到管理員權(quán)限
Oracle對(duì)user的訪問控制
人氣排行 ORACLE SQL 判斷字符串是否為數(shù)字的語句Oracle中使用alter table來增加,刪除,修改列的語法ORACLE和SQL語法區(qū)別歸納(1)oracle grant 授權(quán)語句如何加速Oracle大批量數(shù)據(jù)處理Oracle刪除表的幾種方法ORACLE修改IP地址后如何能夠使用Oracle 10g創(chuàng)建表空間和用戶并指定權(quán)限