Oracle

為什麼 Oracle 不改變用於子分區的列的大小?

  • August 21, 2013

當我收到 ORA-14265 錯誤時,我正在嘗試調整 varchar2 列的大小:

ORA-14265: 不能更改表子分區列的數據類型或長度

Oracle 提供了無用的:

// *原因:使用者發出 ALTER TABLE 語句試圖修改 // 用於對 ALTER TABLE 語句中命名的表進行子分區的列的數據類型和/或長度,這是非法的

// *Action: 避免修改表的數據類型和/或長度 // 子分區列

所以我知道 Oracle 不希望我更改該列。我認為用於子分區的雜湊會被這個破壞。我該如何解決這個問題?

到目前為止,我發現的唯一似乎合理的建議是重命名現有表,並使用更新的模式重新創建原始表,然後從重新創建的表中選擇數據。

由於努力和人為錯誤的可能性,我並不特別喜歡這個想法:

  • 我們有很多分區,在幕後我必須手動確保新表匹配相同,以便支持維護腳本可以很好地繼續進行。
  • 然後是磁碟空間。我們保留了 9 個月的滾動數據,我沒有空間在這張桌子上突然翻倍。

任何建議將不勝感激。我希望我只是錯過了一些簡單的東西。另外,如果有人想讓我知道為什麼 oracle 不能重新排列磁碟上的數據並自動創建一個新的子分區雜湊,那就太好了。

解決方案是dbms_redefinition打包。基本上,重新定義將線上移動到一個新表(稱為臨時表),然後移動所有相關對象,如索引,最後將臨時表與原始表交換。

你要做的是:

  • 使用正確的列定義創建臨時表
  • 執行重新定義
  • 丟棄舊表

dbms_redefinition按以下方式使用:

-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','YOURTABLE', DBMS_REDEFINITION.CONS_USE_ROWID);   
END;
/

-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCHEMA',
ORIG_TABLE => 'YOURTABLE',
INT_TABLE => 'INTERIM_YOURTABLE'
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- IF THE TABLE HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE 
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'YOURTABLE', 'INTERIM_YOURTABLE', 
dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','YOURTABLE','INTERIM_YOURTABLE');

如果您使用SYSTEM使用者執行重新定義,則沒有問題。如果您想使用特權較低的使用者執行它,則必須欺騙一些特權才能使其正常工作。所需的特權是:

  • DBMS_REDEFINITION 的執行權限
  • 創建任何表
  • 更改任何表
  • 刪除任何表
  • 鎖定任何表
  • 選擇任何表

不能線上重新定義具有以下特徵的表:

  • $$ 9.0.1 $$沒有主鍵的表
  • 定義了物化視圖日誌的表
  • $$ 9i $$作為物化視圖容器表和 AQ 表的表
  • $$ 10g $$可以重新定義在 n 路主配置中複製的表,但不允許水平子集(表中行的子集)、垂直子集(表中列的子集)和列轉換。
  • IOT表的溢出表
  • 具有細粒度訪問控制的表(行級安全性)
  • 具有 BFILE 列的表
  • 可以線上重新定義具有 LONG 列的表,但必須將這些列轉換為 CLOBS。此外,LONG RAW 列必須轉換為 BLOBS。帶有 LOB 列的表是可以接受的。
  • SYS 和 SYSTEM 模式中的表
  • 臨時表

其他限制:

  • 表中的行子集
  • 將臨時表中的列映射到原始表中的列時,只能使用簡單的確定性表達式、序列和 SYSDATE。例如,不允許子查詢。
  • 如果添加的新列沒有列映射,則在重新定義完成之前,不得將它們聲明為 NOT NULL。
  • 被重新定義的表和臨時表之間不能有任何引用約束。
  • 表重新定義無法完成 NOLOGGING。
  • $$ 10g $$對於物化視圖日誌和隊列表,線上重新定義僅限於物理屬性的變化。
  • 您不能將嵌套表轉換為 VARRAY。

如果您有嚴格的空間限制,那麼可能值得考慮將 dbms_redefinition 與分區交換結合使用。

  1. 為每個子分區創建一個新表
  2. 執行分區交換以在子分區和表之間交換數據段。
  3. 使用子分區鍵的新數據類型重新創建分區表。
  4. 重新定義每個表的等效列的數據類型。
  5. 再次執行分區交換。

不過,值得在一張小桌子上進行測試,以確保它首先工作。

引用自:https://dba.stackexchange.com/questions/21485