oracle数据导入导出

Author Avatar
流水 1月 25, 2018

  1. 查看U盘信息

    1
    dmesg | tail

    输出为：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    [ 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

  2. 看到U盘名称为 sdb1, 创建目录后挂载U盘

    1
    2
    3
    4
    # 创建文件夹
    sudo mkdir /mnt/usb
    # 挂载U盘
    sudo mount /dev/sdb1 /mnt/usb/

  3. 拷贝数据

    1
    cp -r /mnt/usb/ usb

    copy文件，从 /mnt/usb/ 导出到当前目录下的 usb目录， 如果想要直接拷贝到当前目录，则执行cp -r /mnt/usb/ .

    -r 命令为 递归操作

  4. 卸载u盘

    1
    sudo umount /dev/sdb1

  5. 将数据移入到/home/oracle/oradata

  6. 从.dmp文件中提取创建数据库的表结构：

    1
    2
    3
    4
    5
    6
    for dmpfile in *.dmp; do
    echo "-- $dmpfile";
    echo
    head -c 100000 $dmpfile | strings | grep 'CREATE TABLE' | sed 's/)).*/));/';
    echo
    done > create_tables.sql

    这里遇到了权限问题，虽然移动了文件但是没有更改所有权，导致了不能运行，这里
    更改这个文件夹的所属人, 退到user(管理员)的帐号

    1
    sudo chown -R oracle:oracle /home/oracle

  7. 创建oracle表空间， 这里用了navicat 来创建

  8. 进入docker镜像内， 然后切换到目录/opt/oracle/oradata 这里是linux在docker内的镜像

    1
    2
    docker-compose exec oracle bash
    cd /opt/oracle/oradata

  9. 将数据导入到数据库内, 出现数据超出了字段长度限制，需要更改字段的长度 比如 varchar10 => varchar20

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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.log
    time 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