Oracle创建表、删除表、修改表（添加字段、修改字段、删除字段）语句总结

创建表：

　　create table 表名 (

　　　　字段名1 字段类型 默认值 是否为空 ，

　　　　字段名2 字段类型 默认值 是否为空，

　　　　字段名3 字段类型 默认值 是否为空，

　　　　......

　　);

　　创建一个user表：

　　create table user (

　　　　id number(6) primary key,   ---主键

　　　　name varchar(50) not null,   ---姓名 不为null

　　　　sex varchar2(6) default '男' check ( sex in ('男','女'))   ---性别 默认'男'

　　);

修改表名：

　　rename 旧表名 to 新表名；

　　rename user to newuser;

删除表：

　　delete from 表名；

　　delete删除数据是一条一条的删除数据，后面可以添加where条件，不删除表结构。注意：如果表中有identity产生的自增id列，delete from后仍然从上次的数开始增加。

　　truncate table 表名；

　　truncate是一次性删掉所有数据，不删除表结构。注意：如果表中有identity产生的自增id列，truncate后，会恢复初始值。

　　drop table 表名；

　　drop删除所有数据，会删除表结构。

修改表：

　　添加新字段：

　　alter table 表名 add(字段名 字段类型 默认值 是否为空);

　　alter table user add(age number(6));

　　alter table user add (course varchar2(30) default '空' not null);

　　修改字段：

　　alter table 表名 modify (字段名 字段类型 默认值 是否为空);

　　alter table user modify((age number(8));

　　修改字段名：

　　alter table 表名 rename  column  列名 to 新列名;

　　alter table user rename column course to newcourse;

　　删除字段：

　　alter table 表名 drop column 字段名；

　　alter table user drop column course;

 

增加字段的语句很简单，以用户身份连接oracle服务：

alter table tablename add(colname coltype);  # 填上表名、字段名、字段类型

修改字段顺序前，查看表中各字段的顺序：

　　首先，查看表对应的id：

select object_id from all_objects where owner = 'user' and object_name = 'tablename'; # 填上表的所有者、表名

　　然后，查看表中各字段的顺序：

select obj#,col#,name from sys.col$ where obj#=objectid # 填上刚刚查到的表id

　　再以sysdba身份连接oracle服务，修改字段顺序，否则可能会报权限不够：

update sys.col$ set col#=new where name='colname' and obj#=objectid # 填上字段新的顺序、字段名、表id

　　值得注意的一点是，更新完字段顺序后，若直接插入数据，还是按旧的字段顺序插入的，需要指定插入的字段或者重启oracle。

 

在oracle表中增加字段，并调整字段的顺序

增加字段的语句很简单，以用户身份连接oracle服务：

alter table tablename add(colname coltype);  # 填上表名、字段名、字段类型

修改字段顺序前，查看表中各字段的顺序：

　　首先，查看表对应的id：

select object_id from all_objects where owner = 'user' and object_name = 'tablename'; # 填上表的所有者、表名

　　然后，查看表中各字段的顺序：

select obj#,col#,name from sys.col$ where obj#=objectid # 填上刚刚查到的表id

　　再以sysdba身份连接oracle服务，修改字段顺序，否则可能会报权限不够：

update sys.col$ set col#=new where name='colname' and obj#=objectid # 填上字段新的顺序、字段名、表id

　　值得注意的一点是，更新完字段顺序后，若直接插入数据，还是按旧的字段顺序插入的，需要指定插入的字段或者重启oracle。

 

oracle 大表添加字段方法
近期，考虑到生产一线大表需要进行表结构更改，自身测试了一把，如下：

操作系统版本：centos 6.5
数据库版本：oracle 11.2.0.4
数据表：中小型堆表，千万级

先分享一个创建测试表、序列、数据的小脚本
创建测试表

create table insert_test(
    id number,
    address varchar2(20),
    QRcode varchar2(20),
    password varchar2(30)
);
创建一个自用序列

create sequence wyl_seq
start with 1
increment by 1
nomaxvalue
nominvalue
nocycle
nocache;
插入12,000,000行数据，47分钟。。。。。

begin
    for i in 1 .. 12000000 loop
        insert into insert_test values(WYL_SEQ.NEXTVAL,'www.baidu.com',dbms_random.string('x', 20),dbms_random.string('p',30));
    end loop;
    commit;
end;
/
现在进行加字段并设置默认值测试，主要有两种方法（一是直接添加并设置默认值，二是先添加，然后modify默认值）
我们先测试一下第二种方法

alter table insert_test add str_te varchar2(10);
alter table insert_test modify str_te default '0';

发现很快就完成了，时间可以忽略不计，下面测试一下第一种方法

alter table insert_test add str_te varchar2(10) default '0';

竟然达到了21分钟。。。。

结论：alter table add
语句加上defalut时会刷新存量数据并产生表级锁，需慎用。特别是大表，生产环境，业务产生期间就应该禁止此操作。 改为add table
add不带缺省值，接着来个alter table aa modify column_1 varchar2(2) default
‘Y’;更新存量数据可放到业务较少的凌晨跑。