昨天，接到使用者請求，能否將cache庫匯出excel文件、txt文件的資料匯入到oracle庫，之後將oracle匯入的資料匯出dmp檔案，以前操作也做過，時間長了，也忘記了，今天我們重新來做一遍。

本次選用plsql developer工具來，版本：PL/SQL Developer Version 11.0.3.1770

以上描述其實就是將外部表如何匯入到oracle資料庫中，實現該目標有多種方法：

1、使用 PL/SQL Developer工具將execl、txt原始檔匯入到資料庫，最後透過exp匯出的方式匯出外部表（dmp格式）

2、採用oracle自帶的sql load方式匯入外部表，在透過exp方式匯出外部表資料

第一種 PL/SQL Developer：

（Oracle Release 11.2.0.4.0）

excel匯入到oracle

1、編輯好excel資料

2、配置好本地odbc

3、配置plsql中工具，odbc匯入器

4、匯入資料

SQL> select count(*) from it;

COUNT(*)

----------

25

txt檔案匯入到oracle

1、編輯txt檔案

王五,16,安徽省合肥市

王六,16,安徽省合肥市

王七,16,安徽省合肥市

王八,16,安徽省合肥市

王九,16,安徽省合肥市

王十,16,安徽省合肥市

王十一,16,安徽省合肥市

2、使用plsql匯入oracle

工具-文字匯入器

3、設定欄位對應關係

4、匯入資料

SQL> select * from it;

NAME AGE ADDRESS

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

王五 16 安徽省合肥市

王六 16 安徽省合肥市

王七 16 安徽省合肥市

王八 16 安徽省合肥市

王九 16 安徽省合肥市

王十 16 安徽省合肥市

王十一 16 安徽省合肥市

將匯入到資料的表匯出dmp檔案

[oracle@db1 ~]$ exp scott/oracle@orcl file=scott_it.dmp log=scott_it.log tables=it

Export: Release 11.2.0.4.0 - Production on 星期三 8月 17 15:56:00 2022

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 ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table IT 7 rows exported

Export terminated successfully without warnings.

[oracle@db1 ~]$ ls -lrth scott*

-rw-r--r-- 1 oracle oinstall 427 Aug 17 15:56 scott_it.log

-rw-r--r-- 1 oracle oinstall 16K Aug 17 15:56 scott_it.dmp

第二種，使用sqlload方式匯入資料庫

excel資料匯入資料庫：

1、建表

create table it (name varchar2(20),age number(3),adress varchar2(20));

2、建立.ctl檔案

[oracle@db1 ~]$ cat import.ctl

load data

infile 'it12.csv'

append into table "IT"

fields terminated by ','

(

NAME

,

AGE

,

ADDRESS

)

3、上傳 it12.csv檔案（excel檔案儲存是採用另存為帶分隔符的.csv檔案）

4、透過sqlload命令匯入資料庫

sqlldr userid=scott/oracle control=/home/oracle/import.ctl

[oracle@db1 ~]$ sqlldr userid=scott/oracle control=/home/oracle/import.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on 星期三 8月 17 19:25:02 2022

Commit point reached - logical record count 48

資料匯入完成，檢查資料完整性。

SQL> select count(*) from it;

COUNT(*)

----------

48

txt資料匯入資料庫：

1、建表

create table it (name varchar2(20),age number(3),adress varchar2(20));

2、建立.ctl檔案

[oracle@db1 ~]$ cat import.ctl

load data

infile 'data.txt'

append into table "IT"

fields terminated by ','

(

NAME

,

AGE

,

ADDRESS

)

3、文字檔案格式資料

[oracle@db1 ~]$ cat data.txt

王五,16,安徽省合肥市

王六,16,安徽省合肥市

王七,16,安徽省合肥市

王八,16,安徽省合肥市

王九,16,安徽省合肥市

王十,16,安徽省合肥市

王十一,16,安徽省合肥市

4、透過sqlload工具匯入資料到表

[oracle@db1 ~]$ vi import.ctl

[oracle@db1 ~]$ sqlldr userid=scott/oracle control=/home/oracle/import.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on 星期三 8月 17 19:43:23 2022

Commit point reached - logical record count 6

Commit point reached - logical record count 7

5、檢查資料

SQL> select count(*) from it;

COUNT(*)

----------

7

備註：當我們匯入excel或txt檔案時，檔案大小過大，如上百兆甚至更大時，在匯入到目標資料庫時，新建使用者或已有使用者，

新建表空間，設定為預設表空間等，以此來保證匯入的資料表不影響資料庫的正常執行。

當我們匯出表時，使用exp或expdp方式匯出資料庫的某張表就可以了。

總結：外部表匯入是否成功，不管是plsql或是sqlload方式，難點集中在資料表格式，欄位長度及型別設定上，如果上面都設定

沒有問題，如果匯入出現亂碼的情況，此時需要考慮字符集的問題了。此處列入幾個例子，屬於拋轉引玉了，實際情況可能出現

其他問題，針對出現的問題，做具體的解決了。

Yicheng16
22.08.16

-- The End --

【oracle 多種形式的外部表匯入、匯出】實驗

相關文章