关于物化视图的疑问-白红宇

关于物化视图的疑问

阅读量：2445 次

发布时间：2019-05-10

本文共 3941 字，大约阅读时间需要 13 分钟。

--初始化操作，

创建两个用户一个，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