一：備份單張表
學習知識點：
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的應用

相關文章