Oracle之删除字段（UNUSED）

1、环境准备

 a.创建测试表

CREATE TABLE TEST(
  ID NUMBER,
  NAME VARCHAR2(60),
  SEX VARCHAR2(10) DEFAULT 'F',
  AGE NUMBER(3) DEFAULT 18,
  COL1 NUMBER,
  COL2 VARCHAR2(100)
);

insert into TEST(ID,NAME,Sex,Age,COL1,COL2)values(1,'KevinMa','F',18,1,'NIHAO');
insert into TEST(ID,NAME,Sex,Age,COL1,COL2)values(2,'JACK','M',18,0,'HelloWorld');
insert into TEST(ID,NAME,Sex,Age,COL1,COL2)values(3,'MARRY','M',18,1,'China');
COMMIT

2、字段删除

    a.简单删除字段COL2

SQL> ALTER TABLE TEST DROP COLUMN COL2;
SQL> select * from test;

	ID NAME 		SEX		  AGE	    COL1
---------- -------------------- ---------- ---------- ----------
	 1 KevinMa		F		   18	       1
	 2 JACK 		M		   18	       0
	 3 MARRY		M		   18	       1

SQL>

  3、有时候字段值数据量比较大，我们需要把字段先设置为UNUSED，然后删掉

  ALTER TABLE TEST SET UNUSED(COL1);
  ALTER TABLE TEST DROP UNUSED  COLUMN;

4、字段设置为UNUSED，但是未做删除操作，如何恢复这个字段

  注意：恢复时候需要SYSDBA权限

 

SQL> conn scott/tiger

Connected.

SQL> drop table emp_t;

Table dropped.

SQL> create table emp_t as select * from emp;

Table created.

SQL> select * from emp_t;
     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