Oracle 创建物化视图

一、创建物化视图

--新建表空间
CREATE TABLESPACE MLOG_TBS   
LOGGING   
DATAFILE 'mlog_tbs.dbf'     
SIZE 32M    
AUTOEXTEND ON    
NEXT 32M
MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;  

--方式一：新建物化视图对应的日志表（用于增量同步源表的数据）
CREATE MATERIALIZED VIEW LOG ON UCADM.M_CARD
TABLESPACE MLOG_TBS
WITH PRIMARY KEY;

--查询日志方式
SELECT * FROM all_mview_logs;
SELECT * FROM mlog$_m_card;

--方式二：新建物化视图
CREATE MATERIALIZED VIEW UCADM.VIEW_M_CARD
REFRESH force ON DEMAND
START WITH TO_DATE('15-12-2017 12:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate+1) + 1/24*6;
AS
SELECT 
t.CARDID AS CARDID,
t.EMPLOYEEID AS EMPLOYEEID,
t.EMPLOYEENO AS EMPLOYEENO
FROM UCADM.M_CARD t;

--修改物化视图
alter materialized view UCADM.VIEW_M_CARD
refresh force on demand 
start with to_date('15-12-2017 12:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate+1) + 1/24;

--手动刷新物化视图
exec dbms_mview.refresh('VIEW_M_CARD')
close dbms_refresh ;

--创建存储过程来刷新物化视图
CREATE OR REPLACE PROCEDURE SP_REFRESH_EMPLOYEE
AS
BEGIN
DBMS_REFRESH.refresh('UCADM.VIEW_M_CARD');
END SP_REFRESH_EMPLOYEE;

--在plsql工具中手动刷新物化视图

　　begin
　　dbms_mview.refresh(
　　list=>'view_m_employee',
　　method=>'complete',
　　refresh_after_errors=>true);
　　end;

--注意：不能通过触发器来刷新物化视图！！！

二、创建用户

--Alter the user's password
ALTER USER 用户名 IDENTIFIED BY 新密码

--Alter the user's password when login
--ALTER USER UCADM PASSWORD EXPIRE;

--Create new tablespace
CREATE TABLESPACE 新用户名
  DATAFILE '新文件名.dat'
  SIZE 8M AUTOEXTEND ON;

-- Create the user 
CREATE USER 新用户名 
    IDENTIFIED BY 新密码
    DEFAULT TABLESPACE HFXFYKT 
    QUOTA UNLIMITED ON HFXFYKT 
    TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMITED ON SYSTEM 
    PROFILE DEFAULT;
-- PASSWORD EXPIRE;
-- Grant/Revoke role privileges 
GRANT CONNECT TO HFXFYKT;
-- Grant/Revoke object privileges 
GRANT SELECT, INSERT ON 表名1 TO 新用户名;

 

官方参考资料：

物化视图 https://docs.oracle.com/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#g101239

新建用户 https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm