Oracle-L: RE: 'query rewrite' system privg

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 16 Sep 2003 07:24:40 -0800
Message-ID: <F001.005D00D9.20030916072440@fatcity.com>

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: <bulbultyagi_at_now-india.net.in
INET: bulbultyagi_at_now-india.net.in

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: ListGuru_at_fatcity.com (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: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------