本文包括oracle的回滚段，触发器，同义词以及dblink的创建实例和管理
创建dblink实例
给wmc_test用户创建一个到数据库192.168.100.108的dblink
1．使用管理员授权.
GRANT CREATE PUBLIC DATABASE LINK TO wmc_test;
grant create database link to wmc_test;
2.创建
create database link connectwmc
connect to wmc identified by "zhyhl_wmc_0406"
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.100.108)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
3.应用示例
使用dblink创建表，并复制99行。
create table SENDMSG as select * from SENDMSG@connectwmc where rownum<100;
按照条件插入表99行
insert into SENDMSG select * from SENDMSG@connectwmc where TYPE = 'MMS' and rownum<1000;
创建回滚段
CREATE ROLLBACK SEGMENT "RBS29"
TABLESPACE "RBS"
STORAGE ( INITIAL 512K NEXT 512K OPTIMAL NULL MINEXTENTS 8
MAXEXTENTS 4096)
ALTER ROLLBACK SEGMENT "RBS29" ONLINE
查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name
同义词
SQL> conn hr/hr
已连接。
SQL> create synonym syn_employees for employees;
同义词已创建。
SQL> conn /as sysdba
已连接。
SQL> create public synonym syn_employees for hr.employees;
同义词已创建。
查看同义词的名称
select * from user_synonyms;
创建触发器
create or replace trigger hang_info_offline_trig_bak
before insert on HANG_INFO_OFFLINE_bak
for each row
begin
select HANG_INFO_OFFLINE_SEQ.nextval into :new.INFO_ID from dual;
end;
查看触发器代码
确定触发器的名称
desc user_triggers;
select TRIGGER_NAME from user_triggers;
查看触发器代码
set long 50000;
set heading off;
set pagesize 2000;
select
'create or replace trigger "' ||
trigger_name || '"' || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
chr(10) ||
triggering_event || chr(10) ||
'ON "' || table_owner || '"."' ||
table_name || '"' || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ) || chr(10) ,
trigger_body
from user_triggers where trigger_name='triggername';
关闭和开启触发器
确定触发器状态（ENABLED 在用DIASABLE 停用）
select trigger_name,table_name from user_triggers where status='ENABLED';
修改触发器状态
alter trigger trigger_name disable; 修改为停用
alter trigger trigger_name enable; 修改为可用
欢迎关注我的公众号 扫描二维码或公众号搜索 “我的工作”
