物理视图触发器、作业与调度、表分区相关
来源:互联网 发布:盘锦淘宝装修 编辑:程序博客网 时间:2024/04/28 03:51
1、基于实体视图的触发器(每一个实体视图都有对应的数据库表)
create or replace TRIGGER TRIGGER_MV_TEST01
AFTER INSERT OR DELETE OR UPDATE ON MV_TEST01
REFERENCING OLD AS OOO NEW AS NNN
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO EMPLOYEE_BAK VALUES(:NNN.LID,:NNN.STRNAME,:NNN.STRADDRESS);
ELSIF DELETING THEN
DELETE FROM EMPLOYEE_BAK WHERE EMPLOYEE_BAK.LID = :OOO.LID;
ELSIF UPDATING THEN
UPDATE EMPLOYEE_BAK SET EMPLOYEE_BAK.STRNAME=:NNN.STRNAME,EMPLOYEE_BAK.STRADDRESS=:NNN.STRADDRESS WHERE EMPLOYEE_BAK.LID=:OOO.LID;
END IF;
END;
2、实体视图同步时间间隔 REFRESH FAST ON DEMAND START WITH sysdate NEXT SYSDATE + (60/(24*60*60))
// 60秒更新一次
CREATE MATERIALIZED VIEW "GYL"."MV_TEST01"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + (88/(24*60*60))
WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT
EMPLOYEE.LID,
EMPLOYEE.STRNAME,
EMPLOYEE.STRADDRESS
FROM
EMPLOYEE
WHERE
EMPLOYEE.LID < 500;
3、oracle 计划执行
-- 新建调度和作业
begin
dbms_scheduler.create_schedule
(
schedule_name => 'EVERY_30_MINS',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
comments => 'Every 30-mins'
);
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_2',
schedule_name => 'EVERY_30_MINS',
--job_class => 'OLTP_JOBS', --使用指定的job class
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
-- 删除调度和作业
begin
dbms_scheduler.drop_job(job_name => 'ARC_MOVE_2',force => TRUE);
dbms_scheduler.drop_schedule(schedule_name =>'EVERY_30_MINS',force => TRUE);
end;
-- JOB查询
select * from ALL_SCHEDULER_JOBS
-- JOB_CLASS查询
select * from ALL_SCHEDULER_JOB_CLASSES
4、实体视图使用联合
如果表没有视图日志,手工添加视图日志
5、表分区与索引分区
CREATE TABLE RANGETABLE(
LID INT PRIMARY KEY,
STRNAME VARCHAR(20),
GRADE INT
)
PARTITION BY RANGE(GRADE)
(
PARTITION PART1 VALUES LESS THAN(5000) TABLESPACE "USERS",
PARTITION PART2 VALUES LESS THAN(MAXVALUE) TABLESPACE "USERS"
);
CREATE INDEX "GYL"."RANGETABLE_INDEX_STRNAME" ON "GYL"."RANGETABLE" ("STRNAME")
LOCAL
(
PARTITION PART1 TABLESPACE "USERS",
PARTITION PART2 TABLESPACE "USERS"
);
查询某一个表分区
select * from RANGETABLE partition(part1);
- 物理视图触发器、作业与调度、表分区相关
- 第二节:调度器,触发器与作业
- 调度作业相关
- 存储过程和视图、函数、同义词、表分区、触发器等
- 进程调度与作业调度
- 作业调度与进程调度
- 视图与触发器
- oracle 物理表 分区
- 视图、存储过程与触发器
- 进程调度与作业调度的不同点
- 视图与冗余物理表的查询性能测试
- 用存储过程或触发器+作业调度进行数据库同步
- 用存储过程或触发器+作业调度进行数据库同步
- 用存储过程或触发器+作业调度进行数据库同步
- 用存储过程或触发器+作业调度进行数据库同步
- 作业调度框架Quartz 触发器(SimpleTrigger&CronTrigger )配置说明
- 作业调度框架Quartz 触发器(SimpleTrigger&CronTrigger )配置说明
- 作业调度框架 Quartz 学习笔记(二) -- 简单触发器(SimpleTrigger)
- 无盘集群的安装配置——buxybox切根文件系统篇
- linux fg bg ctrl + z jobs & 等命令
- Ruby 命令行 常用命令
- Bootloader介绍(1)
- 超过4G的文件不能拷在8G的U盘里
- 物理视图触发器、作业与调度、表分区相关
- [转]ListCtrl 技巧集
- 由加载数据库驱动的方法Class.forName()看其作用!
- Autoit3我们用来干什么?
- 用微软企业库加密数据库连接字符串
- 2010-02-03
- 抉择形与自学习形道指令-道指令与人工智能
- (转)图论的最佳实践- JGraph
- compat.linux.osrelease: 2.4.2 is not supported错误的解决方法