-
REBUILD ONLINE ORA-01031 problem
Hi,
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.
Jurij Modic
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.
Thanks!!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|