Execute Immediate error !!

[dbasupuser]

and do you have direct access to dba_tables (NOT through a role)

running as mdm3 and mdm3 has dba role privilege.

[dbasupuser]

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 ????

[dbasupuser]

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>

[marist89]

ORA-01031: insufficient privileges

Sure, you don't have permission.

[thomasp]

You need to grant the system privilege
create table or
create any table
explicitly to MDM3