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;