Re: ORA-1031 when privileges have been granted

From: Jared Still <>
Date: Fri, 9 Jan 2009 07:07:26 -0800
Message-ID: <>

On Thu, Jan 8, 2009 at 2:36 PM, Jason Heinrich <>wrote:

> Oracle EE
> Logical standby database w/dataguard
> I'm working with Oracle support on a performance issue, and they want me to
> run the sqlt scripts to gather some information. However, when I run the
> script to create the sqltxplain user's objects, I get an "ORA-01031:
> insufficient privileges" on the very first object it tries to create. I've
> verified that the permissions for the user are correct (create type, create
> table, etc. have all been granted), as is the tablespace quota. This isn't
> a problem with the scripts: I created a test user manually and confirmed
> that that user had the same problem.
> As mentioned above, this database has dataguard enabled. Could that be
> causing this behavior? If so, I was expecting an "ORA-16224: Database Guard
> is enabled."

What exactly is the SQL statement that is failing?

Use the following SQL to show the list exactly what privs the user has:

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SQLTXPLAIN' ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SQLTXPLAIN' ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SQLTXPLAIN' ) from dual;

You may see a ORA-31608 error from some of this SQL, it is a bug that can be ignored.
(no data found is improperly raising an error in dbms_metadata)

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Received on Fri Jan 09 2009 - 09:07:26 CST

Original text of this message