导出数据库dmp
查看$ORACLE_HOME/network/admin下的 tnsnames.ora
文件,如下所示:
ORCL_YZJ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.132.2.147)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
于是,执行导出表命令如下,导出成功:
[oracle@yingxiang-testServer1 ~]$ exp imgpf/imgpf@ORCL_YZJ
file=/home/oracle/table_space/USDMP20140428.dmp full=y
一个一个用户导入导出
1) 将数据库TEST完全导出,用户名FUZEAPP 密码FUZEAPP_PWD
导出到D:\daochu.dmp中
exp FUZEAPP/FUZEAPP_PWD@ORCL
file=/data/daochu.dmp full=y
2) 将数据库中system用户与sys用户的表导出
exp FUZEAPP/FUZEAPP_PWD@ORCL
file=/data/daochu.dmp owner=(system,sys)
3) 将数据库中的表table1 、table2导出
exp FUZEAPP/FUZEAPP_PWD@ORCL
file=/data/daochu.dmp tables=(table1,table2)
4) 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST
file=d:\daochu.dmp tables=(table1) query=\" where filed1
like '00%'\"
上面是常用的导出,对于压缩我不太在意,用winzip把dmp文件可以很好的压缩。
不过在上面命令后面 加上 compress=y 就可
Oracle数据库Linux下的导入IMP
和相关篇的EXP相对应的用了如下的导入方法。
[oracle@localhost ~]$ imp
Import: Release 11.2.0.1.0 - Production on Fri Sep 26 16:17:48
2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights
reserved.
Username: FUZEAPP
Password: FUZEAPP_PWD
Connected to: Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
Import data only (yes/no): no >【回车】-----选择默认的意思
Import file: expdat.dmp > tachiai.dmp
IMP-00002: failed to open tachiai.dmp for
read -----这里出了个错误,于是我指定了绝对路径
Import file: expdat.dmp > /opt/oracle/tachiai.dmp
Enter insert buffer size (minimum is 8192)
30720>【回车】-----选择默认的意思
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character
set
import server uses JA16SJIS character set (possible charset
conversion)
List contents of import file only (yes/no): no
>【回车】-----选择默认的意思
Ignore create error due to object existence (yes/no): no
>【回车】-----选择默认的意思
Import grants (yes/no): yes >【回车】-----选择默认的意思
Import table data (yes/no): yes >【回车】-----选择默认的意思
Import entire export file (yes/no): no > 【回车】-----选择默认的意思
Username: FUZEAPP_PWD
Enter table(T) or partition(T:P) names. Null list means all tables
for user
Enter table(T) or partition(T:P) name or . if
done:【回车】-----选择默认的意思