ORACLE表test01字段的操作

SQL> create table test01
    (NO NUMBER NOT NULL,NAME VARCHAR(30));



1.修改表名称
    
SQL> alter table test01 rename to test02;

Table altered.

SQL> desc test01
ERROR:
ORA-04043: object test01 does not exist


SQL> desc test02
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NO                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(30)


 
 2.更改表列名称 


 SQL> alter table test01 rename column no to nod;

Table altered.

SQL> desc test01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NOD                                       NOT NULL NUMBER
 NAME                                               VARCHAR2(30)



3.修改表test01的NOD字段类型


SQL> alter table test01 modify nod char;

Table altered.

SQL> desc test01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NOD                                       NOT NULL CHAR(1)
 NAME                                               VARCHAR2(30)



3.给表test01增加单列dept


SQL> alter table test01 add dept varchar2(100);

Table altered.

SQL> desc test01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NOD                                       NOT NULL CHAR(1)
 NAME                                               VARCHAR2(30)
 DEPT                                               VARCHAR2(100)


4.给表test01增加多列sex,ages

SQL> alter table test01 add  (sex char(2),ages number);

Table altered.

SQL> desc test01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NOD                                       NOT NULL CHAR(1)
 NAME                                               VARCHAR2(30)
 DEPT                                               VARCHAR2(100)
 SEX                                                CHAR(2)
 AGES                                               NUMBER



5.同时添加和修改表test01的列


SQL> desc test01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NOD                                       NOT NULL CHAR(1)
 NAME                                               VARCHAR2(30)
 DEPT                                               VARCHAR2(100)
 SEX                                                CHAR(2)
 AGES                                               NUMBER



SQL> alter table test01 add (new01 char,new02 char)
  2  modify (nod number,name number,dept number ,sex number,ages char);

Table altered.

SQL> desc test01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NOD                                       NOT NULL NUMBER
 NAME                                               NUMBER
 DEPT                                               NUMBER
 SEX                                                NUMBER
 AGES                                               CHAR(1)
 NEW01                                              CHAR(1)
 NEW02                                              CHAR(1)




 6.删除表test01的列

 SQL> alter table test01 drop column dept cascade constraints;
alter table test01 drop column dept cascade constraints
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

由于我的表是在sys下建立的所以不能删除sys的表的列，
其他用户可以参考 以下命令 如在scott用户下的表删除列

SQL > alter table scott.test01 drop column dept cascade constraints;