Re: ** MV questions
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: ajoshi977@xxxxxxxxx
- Date: Thu, 25 Oct 2007 12:12:46 -0700
On 10/25/07, A Joshi <ajoshi977@xxxxxxxxx> 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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
