国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

Oracle使用alter table set unused刪除字段的小技巧

IT那活兒 / 2391人閱讀
Oracle使用alter table set unused刪除字段的小技巧
 背 景 

Oracle的刪除字段操作，常規(guī)方法是使用alter table table_name drop column column_name;或者alter table_name drop (column_name1, column_name2);，但是對于大表來說，刪除字段的過程會(huì)消耗大量時(shí)間和資源，甚至?xí)?dǎo)致鎖表。


解決方法

使用ALTER TABLE SET UNUSED COLUMN在邏輯上刪除字段，等系統(tǒng)空閑時(shí)再ALTER TABLE DROP UNUSED COLUMNS。

  • 示例

1. 創(chuàng)建示例表

SQL> create table test.objects as select * from dba_objects;

Table created.

SQL> select count(*) from test.objects;

  COUNT(*)
----------
     86984

SQL> desc test.objects 
 Name                                      Null? Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)


2. 刪除字段

在邏輯上刪除OBJECT_ID字段：

SQL> alter table test.objects set unused column OBJECT_ID;

Table altered.

一旦執(zhí)行該語句，OBJECT_ID字段就不再可見。

SQL> desc test.objects 
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 OWNER VARCHAR2(30)
 OBJECT_NAME VARCHAR2(128)
 SUBOBJECT_NAME VARCHAR2(30)
 DATA_OBJECT_ID NUMBER
 OBJECT_TYPE VARCHAR2(19)
 CREATED DATE
 LAST_DDL_TIME DATE
 TIMESTAMP VARCHAR2(19)
 STATUS VARCHAR2(7)
 TEMPORARY VARCHAR2(1)
 GENERATED VARCHAR2(1)
 SECONDARY VARCHAR2(1)
 NAMESPACE NUMBER
 EDITION_NAME VARCHAR2(30)

SQL> select OBJECT_ID from test.objects ;
select OBJECT_ID from test.objects
       *
ERROR at line 1:
ORA-00904: "OBJECT_ID": invalid identifier

可以從dba_unused_col_tabs視圖查看表中unused的字段數(shù)：

SQL> select * from dba_unused_col_tabs;

OWNER TABLE_NAME COUNT
------------------------------ ------------------------------ ----------
TEST OBJECTS 1

使用以下語句刪除表中所有unused的字段：

SQL> alter table test.objects drop unused columns;

Table altered.


如果要減少產(chǎn)生的undo日志數(shù)量，可以使用在處理了指定的行數(shù)之后強(qiáng)制執(zhí)行檢查點(diǎn)的選項(xiàng)：

SQL> alter table test.objects drop unused columns checkpoint 250;

Table altered.


3. 字段恢復(fù)

由于設(shè)置unused之后，并不是將該列數(shù)據(jù)立即刪除，而是被隱藏起來，物理上還是存在的，因此可以通過修改數(shù)據(jù)字典的方法進(jìn)行恢復(fù)。

先設(shè)置OBJECT_NAME列為unused：

SQL> desc test.objects;
 Name                                      Null? Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> alter table test.objects set unused column OBJECT_NAME;

Table altered.

SQL> desc test.objects;
 Name                                      Null? Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

查看test.objects表的對象號：

SQL> select OBJ# from OBJ$ where name=OBJECTS;

      OBJ#
----------
     88997

對象號為88997

查看test.objects表的字段號、初始字段號、字段名：

SQL> select COL#,INTCOL#,NAME from COL$ where OBJ#=88997;

      COL# INTCOL# NAME
---------- ---------- ------------------------------
         1          1 OWNER
         0          2 SYS_C00002_21052517:15:22$
         2          3 SUBOBJECT_NAME
         3          4 DATA_OBJECT_ID
         4          5 OBJECT_TYPE
         5          6 CREATED
         6          7 LAST_DDL_TIME
         7          8 TIMESTAMP
         8          9 STATUS
         9         10 TEMPORARY
        10         11 GENERATED
        11         12 SECONDARY
        12         13 NAMESPACE
        13         14 EDITION_NAME

14 rows selected.

可以看到，原OBJECT_NAME列的字段號已被置為0，OBJECT_NAME后面列的字段號依次減1，OBJECT_NAME列的列名已被重置為SYS_C00002_21052517:15:22$。

查看test.objects表的字段數(shù)量：

SQL> select COLS from TAB$ where OBJ#=88997;

      COLS
----------
        13

字段數(shù)量已由14個(gè)變?yōu)?3個(gè)。

將test.objects表的字段號重新設(shè)置為初始字段號：

SQL> update COL$ set COL#=INTCOL# where OBJ#=88997;

14 rows updated.

SQL> commit;

Commit complete.

SQL> select COL#,INTCOL#,NAME from COL$ where OBJ#=88997;

      COL# INTCOL# NAME
---------- ---------- ------------------------------
         1 1 OWNER
         2 2 SYS_C00002_21052517:15:22$
         3 3 SUBOBJECT_NAME
         4 4 DATA_OBJECT_ID
         5 5 OBJECT_TYPE
         6 6 CREATED
         7 7 LAST_DDL_TIME
         8 8 TIMESTAMP
         9 9 STATUS
        10 10 TEMPORARY
        11 11 GENERATED
        12 12 SECONDARY
        13 13 NAMESPACE
        14 14 EDITION_NAME

14 rows selected.

將字段數(shù)恢復(fù)為14個(gè)：

SQL> update TAB$ set COLS=COLS+1 where OBJ#=88997;

1 row updated.
SQL> commit;

Commit complete.

SQL> select COLS from TAB$ where OBJ#=88997;

      COLS
----------
        14

將第二個(gè)字段的字段名設(shè)置成與之前相同：

update COL$ set NAME=OBJECT_NAME where OBJ#=88997 and COL#=2;

1 row updated.

SQL> commit;

Commit complete.
SQL> select COL#,INTCOL#,NAME from COL$ where OBJ#=88997;

      COL# INTCOL# NAME
---------- ---------- ------------------------------
         1          1 OWNER
         2          2 OBJECT_NAME
         3          3 SUBOBJECT_NAME
         4          4 DATA_OBJECT_ID
         5          5 OBJECT_TYPE
         6          6 CREATED
         7          7 LAST_DDL_TIME
         8          8 TIMESTAMP
         9          9 STATUS
        10         10 TEMPORARY
        11         11 GENERATED
        12         12 SECONDARY
        13         13 NAMESPACE
        14         14 EDITION_NAME

14 rows selected.


SQL> update COL$ set PROPERTY=0 where OBJ#=88997;

14 rows updated.

SQL> commit;

Commit complete.

至此數(shù)據(jù)字典已全部改回，重啟數(shù)據(jù)庫生效。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1486495744 bytes
Fixed Size 2253384 bytes
Variable Size 1375735224 bytes
Database Buffers 100663296 bytes
Redo Buffers 7843840 bytes
Database mounted.
Database opened.
SQL> desc test.objects 
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 OWNER VARCHAR2(30)
 OBJECT_NAME VARCHAR2(128)
 SUBOBJECT_NAME VARCHAR2(30)
 DATA_OBJECT_ID NUMBER
 OBJECT_TYPE VARCHAR2(19)
 CREATED DATE
 LAST_DDL_TIME DATE
 TIMESTAMP VARCHAR2(19)
 STATUS VARCHAR2(7)
 TEMPORARY VARCHAR2(1)
 GENERATED VARCHAR2(1)
 SECONDARY VARCHAR2(1)
 NAMESPACE NUMBER
 EDITION_NAME VARCHAR2(30)

SQL> select OBJECT_NAME from test.objects where rownum<10;

OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2

9 rows selected.

恢復(fù)完成。

END


更多精彩干貨分享

點(diǎn)擊下方名片關(guān)注

IT那活兒

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://specialneedsforspecialkids.com/yun/129838.html

相關(guān)文章

  • 1.MySQL數(shù)據(jù)庫 2.SQL語句

    摘要：公司的數(shù)據(jù)庫產(chǎn)品收費(fèi)的。常應(yīng)用在銀行系統(tǒng)中公司收費(fèi)的中型的數(shù)據(jù)庫。嵌入式的小型數(shù)據(jù)庫，應(yīng)用在手機(jī)端。關(guān)鍵字，，等數(shù)據(jù)控制語言簡稱，用來定義數(shù)據(jù)庫的訪問權(quán)限和安全級別，及創(chuàng)建用戶。數(shù)據(jù)查詢語言簡稱，用來查詢數(shù)據(jù)庫中表的記錄。 01數(shù)據(jù)庫概念 * A: 什么是數(shù)據(jù)庫 數(shù)據(jù)庫就是存儲(chǔ)數(shù)據(jù)的倉庫，其本質(zhì)是一個(gè)文件系統(tǒng)，數(shù)據(jù)按照特定的格式將數(shù)據(jù)存儲(chǔ)起來，用戶可以對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)...

    dunizb 評論0 收藏0

  • Oracle總結(jié)【視圖、索引、事務(wù)、用戶權(quán)限、批量操作】

    摘要：前言在總結(jié)的第一篇中，我們已經(jīng)總結(jié)了一些常用的相關(guān)的知識(shí)點(diǎn)了那么本篇主要總結(jié)關(guān)于視圖序列事務(wù)的一些內(nèi)容在數(shù)據(jù)庫中，我們可以把各種的語句分為四大類數(shù)據(jù)操縱語言，，，數(shù)據(jù)定義語言，，，數(shù)據(jù)控制語言事務(wù)控制語言，，回滾點(diǎn)批量操作何為批量操作，就是 前言 在Oracle總結(jié)的第一篇中，我們已經(jīng)總結(jié)了一些常用的SQL相關(guān)的知識(shí)點(diǎn)了...那么本篇主要總結(jié)關(guān)于Oralce視圖、序列、事務(wù)的一些內(nèi)容....

    junnplus 評論0 收藏0

發(fā)表評論

0條評論

最新活動(dòng)
閱讀需要支付1元查看
<