DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: REBUILD ONLINE ORA-01031 problem

  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Question 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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Nov 2005
    Posts
    2
    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
  •  


Click Here to Expand Forum to Full Width