通过导入导出更改表字段类型

有你就好

云和恩墨交付战队

768

2页

9次

2020-07-08

免费下载

通过导入导出更改表字段类型

nohup expdp xx/xx@xx parfile=table_ud.par >./expdp_table_ud.log 2>&1 &

DIRECTORY=DUMP

dumpfile=table_ud.dmp

logfile=etable_ud.log

tables=UNDERWRITING.PRPCAUTOPRICE

nohup expdp xx/xx@xx parfile=table_pi.par >./expdp_table_pi.log 2>&1 &

DIRECTORY=DUMP

dumpfile=table_pi.dmp

logfile=etable_pi.log

tables=PICS.CIENDORVALID,PICS.CIINSUREDEMAND

创建测试表

sqlplus pics/xx

CREATE table TMP_CIENDORVALID as select * from PICS.CIENDORVALID where 1=2;

CREATE table TMP_CIINSUREDEMAND as select * from PICS.CIINSUREDEMAND where 1=2;

sqlplus underwriting/xx

CREATE table TMP_PRPCAUTOPRICE as select * from UNDERWRITING.PRPCAUTOPRICE where

1=2;

删字段

underwriting/xx

ALTER TABLE TMP_PRPCAUTOPRICE DROP (REMARK);

pics/pics_sync_2020

ALTER TABLE TMP_CIENDORVALID DROP (PTEXT);

ALTER TABLE TMP_CIINSUREDEMAND DROP (QUESTION);

ALTER TABLE TMP_CIINSUREDEMAND DROP (CHECKCODE);

加字段

underwriting/xx

ALTER TABLE TMP_PRPCAUTOPRICE add (REMARK varchar2(2000));

pics/pics_sync_2020

ALTER TABLE TMP_CIENDORVALID add (PTEXT varchar2(500));

ALTER TABLE TMP_CIINSUREDEMAND add (QUESTION varchar2(4000));

ALTER TABLE TMP_CIINSUREDEMAND add (CHECKCODE varchar2(4000));

导入数据

nohup impdp underwriting/xxx@udwpdb parfile=imp_table_ud.par

>./imp_table_ud.log 2>&1 &

directory=dump

logfile=itable_ud.log

dumpfile=table_ud.dmp

remap_table=UNDERWRITING.PRPCAUTOPRICE:TMP_PRPCAUTOPRICE

content=data_only

nohup impdp pics/xxx@udwpdb parfile=imp_table_pi.par >./imp_table_pi.log 2>&1

directory=dump

logfile=itable_pi.log

dumpfile=table_pi.dmp

remap_table=PICS.CIENDORVALID:TMP_CIENDORVALID,PICS.CIINSUREDEMAND:TMP_CIINSURED

EMAND

content=data_only

备份原表

underwriting/xxx

ALTER TABLE PRPCAUTOPRICE rename to PRPCAUTOPRICE_bak;

pics/xxx

ALTER TABLE CIENDORVALID rename to CIENDORVALID_bak;

ALTER TABLE CIINSUREDEMAND rename to CIINSUREDEMAND_bak;

rename 临时表至原表

underwriting/xx

ALTER TABLE TMP_PRPCAUTOPRICE rename to PRPCAUTOPRICE;

pics/xxx

ALTER TABLE TMP_CIENDORVALID rename to CIENDORVALID;

ALTER TABLE TMP_CIINSUREDEMAND rename to CIINSUREDEMAND;

重建索引

of 2

免费下载

oracle

关注

评论