2

I am trying to create tablespace from stored procedure.

create or replace procedure testprc 
is
query_string VARCHAR2(4000);
tbs_name VARCHAR2(20):='test77';
begin
dbms_output.put_line('test..');

query_string := 'CREATE BIGFILE TABLESPACE '|| tbs_name || ' DATAFILE '''|| tbs_name || '.dat'' SIZE 1G REUSE AUTOEXTEND ON ONLINE';

EXECUTE IMMEDIATE query_string;

end;
/

But when I execute the stored procedure,

execute testprc

I am getting the following error:

test..

BEGIN testprc; END;

  • ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYSTEM.TESTPRC", line 10 ORA-06512: at line 1

When I create the tablespace from sqlplus, it is created successfully. I have connected to sqlplus as system user.

Please help me in fixing this and your help will be valuable.

1
  • User has Create tablespace system privilege. Also i am able to create tablespace directly from sqlplus. System is the user i used to create. Dec 15, 2016 at 8:42

1 Answer 1

3

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure. More

First of all dont create object under SYSTEM schema. Second in order to execute commands in PL/SQL you should have privileges granted directly to the user not from role. And SYSTEM user inherits the CREATE TABLESPACE system privilege via DBA role, so that you are able to create tablespace directly not from PL/SQL procedure.

SQL> conn sys as sysdba

SQL> grant create tablespace to system;

Grant succeeded.

SQL> conn system/password
Connected.

SQL> exec testprc

PL/SQL procedure successfully completed.

SQL> 
2
  • i am not able to connect as sysdba. SQL> conn / as sysdba ERROR: ORA-01031: insufficient privileges Warning: You are no longer connected to ORACLE. SQL> Dec 16, 2016 at 6:01
  • you are awesome... you rocked man.. may be i will trouble you lot by asking questions related to oracle. :P .. hope you will help me.. @JSapkota... Dec 16, 2016 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.