| 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: Aarrrggghhh!!! Creating an index shouldn't be so hard!
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C02E4E.AA800ABE
Content-Type: text/plain;
charset="iso-8859-1"
Do you have the following init.ora parameters defined? They are required for function-based indices:
query_rewrite_integrity = TRUSTED
query_rewrite_enabled = TRUE
You may also need a particular compatible setting, probably at least 8.1.6
-----Original Message-----
From: Dennis Taylor [mailto:ismgr_at_pctc.com]
Sent: Wednesday, October 04, 2000 6:26 PM
To: Multiple recipients of list ORACLE-L
Subject: Aarrrggghhh!!! Creating an index shouldn't be so hard!
Well, I must admit to being flummoxed. The nature of this problem guarantees that it'll be easy, and I'll be embarrased. Well, tough. I'm tired of beating my head against the wall.
I'm trying to create a function-based index on a table. Here's what I do:
create user sysop identified by password
default tablespace data
temporary tablespace temp;
grant CREATE SESSION to sysop;
grant CREATE TABLE to sysop;
grant UNLIMITED TABLESPACE to sysop;
grant CREATE PUBLIC SYNONYM to sysop;
grant DROP PUBLIC SYNONYM to sysop;
grant CREATE DATABASE LINK to sysop;
grant CREATE VIEW to sysop;
grant CREATE TRIGGER to sysop;
grant CREATE PROCEDURE to sysop;
connect sysop/password
drop table issuer;
create table issuer (
ISSUER_ID number(9) primary key,
ISSUER_NAME varchar(80)
);
create index ISSUER_NAME_IDX on ISSUER (UPPER(ISSUER_NAME));
And here's what I get:
Table created.
create index ISSUER_NAME_IDX on ISSUER (UPPER(ISSUER_NAME))
*
ERROR at line 1:
ORA-01031: insufficient privileges
(The '*' is actually under the 'I' in 'ISSUER_NAME')
First, the reading of ora-1031 isn't even close to making sense. Then, if it's a privilege problem, I can't find any mention of any privilege that I don't have that I should need.
Please relieve my groggy brain by pointing out the obvious problem so I can go hide under my desk. Thanks.
---
Dennis Taylor
---
Don't worry about people stealing your ideas. If your ideas
are any good, you'll have to ram them down people's throats. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis Taylor INET: ismgr_at_pctc.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists
-------------------------------------------------------------------- ToREMOVE 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).
------_=_NextPart_001_01C02E4E.AA800ABE
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <TITLE></TITLE> <META content="MSHTML 5.00.3017.1000" name=GENERATOR></HEAD><BODY>
<P>I'm trying to create a function-based index on a table. Here's what I do: </P><BR> <P><FONT face="Courier New" size=2>create user sysop identified by password </FONT></P> <P> <FONT face="Courier New"size=2>default tablespace data </FONT></P> <P> <FONT face="Courier New" size=2>temporary tablespace temp; </FONT></P><BR>
<P><FONT face="Courier New" size=2>grant CREATE SESSION to sysop; </FONT></P> <P><FONT face="Courier New" size=2>grant CREATE TABLE to sysop; </FONT></P> <P><FONT face="Courier New" size=2>grant UNLIMITED TABLESPACE to sysop; </FONT></P> <P><FONT face="Courier New" size=2>grant CREATE PUBLIC SYNONYM to sysop; </FONT></P> <P><FONT face="Courier New" size=2>grant DROP PUBLIC SYNONYM to sysop; </FONT></P> <P><FONT face="Courier New" size=2>grant CREATE DATABASE LINK to sysop; </FONT></P> <P><FONT face="Courier New" size=2>grant CREATE VIEW to sysop; </FONT></P> <P><FONT face="Courier New" size=2>grant CREATE TRIGGER to sysop; </FONT></P> <P><FONT face="Courier New" size=2>grant CREATE PROCEDURE to sysop; </FONT></P><BR> <P><FONT face="Courier New" size=2>connect sysop/password </FONT></P><BR> <P><FONT face="Courier New" size=2>drop table issuer; </FONT></P><BR> <P><FONT face="Courier New" size=2>create table issuer ( </FONT></P> <P> <FONT face="Courier New"size=2>ISSUER_ID number(9) primary key, </FONT></P> <P> <FONT face="Courier New" size=2>ISSUER_NAME varchar(80) </FONT></P> <P><FONT face="Courier New" size=2>); </FONT></P><BR><BR> <P><FONT face="Courier New" size=2>create index ISSUER_NAME_IDX on ISSUER (UPPER(ISSUER_NAME)); </FONT></P><BR>
<P>And here's what I get: </P><BR><BR> <P>Table created. </P><BR> <P>create index ISSUER_NAME_IDX on ISSUER (UPPER(ISSUER_NAME)) </P> <P>* </P> <P>ERROR at line 1: </P> <P>ORA-01031: insufficient privileges </P><BR> <P>(The '*' is actually under the 'I' in 'ISSUER_NAME') </P><BR> <P>First, the reading of ora-1031 isn't even close to making sense. Then, ifit's a privilege problem, I can't find any mention of any privilege that I don't have that I should need. </P><BR> <P>Please relieve my groggy brain by pointing out the obvious problem so I can go hide under my desk. Thanks. </P><BR><BR><BR>
<P>--- </P> <P>Dennis Taylor </P> <P>--- </P> <P>Don't worry about people stealing your ideas. If your ideas </P> <P>are any good, you'll have to ram them down people's throats. -- Please seethe official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis Taylor INET: ismgr_at_pctc.com </P>
 |
 |