一：备份单张表
学习知识点：
1. 备份单张表的语法
2. 生成日志文件
3. 备份文件和日志文件使用相对路径
通过 exp 备份 st 用户下的 t 表，并将备份文件存放在/home/oracle/backup 目录下，生成的备份文件名字： exp_st_t.dmp，日志文件与备份文件目录相同，日志文件名： exp_st_t.log

——准备环境：创建用户及表

SYS@ORA11GR2>create user st identified by oracle account unlock;

User created.

SYS@ORA11GR2>grant connect,resource to st;

Grant succeeded.

SYS@ORA11GR2>conn st/oracle

Connected.

ST@ORA11GR2>create table t (x int);

Table created.

ST@ORA11GR2>insert into t select 1 as id from dual connect by rownum<=10;

10 rows created.

ST@ORA11GR2>commit;

Commit complete.

——建立目录：（不需要使用目录对象，因为exp/imp是客户端工具）

[oracle@wang ~]$ pwd

/home/oracle

[oracle@wang ~]$

[oracle@wang ~]$ mkdir backup

[oracle@wang ~]$ ls

backup

[oracle@wang ~]$ cd backup/

[oracle@wang backup]$ ls

——执行导出作业：

[oracle@wang backup]$ exp st/oracle file=exp_st_t.dmp tables=t log=exp_st_t.log

（用st用户导出自己schema下的t表）

Export: Release 11.2.0.4.0 - Production on Mon Oct 3 16:09:07 2016

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table T 10 rows exported

Export terminated successfully without warnings.

——验证：

[oracle@wang backup]$ ls

exp_st_t.dmp exp_st_t.log

[oracle@wang backup]$ cat exp_st_t.log

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table T 10 rows exported

Export terminated successfully without warnings.

[oracle@wang backup]$

二：备份多张表
1) 在 st 用户下利用 CTAS 方式，通过 T 表创建拥有任意 5条记录的 HAHA 表

ST@ORA11GR2>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T USERS

ST@ORA11GR2>create table haha as select * from t where rownum<=5;

Table created.

ST@ORA11GR2>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

HAHA USERS

T USERS

2) 导出 st 用户下 T 表和 HAHA 表

[oracle@wang backup]$ exp st/oracle file=/home/oracle/backup/exp_t_haha.dmp tables=t,haha log=/home/oracle/backup/exp_t_haha.log

Export: Release 11.2.0.4.0 - Production on Mon Oct 3 16:19:47 2016

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table T 10 rows exported

. . exporting table HAHA 5 rows exported

Export terminated successfully without warnings.

——验证：

[oracle@wang backup]$ ls

exp_st_t.dmp exp_st_t.log exp_t_haha.dmp exp_t_haha.log

[oracle@wang backup]$

三：备份表中部分数据:T1表中，owner 为 SYS 的数据有如下数量

1）.配置环境：

ST@ORA11GR2>create table t1 as select * from all_objects;

Table created.

ST@ORA11GR2>select count(*) from t1;

COUNT(*)

----------

68319

——查询：

ST@ORA11GR2>select count(*) from t1 where owner='SYS';

COUNT(*)

----------

29952

2)通过 exp 命令只导出 owner 为 SYS 的对象(使用 query 参数)

[oracle@wang backup]$ exp st/oracle file=exp_t1_sys.dmp log=exp_t1_sys.log tables=t1 query=\"where owner\=\'SYS'\'\"

Export: Release 11.2.0.4.0 - Production on Mon Oct 3 16:35:56 2016

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table T1

EXP-00056: ORACLE error 1756 encountered

ORA-01756: quoted string not properly terminated

Export terminated successfully with warnings.

[oracle@wang backup]$

不过我们导出虽然成功了，但是出现了警告，其实这个警告不影响导出数据的，但是为了完美，还是没有警告的好。
出现如下：
EXP-00091: Exporting questionable statistics.
错误大概有两种可能，
一是：客户端环境变量中的字符集与服务器端字符集不一致
二是：导出时只导出部分数据，导致统计信息无法导出，可以使用 statistics=none 来解决此问题。

4）解决：
[oracle@wang backup]$ exp st/oracle file=exp_t1_sys.dmp log=exp_t1_sys.log tables=t1 query=\"where owner\=\'SYS'\'\" statistics=none

Export: Release 11.2.0.4.0 - Production on Mon Oct 3 16:39:45 2016

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table T1

EXP-00056: ORACLE error 1756 encountered

ORA-01756: quoted string not properly terminated

Export terminated successfully with warnings.

[oracle@wang backup]$

四：备份模糊匹配的表

ST@ORA11GR2>select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

HAHA TABLE

T TABLE

T1 TABLE

ST@ORA11GR2>

导出以 T 开头的表：

[oracle@wang backup]$ exp st/oracle file=st.dmp log=st.log tables=t%

Export: Release 11.2.0.4.0 - Production on Mon Oct 3 16:43:31 2016

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table T 10 rows exported

. . exporting table T1 68319 rows exported

Export terminated successfully without warnings.

[oracle@wang backup]$

五：备份 schema（即导出整个schema）

oracle@wang backup]$ exp st/oracle file=st_full.dmp log=st_full.log

（在st用户下执行导出，没有指定要导出的参数，即表示导出用户st的所有表）

Export: Release 11.2.0.4.0 - Production on Mon Oct 3 17:27:38 2016

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user ST

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user ST

About to export ST's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export ST's tables via Conventional Path ...

. . exporting table HAHA 5 rows exported

. . exporting table T 10 rows exported

. . exporting table T1 68319 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

[oracle@wang backup]$

另：也可以用如下方式导出 schema（请自行测试，要注意，以 DBA 角色的用户导出后，也要以拥有 DBA角色的用户导入）：
1) 导出单个用户：
exp system/oracle file=aa.dmp owner=scott （以system即dba角色导出）
2) 导出多个用户：
exp system/oracle file=aa.dmp owner=scott,hr （以system即dba角色导出）

六：备份 database（即导出整个database）

[oracle@wang backup]$ exp system/oracle file=/home/oracle/backup/db_full.dmp log=/home/oracle/backup/db_full.log full=y

执行过程略..............................
——验证：
[oracle@wang backup]$ ls db_full.dmp db_full.log

db_full.dmp db_full.log

七：使用参数文件备份
一般多用于使用转义的情况，比如通过 query 参数备份部分数据时。

[oracle@wang ~]$ vi parfile.lst

userid=st/oracle

file=/home/oracle/backup/exp_t1_sys.dmp

log=/home/oracle/backup/exp_t1_sys.log

tables=t1

query="where owner='SYS'"

~

"parfile.lst" 5L, 132C written

[oracle@wang ~]$ exp parfile=parfile.lst statistics=none

Export: Release 11.2.0.4.0 - Production on Mon Oct 3 17:44:42 2016

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table T1 29952 rows exported

Export terminated successfully without warnings.

[oracle@wang ~]$

通过这个备份命令可以知道，在使用参数文件的同时，也可以将参数写在命令行上，不是说必须要把所有的参数都写在参数文件中，比如也可以将 userid 写在外面等等！！！！！！！！

【移动数据】exp的应用