公众号：杨建荣的学习笔记 · 作者：笔记32天 · 发布：2014-04-04 09:40:51 · 原文链接

–初始化操作，

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

就可以了。