0

in below procedure when I am using online then during execution this is giving error "ORA-01031: insufficient privilege" while running without online it is running fine.

CREATE OR REPLACE procedure p_temp_1 is
begin
execute immediate 'CREATE INDEX pp_temp1  ON p_temp  (access_no) online ';
end;

which privileges i need to give ?

Prakhar Agrawal
  • 15
  • 4
  • could you please share exactly what privileges i need to give to my super user and it will be great help if you can share statement too – Prakhar Agrawal May 30 '18 at 09:56
  • We do not want to use AUTHID CURRENT_USER , we want to give missing privileges – Prakhar Agrawal May 30 '18 at 09:59
  • What do you see if you run the create statement outside a procedure? – Alex Poole May 30 '18 at 10:08
  • index is getting created when running outside the procedure – Prakhar Agrawal May 30 '18 at 10:22
  • So just to clarify: outside the procedure you can create the index with or without the `online`option as plain SQL statements; when you execute the procedure it creates the index if the statement does not have `online` but you get the error if it does have `online`? It might be useful to edit the question to show all four scenarios and the results you get; and also show which version and patch level of Oracle you are using. – Alex Poole May 30 '18 at 10:37

1 Answers1

2

Checked for the privilege and the user was granted “CREATE TABLE” but from within RESOURCE role.Stored PL/SQL requires a direct grant and therefore the procedure fails with ‘insufficient privileges’. So granted ‘CREATE TABLE’ system privilege directly to the user, after which the index got created.

From here

vvg
  • 6,325
  • 19
  • 36
  • It might be, `create index online` requires `create table` permissions(works fine), but if you executes it in pl/sql it need ti be granted directly. Exactly this case. – vvg May 30 '18 at 10:48
  • that articles, I've quoted state that if you use `online` option, it put some lock and create some kind of system journal table (that's where you need create table privileges as I understood).. might be wrong – vvg May 30 '18 at 11:37
  • 1
    OK, sorry, I hadn't read that properly I guess, and I still can't recreate it in 11g or 12c while playing with privs. Maybe a PSU fixed it... – Alex Poole May 30 '18 at 11:41