unused的用法和恢复

       有时候，有些列不需要使用了，但是在业务高峰期，删除列是会锁表的，我们可以采用将列设置为unused的方法，这样，用户就看不到这些设置为unused的列，达到了我们想要的结果，但实际上并没有删除，而只是做了个标记，也不会锁表。
        这样，在业务空闲的时间，再将这些列删除(比如: 晚上)，或者，过后又不想再删除了，也可以恢复，但恢复的方法比较复杂一些，并且需要重启数据库。

SQL> conn scott/tiger

Connected.

SQL> drop table emp2;

Table dropped.

SQL> create table emp2 as select * from emp;

Table created.

SQL> select * from emp2;
     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-DEC-80           800                    20
      7499 ALLEN                SALESMAN                 7698 20-FEB-81          1600        300         30
      7521 WARD                 SALESMAN                 7698 22-FEB-81          1250        500         30
      7566 JONES                MANAGER                  7839 02-APR-81          2975                    20
      7654 MARTIN               SALESMAN                 7698 28-SEP-81          1250       1400         30
      7698 BLAKE                MANAGER                  7839 01-MAY-81          2850                    30
      7782 CLARK                MANAGER                  7839 09-JUN-81          2450                    10
      7788 SCOTT                ANALYST                  7566 19-APR-87          3000                    20
      7839 KING                 PRESIDENT                     17-NOV-81          5000                    10
      7844 TURNER               SALESMAN                 7698 08-SEP-81          1500          0         30
      7876 ADAMS                CLERK                    7788 23-MAY-87          1100                    20
      7900 JAMES                CLERK                    7698 03-DEC-81           950                    30
      7902 FORD                 ANALYST                  7566 03-DEC-81          3000                    20
      7934 MILLER               CLERK                    7782 23-JAN-82          1300                    10

14 rows selected.
SQL> alter table emp2 set unused column comm;

Table altered.

SQL> select * from emp2;

     EMPNO ENAME                JOB                       MGR HIREDATE            SAL     DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ----------
      7369 SMITH                CLERK                    7902 17-DEC-80           800         20
      7499 ALLEN                SALESMAN                 7698 20-FEB-81          1600         30
      7521 WARD                 SALESMAN                 7698 22-FEB-81          1250         30
      7566 JONES                MANAGER                  7839 02-APR-81          2975         20
      7654 MARTIN               SALESMAN                 7698 28-SEP-81          1250         30
      7698 BLAKE                MANAGER                  7839 01-MAY-81          2850         30
      7782 CLARK                MANAGER                  7839 09-JUN-81          2450         10
      7788 SCOTT                ANALYST                  7566 19-APR-87          3000         20
      7839 KING                 PRESIDENT                     17-NOV-81          5000         10
      7844 TURNER               SALESMAN                 7698 08-SEP-81          1500         30
      7876 ADAMS                CLERK                    7788 23-MAY-87          1100         20
      7900 JAMES                CLERK                    7698 03-DEC-81           950         30
      7902 FORD                 ANALYST                  7566 03-DEC-81          3000         20
      7934 MILLER               CLERK                    7782 23-JAN-82          1300         10

14 rows selected.
 
SQL> select * from user_unused_col_tabs where table_name='EMP2';

TABLE_NAME                                                        COUNT
------------------------------------------------------------ ----------
EMP2                                                                  1

SQL> SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS where object_name='EMP2';

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     62877 EMP2

SQL> conn / as sysdba
Connected.

SQL>  select col#,intcol#,name from col$ where obj#=62877;
      COL#    INTCOL# NAME
---------- ---------- --------------------------------------------
         1          1 EMPNO
         2          2 ENAME
         3          3 JOB
         4          4 MGR
         5          5 HIREDATE
         6          6 SAL
         0          7 SYS_C00007_14042410:47:45$
         7          8 DEPTNO

8 rows selected.

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

      COLS
----------
         7

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

8 rows updated.

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

1 row updated.

SQL> update col$ set name='COMM' where obj#=62877 and col#=7;

1 row updated.

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

8 rows updated.

SQL> commit;

Commit complete.

SQL> conn scott/tiger

Connected.

SQL> select * from emp2;

     EMPNO ENAME                JOB                       MGR HIREDATE            SAL     DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ----------
      7369 SMITH                CLERK                    7902 17-DEC-80           800         20
      7499 ALLEN                SALESMAN                 7698 20-FEB-81          1600         30
      7521 WARD                 SALESMAN                 7698 22-FEB-81          1250         30
      7566 JONES                MANAGER                  7839 02-APR-81          2975         20
      7654 MARTIN               SALESMAN                 7698 28-SEP-81          1250         30
      7698 BLAKE                MANAGER                  7839 01-MAY-81          2850         30
      7782 CLARK                MANAGER                  7839 09-JUN-81          2450         10
      7788 SCOTT                ANALYST                  7566 19-APR-87          3000         20
      7839 KING                 PRESIDENT                     17-NOV-81          5000         10
      7844 TURNER               SALESMAN                 7698 08-SEP-81          1500         30
      7876 ADAMS                CLERK                    7788 23-MAY-87          1100         20
      7900 JAMES                CLERK                    7698 03-DEC-81           950         30
      7902 FORD                 ANALYST                  7566 03-DEC-81          3000         20
      7934 MILLER               CLERK                    7782 23-JAN-82          1300         10

14 rows selected.
SQL> conn /as sysdba

Connected.

SQL> startup force
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size                  2066080 bytes
Variable Size             385878368 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14708736 bytes
Database mounted.
Database opened.

SQL> conn scott/tiger

Connected.

SQL> select * from emp2;

     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-DEC-80           800                    20
      7499 ALLEN                SALESMAN                 7698 20-FEB-81          1600        300         30
      7521 WARD                 SALESMAN                 7698 22-FEB-81          1250        500         30
      7566 JONES                MANAGER                  7839 02-APR-81          2975                    20
      7654 MARTIN               SALESMAN                 7698 28-SEP-81          1250       1400         30
      7698 BLAKE                MANAGER                  7839 01-MAY-81          2850                    30
      7782 CLARK                MANAGER                  7839 09-JUN-81          2450                    10
      7788 SCOTT                ANALYST                  7566 19-APR-87          3000                    20
      7839 KING                 PRESIDENT                     17-NOV-81          5000                    10
      7844 TURNER               SALESMAN                 7698 08-SEP-81          1500          0         30
      7876 ADAMS                CLERK                    7788 23-MAY-87          1100                    20
      7900 JAMES                CLERK                    7698 03-DEC-81           950                    30
      7902 FORD                 ANALYST                  7566 03-DEC-81          3000                    20
      7934 MILLER               CLERK                    7782 23-JAN-82          1300                    10

14 rows selected.

 

如果被标记为unused的列，想要删除，可以用下面的命令：

SQL> alter table emp2 set unused column comm;

Table altered.

SQL> alter table emp2 drop unused columns;

Table altered.