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