Re: ** materialized view fast not working
Does the user have select access on the mlog? A Joshi wrote: Hi, I have a table tableA owned by userA. If I create a materialized view in another schema/user fast and complete refresh set to refresh every 10 minutes then only complete refresh works and refreshes automatically every 10 minutes. However the fast refresh mv does not refresh automatically. It refreshes fine if the user executes DBMS_SNAPSHOT.REFRESH procedure. If I create a materialized view in the same user fast and complete refresh set to refresh every 10 minutes both work fine and refresh automatically every 10 minutes. Can some one help. Thank you Do you Yahoo!? The New Yahoo! Shopping http://shopping.yahoo.com/?__yltc=s%3A15443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail - with improved product search -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ** materialized view fast not working
Mike : Yes the user has select access and is able to do a select on the MLOG table and the original table. Thanks for your help.Mike Spalinger [EMAIL PROTECTED] wrote: Does the user have select access on the mlog?A Joshi wrote: Hi, I have a table tableA owned by userA. If I create a materialized view in another schema/user fast and complete refresh set to refresh every 10 minutes then only complete refresh works and refreshes automatically every 10 minutes. However the fast refresh mv does not refresh automatically. It refreshes fine if the user executes DBMS_SNAPSHOT.REFRESH procedure. If I create a materialized view in the same user fast and complete refresh set to refresh every 10 minutes both work fine and refresh automatically every 10 minutes. Can some one help. Thank you Do you Yahoo!! ? The New Yahoo! Shopping - with improved product search-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mike SpalingerINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Exclusive Video Premiere - Britney Spears
Re: Materialized view and index
Chuan, You can crteate indexes on MVs if you want to - there is nothing against it; in fact it may be specifically desirable to do so. MVs are designed to help in query optimization by selecting against a materialized collection f data as opposed to selecting from a set of tables as in case of normal views and hence you can create as many indexes as you like to improve access plan. The only downside I can think of, and it is pretty minimal, is increased time spent on index maintenance - be it in a fast refresh or complete one. But the cost is marginal compared to the benefit. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 10:29 PM Hi, All Wondering whether anyone created indexes on materialized view to further improve the performance? What's the pros and cons of this method? Thanks in advance. Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Materialized view and index
Creating Indexes on MV is pretty ok and it is good for performance. I even create mv on mv to further improve the performance of some really frequently executed SQL.:) Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 11:29 AM Hi, All Wondering whether anyone created indexes on materialized view to further improve the performance? What's the pros and cons of this method? Thanks in advance. Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized view
I'm not sure if you want to improve the query, or the replication process. If it's the replication process, one other option is 'refresh force'. Then, if I understand the docs correctly, you get a fast refresh if it can; and if refresh fast is not possible, then refresh complete. That's the theory. Now whether or not it actually works that way For what it's worth, I always use 'refresh force'. -Original Message- How can I create materialized view(mv) for the following statement using 'refresh fast' method. I can use 'refresh complete' option to create this mv, but how can I use 'refresh fast' option for the below statement in order to improve performance in query. Any workaround ? select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) from sales_master mas, sales_details del where mas.transaction_id =del.transaction_id group by mas.salesrep_id. Thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized view
Title: RE: Materialized view don't you need a 'count(*)' for refresh fast? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Stephen Lee [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 26, 2003 11:50 AM To: Multiple recipients of list ORACLE-L Subject: RE: Materialized view I'm not sure if you want to improve the query, or the replication process. If it's the replication process, one other option is 'refresh force'. Then, if I understand the docs correctly, you get a fast refresh if it can; and if refresh fast is not possible, then refresh complete. That's the theory. Now whether or not it actually works that way For what it's worth, I always use 'refresh force'. -Original Message- How can I create materialized view(mv) for the following statement using 'refresh fast' method. I can use 'refresh complete' option to create this mv, but how can I use 'refresh fast' option for the below statement in order to improve performance in query. Any workaround ? select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) from sales_master mas, sales_details del where mas.transaction_id =del.transaction_id group by mas.salesrep_id. Thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Materialized view
For fast refresh, you need to have a snapshot log on the master table and the snapshot must be registered with the master. Keep in mind, we are only talking about simple, read-only, snapshots here. There are fancier ways of moving data here and there. But I would have to do the RTFM thing before expounding on those. -Original Message- don't you need a 'count(*)' for refresh fast? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, June 26, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I'm not sure if you want to improve the query, or the replication process. If it's the replication process, one other option is 'refresh force'. Then, if I understand the docs correctly, you get a fast refresh if it can; and if refresh fast is not possible, then refresh complete. That's the theory. Now whether or not it actually works that way For what it's worth, I always use 'refresh force'. -Original Message- How can I create materialized view(mv) for the following statement using 'refresh fast' method. I can use 'refresh complete' option to create this mv, but how can I use 'refresh fast' option for the below statement in order to improve performance in query. Any workaround ? select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) from sales_master mas, sales_details del where mas.transaction_id =del.transaction_id group by mas.salesrep_id. Thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: materialized view of a remote partition
Hi Ray, Please let me know if you received any answer. Thanks, Ashoke -Original Message- Sent: Friday, September 06, 2002 10:23 AM To: Multiple recipients of list ORACLE-L 8.1.7.4 create snapshot TEMP as select * from [EMAIL PROTECTED] partition (020904) ERROR at line 1: ORA-14100: partition extended table name cannot refer to a remote object why not? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: materialized view of a remote partition
On Fri, Sep 06, 2002 at 07:23:28AM -0800, Ray Stell wrote: 8.1.7.4 create snapshot TEMP as select * from [EMAIL PROTECTED] partition (020904) ERROR at line 1: ORA-14100: partition extended table name cannot refer to a remote object Well, it ain't pretty, but... on the remote db: SQL create snapshot eh_snap as select * from emp partition (e18); Materialized view created. on the local db: SQL create snapshot eh_snap as select * from [EMAIL PROTECTED]; Materialized view created. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Materialized view selection.
Depends mostly on your users needs. If most of the time your users would want monthly sum of measures, you give them a materialized view for that. You can also use the summary advisor feature of oracle (dbms_olap) to find out which mviews oracle thinks is needed based on usage. =) Chuan Zhang wrote: Hi, All, Just wonder what's tool or methodology you are using to select materialized view in Oracle data warehouse environment? Many thanks in advance, Chuan Name: InterScan_Disclaimer.txt InterScan_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7bit -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized View
Title: RE: Simple question on logging.. Not sure but I think you need to have Enterprise Edition for that. Otherwise it should work fine Aleem -Original Message- From: Mandal, Ashoke [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 5:18 AM To: Multiple recipients of list ORACLE-L Subject: Materialized View Hi All, Is it true that I cannot create a materialized view by accessing a remote table? Here is the syntax for creating the materialized view but it does not work. REM Create materialized view mast_time_mv CREATE MATERIALIZED VIEW mast_time_mv PCTFREE 0 TABLESPACE mviews_tb STORAGE (INITIAL 256k NEXT 256k PCTINCREASE 0) PARALLEL BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT * from [EMAIL PROTECTED]; Where WHST.TEST.CORP.ICI.COM is database link. Thanks, Ashoke
Re: Materialized View
I would start with checking what database links are vissible. Do a select * from all_db_links. A database link in the invoking schema should take precedence over a public one. Whatever link is being used, verify that you can connect to the remote database using it's connection symantics. You can pull the clear text password out of sys.link$ for this puspose. Bill --- Atul Kumar [EMAIL PROTECTED] wrote: I tried following SQL SQL create materialized view mat_dept 2 refresh fast with rowid 3 start with sysdate next sysdate+ 1/(24*60) 4 as (select * from dept@softek1); as (select * from dept@softek1) * ERROR at line 4: ORA-01435: user does not exist ORA-06512: at SYS.DBMS_SNAPSHOT_UTL, line 845 ORA-06512: at line 1 Why such error is coming and how can i create materialized view? Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Attn. : Bill Pass (RE: Materialized View)
Hi Bill Thanks for your reply. In fact when I try following SQL i get result (few records available in table DEPT) select * from dept@softek1 I could not understand the meaning of your statement You can pull the clear text password out of sys.link$ for this puspose.. Please clarify and help. Also, please note that I am not a DBA instead I am an Oracle Developer. Thanks again Atul Kumar -Original Message- Sent: Wednesday, April 24, 2002 7:54 PM To: Multiple recipients of list ORACLE-L I would start with checking what database links are vissible. Do a select * from all_db_links. A database link in the invoking schema should take precedence over a public one. Whatever link is being used, verify that you can connect to the remote database using it's connection symantics. You can pull the clear text password out of sys.link$ for this puspose. Bill --- Atul Kumar [EMAIL PROTECTED] wrote: I tried following SQL SQL create materialized view mat_dept 2 refresh fast with rowid 3 start with sysdate next sysdate+ 1/(24*60) 4 as (select * from dept@softek1); as (select * from dept@softek1) * ERROR at line 4: ORA-01435: user does not exist ORA-06512: at SYS.DBMS_SNAPSHOT_UTL, line 845 ORA-06512: at line 1 Why such error is coming and how can i create materialized view? Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized View over 2 tables
Title: Materialized View over 2 tables Thats not a problem. That is well-documented feature and you can check it with standard documents. Fast Refreshes are meant only for simple MVs/Snapshots and not for complex ones. You are supposed to do a complete refresh for complex ones. HTH, Rajesh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Daiminger, Helmut Sent: Tuesday, October 30, 2001 11:40 AM To: Multiple recipients of list ORACLE-L Subject: Materialized View over 2 tables Hi! Can I have a materialized view to be refreshed fast, when it is created over 2 base tables? Fast refreshes work fine as long as the materialized view is based on 1 base table. But if the view is based on more base tables, it only seems to work with a complete refresh. Does anybody have any experience with that problem? This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: Materialized View over 2 tables
Helmut, Rajesh is absolutely correct. If one or more of the base tables are in a remote db then the fast refresh restrictions in the Replication manual apply; if all the base tables are local then the fast refresh restrictions in the Data Warehousing manual apply. A workaround would be to create simple snapshots off of the base tables so they can be fast refreshed and then either create a view or another snapshot off of the base tables. HTH, -- Anita --- Rajesh Dayal [EMAIL PROTECTED] wrote: That's not a problem. That is well-documented feature and you can check it with standard documents. Fast Refreshes are meant only for simple MVs/Snapshots and not for complex ones. You are supposed to do a complete refresh for complex ones. HTH, Rajesh -Original Message- Helmut Sent: Tuesday, October 30, 2001 11:40 AM To: Multiple recipients of list ORACLE-L Hi! Can I have a materialized view to be refreshed fast, when it is created over 2 base tables? Fast refreshes work fine as long as the materialized view is based on 1 base table. But if the view is based on more base tables, it only seems to work with a complete refresh. Does anybody have any experience with that problem? This is 8.1.7 on Sun Solaris. Thanks, Helmut __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Materialized View/dbms_mview.refresh Question
Just solved it...Duh!
as sys
grant alter any materialized view to User_B;
--- mohammed bhatti [EMAIL PROTECTED] wrote:
I have a materialized view created by user A in
schema
A. The view consists of simple table joins and
union
statements. I want user B to be able to execute the
refresh on the MV using dbms_mview.refresh.
I've granted select on all the tables to user B
along
with creating public synonyms for the tables. I've
also granted all privileges on the MV to user B and
granted REWRITE privs to user B.
When I exec dbms_mview.refresh('A.MVIEW_NAME', 'A');
as user B, I get the following error:
ORA-01031: Insufficent privileges
ORA-06512: at SYS.DBMS_SNAPSHOT, line 610
ORA-06512: at SYS.DBMS_SNAPSHOT, line 667
ORA-06512: at SYS.DBMS_SNAPSHOT, line 647
ORA-06512: at line 1
Any thoughts?
Thanks
__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: mohammed bhatti
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mohammed bhatti
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Materialized View Problem
Can you post the entire CREATE statement? Rick -Original Message- From: Terri Williamson/MIS/HQ/KEMET/US [SMTP:[EMAIL PROTECTED]] Sent: Thursday, February 22, 2001 9:26 AM To: Multiple recipients of list ORACLE-L Subject: Materialized View Problem Hi All, I am trying to create a materialized view that contains a subquery for the FROM clause. I can run the sql in the view fine, but when I try to create the view I get the following error: MES_LOT a, * ERROR at line 27: ORA-00998: must name this expression with a column alias This is the first table listed in the subquery. As you can see, I have the table aliased. Does anyone have an idea of what could be causing this error, or what I am doing wrong? Thanks; Terri Williamson Kemet Electronics [EMAIL PROTECTED] (864) 963-6428 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terri Williamson/MIS/HQ/KEMET/US INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
