我可以從磁區查詢資料，但不能洗掉磁區(ORA-14006)？

我有一個名為 schema.exampletab 的表，它有磁區，其中磁區名稱的格式為 Pxxxxxx，其中 x 是磁區周期（年和月 YYYYMM），然后有一個本地 PK。我想洗掉一個磁區，但在嘗試洗掉它時收到錯誤訊息 ORA-14006: Invalid partition name，但是當我嘗試簡單地從一個磁區查詢資料時，它會選擇并顯示來自相關磁區的資料。請注意，我使用的調度程式使用日期作為使用 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。

我希望我可以將 drop 磁區與這樣的子查詢一起使用：

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 而不是父母，但沒有運氣。

如何撰寫 drop partition 陳述句，使其洗掉磁區而不是給出 ORA-14006 錯誤？

這是我必須經常做的事情，所以很高興知道如何正確選擇和洗掉磁區，或截斷它們等。此外，我使用一個調度程式，它以指定的時間間隔運行 sql 查詢，所以我必須將日期指定為引數，這意味著在我的示例代碼中，YYYYMM 是一個被決議的引數值，因此我需要將 P 與這個輸出的引數值連接起來，因為如果我只輸入 P202110，它實際上會丟棄它。

uj5u.com熱心網友回復：

您有兩個選擇，同時考慮到您正在使用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

更好的選擇是使用PLSQL. 一個小示例，當您只想根據當前sysdate. 您可以擴展/修改此代碼以涵蓋任何型別的時間范圍。

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

uj5u.com熱心網友回復：

您不能在磁區名稱中使用“運算式”

這是錯誤的：

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

這是對的：

ALTER TABLE schema.exampletab DROP PARTITION P20211011
 UPDATE INDEXES;

您可以使用此代碼動態洗掉磁區。只需在標題中更新 C_OWNER 、C_TABLE_NAME 、 C_PARTITION_TEMPLATE 。請注意 sql 命令是動態構建的，但是當它準備好時，磁區名稱會被完全決議。

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

標籤：sql 甲骨文 语法错误 分区

上一篇：將最后的元素添加到Oracle中的json物件陣列

下一篇：TSQL使磁區“間隙和孤島”