更改表中列的顺序,当中插入一个列.而不是在后面添加

--更改表中列的顺序,当中插入一个列.而不是在后面添加

第一种方法,进入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#= 27

WHERE 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);