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.
Best Answer
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. AndSYSTEM
user inherits theCREATE TABLESPACE
system privilege viaDBA
role, so that you are able to create tablespace directly not from PL/SQL procedure.