轉載自?Oracle設置和刪除不可用列

一、不可用列的定義

1、不可用列是什么？

就是表中的1個或多個列被ALTER TABLE…SET UNUSED 語句設置為無法再被程序利用的列。

2、使用場景？?
If you are concerned about the length of time it could take to drop column data from?
all of the rows in a large table, you can use the ALTER TABLE…SET UNUSED statement.

如果你擔心從一個大表中刪除一列可能花費大量時間，你可以使用ALTER TABLE…SET UNUSED語句。

如果你有這個需求，要刪除某一個讀寫頻繁的大表上的某些列，?
如果你在業務繁忙時間直接執行 ALTER TABLE ABC DROP （COLUMN）；?
可能會收到 ORA-01562 - failed to extend rollback segment number string，?
這是因為在這個刪除列的過程中你可能會可能消耗掉整個回滾表空間，造成這樣的錯誤出現。

3、使用理由（原理和優勢）？?
3.1 設置不可用列?
This statement marks one or more columns as unused, but does not actually remove?
the target column data or restore the disk space occupied by these columns。?
a column that is marked as unused is not displayed in queries or data dictionary?
views, and its name is removed so that a new column can reuse that name.

該語句可將一個或多個列標識為不可用，但實際上并不是移除了列數據或回收了這些列占用的空間。?
一個不可用列不會在查詢或數據字典視圖中顯示， 其列名被刪除以至于新增的列可以重用其列名。

In most cases, constraints, indexes, and statistics defined on the column are also removed.?
在多數情況下，列上的約束，索引，和統計信息也被移除。

The exception is that any internal indexes for LOB columns that are marked unused are not removed.?
例外情況是被標識為不可用的LOB列的內部索引不會被移除。

二、 刪除不可用列?

ALTER TABLE…DROP UNUSED COLUMNS 語句僅針對不可用列，用于正式刪除被標識為不可用的列（物理上刪除列同時回收被占用的空間）。

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified.?
This clause causes a checkpoint to be applied after processing the specified number of?
rows, in this case 250. Checkpointing cuts down on the amount of undo logs?
accumulated during the drop column operation to avoid a potential exhaustion of?
undo space.

在接下來的ALTER TABLE語句中個，指定了可選條件 CHECKPOINT。?
這個條件將在處理過程達到指定行數時觸發一個檢查點，此處為250. 檢查點削減了在刪除列操作中累積的undo logs的數量，?
從而避免潛在的undo空間耗盡。

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

4、使用限制？?
1）無法刪除屬于 SYS 的表中的列?
2）

5、語法結構？?
ALTER TABLE…SET UNUSED(C1,C2..)?
ALTER TABLE…DROP UNUSED COLUMNS

例如：?
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);?
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS;

5、數據字典?
USER_UNUSED_COL_TABS?
ALL_UNUSED_COL_TABS?

DBA_UNUSED_COL_TABS

SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT --------------------------- --------------------------- ----- HR ADMIN_EMP 2

–count列代表不可用列數量

6、使用案例？

SCOTT@orcl> create table tmp_all_objects2 AS3 SELECT object_id, object_name4 from dba_objects5 ;表已創建。

SYS@orcl> exec show_space('TMP_ALL_OBJECTS','SCOTT'); Unformatted Blocks .................... 0 FS1 Blocks (0-25) .................... 0 FS2 Blocks (25-50) .................... 0 FS3 Blocks (50-75) .................... 0 FS4 Blocks (75-100) .................... 0 Full Blocks .................... 352 Total Blocks ........................... 384 Total Bytes ........................... 3,145,728 Total MBytes ........................... 3 Unused Blocks........................... 18 Unused Bytes ........................... 147,456 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 14,592 Last Used Block......................... 110PL/SQL 過程已成功完成。SYS@orcl> DESC DBA_UNUSED_COL_TABS名稱 是否為空? 類型---------------------------------------- -------- ---------------------------OWNER NOT NULL VARCHAR2(30)TABLE_NAME NOT NULL VARCHAR2(30)COUNT NUMBERSYS@orcl> SELECT * FROM DBA_UNUSED_COL_TABS;未選定行SCOTT@orcl> ALTER TABLE TMP_ALL_OBJECTS SET UNUSED(OBJECT_NAME);表已更改。SYS@orcl> SELECT * FROM DBA_UNUSED_COL_TABS;OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- SCOTT TMP_ALL_OBJECTS 1SYS@orcl> exec show_space('TMP_ALL_OBJECTS','SCOTT'); Unformatted Blocks .................... 0 FS1 Blocks (0-25) .................... 0 FS2 Blocks (25-50) .................... 0 FS3 Blocks (50-75) .................... 0 FS4 Blocks (75-100) .................... 0 Full Blocks .................... 352 Total Blocks ........................... 384 Total Bytes ........................... 3,145,728 Total MBytes ........................... 3 Unused Blocks........................... 18 Unused Bytes ........................... 147,456 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 14,592 Last Used Block......................... 110PL/SQL 過程已成功完成。--刪除不可用列 SCOTT@orcl> ALTER TABLE TMP_ALL_OBJECTS DROP UNUSED COLUMNS CHECKPOINT 250;表已更改。SYS@orcl> SELECT * FROM DBA_UNUSED_COL_TABS;未選定行SYS@orcl> exec show_space('TMP_ALL_OBJECTS','SCOTT'); Unformatted Blocks .................... 0 FS1 Blocks (0-25) .................... 0 FS2 Blocks (25-50) .................... 1 FS3 Blocks (50-75) .................... 350 FS4 Blocks (75-100) .................... 1 Full Blocks .................... 0 Total Blocks ........................... 384 Total Bytes ........................... 3,145,728 Total MBytes ........................... 3 Unused Blocks........................... 18 Unused Bytes ........................... 147,456 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 14,592 Last Used Block......................... 110PL/SQL 過程已成功完成。--move操作，減少碎片 SCOTT@orcl> ALTER TABLE TMP_ALL_OBJECTS MOVE;表已更改。SYS@orcl> exec show_space('TMP_ALL_OBJECTS','SCOTT'); Unformatted Blocks .................... 0 FS1 Blocks (0-25) .................... 0 FS2 Blocks (25-50) .................... 0 FS3 Blocks (50-75) .................... 0 FS4 Blocks (75-100) .................... 0 Full Blocks .................... 113 Total Blocks ........................... 128 Total Bytes ........................... 1,048,576 Total MBytes ........................... 1 Unused Blocks........................... 5 Unused Bytes ........................... 40,960 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 14,808 Last Used Block......................... 3PL/SQL 過程已成功完成。--可以看到總塊數下降--刪除測試表 SCOTT@orcl> drop table TMP_ALL_OBJECTS;表已刪除。

三、關于不可用列的恢復（以下摘自網絡）?

剛才有個人問我如何修復被設置為UNUSED的字段，我考慮了一下，以下的方法可以恢復（以下步驟執行前要做好備份），沒有經驗的DBA不要輕易嘗試。

1、創建實驗表TTTA

SQL> CREATE TABLE TTTA ( A INTEGER,B INTEGER,C VARCHAR2(10),D INTEGER); 表已創建。 SQL> INSERT INTO TTTA VALUES (1,2,'3',4); 已創建 1 行。 SQL> INSERT INTO TTTA VALUES (2,3,'4',5); 已創建 1 行。 SQL> COMMIT; 提交完成。 ALTER TABLE TTTA SET UNUSED COLUMN C;

2、以下進行恢復

SQL> SELECT OBJ# FROM OBJ$ WHERE NAME='TTTA';OBJ# ----------32067 SELECT COL#,INTCOL#,NAME FROM COL$ WHERE OBJ#=32067;COL# INTCOL# NAME ---------- ---------- ------------------------------1 1 A2 2 B0 3 SYS_C00003_08031720:09:55$ 被UNUSED的字段3 4 D SQL> SELECT COLS FROM TAB$ WHERE OBJ#=32067;COLS ----------3 ------字段數變為3了SQL> UPDATE COL$ SET COL#=INTCOL# WHERE OBJ#=32067; 已更新4行。 SQL> UPDATE TAB$ SET COLS=COLS+1 WHERE OBJ#=32067; 已更新 1 行。 UPDATE COL$ SET NAME='C' WHERE OBJ#=32067 AND COL#=3; UPDATE COL$ SET PROPERTY=0 WHERE OBJ#=32067; SQL> COMMIT;

3、重啟數據庫

SQL> SELECT * FROM SCOTT.TTTA;A B C D ---------- ---------- ---------- ----------1 2 3 42 3 4 5

恢復完成

總結

以上是生活随笔為你收集整理的Oracle入门（十二H）之设置、恢复和删除不可用列的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。