Oracle洗掉表的最后一個PARTITION

我正在嘗試洗掉表的所有磁區，但遇到以下錯誤：

ORA-14083: 無法洗掉磁區表的唯一磁區 ORA-06512:

我知道我可以洗掉表并重新創建它，但我想看看我的程序是否可以修改為使用將保留設定為空、洗掉 PARTITION 并將 INTERVAL 設定回其原始值的例外？

任何幫助和專業知識將不勝感激。下面是我的測驗用例。

CREATE OR REPLACE PROCEDURE ddl(p_cmd varchar2) 
 authid current_user
is
BEGIN 

dbms_output.put_line(p_cmd);

 execute immediate p_cmd;

END;
/

CREATE  OR REPLACE PROCEDURE
drop_partition(
  p_tab varchar2,
  p_date date
) authid current_user
is
  v_high_value date;
    cursor v_cur is
    select table_name,
           partition_name,
           high_value,
           partition_position
    from   user_tab_partitions
    where  table_name = upper(p_tab);
begin
  for v_rec in v_cur loop
    execute immediate 'select ' || v_rec.high_value || ' from dual' into v_high_value;
    if     v_high_value <= trunc(p_date)
           then
      
    -- dbms_output.put_line ('partition ' || v_rec.partition_name || ' high value is ' || to_char(v_high_value,'mm/dd/yyyy'));

--ddl( 'ALTER TABLE ' || p_tab || ' DROP PARTITION FOR(DATE ' || TO_CHAR(v_high_value,'YYYY-MM-DD') || ')');

      ddl('alter table '||p_tab||' drop partition '||v_rec.partition_name);

          end if;    
  end loop;
END;
/

CREATE  TABLE partition_retention
(
  seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   TABLE_NAME VARCHAR2(30),
DAYS NUMBER(6),
CONSTRAINT
partition_retention_pk primary key (table_name)); 
/

INSERT into partition_retention(TABLE_NAME, DAYS) 
        WITH data as (
          select 'T1', 0
from dual union all
         select 'T3', 15
from dual union all
        select 'T4', 10
from dual union all
       select 'T5', 5
from dual)
        SELECT * from data;
/

CREATE TABLE t1 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/

INSERT into t1 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(1,'DAY') from dual
union all
select dt.dt   interv, interv from dt
where dt.dt   interv < date '2022-02-01')
select dt from dt;
/

BEGIN
    FOR td IN
    (
        SELECT table_name
        ,      NVL (pr.days, 30) AS days
        FROM       user_part_tables     pt
          JOIN       user_part_key_columns pkc ON pkc.name = pt.table_name
          JOIN       user_tab_cols         tc  USING (table_name, column_name)
        LEFT JOIN partition_retention   pr  USING (table_name)
        WHERE       pkc.object_type     = 'TABLE'
        AND       pt.partitioning_type = 'RANGE'
        AND REGEXP_LIKE (tc.data_type, '^DATE$|^TIMESTAMP.*')
        ORDER BY table_name
    )
    LOOP
                      
      drop_partition(
          td.table_name,
  trunc(sysdate-td.days)
         );
      END LOOP;
END;
/

uj5u.com熱心網友回復：

您的問題是“洗掉最后一個磁區”，這可能與“洗掉唯一的磁區”不同

如評論中所述，當只剩下一個磁區時，您不能洗掉該磁區。如果您想洗掉資料，則可以TRUNCATE磁區。

此 PL/SQL 塊應涵蓋您的所有情況：

CREATE TABLE PARTITION_RETENTION (
    TABLE_NAME VARCHAR2(30),
    RETENTION INTERVAL DAY(3) TO SECOND(0) --> More generic than number of days
);



DECLARE
    CANNOT_DROP_LAST_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);

    CANNOT_DROP_ONLY_ONE_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_ONLY_ONE_PARTITION, -14083);

   ts TIMESTAMP;
 
   CURSOR TablePartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, p.HIGH_VALUE, t.INTERVAL, RETENTION, DATA_TYPE
    FROM USER_PART_TABLES t
        JOIN USER_TAB_PARTITIONS p USING (TABLE_NAME)
        JOIN USER_PART_KEY_COLUMNS pk ON pk.NAME = TABLE_NAME
        JOIN USER_TAB_COLS tc USING (TABLE_NAME, COLUMN_NAME)
        JOIN PARTITION_RETENTION r USING (TABLE_NAME);
  
BEGIN

    FOR aPart IN TablePartitions LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts < SYSTIMESTAMP - aPart.RETENTION THEN
            BEGIN
                EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                DBMS_OUTPUT.PUT_LINE('Dropped partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
            EXCEPTION
                WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN
                    DBMS_OUTPUT.PUT_LINE('Cannot drop the only partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                    EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                    DBMS_OUTPUT.PUT_LINE('Truncated partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                WHEN CANNOT_DROP_LAST_PARTITION THEN
                    BEGIN
                        DBMS_OUTPUT.PUT_LINE('Drop last partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aPart.PARTITION_NAME;
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';            
                    EXCEPTION
                        WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN 
                            -- Depending on the order the "last" partition can be also the "only" partition at the same time
                            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';
                            DBMS_OUTPUT.PUT_LINE('Cannot drop the only partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                            DBMS_OUTPUT.PUT_LINE('Truncated partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                    END;
            END;
        END IF;
    END LOOP;

END;

標籤：甲骨文 分区

上一篇：OracleJDBC連接配置/JDBC請求中的問題-JMETER

下一篇：如何在Oracle中重命名序列？