--更改表中列的顺序,当中插入一个列.而不是在后面添加
第一种方法,进入sys系统用户
select object_id from all_objects where owner = 'RRFP' and object_name = 'CUST_USER' ; select obj#,col#,name from sys.col$ where obj#=54884 FOR UPDATE UPDATE sys.col$ SET COL#= COL#+1 WHERE OBJ#=54884 AND COL#>26 UPDATE sys.col$ SET COL#= 27WHERE OBJ#=54884 AND NAME='ORG_NAME'
第二种方法
当我们重建一个表的时候,会丢失注释,主键,外键. 然而增加一个列,只能放在最后面.可以生产sql.
-- Create table create table CUST_USER ( id VARCHAR2(64) not null, cust_name VARCHAR2(30), card_id VARCHAR2(64), card_valid VARCHAR2(18), sex VARCHAR2(18), country VARCHAR2(64), call VARCHAR2(64), address VARCHAR2(64), birthday VARCHAR2(64), nation VARCHAR2(64), reg_residence VARCHAR2(64), marriage VARCHAR2(18), education VARCHAR2(30), degree VARCHAR2(30), house_class VARCHAR2(64), house_place VARCHAR2(64), local VARCHAR2(64), local_years NUMBER(10), job_class VARCHAR2(30), duty VARCHAR2(64), title VARCHAR2(30), work_time VARCHAR2(64), company VARCHAR2(64), wkplace VARCHAR2(64), industry_years NUMBER(10), industry_class VARCHAR2(64), org_name VARCHAR2(60), income VARCHAR2(64), manager_id NVARCHAR2(64), create_by VARCHAR2(64), create_date DATE, update_by VARCHAR2(64), update_date DATE, del_flag VARCHAR2(64), remarks NVARCHAR2(255) ) tablespace CMP_DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table CUST_USER is '客户信息表'; -- Add comments to the columns comment on column CUST_USER.id is '主键'; comment on column CUST_USER.cust_name is '客户名称'; comment on column CUST_USER.card_id is '身份证号码'; comment on column CUST_USER.card_valid is '证件有效期'; comment on column CUST_USER.sex is '性别'; comment on column CUST_USER.country is '国家或地区'; comment on column CUST_USER.call is '联系电话'; comment on column CUST_USER.address is '居住详细地址'; comment on column CUST_USER.birthday is '出生日期'; comment on column CUST_USER.nation is '民族'; comment on column CUST_USER.reg_residence is '户籍所在行政区域'; comment on column CUST_USER.marriage is '婚姻状况'; comment on column CUST_USER.education is '教育水平'; comment on column CUST_USER.degree is '最高学位'; comment on column CUST_USER.house_class is '住宅类型'; comment on column CUST_USER.house_place is '住所行政区域'; comment on column CUST_USER.local is '是否本地户口'; comment on column CUST_USER.local_years is '本地居住年限'; comment on column CUST_USER.job_class is '就业类别'; comment on column CUST_USER.duty is '职务'; comment on column CUST_USER.title is '职称'; comment on column CUST_USER.work_time is '入职时间'; comment on column CUST_USER.company is '工作单位名称'; comment on column CUST_USER.wkplace is '单位详细地址'; comment on column CUST_USER.industry_years is '本行业从业年限'; comment on column CUST_USER.industry_class is '行业类别'; comment on column CUST_USER.org_name is '所属机构'; comment on column CUST_USER.income is '年收入'; comment on column CUST_USER.manager_id is '管户人员'; comment on column CUST_USER.create_by is '创建者'; comment on column CUST_USER.create_date is '创建时间'; comment on column CUST_USER.update_by is '更新者'; comment on column CUST_USER.update_date is '更新时间'; comment on column CUST_USER.del_flag is '逻辑删除标记（0：显示；1：隐藏）'; comment on column CUST_USER.remarks is '备注信息'; -- Create/Recreate indexes create unique index BIN$mj7vuRyWRDyKbd5a3dJfgA==$0 on CUST_USER (ID) tablespace CMP_DATA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Create/Recreate primary, unique and foreign key constraints alter table CUST_USER add constraint BIN$lJB3LjUIT7iamK984HMYqA==$0 primary key (ID);
