Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** MV questions

Re: ** MV questions

From: A Joshi <>
Date: Mon, 29 Oct 2007 08:34:33 -0700 (PDT)
Message-ID: <>

Thanks Jared, Thomas, Taylor. Apreciated.    

  Can someone answer :    

  For getting sql being executed by a session I generally use v$sqlarea.sql_text by joining it with address. However, I see that sometimes if a session is doing DDL like create I cannot get it. Is there some other way of getting ddl. Especially in case of heavy load and trying to find out what a long running session is doing.   

Jared Still <> wrote:
  On 10/25/07, A Joshi 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


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;

create view tv
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 - Production on Thu Oct 25 12:11:18 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

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

12:11:27 user_c_at_orcl SQL> select * from;


10/25/2007 12:08:14

1 row selected.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
Received on Mon Oct 29 2007 - 10:34:33 CDT

Original text of this message