一.使用set unused的情況

當表中的某一個列不再使用的時候，我們可以刪除這個列

alter table xxx drop column xxx;

或者

alter table xxx drop (xxx,xxx);

當刪除列的時候，需要注意的是，不能刪除一個表的所有列和刪除sys下面表的列。

You cannot drop all columns from a table, nor can you drop columns from a table owned by?SYS. Any attempt to do so results in an error

當我們刪除一個大表中的數據，這個操作會需要一定的時間。這個時候，我們就可以用ALTER TABLE XXX SET UNUSED;

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. However, 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列中標記為未使用的任何內部索引都不會被刪除。

You can later remove columns that are marked as unused by issuing an?ALTER TABLE...DROP UNUSED COLUMNS?statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

稍后，您可以通過發出ALTER TABLE來刪除標記為未使用的列…刪除未使用的列語句。每當發出任何特定列或表的列的顯式刪除時，還將從目標表中刪除未使用的列

The data dictionary views?USER_UNUSED_COL_TABS,?ALL_UNUSED_COL_TABS, or?DBA_UNUSED_COL_TABS?can be used to list all tables containing unused columns. The?COUNT?field shows the number of unused columns in the table.

USER_UNUSED_COL_TABS、ALL_UNUSED_COL_TABS或DBA_UNUSED_COL_TABS可用于列出包含未使用列的所有表。COUNT字段顯示表中未使用的列的數量。

只是可以查看表明和unused的列的數量。無法查看具體的unused的列名

For external tables, the?SET?UNUSED?statement is transparently converted into an?ALTER?TABLE?DROP?COLUMN?statement. Because external tables consist of metadata only in the database, the?DROP?COLUMN?statement performs equivalently to the?SET?UNUSED?statement.

對于外部表，SET used語句被透明地轉換為ALTER TABLE DROP COLUMN語句。因為外部表只包含數據庫中的元數據，所以DROP列語句的執行與SET used語句相當。

The?ALTER TABLE...DROP UNUSED COLUMNS?statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

ALTER TABLE……刪除未使用的列語句是未使用的列上唯一允許的操作。它從物理上刪除表中未使用的列并回收磁盤空間。

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語句中，指定了可選子句檢查點。此子句導致在處理指定行數(本例中為250)后應用檢查點。檢查點減少了drop列操作期間積累的撤消日志數量，以避免潛在的撤消空間耗盡。

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

二.恢復unused的列

設置unused的作用是為了在cpu、內存等資源不充足的時候，先做上unused標記再等數據庫資源空閑的時候用drop set unused刪除

設置unused列之后，并不是將該列數據立即刪除，而是被隱藏起來，物理上還是存在的，以下為恢復步驟：對數據字典不熟悉的朋友測試前做好備份工作

SQL> conn scott/oracle

Connected.

SQL> create table xs (id number, name char(10),age number);

Table created.

SQL> insert into xs values(1,'JACK',20);

1 row created.

SQL> insert into xs values(2,'BILL',21);

1 row created.

SQL> insert into xs values(3,'TOM',22);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xs;

>select * from xs;

ID ?????NAME????????????? AGE

---------- ---------- ??????? ----------

1 JACK?????????????? 20

2 BILL?????????????? 21

3 TOM??????????????? 22

SQL> alter table xs set unused column AGE;

Table altered.

SQL> select * from xs;

ID ?????NAME

---------- ----------

1 JACK

2 BILL

3 TOM

SQL> SET LINESIZE 200?設置顯示列寬

SQL> SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS;

OBJECT_ID OBJECT_NAME

--------------------------------------------------------------------------------------------------???? 51147 PK_DEPT

51146 DEPT

51148 EMP

51149 PK_EMP

51150 BONUS

51151 SALGRADE

52613 D

52614 D1

52615 D3

52696 BIG

52717 XS------------------------ OBJECT_ID=52717

11 rows selected.

SQL> conn / as sysdba

Connected.

SQL> select col#,intcol#,name from col$ where obj#=52717;

COL#??? INTCOL# NAME

---------- ---------- ------------------------------

1????????? 1 ID

2????????? 2 NAME

0???????? ?3 SYS_C00003_12092313:06:51$----------原來的列名為C，被系統修了.

SQL> select cols from tab$ where obj#=71930;

COLS

----------

2??? -----------------------系統的字段數目也發生了變化

SQL> update col$ set col#=intcol# where obj#=52717;

3 rows updated.

SQL> update tab$ set cols=cols+1 where obj#=52717;

1 row updated.

SQL> update col$ set name='AGE' where obj#=52717 and col#=3;

1 row updated.

SQL> update col$ set property=0 where obj#=52717;

3 rows updated.

SQL> commit;

Commit complete.

SQL> startup force;?? -----------這一步是必不可少的

ORACLE instance started.

Total System Global Area?285212672 bytes

Fixed Size????????????????? 1218992 bytes

Variable Size????????????? 92276304 bytes

Database Buffers????????? 188743680 bytes

Redo Buffers???? ???????????2973696 bytes

Database mounted.

Database opened.

SQL> select * from scott.xs;

ID ????NAME????? AGE

---------- ---------- ----------

1 JACK?????? 20

2 BILL????????21

3 TOM??????? 22

我們可以看到，對于設置了unused的列，恢復的話我們需要知道設置之前列的名稱。

總結

以上是生活随笔為你收集整理的oracle unused 语法_oracle--set unused的全部內容，希望文章能夠幫你解決所遇到的問題。

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