如何删除以分区名称作为生成参数的 Oracle 分区

发布于 2024-11-16 12:21:18 字数 1095 浏览 4 评论 0原文

我必须删除超过 364 天的分区。分区被命名为“log_20110101”，因此旧的分区今天必须是

CONCAT('log_',TO_CHAR(SYSDATE -364,'YYYYMMDD'))

现在如果我尝试这样的语句，我会收到错误

ALTER TABLE LOG
DROP PARTITION CONCAT('log_',TO_CHAR(SYSDATE -364,'YYYYMMDD'));

Error report:
SQL Error: ORA-14048: a partition maintenance operation may not be combined with other operations
14048. 00000 -  "a partition maintenance operation may not be combined with other operations"
*Cause:    ALTER TABLE or ALTER INDEX statement attempted to combine
           a partition maintenance operation (e.g. MOVE PARTITION) with some
           other operation (e.g. ADD PARTITION or PCTFREE which is illegal
*Action:   Ensure that a partition maintenance operation is the sole
           operation specified in 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) may be
           combined at will

原文

i have to drop partitions which are older than 364 days.
Partitions are named as "log_20110101", so partitions which are older than
today will have to be

CONCAT('log_',TO_CHAR(SYSDATE -364,'YYYYMMDD'))

now if i try such a statement i get error

ALTER TABLE LOG
DROP PARTITION CONCAT('log_',TO_CHAR(SYSDATE -364,'YYYYMMDD'));

Error report:
SQL Error: ORA-14048: a partition maintenance operation may not be combined with other operations
14048. 00000 -  "a partition maintenance operation may not be combined with other operations"
*Cause:    ALTER TABLE or ALTER INDEX statement attempted to combine
           a partition maintenance operation (e.g. MOVE PARTITION) with some
           other operation (e.g. ADD PARTITION or PCTFREE which is illegal
*Action:   Ensure that a partition maintenance operation is the sole
           operation specified in 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) may be
           combined at will

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

少女七分熟 2024-11-23 12:21:18

分区名称需要在发出 SQL 语句时固定，它不能是表达式。您应该能够执行类似的操作，迭代 USER_TAB_PARTITIONS 表，找出要删除的分区，并构建动态 SQL 来实际删除它们。

DECLARE
  l_sql_stmt VARCHAR2(1000);
  l_date     DATE;
BEGIN
  FOR x IN (SELECT * 
              FROM user_tab_partitions
             WHERE table_name = 'LOG')
  LOOP
    l_date := to_date( substr( x.partition_name, 5 ), 'YYYYMMDD' );
    IF( l_date < add_months( trunc(sysdate), -12 ) )
    THEN
      l_sql_stmt := 'ALTER TABLE log ' ||
                    ' DROP PARTITION ' || x.partition_name;
      dbms_output.put_line( l_sql_stmt );
      EXECUTE IMMEDIATE l_sql_stmt;
    END IF;
  END LOOP;
END;

The partition name needs to be fixed at the time you issue the SQL statement, it cannot be an expression. You should be able to do something like this where you iterate over the USER_TAB_PARTITIONS table, figure out which partitions to drop, and construct the dynamic SQL to actually drop them.

DECLARE
  l_sql_stmt VARCHAR2(1000);
  l_date     DATE;
BEGIN
  FOR x IN (SELECT * 
              FROM user_tab_partitions
             WHERE table_name = 'LOG')
  LOOP
    l_date := to_date( substr( x.partition_name, 5 ), 'YYYYMMDD' );
    IF( l_date < add_months( trunc(sysdate), -12 ) )
    THEN
      l_sql_stmt := 'ALTER TABLE log ' ||
                    ' DROP PARTITION ' || x.partition_name;
      dbms_output.put_line( l_sql_stmt );
      EXECUTE IMMEDIATE l_sql_stmt;
    END IF;
  END LOOP;
END;

回复收藏 0 原文

~没有更多了~