Solved

Ora-01031 when execute immediate 'create table ...'

Posted on 2004-04-26
5
2,905 Views
Last Modified: 2012-08-14

Dear advisor !

i have user leaseline with connect, resource, DBA role.

I have a 'CreateData' procedure and i could run it well before. Now, i run it with error.

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "LEASELINE.CreateData", line 3
ORA-06512: at line 1

the CreateData procedure

As
Begin
    Execute Immediate 'Create table aaaaaaaaaaa (a varchar2(1))'      ;
end ;

Please show me how to corecct it. Maybe, i have change some configure at Oracle , but i do not remember.

Why error

Thank for all consider
0
Comment
Question by:namcit99
5 Comments
 
LVL 8

Accepted Solution

by:
baonguyen1 earned 30 total points
Comment Utility
You may need to grant CREATE TABLE privilege to the user. By defaut users are granted via role:

SQL>Grant CREATE TABLE to <user>

the try again

0
 
LVL 8

Expert Comment

by:annamalai77
Comment Utility
dear friend

once again grant all the roles to the user and try it.

regards
annamalai
0
 

Author Comment

by:namcit99
Comment Utility

the 'leaseline' user has DBA, connect, resource Role. So the Leaseline user has 'Create Table' priviledge

Why ? i has modify some Role before, but i just test and the 'Create tbel ' still exist on connect and DBA role
0
 
LVL 8

Expert Comment

by:annamalai77
Comment Utility
hi

even though u give the resource its for the unlimited quota on the tablespace. and dba role is for performing certain dba privilieges.

try this for the user.
grant create session to <user>;

regards
annamalai
0
 
LVL 15

Expert Comment

by:ishando
Comment Utility
Privileges granted through roles are not recognised in PL/SQL, only eplicitly granted privileges.

As baonguyen1 said - grant the create table privilege to the user and you should be ok.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now