Oracle: Not able to create tablespace from stored procedure

Question

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.

Answer 1

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>