我可以从分区查询数据，但不能删除分区（ORA-14006）？-解网

问：

我有一个名为 schema.exampletab 的表，其中分区名称的形式为 Pxxxxxx，其中 x 是分区周期（年和月 YYYYMM），然后是本地 PK。我想删除一个分区，但尝试删除分区时收到错误消息 ORA-14006：分区名称无效，但当我尝试从分区中查询数据时，它会选择并显示相关分区中的数据。请注意，我使用的是调度程序，它使用 YYYYMMDD 将日期用作参数，因此我通常使用子字符串。

例如，我可以使用以下语句查询数据：

SELECT *
FROM   schema.exampletab
PARTITION (P202110);

这将在我的表中返回 20 行数据。然后，我尝试使用以下语句删除分区：

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

但是，这会导致 ORA-14006 错误消息。为了控制分区是否存在，我尝试查找模式、表和分区是否存在于记录我处理的数据库中所有分区的all_tab_partitions中。

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);

这将返回查询结果中P202110分区名称。

我希望我可以将放置分区与这样的子查询一起使用：

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);

但是，这仍然会导致 ORA-14006 错误。我试图写像“P202110”这样的分区名称，并用P202110代替括号，但没有运气。

如何编写删除分区语句，使其删除分区而不是给出 ORA-14006 错误？

这是我必须经常做的事情，所以知道如何正确选择和删除分区，或截断它们等会很好。此外，我使用一个调度程序，它以指定的时间间隔运行 sql 查询，因此我必须将日期指定为参数，这意味着在我的示例代码中，YYYYMM 是一个被解析的参数值，因此我需要将 P 与这个输出的参数值联系起来，因为如果我只键入 P202110它实际上会删除它。

SQL Oracle 语法错误分区

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;
/

1赞 Roberto Hernandez 10/24/2021 #2

您有两个选项，考虑到您正在使用哪个分区来知道应该删除哪个分区。sysdate

选项 1 -> 动态 SQL，结果必须在查询外部执行

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) ;  ;

此查询提供命令输出，但需要执行它

alter table yourschema.yourtable drop partition P202110 update indexes;

我自己环境中的例子

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 ;

选项 2 -> PLSQL

更好的选择是使用 .一个小例子，当你只想根据当前删除一个分区时。您可以扩展/修改此代码以涵盖任何类型的时间范围。PLSQLsysdate

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;
/

我可以从分区查询数据，但不能删除分区（ORA-14006）？

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

评论

评论

我可以从分区查询数据，但不能删除分区 （ORA-14006）？

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

评论

评论

我可以从分区查询数据，但不能删除分区（ORA-14006）？