Oracle數據的導入與導出

来源:https://www.cnblogs.com/nearWind/archive/2019/09/14/11518226.html
-Advertisement-
Play Games

本文針對window操作系統與oracle12C的版本。 1.sqlplus執行單個sql文件 1.執行sqlplus登陸命令：sqlplus username/password@host:port/service_name as sysdba （其中普通用戶可以不加後面的 as sysdba） 2 ...


本文針對window操作系統與oracle12C的版本。

1.sqlplus執行單個sql文件

1.執行sqlplus登陸命令：sqlplus username/password@host:port/service_name as sysdba （其中普通用戶可以不加後面的 as sysdba）

2.在sqlplus下執行sql文件命令：@file_path

 

2.sqlplus執行多個sql文件

1.新建一個sql文件，在其添加需執行的sql文件路徑：

@file_path1

@file_path2

。。。。。

2.在sqlplus下執行新建的sql文件即可

 

3.oracle導出與導入工具expdp，impdp

使用expdp和impdp時應該註意的事項：

exp和imp是客戶端工具程式，它們既可以在客戶端使用，也可以在服務端使用。
expdp和impdp是服務端的工具程式，他們只能在oracle服務端使用，不能在客戶端使用。
imp只適用於exp導出的文件，不適用於expdp導出文件；impdp只適用於expdp導出的文件，而不適用於exp導出文件。

1.導出

1.以sysdba身份登陸sqlplus，如命令：sqlplus / as sysdba

2.創建邏輯目錄，該命令不會在操作系統創建真正的目錄，create or replace directory 目錄別名 as '操作系統上目錄的絕對路徑'；

如：create directory xyy as 'D:\cowry\cj'；

3.查看管理理員目錄（同時查看操作系統是否存在，因為oracle並不關心該目錄是否存在，如果不存在，後面會出錯）

select * from dba_directories;

4.賦予需要導出的用戶在指定目錄的操作許可權。命令：grant read,write on directory 目錄別名 to user_name；

5.退出sqlplus，在cmd視窗執行以下命令：

1）導出用戶

　expdp user_name/pwd@host:port/service_name dumpfile=xxx.dmp [logfile=xxx.log] directory=xxx [schemas=xxx] [compression=xxx] [parallel=xxx]

 

2)導出指定表

　expdp user_name/pwd@host:port/service_name dumpfile=xxx.dmp directory=xxx tables=xxx,xxx1,...

 

還有其他導出表空間，整個資料庫等等模式，具體可查相關資料。

dumpfile：導出的數據文件的名稱。

logfile：日誌文件

directory：導出的邏輯目錄，一定要在oracle中創建完成的，並授權用戶讀寫許可權

schemas：使用dblink導出的用戶不是本地的用戶，需要加上schema來確定導出的用戶，類似於exp中的owner，但還有一定的區別。

compression：壓縮轉儲文件。有效的關鍵字值為: ALL, DATA_ONLY, [METADATA_ONLY] 和 NONE。

parallel：並行數量。

tables：指定導出的表。

network_link：源系統的遠程資料庫鏈接的名稱。使用的dblink來遠程導出，需要指定dblink的名稱。

 

2.導入

1）導入用戶

　　impdp user_name/pwd@host:port/service_name dumpfile=xxx.dmp [logfile=xxx.log] directory=xxx [remap_schema=xxx] [parallel=xxx]

 

2）導入表

　　impdp user_name/pwd@host:port/service_name dumpfile=xxx.dmp directory=xxx [remap_schema=xxx:xxx1] [parallel=xxx] tables=xxx,xxx1,...

 

remap_schema：將一個用戶的的數據遷移到另外一個用戶

 

4.oracle使用sqlloader導入批量數據

 

SQL * Loader功能

SQL * Loader將外部文件中的數據載入到Oracle資料庫的表中。

它具有強大的數據解析引擎，對數據文件中的數據格式幾乎沒有限制。您可以使用SQL * Loader執行以下操作：

  • 如果數據文件位於與資料庫不同的系統上，則通過網路載入數據。

  • 在同一個載入會話期間從多個數據文件載入數據。

  • 在同一個載入會話期間將數據載入到多個表中。

  • 指定數據的字元集。

  • 有選擇地載入數據（您可以根據記錄的值載入記錄）。

  • 使用SQL函數在載入數據之前處理數據。

  • 在指定列中生成唯一的順序鍵值。

  • 使用操作系統的文件系統訪問數據文件。

  • 從磁碟，磁帶或命名管道載入數據。

  • 生成複雜的錯誤報告，極大地幫助排除故障。

  • 載入任意複雜的對象關係數據。

  • 使用輔助數據文件來載入LOB和集合。

  • 使用傳統的，直接的路徑或外部表負載。

 

您可以通過兩種方式使用SQL * Loader：帶或不帶控制文件。控制文件控制SQL * Loader的行為以及載入中使用的一個或多個數據文件。使用控制文件可以更好地控制載入操作，這對於更複雜的載入情況可能是理想的。但是對於簡單載入，您可以在不指定控制文件的情況下使用SQL * Loader; 這被稱為SQL * Loader express模式。

sqlloader參數說明，具體可在cmd輸入sqlldr查看

 1 有效的關鍵字:
 2 
 3     userid -- ORACLE 用戶名/口令
 4    control -- 控制文件名
 5        log -- 日誌文件名
 6        bad -- 錯誤文件名
 7       data -- 數據文件名
 8    discard -- 廢棄文件名
 9 discardmax -- 允許廢棄的文件的數目         (全部預設)
10       skip -- 要跳過的邏輯記錄的數目  (預設 0)
11       load -- 要載入的邏輯記錄的數目  (全部預設)
12     errors -- 允許的錯誤的數目         (預設 50)
13       rows -- 常規路徑綁定數組中或直接路徑保存數據間的行數
14                (預設: 常規路徑 64, 所有直接路徑)
15   bindsize -- 常規路徑綁定數組的大小 (以位元組計)  (預設 256000)
16     silent -- 運行過程中隱藏消息 (標題,反饋,錯誤,廢棄,分區)
17     direct -- 使用直接路徑                     (預設 FALSE)
18    parfile -- 參數文件: 包含參數說明的文件的名稱
19   parallel -- 執行並行載入                    (預設 FALSE)
20       file -- 要從以下對象中分配區的文件
21 skip_unusable_indexes -- 不允許/允許使用無用的索引或索引分區  (預設 FALSE)
22 skip_index_maintenance -- 沒有維護索引, 將受到影響的索引標記為無用  (預設 FALSE)
23 commit_discontinued -- 提交載入中斷時已載入的行  (預設 FALSE)
24   readsize -- 讀取緩衝區的大小               (預設 1048576)
25 external_table -- 使用外部表進行載入; NOT_USED, GENERATE_ONLY, EXECUTE
26 columnarrayrows -- 直接路徑列數組的行數  (預設 5000)
27 streamsize -- 直接路徑流緩衝區的大小 (以位元組計)  (預設 256000)
28 multithreading -- 在直接路徑中使用多線程
29  resumable -- 對當前會話啟用或禁用可恢復  (預設 FALSE)
30 resumable_name -- 有助於標識可恢復語句的文本字元串
31 resumable_timeout -- RESUMABLE 的等待時間 (以秒計)  (預設 7200)
32 date_cache -- 日期轉換高速緩存的大小 (以條目計)  (預設 1000)
33 no_index_errors -- 出現任何索引錯誤時中止載入  (預設 FALSE)
34 partition_memory -- 開始溢出的直接路徑分區記憶體限制 (kb)  (預設 0)
35      table -- 用於快速模式載入的表
36 date_format -- 用於快速模式載入的日期格式
37 timestamp_format -- 用於快速模式載入的時間戳格式
38 terminated_by -- 由用於快速模式載入的字元終止
39 enclosed_by -- 由用於快速模式載入的字元封閉
40 optionally_enclosed_by -- (可選) 由用於快速模式載入的字元封閉
41 characterset -- 用於快速模式載入的字元集
42 degree_of_parallelism -- 用於快速模式載入和外部表載入的並行度
43       trim -- 用於快速模式載入和外部表載入的截取類型
44        csv -- 用於快速模式載入的 csv 格式數據文件
45     nullif -- 用於快速模式載入的表級 nullif 子句
46 field_names -- 用於快速模式載入的數據文件第一條記錄欄位名設置
47 dnfs_enable -- 啟用或禁用輸入數據文件 Direct NFS (dNFS) 的選項  (預設 FALSE)
48 dnfs_readbuffers -- Direct NFS (dNFS) 讀緩衝區數  (預設 4)
49 sdf_prefix -- 要附加到每個 LOB 文件和輔助數據文件的開頭的首碼
50       help -- 顯示幫助消息  (預設 FALSE)
51 empty_lobs_are_null -- 將空白 LOB 設置為空值  (預設 FALSE)
52   defaults -- 直接路徑預設值載入; EVALUATE_ONCE, EVALUATE_EVERY_ROW, IGNORE, IGNORE_UNSUPPORTED_EVALUATE_ONCE, IGNORE_UNSUPPORTED_EVALUATE_EVERY_ROW
53 direct_path_lock_wait -- 當前已鎖定時, 等待表訪問許可權  (預設 FALSE)
54 
55 PLEASE NOTE: 命令行參數可以由位置或關鍵字指定
56 。前者的例子是 'sqlldr
57 scott/tiger foo'; 後一種情況的一個示例是 'sqlldr control=foo
58 userid=scott/tiger'。位置指定參數的時間必須早於
59 但不可遲於由關鍵字指定的參數。例如,
60 允許 'sqlldr scott/tiger control=foo logfile=log', 但是
61 不允許 'sqlldr scott/tiger control=foo log', 即使
62 參數 'log' 的位置正確。

 

1.建立控制文件，尾碼名為.ctl，在控制文件加入以下內容：

OPTIONS (SKIP=num,ROWS=num,DIRECT=true,BINDSIZE=num)

LOAD DATA

CHARACTERSET 字元集

INFILE "數據文件路徑" BADFILE "錯誤文件路徑，其文件尾碼為.bad" DISCARDFILE "廢棄文件路徑，其文件尾碼為.dis"

如有多個數據文件繼續添加
INFILE "xxx" BADFILE "xxx" DISCARDFILE "xxx"

......

[操作類型] INTO TABLE table_name
fields terminated by "xxx"

optionally enclosed by "xxx"
trailing nullcols
(col，col1，col2，...)

參數說明：

SKIP：跳過開始的行數，即不讀取的行數。

 

ROWS：對於傳統常規路徑導入的情況，代表一次提交的行數。

 

BINDSIZE：每次提交記錄的緩衝區的最大值（僅適用於傳統常規路徑載入），預設256000 Bytes。通過BINDSIZE的設定，要比預設值和通過參數ROWS計算的緩衝區大小更優先。

即BINDSIZE能夠制約ROWS，如果ROWS提交的數據需要的緩衝區大於BINDSIZE的設定，會以BINDSIZE的設定為準。

 

DIRECT：使用直接路徑(預設FALSE)。

 

CHARACTERSET：通常會出現中文亂碼問題。即數據文件的字元集編碼與oracle字元集編碼不一致導致中文亂碼問題。其值有：ZHS16GBK。。。

 

操作類型：

　　insert     --為預設方式，在數據裝載開始時要求表為空
　　append  --在表中追加新記錄
　　replace  --刪除舊記錄(用 delete from table 語句)，替換成新裝載的記錄
　　truncate --刪除舊記錄(用 truncate table 語句)，替換成新裝載的記錄

 

fields terminated by：每行欄位與欄位之間的分隔符。

 

optionally enclosed by：數據中每個欄位用 "" 框起，比如欄位中有 "," 分隔符時。對於包裹的欄位為中文，可能會出現導不進資料庫，那麼可嘗試把源數據文件編碼格式修改為ASCII。

 

trailing nullcols：如要導入源文件此列內容為空，在導入到資料庫表中，此列內容就是null。

 

2.在cmd視窗執行命令：

　　sqlldr user_name/pwd@service_name control=控制文件路徑 log=日誌文件路徑

 

5.oracle使用UTL_FILE包導出批量數據

文件I/O對於資料庫的開發來說顯得很重要，比如資料庫中的一部分數據來自於磁碟文件，那麼就需要使用I/O介面把數據導入到資料庫中來。在PL/SQL中沒有直接的I/O介面，  

一般在調試程式時可以使用Oracle自帶的DBMS_OUTPUT包的put_line函數（即向屏幕進行I/O操作）即可，但是對於磁碟文件的I/O操作它就無能為力了。

其實Oracle同樣也提供了可以進行文件I/O的實用包-----UTL_FILE包，利用這個實用包提供的函數來實現對磁碟的I/O操作。

 

1.以sysdba身份創建目錄：CREATE OR REPLACE DIRECTORY 目錄別稱 AS '目錄路徑';

 

2.把路徑的讀寫許可權賦予用戶：GRANT READ,WRITE ON DIRECTORY 目錄別稱 TO 用戶名；

 

3.在用戶中創建以下存儲過程：

 1 create or replace PROCEDURE SQL_TO_FILE(P_QUERY IN VARCHAR2,P_DIR IN VARCHAR2,P_FILENAME IN VARCHAR2)
 2 IS
 3   L_OUTPUT UTL_FILE.FILE_TYPE; 
 4   L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
 5   L_COLUMNVALUE VARCHAR2(4000);
 6   L_STATUS INTEGER;
 7   L_COLCNT NUMBER := 0;
 8   L_SEPARATOR VARCHAR2(1);
 9   L_DESCTBL DBMS_SQL.DESC_TAB;
10   P_MAX_LINESIZE NUMBER := 32000;
11 BEGIN
12   L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
13   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
14   DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
15   DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);
16   FOR I IN 1 .. L_COLCNT LOOP
17     UTL_FILE.PUT(L_OUTPUT,L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"');
18     DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);
19     L_SEPARATOR := ',';
20   END LOOP;
21   UTL_FILE.NEW_LINE(L_OUTPUT);
22   L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
23   WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP
24     L_SEPARATOR := '';
25     FOR I IN 1 .. L_COLCNT LOOP
26       DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
27       UTL_FILE.PUT(L_OUTPUT,
28                   L_SEPARATOR || '"' ||
29                   TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '"', '""')) || '"');
30       L_SEPARATOR := ',';
31       END LOOP;
32     UTL_FILE.NEW_LINE(L_OUTPUT);
33   END LOOP;
34   DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
35   UTL_FILE.FCLOSE(L_OUTPUT);
36 EXCEPTION
37   WHEN OTHERS THEN
38     RAISE;
39 END;

 

4.執行上述過程

EXEC SQL_TO_FILE('para','para1','para2');

其中第一個參數為：查詢數據的SQL，第二個為：目錄別稱，第三個為：導出的文件名。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Ubuntu14.04中stopping log initial device creation錯誤或自動掛載錯誤失敗（導致系統無法自動啟動）
    出現問題的原因： 圖一 出現問題的界面： 圖二 按esc，無作用。 圖三 按esc，按i，依然無作用。 圖四 解決方法： （1）按esc，i無用， 在圖2按s成功到登陸界面。 （2）桌面修改為disk，重啟依然需要按s。 （3）gedit /etc/fstab 刪除了添加的自動掛載的命令。 （4）重 ...
  • Dockfile 生成docker鏡像文件大小的比較
    下麵就是我針對docker file同一個文件，按照layer層的個數的多少，分別構建了兩個鏡像的jenkins-master。兩者大小相差300MB。 <1> layer層數太多，沒有將命令合併為一條命令 <2> layer層數很少，將全部命令合併為一條命令 然後，分別執行構建命令 root >> ...
  • teamviewer遠程式控制製程序免費版百度雲下載
    TeamViewer是一個遠程共用桌面軟體，使遠程傳輸變得簡單快速，遠程訪問安全可靠，能在任何防火牆後臺進行遠程式控制制。只需用戶在兩台電腦上同時運行這個軟體就可以開始工作。使用時關閉殺毒軟體，防止誤報被禁用！ 下載地址： 鏈接：https://pan.baidu.com/s/1mZtmg8VBzDp ...
  • windows下捕獲本地迴環網路中的報文RawCap
    一、下載地址： 官網地址：https://www.netresec.com/?page=RawCap 百度雲：鏈接：https://pan.baidu.com/s/1mWCOTRF5XicuJitBAVQH7g 提取碼：03lh 二、使用方法： cmd命令行內執行RawCap.exe如下： D:\r ...
  • 內網穿透工具：小米球的應用
    一、下載地址： 小米球官網：http://ngrok.ciqiuwl.cn/ windows上使用小米球內網穿透軟體： 鏈接：https://pan.baidu.com/s/1VE6uDLowJnCb9dRg7iHKJw 提取碼：49ln 二、使用方法： 下載解壓後，在cmd命令行下輸入： ngro ...
  • Docker的學習1
    Docker是開源的應用容器引擎。可以理解為輕量級的虛擬機，又可以理解為開了掛的chroot。 官方解釋為docker是一個開源的項目，可以用來將任何應用以輕量級容器的形式，打包，發佈和運行。 docker架構： docker run images_name 運行容器 docker images 查 ...
  • 線程同步
    [TOC] 1. 線程同步概述 線程同步定義 線程同步，指的是控制多線程間的相對執行順序，從而線上程間正確、有序地共用數據，以下為線程同步常見使用場合。 多線程執行的任務在順序上存在依賴關係 線程間共用數據只能同時被一個線程使用 線程同步方法 在實際項目中，經常使用的線程同步方法主要分為三種： 互斥 ...
  • d-堆
    二叉堆因為實現簡單，因此在需要優先隊列的時候幾乎總是使用二叉堆。d 堆是二叉堆的簡單推廣，它恰像一個二叉堆，只是所有的節點都有d個兒子（因此，二叉堆又叫2 堆）。下圖表示的是一個3 堆。註意，d 堆要比二叉堆淺得多，它將Insert操作的運行時間改進為。然而，對於大的d，DeleteMin操作費時得 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類