Oracle修改表字段类型(number-->varchar2(len)),亲测可用

思路：

--新建临时表以存储正确的顺序
create table A_2 as select （column1,colum2,……A表中的顺序） from A_1 ;

--删除表A_1
drop table A_1;

--新建A_1并从A_2表中赋予其正确的顺序和值
create table A_1 as select * from A_2;

--删除临时表A_2
drop table A_2;

 

以下为亲测实例--
--step1:
create table M_YS_YSDFZTB_6101_2017_tmp
as select YSBH, DFFXDH,TO_CHAR(YSZZDH) AS YSZZDH,TO_CHAR(YSJZDH) AS YSJZDH,ZT,ZWYF, ZWRQ, YWRQ, YWLSH
from M_YS_YSDFZTB_6101_2017 ;

--step2:
drop table M_YS_YSDFZTB_6101_2017;

--step3:
create table M_YS_YSDFZTB_6101_2017 as select * from M_YS_YSDFZTB_6101_2017_tmp ;
--修改字段长度并置为不为空
alter table M_YS_YSDFZTB_6101_2017 modify (YSZZDH VARCHAR2(16) not null,YSJZDH VARCHAR2(16) not null);
--添加字段备注说明
-- Add comments to the columns
comment on column M_YS_YSDFZTB_6101_2017.YSBH
is '应收编号';
comment on column M_YS_YSDFZTB_6101_2017.DFFXDH
is '电费发行单号';
comment on column M_YS_YSDFZTB_6101_2017.YSZZDH
is '应收轧账单号';
comment on column M_YS_YSDFZTB_6101_2017.YSJZDH
is '应收结账单号';
comment on column M_YS_YSDFZTB_6101_2017.ZT
is '状态（－1 已发行未轧账0已生成轧账待核对1已核对待发起结账2已发起待提交 3已提交待审核 4已审核待推送 5已推送未结 6已结）';
comment on column M_YS_YSDFZTB_6101_2017.ZWYF
is '账务月份';
comment on column M_YS_YSDFZTB_6101_2017.ZWRQ
is '账务日期';
comment on column M_YS_YSDFZTB_6101_2017.YWRQ
is '业务日期';
comment on column M_YS_YSDFZTB_6101_2017.YWLSH
is '业务流水号';

--添加/修改主键机表空间
alter table M_YS_YSDFZTB_6101_2017
add primary key (YSBH)
using index
--tablespace USERS 148 表空间
--tablespace IDX_HIS 134 表空间
tablespace IDX_HIS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
--step4:
drop table M_YS_YSDFZTB_6101_2017_tmp;