Re: ORA-1031 when privileges have been granted

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 9 Jan 2009 10:32:13 -0800
Message-ID: <bf46380901091032i47d7f1c5q485a81d4a911bebf_at_mail.gmail.com>

On Fri, Jan 9, 2009 at 8:19 AM, Jason Heinrich <jheinrichdba_at_gmail.com>wrote:

> SQL> create type varchar2_table as table of varchar2(2000);
> 2 /
> create type varchar2_table as table of varchar2(2000);
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>

It appears that something is broke, not just a permissions problem.

This if course is not news to you. :)

You might be able to track down what is happening by setting a system wide 10046
event, run the single SQL statement that generates a 10046, and then turn off
the 10046 trace.

Open a session as SYSDBA and a session as a user.

In the SYSDBA session turn tracing on.

In the user session, run the create table statement that generates and ORA-1031, and
then logoff.

Following that reset 10046 in the SYSDBA session.

  alter system set events '10046 trace name context forever, level 12';

  alter system set events '10046 trace name context off';

Examining the resulting trace files may be interesting.

I tried doing this with setting 10046 at the session level, but it did not include
the SYS queries to show how the dictionary was being queried.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 09 2009 - 12:32:13 CST