I can query data from partitions, but not drop the partitions (ORA-14006)?-CodePudding

I have a table called schema.exampletab, which has partitions where the partition name is on the form Pxxxxxx where the x's are partition periods (year and month YYYYMM) and then there is a local PK. I want to drop a partition, but I get the error message ORA-14006: Invalid partition name when attempting to drop it, but when I try to simply query data from a partition it selects and displays the data from the partition in question. Note that I am using a scheduler which uses date as a parameter using YYYYMMDD, so I usually substring.

For instance I can query data by using the following statement:

SELECT *
FROM   schema.exampletab
PARTITION (P202110);

This returns 20 rows of data in my table. I then try to drop the partition using the following statement:

ALTER TABLE schema.exampletab DROP PARTITION (concat(P,substr('20211011',1,6))
 UPDATE INDEXES;

This however, leads to the ORA-14006 error message. In order to control that the partition exists I tried to look up that the schema, table, and partition exists in the all_tab_partitions where all partitions in the database I work on are logged.

select partition_name from all_tab_partitions 
where table_owner = 'schema' and table_name = 'exampletab' and 
substr(partition_name,2,7) = substr('20211022',1,6);

This returns the partition name P202110 in the query result.

I hoped I could use the drop partition with a subquery like this:

ALTER TABLE schema.exampletab DROP PARTITION select partition_name from all_tab_partitions 
where table_owner = 'schema' and table_name = 'exampletab' and 
substr(partition_name,2,7) = substr('20211022',1,6);

However, this still leads to ORA-14006 error. I have tried to write the partition name like 'P202110' and P202110 instead of the parenteces, but no luck.

How can I write the drop partition statement such that it drops the partition instead of giving the ORA-14006 error?

This is something which I have to do routinely and so it would be nice to know how to properly select and drop partitions, or truncate them, etc. Also, I use a scheduler which runs the sql queries at specified intervals so there I have to specify dates as a parameter, meaning that in my example code the YYYYMM is a parametervalue that gets parsed in and so I need to concactinate the P with this outputted parameter value, because if I type only P202110 it actually drops it.

CodePudding user response：

You have two options, keeping in consideration that you are using sysdate to know which partition you should drop.

Option 1 -> Dynamic SQL and the result must be executed outside the query

select ' alter table '||table_owner||'.'||table_name||' drop partition '||partition_name||' update indexes ; ' 
from 
dba_Tab_partitions
where table_owner = 'your_schema' 
and table_name = 'your_table' 
and partition_name = 'P'||substr(to_char(sysdate,'yyyymmdd'),1,6) ;  ;

This query gives you the command output, but you need to execute it

alter table yourschema.yourtable drop partition P202110 update indexes;

Example in my own environment

SQL> select ' alter table '||table_owner||'.'||table_name||' drop partition '||partition_name||' update indexes ; '
from
dba_Tab_partitions
where table_owner = 'FDM_DATA'
and table_name = 'FDM_DIM_CUSTOMER'
and partition_name = 'P_'||substr(to_char(sysdate,'yyyymmdd'),1,6) ;

'ALTERTABLE'||TABLE_OWNER||'.'||TABLE_NAME||'DROPPARTITION'||PARTITION_NAME||'UP
--------------------------------------------------------------------------------
 alter table FDM_DATA.FDM_DIM_CUSTOMER drop partition P_202110 update indexes ;

Option 2 -> PLSQL

A better option is to use PLSQL. A small example when you want to drop only one partition based on the current sysdate. You can extend / modify this code to cover any kind of time frame.

declare
    v_owner          varchar2(128) := 'YOUR_SCHEMA';
    v_table_name     varchar2(128) := 'YOUR_TABLE';
    v_partition_name varchar2(128);
begin 
  select partition_name into v_partition_name from all_tab_partitions
            where table_owner = v_owner        and
            table_name        = v_table_name   and
            partition_name    = 'P'||substr(to_char(sysdate,'yyyymmdd'),1,6) ;
   execute immediate 'alter table '||v_owner||'.'||v_table_name||' drop partition '||v_partition_name||' update indexes' ;
exception 
when no_data_found then null; -- if there is no partition, nothing to do and no error is raised
when others then raise;
end;
/

CodePudding user response：

You cannot use "expressions" in partition name

This is wrong:

ALTER TABLE schema.exampletab DROP PARTITION (concat(P,substr('20211011',1,6))
 UPDATE INDEXES;

this is correct:

ALTER TABLE schema.exampletab DROP PARTITION P20211011
 UPDATE INDEXES;

You may use this code to drop partitions dynamically. Just update C_OWNER ,C_TABLE_NAME , C_PARTITION_TEMPLATE in the header. Please pay attention that the sql command is built dynamically, but when it is ready is has the partition name fully parsed.

declare
    C_OWNER varchar2(128) := 'MYOWNER';
    C_TABLE_NAME varchar2(128) := 'MYTABLE';
    C_PARTITION_TEMPLATE varchar2(128) := '2011';
    cursor part_cur is 
        select * 
        from all_tab_partitions
        where table_owner=C_OWNER and
            table_name = C_TABLE_NAME and
            partition_name like '%'||C_PARTITION_TEMPLATE||'%'
        order by partition_position;
BEGIN
    for part_rec in part_cur loop
        execute immediate 'ALTER TABLE "'||part_rec.table_owner||'"."'||part_rec.table_name||'"'||
            ' DROP PARTITION ("'||part_rec.partition_name||'") UPDATE INDEXES';
    end loop;
END;
/