Re: ** MV questions
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: ajoshi977@xxxxxxxxx, jkstill@xxxxxxxxx
- Date: Tue, 30 Oct 2007 12:14:40 -0700 (PDT)
One more question :
I have a MV. Is there a way to remove the MV part and make it a simple table.
Dropping the MV will drop the table and MV too.
I need to update a column. Table is huge and I do not want to go thru
re-creating it. Thanks
For getting sql being executed by a session I generally use
v$sqlarea.sql_text by joining it with address with v$session.
However, I see that sometimes if a session is doing DDL like create I cannot
get it. Even tools like TOAD say : 'no sql for session'. Is there some other
way of getting ddl being executed. Especially in case of heavy load and trying
to find out what a long running session is doing.
A Joshi <ajoshi977@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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@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
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Other related posts:
