| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** MV questions
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 <jkstill_at_gmail.com> 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 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_at_orcl SQL> select * from user_b.tv;
TODAY
1 row selected.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist -- http://www.freelists.org/webpage/oracle-l __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 29 2007 - 10:34:33 CDT
 |
 |