| 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
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
1 row selected.
Pat
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_at_yahoo.com> 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_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 __________________________________________________ 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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 30 2007 - 15:07:06 CDT
 |
 |