oracle 里面的test,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;