Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h8GMiZ611806 for ; Tue, 16 Sep 2003 17:44:35 -0500 X-ClientAddr: 66.27.56.210 Received: from ns3.fatcity.com (rrcs-west-66-27-56-210.biz.rr.com [66.27.56.210]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h8GMiY511801 for ; Tue, 16 Sep 2003 17:44:35 -0500 Received: from ns3.fatcity.com (localhost.localdomain [127.0.0.1]) by ns3.fatcity.com (8.12.5/8.12.5) with ESMTP id h8GGMD1W023704 for ; Tue, 16 Sep 2003 09:29:44 -0700 Received: (from root@localhost) by ns3.fatcity.com (8.12.5/8.12.5/Submit) id h8GEPQ7E003849 for oracle-l@orafaq.net; Tue, 16 Sep 2003 07:25:27 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005D00D9; Tue, 16 Sep 2003 07:24:40 -0800 Message-ID: Date: Tue, 16 Sep 2003 07:24:40 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Mercadante, Thomas F" Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Mercadante, Thomas F" Subject: RE: 'query rewrite' system privg Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit My guess is that just because the Mat View is enabled for query rewrite doesn't mean that it's going to happen. The Query Rewite attribute on the Mat View probably indicates that it is eligible for rewrite. Tom Mercadante Oracle Certified Professional -----Original Message----- Sent: Tuesday, September 16, 2003 11:05 AM To: Multiple recipients of list ORACLE-L Hello list , I am using oracle 9.2.0.1.0 enterprise edition on windows A user in my database is able to create materialized views with query rewrite enabled. But I have not given him 'query rewrite' system privilege. Isn't it required to create a mat. view with query rewrite enabled ? drop user test cascade; create user test identified by test default tablespace users temporary tablespace temp quota unlimited on users; grant create session, create table, create materialized view to test ; Now this user is able to create materialized view mymatview tablespace users build immediate refresh on demand enable query rewrite as select * from dual ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: