Oracle导入导出步骤

Oracle导入导出步骤

导出

查看字符集

查看下字符集,需要在导出和导入的时候使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 切换到oracle用户
[oracle@MCDB bin]$ su - oracle
# 连接到Oracle数据库
[oracle@MCDB bin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 18 18:18:06 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.
# 使用用户登录
SQL> conn mgplat/mgplat
Connected.
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

查看数据库实例

用作下面导出命令中的instanceid

1
2
3
4
5
SQL>  select name from v$database;

NAME
---------
JNMCDB

(重要)设置环境

1
2
3
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=JNMCDB
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

执行导出命令

1
[oracle@MCDB bin]$ exp username/password@serverip:serverport/instanceid file=/home/oracle/temp.dmp owner=username

导入

创建表空间和用户

需要看导出文件中的表空间,常规会创建一个数空间和一个临时表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@MCDB ~]#su - oracle
[oracle@MCDB ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 18 16:18:31 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba;
Connected.
# 创建临时表空间
SQL> create temporary tablespace USR_XYD_DATA_TEMP tempfile '/home/oracle/app/oradata/jnmcdb/usr_xyd_data_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
# 创建表空间
SQL> create tablespace USR_XYD_DATA logging datafile '/home/oracle/app/oradata/jnmcdb/user_xyd_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
# 创建用户(注意换成对应的表空间和临时表空间)
SQL> create user XYD identified by xydtech default tablespace USR_XYD_DATA temporary tablespace USR_XYD_DATA_TEMP ;
# 赋权限(这里根据实际需求来,为了简单我直接给了DBA)
SQL> grant dba to XYD;

(重要)设置字符集、ORACLE_HOME、ORACLE_SID

上面导出的时候会有对应的字符集,如果只有dmp文件没法连接导出的数据时,可通过下面的命令查看字符集这个命令会输出一个数字,拿着这个数字(这里是0345),放在下面的SQL里面执行下,字符集就出来了,这里的字符集是ZHS16GBK

1
2
[root@MCDB oracle]# cat XYD.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
0354

1
2
SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;
  ZHS16GBK

设置环境

1
2
3
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
export ORACLE_SID=JNMCDB
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

导入命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[oracle@MCDB ~]$ imp username/password@serverip:serverport/instanceid file=/home/oracle/XYD.dmp full=y
Import: Release 11.2.0.4.0 - Production on Fri Oct 18 16:23:34 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing XYD's objects into XYD
. . importing table "AA" 198 rows imported
. . importing table "BANKLOG_BASEINFO" 11 rows imported
. . importing table "BANKLOG_FLOWLIST" 280 rows imported
. . importing table "BB" 0 rows imported
. . importing table "BUSIFL_DOCLIST" 7 rows imported
. . importing table "BUSIFL_DOCREG" 14 rows imported
. . importing table "BUSIFL_HISTORYTASK" 8 rows imported
. . importing table "BUSIFL_INSTANCE" 6 rows imported
.....
0%