Re: ** MV questions

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 25 Oct 2007 12:12:46 -0700
Message-ID: <bf46380710251212w59a22129h56a67de9d8ccfef3@mail.gmail.com>

On 10/25/07, A Joshi <ajoshi977_at_yahoo.com> wrote:
> Hi,
> 1) I am gettting error ORA-12014 when creating a MV on local table with
> complete refresh. Not on MV log create. Sounds strange.
> Primary key cannot be created on this table. I do not need fast refresh
> anyway. Why does it need PK for complete refresh. I tried creating a MV log
> with rowid option on the table and that did not help either.
> Any workaround.

Seeing the SQL might help. If you used ROWID, you should not see this error.

> 2) Is there any issue with creating a MV on a MV. For some reason access is
> not available to the original table.

Look in the docs for 'Nested' Materialized Views

>
> 5) I have schema schemaA which has a table tableA, tableB. Another schema
> schemaB has a view viewA selecting from these tables. There is user userC
> who needs to select from view viewA. I gave grant on schemaA tables to
> schemaB with "grant option". Then select on viewA was granted to userC. Did
> not work. : error :
> ORA-01031: insufficient privileges
> Then after long struggle I gave select any table select any dictionary to
> userD. It works with just those two privs. Howver if I grant select on viewA
> to userC it does not work. STrange.

You may need to review what you have done. The following example worked for me on 9.2.0.8:

As DBA:

create user user_a identified by user_a profile barebones default tablespace users temporary tablespace temp; create user user_b identified by user_b profile barebones default tablespace users temporary tablespace temp; create user user_c identified by user_c profile barebones default tablespace users temporary tablespace temp;

grant connect, resource to user_a;
grant connect, resource to user_b;
grant connect, resource to user_c;

As user USER_A:

create table t1 as select sysdate today from dual;

grant select on t1 to user_b with grant option;

As USER_B:
create view tv
as
select *
from user_a.t1
/

grant select on tv to user_c;

And finally as USER_C:

> sqlplus user_c/user_c

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Oct 25 12:11:18 2007

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option
JServer Release 9.2.0.8.0 - Production

12:11:27 user_c_at_orcl SQL> select * from user_b.tv;

TODAY

10/25/2007 12:08:14

1 row selected.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 25 2007 - 14:12:46 CDT