時(shí)間：2015-06-28 00:00:00 來(lái)源：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來(lái)增加,刪除,修改列的語(yǔ)法
oracle中使用SQL語(yǔ)句修改字段類型-oracle修改SQL語(yǔ)句案例
誤刪Oracle數(shù)據(jù)庫(kù)實(shí)例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語(yǔ)句修改字段類型-oracle修改SQL語(yǔ)句案例 Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法 ORACLE SQL 判斷字符串是否為數(shù)字的語(yǔ)句 ORACLE和SQL語(yǔ)法區(qū)別歸納(1) oracle grant 授權(quán)語(yǔ)句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法