oracle数据导入导出
查看U盘信息
1dmesg | tail输出为:
12345678910[ 2396.400939] usbcore: registered new interface driver usb-storage[ 2396.424785] usbcore: registered new interface driver uas[ 2398.226747] scsi 11:0:0:0: Direct-Access Lenovo USB Hard Drive 0U PQ: 0 ANSI: 6[ 2398.227473] sd 11:0:0:0: Attached scsi generic sg2 type 0[ 2398.227989] sd 11:0:0:0: [sdb] 3907029168 512-byte logical blocks: (2.00 TB/1.82 TiB)[ 2398.228990] sd 11:0:0:0: [sdb] Write Protect is off[ 2398.228996] sd 11:0:0:0: [sdb] Mode Sense: 43 00 00 00[ 2398.230000] sd 11:0:0:0: [sdb] Write cache: disabled, read cache: enabled, doesn't support DPO or FUA[ 2398.243497] sdb: sdb1[ 2398.246711] sd 11:0:0:0: [sdb] Attached SCSI disk看到U盘名称为 sdb1, 创建目录后挂载U盘
1234# 创建文件夹sudo mkdir /mnt/usb# 挂载U盘sudo mount /dev/sdb1 /mnt/usb/拷贝数据
1cp -r /mnt/usb/ usbcopy文件,从 /mnt/usb/ 导出到当前目录下的 usb目录, 如果想要直接拷贝到当前目录,则执行
cp -r /mnt/usb/ .
-r 命令为 递归操作
卸载u盘
1sudo umount /dev/sdb1将数据移入到/home/oracle/oradata
从
.dmp
文件中提取创建数据库的表结构:123456for dmpfile in *.dmp; doecho "-- $dmpfile";echohead -c 100000 $dmpfile | strings | grep 'CREATE TABLE' | sed 's/)).*/));/';echodone > create_tables.sql这里遇到了权限问题,虽然移动了文件但是没有更改所有权,导致了不能运行,这里
更改这个文件夹的所属人, 退到user(管理员)的帐号1sudo chown -R oracle:oracle /home/oracle创建oracle表空间, 这里用了navicat 来创建
进入
docker
镜像内, 然后切换到目录/opt/oracle/oradata 这里是linux在docker内的镜像12docker-compose exec oracle bashcd /opt/oracle/oradata将数据导入到数据库内, 出现数据超出了字段长度限制,需要更改字段的长度 比如 varchar10 => varchar20
12345678910111213141516171819202122232425262728time imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=ADPXX_CQ_jz_boai_20180108_16283.dmp data_only=y log=log-ADPXX_CQ_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=AZJCPH_CQ_jz_boai_20180108_16283.dmp data_only=y log=log-AZJCPH_CQ_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=BDPAL_CQ_jz_boai_20180108_16283.dmp data_only=y log=log-BDPAL_CQ_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=BDSKH_CQ_jz_boai_20180108_16283.dmp data_only=y log=log-BDSKH_CQ_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_CD_VYKTD_jz_boai_20180108_16283.dmp data_only=y log=log-F_CD_VYKTD_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_CH_BSHRZ_jz_boai_20180108_16283.dmp data_only=y log=log-F_CH_BSHRZ_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_CH_CUPMERCHANTINFO_jz_boai_20180108_16283.dmp data_only=y log=log-F_CH_CUPMERCHANTINFO_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_CI_BCAPA_jz_boai_20180108_16283.dmp data_only=y log=log-F_CI_BCAPA_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_CI_BCUAC_jz_boai_20180108_16283.dmp data_only=y log=log-F_CI_BCUAC_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_CM_PJGCS_jz_boai_20180108_16283.dmp data_only=y log=log-F_CM_PJGCS_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_CM_PSHZL_jz_boai_20180108_16283.dmp data_only=y log=log-F_CM_PSHZL_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_DP_ADPAU_jz_boai_20180108_16283.dmp data_only=y log=log-F_DP_ADPAU_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_DP_ANBFH_jz_boai_20180108_16283.dmp data_only=y log=log-F_DP_ANBFH_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_GL_AKMZZ_jz_boai_20180108_16283.dmp data_only=y log=log-F_GL_AKMZZ_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_LN_ADKZH_jz_boai_20180108_16283.dmp data_only=y log=log-F_LN_ADKZH_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_LN_ATXZH_jz_boai_20180108_16283.dmp data_only=y log=log-F_LN_ATXZH_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_LN_GAGE_IMPAWN_jz_boai_20180108_16283.dmp data_only=y log=log-F_LN_GAGE_IMPAWN_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_LN_LN_DUE_jz_boai_20180108_16283.dmp data_only=y log=log-F_LN_LN_DUE_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_LN_LN_PACT_jz_boai_20180108_16283.dmp data_only=y log=log-F_LN_LN_PACT_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_LN_RISK_FIVE_jz_boai_20180108_16283.dmp data_only=y log=log-F_LN_RISK_FIVE_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_NI_BLCMX_jz_boai_20180108_16283.dmp data_only=y log=log-F_NI_BLCMX_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_TX_ADKMX_jz_boai_20180108_16283.dmp data_only=y log=log-F_TX_ADKMX_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_TX_AZSZF_jz_boai_20180108_16283.dmp data_only=y log=log-F_TX_AZSZF_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_TX_BFRBL_jz_boai_20180108_16283.dmp data_only=y log=log-F_TX_BFRBL_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_TX_BGSDJ_jz_boai_20180108_16283.dmp data_only=y log=log-F_TX_BGSDJ_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_TX_BHKDJ_jz_boai_20180108_16283.dmp data_only=y log=log-F_TX_BHKDJ_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_TX_BKJMX_jz_boai_20180108_16283.dmp data_only=y log=log-F_TX_BKJMX_jz_boai_20180108_16283.dmp.logtime imp hpdba/hpst1231@orclpdb1 fromuser=fdm touser=hpdba file=F_TX_CUPMTRANJNLH_jz_boai_20180108_16283.dmp data_only=y log=log-F_TX_CUPMTRANJNLH_jz_boai_20180108_16283.dmp.log