CODEWITHSUNDEEP
oracle-databasepartitioning
Tom
Tom

Reputation: 121

when drop partition and update global indexes exists error :a partition maintenance operation may not be combined with other operations

Here I wrote a Oracle SP to drop the partition and update the global index. but It encountered an error , 

begin
EXECUTE IMMEDIATE 'alter session set nls_date_format="YYYY-MM-DD"';
dbms_output.put_line(need_housekeeping_month);

EXECUTE IMMEDIATE 'ALTER TABLE pos_data DROP PARTITION FOR (need_housekeeping_month))  UPDATE GLOBAL INDEXES';

end;

I searched some solutions but no idea on that,Please help.

 ORA-14048: a partition maintenance operation may not be combined with other operations

        Cause: The ALTER TABLE or ALTER INDEX statement attempted to combine a partition maintenance operation (for example, MOVE PARTITION) with some other operation (for example, ADD PARTITION or PCTFREE) which is illegal.

        Action: Ensure that a partition maintenance operation is the sole operation specified in an ALTER TABLE or ALTER INDEX statement; operations other than those dealing with partitions, default attributes of partitioned tables/indices, or specifying that a table be renamed (ALTER TABLE RENAME) can be combined.

for add another question 

    create or replace procedure   SP_HOUSE_KEEPING(p_dt_cycle_dt in VARCHAR2) is
hotspot_data_months number :=27;
need_housekeeping_month DATE := ADD_MONTHS( to_date(p_dt_cycle_dt,'yyyymmdd'), -(hotspot_data_months-1) );
begin
EXECUTE IMMEDIATE 'alter session set nls_date_format="YYYY-MM-DD"';
dbms_output.put_line(need_housekeeping_month);
EXECUTE IMMEDIATE 'ALTER TABLE pos_data DROP PARTITION FOR (need_housekeeping_month)  UPDATE GLOBAL INDEXES';
end;

ORA-14755: Invalid partition specification for FOR VALUES clause.

Upvotes: 0

Views: 2723

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You cannot put the partition as variable, try this one:

EXECUTE IMMEDIATE 'ALTER TABLE pos_data DROP PARTITION FOR (DATE '''||to_char(need_housekeeping_month, 'YYYY-MM-DD')||''') UPDATE GLOBAL INDEXES';

Upvotes: 1

Related Questions