管理表（三）--删除列的方式

 

1、删除列

03:44:40 SQL> create table test as select * from emp;

Table created.

03:45:06 SQL> desc test;

Name                                                                     Null     Type

----------------------------------------------------------------------  -------- -----------------

EMPNO                                                                             NUMBER(4)

ENAME                                                                             VARCHAR2(10)

JOB                                                                               VARCHAR2(9)

MGR                                                                               NUMBER(4)

HIREDATE                                                                          DATE

SAL                                                                               NUMBER(7,2)

COMM                                                                              NUMBER(7,2)

DEPTNO                                                                            NUMBER(2)

03:45:10 SQL> alter table test drop column comm;

Table altered.

03:45:26 SQL> desc test;

Name                                                                     Null     Type

----------------------------------------------------------------------  -------- -----------------

EMPNO                                                                             NUMBER(4)

ENAME                                                                             VARCHAR2(10)

JOB                                                                               VARCHAR2(9)

MGR                                                                               NUMBER(4)

HIREDATE                                                                          DATE

SAL                                                                               NUMBER(7,2)

DEPTNO                                                                            NUMBER(2)

03:45:28 SQL> alter table test drop (sal,hiredate);

Table altered.

03:45:54 SQL> desc test;

Name                                                                     Null     Type

----------------------------------------------------------------------  -------- -----------------

EMPNO                                                                             NUMBER(4)

ENAME                                                                             VARCHAR2(10)

JOB                                                                               VARCHAR2(9)

MGR                                                                               NUMBER(4)

DEPTNO                                                                            NUMBER(2)

2、使用unused 删除列

将列标记为UNUSED，然后再数据库空闲时删除列

03:48:40 SQL> alter table test set unused column mgr;

Table altered.

03:49:03 SQL> desc test;

Name                                                                     Null     Type

----------------------------------------------------------------------  -------- -----------------

EMPNO                                                                             NUMBER(4)

ENAME                                                                             VARCHAR2(10)

JOB                                                                               VARCHAR2(9)

DEPTNO                                                                            NUMBER(2)

03:49:04 SQL> alter table emp drop unused columns checkpoint 1000;

Table altered.

03:49:26 SQL>

Checkpoint 1000 用于指定每删除1000行发出一次检查点。