REBUILD ONLINE ORA-01031 problem
When i run a stored proc containing a line:
PROC1 Code Snippet
EXECUTE IMMEDIATE 'ALTER INDEX ' || w_index_names(w_index_cnt) || ' REBUILD';
and then execute it, the stored proc runs successfully.
However, when i use REBUILD ONLINE instead, it gives ORA-01031 "Insufficient privileges" error!!!
PROC 2 Code Snippet
EXECUTE IMMEDIATE 'ALTER INDEX ' || w_index_names(w_index_cnt) || ' REBUILD ONLINE';
The user has connect and resource along with create/alter/drop any index system privs. The current login id owns both proc1 and proc2
Which privileges can be granted to avoid this error.
Proc2 runs successfully when i store and run it from a schema having sysdba privs.
Does your user have CREATE TABLE privilege assigned directly, not through role?
When rebuilding index online, oarcle creates a journal table in the schema where the index resides. After the rebuild, it drops this journal table. Your user should have appropriate *directly granted* system privileges (and sufficient quota on that tablespace) for creating this journal table during the online rebuild.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Granting the CREATE TABLE privilege directly worked.
Click Here to Expand Forum to Full Width