关于物化视图的疑问
–初始化操作，
创建两个用户一个，testo,一个test.
在testo上创建表，test上创建物化视图。
SQL> create user testo identified by testo;
User created.
SQL> create user test identified by test;
User created.
SQL> grant connect,resource to testo,test;
Grant succeeded.
SQL> grant create materialized view to test;
Grant succeeded.
SQL> grant create role to testo;
Grant succeeded.
SQL> conn testo/testo;
Connected.
SQL> create table mv as select *from all_objects where object_id is not null and rownum<50;
Table created.
SQL> create role t1;
Role created.
SQL> grant select on testo.mv to t1;
Grant succeeded.
SQL> grant t1 to test;
Grant succeeded.
SQL> conn test/test –连接到test用户，准备创建物化视图。
Connected.
SQL> create materialized view test_mv as select *from testo.mv;–尝试默认的选项，可以成功创建。
Materialized view created.
SQL> drop materialized view test_mv;
Materialized view dropped.
SQL> create materialized view test_mv refresh force on demand with rowid using default local rollback segment disable query rewrite as select *from testo.mv; -尝试指定选项创建，也可以成功创建。
Materialized view created.
SQL> drop materialized view test_mv;
Materialized view dropped.
问题 1：
SQL> conn system/unix11 –连接到system
Connected.
SQL> create materialized view test.test_mv refresh force on demand with rowid using default local rollback segment disable query rewrite as select *from testo.mv;
create materialized view test.test_mv refresh force on demand with rowid using default local rollback segment disable query rewrite as select *from testo.mv
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> desc testo.mv
Name Null? Type
—————————————– ——– —————————-
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)
SQL>
问题 2：
SQL> grant select on testo.mv to public; –赋予public权限
Grant succeeded.
SQL> create materialized view test.test_mv refresh force on demand with rowid using default local rollback segment disable query rewrite as select *from testo.mv
2 ;
create materialized view test.test_mv refresh force on demand with rowid using default local rollback segment disable query rewrite as select *from testo.mv
*
ERROR at line 1:
ORA-01031: insufficient privileges
排除role的影响
SQL> grant select on testo.mv to test;
Grant succeeded.
SQL> create materialized view test.test_mv refresh force on demand with rowid using default local rollback segment disable query rewrite as select *from testo.mv
2 ;
create materialized view test.test_mv refresh force on demand with rowid using default local rollback segment disable query rewrite as select *from testo.mv
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> revoke select on testo.mv from test;
Revoke succeeded.
问题 3（已解决）
SQL> select dbms_metadata.get_ddl(‘MATERIALIZED VIEW’,’MV’,’TEST’) from dual;
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at “SYS.DBMS_METADATA”, line 3773
ORA-06512: at “SYS.DBMS_METADATA”, line 3828
ORA-06512: at “SYS.DBMS_METADATA”, line 5678
ORA-06512: at line 1
生成报错，
确认了下，应该得改成MATERIALIZED_VIEW
SQL> select dbms_metadata.get_ddl(‘MATERIALIZED_VIEW’,’TEST_MV’,’TEST’)from dual;
CREATE MATERIALIZED VIEW “TEST”.”TEST_MV” (“OWNER”, “OBJECT_NAME”, “SUBOBJECT_
NAME”, “OBJECT_ID”, “DATA_OBJECT_ID”, “OBJECT_TYPE”, “CREATED”, “LAST_DDL_TIME”,
“TIMESTAMP”, “STATUS”, “TEMPORARY”, “GENERATED”, “SECONDARY”, “NAMESPACE”, “EDI
TION_NAME”)
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING……..
就可以了。
📌 本文来自作者的 ITPUB 技术博客存档（2013 年），原文链接：https://blog.itpub.net/23718752/viewspace-766193/