Oracle設定和刪除不可用列

indexman發表於2015-08-08
Oracle

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列的內部索引不會被移除。

3.2 刪除不可用列
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_objects
  2  AS
  3  SELECT object_id, object_name
  4  from dba_objects
  5  ;

表已建立。
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.........................             110

PL/SQL 過程已成功完成。




SYS@orcl> DESC DBA_UNUSED_COL_TABS
 名稱                                     是否為空? 型別
 ---------------------------------------- -------- ---------------------------
 OWNER                                    NOT NULL VARCHAR2(30)
 TABLE_NAME                               NOT NULL VARCHAR2(30)
 COUNT                                             NUMBER

SYS@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                         1

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.........................             110

PL/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.........................             110

PL/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.........................               3

PL/SQL 過程已成功完成。

--可以看到總塊數下降

--刪除測試表
SCOTT@orcl> drop table TMP_ALL_OBJECTS;

表已刪除。

7、關於不可用列的恢復（以下摘自網路）
剛才有個人問我如何修復被設定為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 A
         2          2 B
         0          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                   4
         2          3 4                   5

恢復完成

相關文章