Execute Immediate error !! - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Execute Immediate error !!

  1. #11
    Join Date
    Nov 2002
    Posts
    170
    Quote Originally Posted by davey23uk
    and do you have direct access to dba_tables (NOT through a role)

    running as mdm3 and mdm3 has dba role privilege.

  2. #12
    Join Date
    Nov 2002
    Posts
    170
    SQL> grant alter any index to mdm3;

    Grant succeeded.

    SQL>
    SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
    BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "MDM3.INDEX_REBUILT", line 15
    ORA-06512: at line 1


    SQL>
    SQL> grant dba to mdm3;

    Grant succeeded.

    SQL>
    SQL>
    SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
    BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "MDM3.INDEX_REBUILT", line 15
    ORA-06512: at line 1


    SQL>
    LINE 15 is
    ==========================
    15 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7 ';
    ============================

    What privilege does it need for execute immediate ????

  3. #13
    Join Date
    Nov 2002
    Posts
    170
    My Error is for the execute immediate.
    Even tho I pass a direct alter index stmt it doesn't work.
    ANy reason ??


    SQL> CREATE OR REPLACE PROCEDURE index_rebuilt (
    2 table_name IN VARCHAR2)
    3 IS
    4 TYPE cv_type IS REF CURSOR;
    5 cv cv_type;
    6 val VARCHAR2(32767);
    7 BEGIN
    8 /* Construct the very dynamic query and open the cursor. */
    9 OPEN cv FOR
    10 'select index_name from dba_indexes where table_name = ''' || table_name || ''' and status = '
    'UNUSABLE''';
    11 LOOP
    12 /* Fetch the next row, and stop if no more rows. */
    13 FETCH cv INTO val;
    14 EXIT WHEN cv%NOTFOUND;
    15 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.SYS_C007499 REBUILD ONLINE';
    16 END LOOP;
    17 CLOSE cv;
    18 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.SYS_C007499 REBUILD ONLINE';
    19 END;
    20 /

    Procedure created.

    SQL>
    SQL>
    SQL> exec index_rebuilt('test');
    BEGIN index_rebuilt('test'); END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "MDM3.INDEX_REBUILT", line 18
    ORA-06512: at line 1


    SQL>

  4. #14
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ORA-01031: insufficient privileges
    Sure, you don't have permission.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #15
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    You need to grant the system privilege
    create table or
    create any table
    explicitly to MDM3
    thomasp

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