RE: Function based index - insufficient priveleges
hi i found this on metalink the proper privilege required to create function-based indexes. Connect as dba and provide the user with the privileges required to create a function based index. If the user is creating indexes in their own schema: SVRMGR grant query rewrite to ; If the user is creating indexes in schemas other than their own: SVRMGR grant global query rewrite to ; -Oorspronkelijk bericht- Van: Andor, Gyula [SMTP:[EMAIL PROTECTED]] Verzonden:maandag 28 mei 2001 12:45 Aan: Multiple recipients of list ORACLE-L Onderwerp:Function based index - insufficient priveleges Hi Gurus ! Please help me. I can't create function based index because it says: ERROR at line 1: ORA-01031: insufficient privileges I have a table which contains a column called lang_name. I try to create function based index on the table with the following sql statement: create index func_idx_lang_name on lang (upper(lang_name)); I have create any index privilege Enviroment: Oracle 8.1.6 on WinNT Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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: 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).
Function based index - insufficient priveleges
Hi Gurus ! Please help me. I can't create function based index because it says: ERROR at line 1: ORA-01031: insufficient privileges I have a table which contains a column called lang_name. I try to create function based index on the table with the following sql statement: create index func_idx_lang_name on lang (upper(lang_name)); I have "create any index privilege" Enviroment: Oracle 8.1.6 on WinNT Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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: Function based index - insufficient priveleges
When in doubt, check the manual ;) Manuals are available in pdf format from metalink and in html and pdf format from http://technet.oracle.com (free registration). Per the SQL Reference manual on the create index statement: To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. In both cases, the table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index. In addition, in order for Oracle to use function-based indexes in queries, the QUERY_REWRITE_ENABLED parameter must be set to true, and the QUERY_REWRITE_INTEGRITY parameter must be set to trusted. Also see Note: 66277.1 Oracle 8i: Concepts and Usage of Function Based Indexes HTH, -- Anita --- Andor, Gyula [EMAIL PROTECTED] wrote: Hi Gurus ! Please help me. I can't create function based index because it says: ERROR at line 1: ORA-01031: insufficient privileges I have a table which contains a column called lang_name. I try to create function based index on the table with the following sql statement: create index func_idx_lang_name on lang (upper(lang_name)); I have create any index privilege Enviroment: Oracle 8.1.6 on WinNT Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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! Auctions - buy the things you want at great prices http://auctions.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: Function based index - insufficient priveleges
Thank you ! This solved the problem. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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: Function based index - insufficient priveleges
Oracle manual states that in order to use function based indexes in queries the query_rewrite_enabled parameter needs to be set to 'true', and the query_rewrite_integrity parameter to 'trusted', apart from granting the query rewrite privilege. rgds amar -Original Message- Sent: Monday, May 28, 2001 3:00 PM To: Multiple recipients of list ORACLE-L hi i found this on metalink the proper privilege required to create function-based indexes. Connect as dba and provide the user with the privileges required to create a function based index. If the user is creating indexes in their own schema: SVRMGR grant query rewrite to ; If the user is creating indexes in schemas other than their own: SVRMGR grant global query rewrite to ; -Oorspronkelijk bericht- Van: Andor, Gyula [SMTP:[EMAIL PROTECTED]] Verzonden:maandag 28 mei 2001 12:45 Aan: Multiple recipients of list ORACLE-L Onderwerp:Function based index - insufficient priveleges Hi Gurus ! Please help me. I can't create function based index because it says: ERROR at line 1: ORA-01031: insufficient privileges I have a table which contains a column called lang_name. I try to create function based index on the table with the following sql statement: create index func_idx_lang_name on lang (upper(lang_name)); I have create any index privilege Enviroment: Oracle 8.1.6 on WinNT Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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: 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: Amar Kumar Padhi 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: Function based index - insufficient priveleges
You need to set a couple of parameters in the init.ora, these are query_rewrite_enabled = true query_rewrite_integrity = trusted and you need to grant the privilege QUERY_REWRITE to the user. HTH. Zabair _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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).
