ORA-1031 When Using Dynamic Sql To Alter Table (Doc ID 274714.1)

Last updated on AUGUST 29, 2017

Applies to:

PL/SQL - Version 8.1.7.4 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Aug-2017***


Symptoms

1. The following error occurs when using dynamic sql in a procedure to execute an "alter table <table_name>modify partition<partition_name>rebuild unusable local indexes" statement :

ORA-01031 insufficient privileges

Example:
create or replace procedure test as
begin
EXECUTE IMMEDIATE '
ALTER TABLE test_tab
MODIFY PARTITION part_test REBUILD UNUSABLE LOCAL INDEXES';
end;

2.The table owner and procedure owner are the same and the procedure is being executed by the owner.

3.The procedure owner already has CREATE TABLE privilege granted explicitly i.e not via role.

4.All statements work fine in SQL*Plus

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms