RE: ** MV questions

Here is an example showing how to update a materialized view using exchange 
partition.  The catch to this is you will need to rebuild indexes afterwards, 
and I believe the materialized view itself will be STALE.

SQL> create materialized view junk as select table_name from dba_tables;

Materialized view created.

SQL> create table junk2
  2  partition by hash (table_name) (partition junk2_p1)
  3  as select * from junk
  4  where 1 = 0
  5  /

Table created.

SQL> alter table junk2 exchange partition junk2_p1 with table junk;

Table altered.

SQL> update junk2 set table_name = 'hello';

737 rows updated.

SQL> commit;

Commit complete.

SQL> alter table junk2 exchange partition junk2_p1 with table junk;

Table altered.

SQL> select distinct table_name from junk;

Table Name
---------------------
hello

1 row selected.


Pat



________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of A Joshi
Sent: Tuesday, October 30, 2007 2:15 PM
To: ajoshi977@xxxxxxxxx; jkstill@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: ** MV questions

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
--
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


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


___________________________________________________________________________________________________
CONFIDENTIALITY AND PRIVACY NOTICE
Information transmitted by this email is proprietary to Medtronic and is 
intended for use only by the individual or entity to which it is addressed, and 
may contain information that is private, privileged, confidential or exempt 
from disclosure under applicable law. If you are not the intended recipient or 
it appears that this mail has been forwarded to you without proper authority, 
you are notified that any use or dissemination of this information in any 
manner is strictly prohibited. In such cases, please delete this mail from your 
records.

To view this notice in other languages you can either select the following link 
or manually copy and paste the link into the address bar of a web browser: 
http://emaildisclaimer.medtronic.com

Other related posts: