Thursday, October 8, 2009

ORA-01031 Insufficient privileges Error in EXECUTE IMMEDIATE Statement

Error Description 1:
Noticed while using EXECUTE IMMEDIATE with TRUNCATE TABLE Statement in Procedure, error message as ORA-01031: insufficient privileges.

Work Around:

SQL> EXECUTE IMMEDIATE 'TRUNCATE TABLE TestSchema.T1';
ORA-01031: insufficient privileges

Solution:
SQL>Grant drop any table to TestSchema;

Error Description 2:
Noticed while using EXECUTE IMMEDIATE with ANALYZE TABLE statement in Procedure, error message as ORA-01031: insufficient privileges.

Work Around:
SQL> EXECUTE IMMEDIATE 'ANALYZE TABLE TestSchema.T1';
ORA-01031: insufficient privileges

Solution:
SQL>Grant Analyze Any to TestSchema;

Error Description 3:
Noticed while using EXECUTE IMMEDIATE with MOVE TABLESPACE statement in Procedure, error message as ORA-01031: insufficient privileges.

Work Around:
SQL> EXECUTE IMMEDIATE 'ANALYZE TABLE TestSchema.T1';
ORA-01031: insufficient privileges

Solution:
SQL>Grant ALTER any TABLE to TestSchema;

1 comment:

Rucknrun said...

Thanks for the post!