熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle中如何快速的卸載和加載數據

2013-11-13 22:19:35  來源: Oracle 

  前幾天有個朋友在MSN上問如何能更快速的從數據庫中卸載和加載數據他原來是用sql查詢spool出來效率很低

  這讓我想起DCBA的一個工具ociuldr這個工具是用C寫成的通過OCI和數據庫交互方便而且效率很高
    通過ociuldr轉儲的數據可以很容易用sqlldr加載入數據庫兩者結合使用效果非凡
我簡單測試了一下轉儲很簡單

D:\OraDoc\OracleTools\ociuldr>ociuldr user=scott/tiger@eygle query=select * from emp field=# record=xa file=emptxt

   rows exported at ::
rows exported at ::
output file emptxt closed at rows

  創建好測試表

D:\OraDoc\OracleTools\ociuldr>sqlplus scott/tiger@eygle

  SQL*Plus: Release Production on 星期二 ::

  Copyright (c) Oracle All rights reserved

  連接到:
Oraclei Enterprise Edition Release Production
With the Partitioning option
JServer Release Production

  SQL> create table empt as select * from emp where =;

  表已創建

  編寫簡單的控制文件通過sqlldr輕松加載入數據庫

D:\OraDoc\OracleTools\ociuldr>cat actl
LOAD DATA
INFILE emptxt
INTO TABLE empt
APPEND
FIELDS TERMINATED BY # TRAILING NULLCOLS
(
EMPNO
ENAME CHAR
JOB CHAR
MGR
HIREDATE DATE
SAL
COMM
DEPTNO
)

  D:\OraDoc\OracleTools\ociuldr>sqlldr scott/tiger@eygle control=actl

  SQL*Loader: Release Production on 星期二 ::

  Copyright (c) Oracle All rights reserved

  達到提交點 邏輯記錄計數

  D:\OraDoc\OracleTools\ociuldr>sqlplus scott/tiger@eygle

  SQL*Plus: Release Production on 星期二 ::

  Copyright (c) Oracle All rights reserved

  連接到:
Oraclei Enterprise Edition Release Production
With the Partitioning option
JServer Release Production

  SQL> set linesize
SQL> select * from empt;

  EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK

  已選擇

  SQL>

  dcba說將在下一版增加自動控制文件生成的功能那時候這個工具將會更加方便了

  The End


From:http://tw.wingwit.com/Article/program/Oracle/201311/18783.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.