【SQL】不可用SET UNUSED

描述

当一张表的字段不在需要的时候可以使用

alter table TABLE_NAME drop column COLUMN_NAME;或alter table TABLE_NAME drop(COLUMN1,COLUMN2);来删除多个列，但是不能删除一个表的所有列和sys表下的列

然而当使用drop column的时候如果表中有大量数据，删除过程中会锁定表，并消耗大量时间，这个时候可以先将该列置为不可用(UNSUED)

alter table TABLE_NAME set unused(COLUMN1,COLUMN2);此语句可同时将多列标记为unsued，但并不会真正删除该列，但是被标记为unused的列不会显示在select结果或数据字典视图中，在大多数情况下会连同列上所定义的索引、约束和统计信息一并删除，除非该列为LOB字段

可以使用alter table drop unsued columns[checkpoint COUNT];来删除不可用的列并回收磁盘空间

在user_unused_col_tabs、all_unused_col_tabs、dba_unused_col_tabs中可以看到被标记为unused的所有表count字段中表示未使用列的数量，但是只能查看表的名称和被unused的数量，无法查看是哪一列被标为unused

对于外部表set unused将被转换成对应的drop语句

 

测试

创建两张一样的大表 有800W+条数据tb_big10和tb_big20

SQL> create table tb_big01 as select * from dept;   Table created.   SQL> insert into tb_big01 select * from tb_big01;   4 rows created.   SQL> /   8 rows created.   …   SQL> /   4194304 rows created.   SQL> create table tb_big02 as select * from tb_big01;   Table created.   SQL> select count(*) from tb_big01;     COUNT(*) ----------    8388608   SQL> select count(*) from tb_big02;     COUNT(*) ----------    8388608   SQL> desc tb_big01;  Name                    Null?    Type  ----------------------- -------- ---------------  DEPTNO                           NUMBER(2)  DNAME                            VARCHAR2(14)  LOC                              VARCHAR2(13)   SQL> desc tb_big02;  Name                    Null?    Type  ----------------------- -------- ---------------  DEPTNO                           NUMBER(2)  DNAME                            VARCHAR2(14)  LOC                              VARCHAR2(13)

打开执行时间并删除tb_big10的其中两列

SQL> set timeing on SQL> alter table tb_big01 drop(DEPTNO,LOC);   Table altered.   Elapsed: 00:01:39.86   SQL> desc tb_big01;  Name                    Null?    Type  ----------------------- -------- --------------  DNAME                            VARCHAR2(14)   SQL> rollback;   Rollback complete.   Elapsed: 00:00:00.00 SQL> desc tb_big01;  Name                    Null?    Type  ----------------------- -------- --------------  DNAME                            VARCHAR2(14)

耗时一分半，drop的两列顺理成章地消失了，由于drop属于ddl，不记redo因此也不能回滚

将tb_big20的同样两列设为unused

SQL> alter table tb_big02 set unused(DEPTNO,LOC);   Table altered.   Elapsed: 00:00:00.04 SQL> desc desc tb_big02; Usage: DESCRIBE [schema.]object[@db_link] SQL> desc tb_big02;  Name                    Null?    Type  ----------------------- -------- --------------  DNAME                            VARCHAR2(14)   SQL> rollback;   Rollback complete.   Elapsed: 00:00:00.00 SQL> desc tb_big02;  Name                    Null?    Type  ----------------------- -------- --------------  DNAME                            VARCHAR2(14)

耗时基本上可以忽略不计，同样无法回滚

可以在user_unused_col_tabs中查看被unused的列数

SQL> select * from user_unused_col_tabs;   TABLE_NAME           COUNT --------------- ---------- TB_BIG02                 2

如果在业务高峰的时候做删除列的操作还是set unused比较快

然后在业务闲时进行空间回收

为避免ORA-01562报错的发生，可以加上checkpoint参数，设置每1000条提交一次

SQL> alter table tb_big02 drop unused columns checkpoint 1000;   Table altered.   Elapsed: 00:01:59.63

由于commit比较频繁，因此耗时会比一次commit要长一些

 

UNUSED恢复

一般来说column设置为unused之后是不可以恢复的，但也不是完全不能恢复

建立测试表tb_unused01

SQL> create table tb_unused01 as select * from dept;   Table created.   SQL> desc dept;  Name                    Null?    Type  ----------------------- -------- ---------------  DEPTNO                  NOT NULL NUMBER(2)  DNAME                            VARCHAR2(14)  LOC                              VARCHAR2(13)   SQL> select * from dept;    DEPTNO DNAME      LOC ------- ---------- ----------      10 ACCOUNTING NEW YORK      20 RESEARCH   DALLAS      30 SALES      CHICAGO      40 OPERATIONS BOSTON

将LOC列设为unused

SQL> alter table tb_unused01 set unused column LOC;   Table altered.

查看tb_unused01表

SQL> select * from tb_unused01;       DEPTNO DNAME ---------- ---------------         10 ACCOUNTING         20 RESEARCH         30 SALES         40 OPERATIONS

 

在dba用户在基础对象表obj$中查找tb_unused01的对象号

SQL> select OBJ#,OWNER#,NAME from obj$ where name='TB_UNUSED01';         OBJ#     OWNER# NAME ---------- ---------- -----------      75161        106 TB_UNUSED01

在col$数据字典中查看该表列的信息

SQL> select COL#,INTCOL#,NAME,PROPERTY from col$ where obj#='75161';         COL#    INTCOL# NAME                         PROPERTY ---------- ---------- -------------------------- ----------          1          1 DEPTNO                              0          2          2 DNAME                               0          0          3 SYS_C00003_21012514:28:22$      32800

 

在tab$中查看列的情况

SQL> select OBJ#,COLS from tab$ where obj#='75161';         OBJ#       COLS ---------- ----------      75161          2

恢复该列的信息

SQL> update col$ set col#=intcol# where obj#='75161';   3 rows updated.   SQL> select COL#,INTCOL#,NAME from col$ where obj#='75161';         COL#    INTCOL# NAME                                                               PROPERTY ---------- ---------- --------------------------- ---------          1          1 DEPTNO                              0          2          2 DNAME                               0          3          3 SYS_C00003_21012514:28:22$      32800

再更新到表的数据字典中

SQL> update tab$ set cols=cols+1 where obj#='75161';   1 row updated.   SQL> select OBJ#,COLS from tab$ where obj#='75161';         OBJ#       COLS ---------- ----------      75161          3

将列的属性值改为0

SQL> select COL#,INTCOL#,NAME,PROPERTY from col$ where obj#='75161';         COL#    INTCOL# NAME                         PROPERTY ---------- ---------- -------------------------- ----------          1          1 DEPTNO                              0          2          2 DNAME                               0          3          3 SYS_C00003_21012514:28:22$          0

将乱码列名称改一下

SQL> select COL#,INTCOL#,NAME,PROPERTY from col$ where obj#='75161';         COL#    INTCOL# NAME                         PROPERTY ---------- ---------- -------------------------- ----------          1          1 DEPTNO                              0          2          2 DNAME                               0          3          3 loc_new                             0

提交并重启数据库

SQL> commit;   Commit complete.   SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started.   Total System Global Area  763363328 bytes Fixed Size                  8625368 bytes Variable Size             557843240 bytes Database Buffers          192937984 bytes Redo Buffers                3956736 bytes Database mounted. Database opened. SQL> conn scott/tiger Connected. SQL> select * from tb_unused01;       DEPTNO DNAME      loc_new ---------- ---------- -------------------------------------         10 ACCOUNTING NEW YORK         20 RESEARCH   DALLAS         30 SALES      CHICAGO         40 OPERATIONS BOSTON

该列已经恢复

可见col$中可以更改列的名称