物理视图触发器、作业与调度、表分区相关

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);