资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/Oracle使用alter table set unused删除字段的小技巧

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

IT那活儿 / 2390人阅读
Oracle使用alter table set unused删除字段的小技巧
 背 景 

Oracle的删除字段操作，常规方法是使用alter table table_name drop column column_name;或者alter table_name drop (column_name1, column_name2);，但是对于大表来说，删除字段的过程会消耗大量时间和资源，甚至会导致锁表。


解决方法

使用ALTER TABLE SET UNUSED COLUMN在逻辑上删除字段，等系统空闲时再ALTER TABLE DROP UNUSED COLUMNS。

  • 示例

1. 创建示例表

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.

一旦执行该语句，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的字段数：

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.


如果要减少产生的undo日志数量，可以使用在处理了指定的行数之后强制执行检查点的选项：

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

Table altered.


3. 字段恢复

由于设置unused之后，并不是将该列数据立即删除，而是被隐藏起来，物理上还是存在的，因此可以通过修改数据字典的方法进行恢复。

先设置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表的字段数量：

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

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

字段数量已由14个变为13个。

将test.objects表的字段号重新设置为初始字段号：

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.

将字段数恢复为14个：

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

将第二个字段的字段名设置成与之前相同：

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.

至此数据字典已全部改回，重启数据库生效。

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.

恢复完成。

END


更多精彩干货分享

点击下方名片关注

IT那活儿

GPU云服务器 云服务器 PPAS set oracle sid 删除字段 删除表字段 oracle修改表字段

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129838.html

相关文章

  • 1.MySQL数据库 2.SQL语句

    摘要：公司的数据库产品收费的。常应用在银行系统中公司收费的中型的数据库。嵌入式的小型数据库，应用在手机端。关键字，，等数据控制语言简称，用来定义数据库的访问权限和安全级别，及创建用户。数据查询语言简称，用来查询数据库中表的记录。 01数据库概念 * A: 什么是数据库 数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进...

    dunizb 评论0 收藏0

  • Oracle总结【视图、索引、事务、用户权限、批量操作】

    摘要：前言在总结的第一篇中，我们已经总结了一些常用的相关的知识点了那么本篇主要总结关于视图序列事务的一些内容在数据库中，我们可以把各种的语句分为四大类数据操纵语言，，，数据定义语言，，，数据控制语言事务控制语言，，回滚点批量操作何为批量操作，就是 前言 在Oracle总结的第一篇中，我们已经总结了一些常用的SQL相关的知识点了...那么本篇主要总结关于Oralce视图、序列、事务的一些内容....

    junnplus 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<