Home » SQL & PL/SQL » SQL & PL/SQL » Function-base index's problem (oracle10.0.2 winXP)
Function-base index's problem [message #414870] Thu, 23 July 2009 22:36 Go to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
when I create a function-base index on hr.employees.hire_date, just like the following:
CREATE INDEX ind_emp_hire_date ON employees(to_char(hire_date, 'yyyymmdd'));

but failed and got a message: ora-01031 insufficient privileges.

I look up the hand book Oracle Conception ,it says:
Privileges on the Defining Function
The index owner needs the EXECUTE privilege on the function used to define a function-based index. .....

but how can I get an EXECUTE privilege on function TO_CHAR()?

Thanks.
Re: Function-base index's problem [message #414871 is a reply to message #414870] Thu, 23 July 2009 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
SQL> @fbi
SQL> create table fbi (hire_date date);

Table created.

SQL> CREATE INDEX ind_emp_hire_date ON fbi(to_char(hire_date, 'yyyymmdd'))
SQL> /

Index created.


works for me.
Re: Function-base index's problem [message #414875 is a reply to message #414871] Thu, 23 July 2009 22:55 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
maybe the user you login has some system privilege that hr does not have, but the hand book does not mention it.

[Updated on: Thu, 23 July 2009 22:57]

Report message to a moderator

Re: Function-base index's problem [message #414876 is a reply to message #414870] Thu, 23 July 2009 22:58 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>but the hand book does not mention it.
It is difficult to document all combinations & permutations.
Sometimes trial & error experimentation is required.
Re: Function-base index's problem [message #414878 is a reply to message #414875] Thu, 23 July 2009 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No privilege needed:
SQL> create user test identified by test
  2  default tablespace ts_d01 quota unlimited on ts_d01;

User created.

SQL> grant create session, create table to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> create table t (col date);

Table created.

TEST> create index t_i on t (to_char(col,'yyyymmdd'));

Index created.

Not even CREATE TABLE:
TEST> drop table t;

Table dropped.

TEST> @c
Connected.
SQL> revoke create table from test;

Revoke succeeded.

SQL> create table test.t (col date);

Table created.

SQL> connect test/test
Connected.
TEST> create index t_i on t (to_char(col,'yyyymmdd'));

Index created.

Regards
Michel
Re: Function-base index's problem [message #414882 is a reply to message #414870] Thu, 23 July 2009 23:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you got this message at a recursive level from a DDL trigger.
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Function-base index's problem [message #414886 is a reply to message #414882] Thu, 23 July 2009 23:32 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
I create a new user, and create a new table, I do these just like Michel do, but the error msg ora-01031 was still there.
Re: Function-base index's problem [message #414893 is a reply to message #414870] Thu, 23 July 2009 23:47 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


Re: Function-base index's problem [message #414908 is a reply to message #414886] Fri, 24 July 2009 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
crazyzhou wrote on Fri, 24 July 2009 06:32
I create a new user, and create a new table, I do these just like Michel do, but the error msg ora-01031 was still there.


Michel Cadot wrote on Fri, 24 July 2009 06:19
Maybe you got this message at a recursive level from a DDL trigger.
Use SQL*Plus and copy and paste your session.

Regards
Michel


Re: Function-base index's problem [message #414915 is a reply to message #414908] Fri, 24 July 2009 00:51 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
I do this in PL/SQL Developer :
SQL> create user test identified by test
  2  default tablespace users quota unlimited on users;
 
User created
 
SQL> grant create session, create table to test;
 
Grant succeeded
 
SQL> conn test/test
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as test
 
SQL> create table t (hire_date date);
 
Table created
 
SQL> create index ind_t on t(to_char(hire_date, 'yyyymmdd'));
 
create index ind_t on t(to_char(hire_date, 'yyyymmdd'))
 
ORA-01031: 权限不足     ----(insufficient privilege)
 
SQL> 
Re: Function-base index's problem [message #414929 is a reply to message #414915] Fri, 24 July 2009 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0

In your title:
Quote:
oracle10.0.2 winXP

Which one is correct (assuming 10.0.2 means 10.2.0)?

Please use SQL*Plus.

Regards
Michel
Re: Function-base index's problem [message #414933 is a reply to message #414929] Fri, 24 July 2009 01:49 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
sorry, the oracle version is 9i
and I do it again in SQL*PLUS:
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>cd\

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 7月 24 14:42:02 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> drop user test cascade;

用户已丢弃

SQL> create user test identified by test
  2  default tablespace users quota unlimited on users;

用户已创建

SQL> grant create session, create table to test;

授权成功。

SQL> conn test/test
已连接。
SQL> create table t(hire_date date);

表已创建。

SQL> create index ind_t on t(to_char(hire_date,'yyyymmdd'));
create index ind_t on t(to_char(hire_date,'yyyymmdd'))
                                          *
ERROR 位于第 1 行:
ORA-01031: 权限不足  ----insufficient privilege 


SQL>
Re: Function-base index's problem [message #414939 is a reply to message #414933] Fri, 24 July 2009 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 9i, documentation states:
Quote:
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.

Althought it works without it in this case in 9.2.0.8, it might not be the case in 9.2.0.1.

Regards
Michel
Re: Function-base index's problem [message #414965 is a reply to message #414939] Fri, 24 July 2009 03:23 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
I grant QUERY REWRITE to test, and the function-base index have been created successfully.
the document that I look up is 10.2, but the Oracle server is 9.2 so, that's the problem.
thanks Michel, you're so kindly and so patiently.
and thanks BlackSman. I also got some help from you.

you're so familiar with all of the documents, do you read them all?

[Updated on: Fri, 24 July 2009 03:27]

Report message to a moderator

Re: Function-base index's problem [message #414983 is a reply to message #414933] Fri, 24 July 2009 05:13 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
crazyzhou wrote on Fri, 24 July 2009 14:49
sorry, the oracle version is 9i
and I do it again in SQL*PLUS:
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>cd\

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 7月 24 14:42:02 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> drop user test cascade;

用户已丢弃

SQL> create user test identified by test
  2  default tablespace users quota unlimited on users;

用户已创建

SQL> grant create session, create table to test;

授权成功。

SQL> conn test/test
已连接。
SQL> create table t(hire_date date);

表已创建。

SQL> create index ind_t on t(to_char(hire_date,'yyyymmdd'));
create index ind_t on t(to_char(hire_date,'yyyymmdd'))
                                          *
ERROR 位于第 1 行:
ORA-01031: 权限不足  ----insufficient privilege 


SQL>



You could use the following code to change the SQL*Plus output from Chinese to English:
alter session set nls_language='AMERICAN';


You could also change the environment variable NLS_LANG before you login with SQL*Plus:
set nls_lang=american


This is an English forum. So it is a good practice to display all your messages in English.
Re: Function-base index's problem [message #415008 is a reply to message #414983] Fri, 24 July 2009 06:39 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
cnvegnix wrote on Fri, 24 July 2009 12:13
Quote:

ERROR 位于第 1 行:
ORA-01031: 权限不足 ----insufficient privilege


SQL>[/code]


You could use the following code to change the SQL*Plus output from Chinese to English:
alter session set nls_language='AMERICAN';


You could also change the environment variable NLS_LANG before you login with SQL*Plus:
set nls_lang=american


This is an English forum. So it is a good practice to display all your messages in English.


I disagree.
The poster went through the trouble of looking up the English translation of his error.

[Updated on: Fri, 24 July 2009 06:41]

Report message to a moderator

Previous Topic: selecting all data from a single row
Next Topic: 2 FULL Table Access for the update statement
Goto Forum:
  


Current Time: Mon Dec 05 23:45:49 CST 2016

Total time taken to generate the page: 0.16965 seconds