[20140109]sqlldr使用direct=true載入資料的問題.txt
[20140109]sqlldr使用direct=true載入資料的問題.txt
昨天要同步更新一個資料庫的資訊,我僅僅負責取出資料給別人載入,我使用toad帶的save選單可以自動生產sqlldr的匯入引數檔案以及資料.
自己以前從來沒有做過.結果我使用引數direct=true,因為有重複資料存在,導致正常的索引失效.好在表不是太大,對業務影響不大,自己做
一個例子測試看看.
1.建立測試環境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
拿scott.dept表為模板建立測試表depta:
create table scott.depta as select * from dept where deptno=10;
create unique index pk_depta on depta (deptno);
alter table scott.depta add ( constraint pk_depta primary key (deptno) using index pk_depta enable validate);
--建立sqlldr載入檔案,在toad下很簡單,選擇export format為sql loader就ok,生成指令碼如下:
內容如下:
-- SQL Loader Control and Data File created by TOAD
-- Variable length, terminated enclosed data formatting
--
-- The format for executing this file with SQL Loader is:
-- SQLLDR control= Be sure to substitute your
-- version of SQL LOADER and the filename for this file.
--
-- Note: Nested table datatypes are not supported here and
-- will be exported as nulls.
LOAD DATA
INFILE *
BADFILE './c.BAD'
DISCARDFILE './c.DSC'
APPEND INTO TABLE SCOTT.DEPTA
Fields terminated by ";" Optionally enclosed by '"'
(
DEPTNO NULLIF (DEPTNO="NULL"),
DNAME,
LOC
)
BEGINDATA
10;"ACCOUNTING";"NEW YORK"
20;"RESEARCH";"DALLAS"
30;"SALES";"CHICAGO"
40;"OPERATIONS";"BOSTON"
2.使用sqlldr匯入:
$ sqlldr
SQL*Loader: Release 11.2.0.3.0 - Production on Thu Jan 9 11:18:38 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
no_index_errors -- abort load on any index errors (Default FALSE)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.
--direct -- use direct path (Default FALSE)
$ sqlldr scott/xxxx control=c.ctl direct=true
SQL*Loader: Release 12.1.0.1.0 - Production on Thu Jan 9 10:54:57 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
Load completed - logical record count 4.
Table SCOTT.DEPTA:
4 Rows successfully loaded.
Check the log file:
c.log
for more information about the load.
SCOTT@test> select rowid,depta.* from depta ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABEz4AAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AABEz4AAEAAAAIkAAA 10 ACCOUNTING NEW YORK
AABEz4AAEAAAAIkAAB 20 RESEARCH DALLAS
AABEz4AAEAAAAIkAAC 30 SALES CHICAGO
AABEz4AAEAAAAIkAAD 40 OPERATIONS BOSTON
--哦!發現匯入成功!,但是資料的唯一性破壞了.
SCOTT@test> select index_name,STATUS from user_indexes where table_name='DEPTA';
INDEX_NAME STATUS
------------------------------ --------
PK_DEPTA UNUSABLE
--索引失效.導致執行計劃改變.
SCOTT@test> select * from depta where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID 6bxpvjvct2nc5, child number 0
-------------------------------------
select * from depta where deptno=10
Plan hash value: 4292077131
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| DEPTA | 4 | 3 (0)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
SCOTT@test> delete /*+ full(depta) */ from depta where rowid='AABEz4AAEAAAAIkAAA';
delete /*+ full(depta) */ from depta where rowid='AABEz4AAEAAAAIkAAA'
*
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_DEPTA' or partition of such index is in unusable state
SCOTT@test> @hide skip_unusable_indexes
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%skip_unusable_indexes%')
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------- ------------------------------------- ---------------------- ---------------------- ----------------------
skip_unusable_indexes skip unusable indexes if set to TRUE TRUE TRUE TRUE
SCOTT@test> set serveroutput on
SCOTT@test> exec print_table('select * from user_CONSTRAINTS where constraint_name=''PK_DEPTA''');
OWNER : SCOTT
CONSTRAINT_NAME : PK_DEPTA
CONSTRAINT_TYPE : P
TABLE_NAME : DEPTA
SEARCH_CONDITION :
R_OWNER :
R_CONSTRAINT_NAME :
DELETE_RULE :
STATUS : ENABLED
DEFERRABLE : NOT DEFERRABLE
DEFERRED : IMMEDIATE
VALIDATED : VALIDATED
GENERATED : USER NAME
BAD :
RELY :
LAST_CHANGE : 2014-01-09 10:45:43
INDEX_OWNER : SCOTT
INDEX_NAME : PK_DEPTA
INVALID :
VIEW_RELATED :
-----------------
PL/SQL procedure successfully completed.
--約束還是enabled的.我採用的disable約束,再刪除重複記錄.
3.解決方法:
SCOTT@test> alter table depta modify constraint pk_depta disable;
Table altered.
SCOTT@test> delete /*+ full(depta) */ from depta where rowid='AABEz4AAEAAAAIkAAA';
delete /*+ full(depta) */ from depta where rowid='AABEz4AAEAAAAIkAAA'
*
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_DEPTA' or partition of such index is in unusable state
--依舊不行.可能我是先建立索引,再建立約束的原因,這樣索引不會刪除.
SCOTT@test> drop index pk_depta ;
Index dropped.
SCOTT@test> delete from depta where rowid='AABEz4AAEAAAAIkAAA';
1 row deleted.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter table depta modify constraint pk_depta enable;
Table altered.
SCOTT@test> select index_name,STATUS from user_indexes where table_name='DEPTA';
INDEX_NAME STATUS
------------------------------ --------
PK_DEPTA VALID
SCOTT@test> select * from depta where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 6bxpvjvct2nc5, child number 0
-------------------------------------
select * from depta where deptno=10
Plan hash value: 2411761151
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPTA | 1 | 1 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_DEPTA | 1 | 0 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
總結:
1.使用sqlldr確實應該注意這個引數direct=true.
2.這次好在表不是太大,否則後果很嚴重,對生產系統影響不大.總之年關做事還是小心為上.
昨天要同步更新一個資料庫的資訊,我僅僅負責取出資料給別人載入,我使用toad帶的save選單可以自動生產sqlldr的匯入引數檔案以及資料.
自己以前從來沒有做過.結果我使用引數direct=true,因為有重複資料存在,導致正常的索引失效.好在表不是太大,對業務影響不大,自己做
一個例子測試看看.
1.建立測試環境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
拿scott.dept表為模板建立測試表depta:
create table scott.depta as select * from dept where deptno=10;
create unique index pk_depta on depta (deptno);
alter table scott.depta add ( constraint pk_depta primary key (deptno) using index pk_depta enable validate);
--建立sqlldr載入檔案,在toad下很簡單,選擇export format為sql loader就ok,生成指令碼如下:
內容如下:
-- SQL Loader Control and Data File created by TOAD
-- Variable length, terminated enclosed data formatting
--
-- The format for executing this file with SQL Loader is:
-- SQLLDR control=
-- version of SQL LOADER and the filename for this file.
--
-- Note: Nested table datatypes are not supported here and
-- will be exported as nulls.
LOAD DATA
INFILE *
BADFILE './c.BAD'
DISCARDFILE './c.DSC'
APPEND INTO TABLE SCOTT.DEPTA
Fields terminated by ";" Optionally enclosed by '"'
(
DEPTNO NULLIF (DEPTNO="NULL"),
DNAME,
LOC
)
BEGINDATA
10;"ACCOUNTING";"NEW YORK"
20;"RESEARCH";"DALLAS"
30;"SALES";"CHICAGO"
40;"OPERATIONS";"BOSTON"
2.使用sqlldr匯入:
$ sqlldr
SQL*Loader: Release 11.2.0.3.0 - Production on Thu Jan 9 11:18:38 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
no_index_errors -- abort load on any index errors (Default FALSE)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.
--direct -- use direct path (Default FALSE)
$ sqlldr scott/xxxx control=c.ctl direct=true
SQL*Loader: Release 12.1.0.1.0 - Production on Thu Jan 9 10:54:57 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
Load completed - logical record count 4.
Table SCOTT.DEPTA:
4 Rows successfully loaded.
Check the log file:
c.log
for more information about the load.
SCOTT@test> select rowid,depta.* from depta ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABEz4AAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AABEz4AAEAAAAIkAAA 10 ACCOUNTING NEW YORK
AABEz4AAEAAAAIkAAB 20 RESEARCH DALLAS
AABEz4AAEAAAAIkAAC 30 SALES CHICAGO
AABEz4AAEAAAAIkAAD 40 OPERATIONS BOSTON
--哦!發現匯入成功!,但是資料的唯一性破壞了.
SCOTT@test> select index_name,STATUS from user_indexes where table_name='DEPTA';
INDEX_NAME STATUS
------------------------------ --------
PK_DEPTA UNUSABLE
--索引失效.導致執行計劃改變.
SCOTT@test> select * from depta where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID 6bxpvjvct2nc5, child number 0
-------------------------------------
select * from depta where deptno=10
Plan hash value: 4292077131
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| DEPTA | 4 | 3 (0)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
SCOTT@test> delete /*+ full(depta) */ from depta where rowid='AABEz4AAEAAAAIkAAA';
delete /*+ full(depta) */ from depta where rowid='AABEz4AAEAAAAIkAAA'
*
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_DEPTA' or partition of such index is in unusable state
SCOTT@test> @hide skip_unusable_indexes
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%skip_unusable_indexes%')
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------- ------------------------------------- ---------------------- ---------------------- ----------------------
skip_unusable_indexes skip unusable indexes if set to TRUE TRUE TRUE TRUE
SCOTT@test> set serveroutput on
SCOTT@test> exec print_table('select * from user_CONSTRAINTS where constraint_name=''PK_DEPTA''');
OWNER : SCOTT
CONSTRAINT_NAME : PK_DEPTA
CONSTRAINT_TYPE : P
TABLE_NAME : DEPTA
SEARCH_CONDITION :
R_OWNER :
R_CONSTRAINT_NAME :
DELETE_RULE :
STATUS : ENABLED
DEFERRABLE : NOT DEFERRABLE
DEFERRED : IMMEDIATE
VALIDATED : VALIDATED
GENERATED : USER NAME
BAD :
RELY :
LAST_CHANGE : 2014-01-09 10:45:43
INDEX_OWNER : SCOTT
INDEX_NAME : PK_DEPTA
INVALID :
VIEW_RELATED :
-----------------
PL/SQL procedure successfully completed.
--約束還是enabled的.我採用的disable約束,再刪除重複記錄.
3.解決方法:
SCOTT@test> alter table depta modify constraint pk_depta disable;
Table altered.
SCOTT@test> delete /*+ full(depta) */ from depta where rowid='AABEz4AAEAAAAIkAAA';
delete /*+ full(depta) */ from depta where rowid='AABEz4AAEAAAAIkAAA'
*
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_DEPTA' or partition of such index is in unusable state
--依舊不行.可能我是先建立索引,再建立約束的原因,這樣索引不會刪除.
SCOTT@test> drop index pk_depta ;
Index dropped.
SCOTT@test> delete from depta where rowid='AABEz4AAEAAAAIkAAA';
1 row deleted.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter table depta modify constraint pk_depta enable;
Table altered.
SCOTT@test> select index_name,STATUS from user_indexes where table_name='DEPTA';
INDEX_NAME STATUS
------------------------------ --------
PK_DEPTA VALID
SCOTT@test> select * from depta where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 6bxpvjvct2nc5, child number 0
-------------------------------------
select * from depta where deptno=10
Plan hash value: 2411761151
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPTA | 1 | 1 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_DEPTA | 1 | 0 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
總結:
1.使用sqlldr確實應該注意這個引數direct=true.
2.這次好在表不是太大,否則後果很嚴重,對生產系統影響不大.總之年關做事還是小心為上.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1068688/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle資料庫使用sqlldr命令匯入txt資料Oracle資料庫SQL
- sqlldr載入效能問題的排查SQL
- 【sqlldr載入資料】SQL
- [20160910]sqlldr使用問題.txtSQL
- 測試TOM=SQLLDR使用函式載入資料SQL函式
- 使用sqlldr匯入日期格式欄位的問題SQL
- 測試TOM=SQLLDR載入日期資料SQL
- 利用SQLLDR載入包含LOB物件的資料（三）SQL物件
- 利用SQLLDR載入包含LOB物件的資料（二）SQL物件
- 利用SQLLDR載入包含LOB物件的資料（一）SQL物件
- 使用oracle sqlldr匯入文字資料的例子OracleSQL
- sqlldr 載入資料 OGG 是否會同步SQL
- 資料匯入SQLLDRSQL
- sqlldr的問題SQL
- [20140109]顯示執行計劃的問題.txt
- 測試TOM==SQLLDR載入固定格式資料SQL
- [20140426]使用sqlldr匯入.txtSQL
- 生產環境sqlldr載入效能問題及分析之一SQL
- 生產環境sqlldr載入效能問題及分析之二SQL
- 【實驗】【SQL*Loader】使用SQLLDR將資料載入到CLOB欄位SQL
- 使用sqlldr載入外部檔案中的資料到Oracle中(轉)SQLOracle
- sqlldr 匯入資料範例SQL
- 測試TOM=SQLLDR載入內嵌換行符資料SQL
- 動態頁面資料載入不全的問題
- Oracle使用sqlldr load匯入txt出現中文亂碼OracleSQL
- 文字檔案的資料裝載工具sqlldrSQL
- 使用sqlldr匯入文字資料到oracleSQLOracle
- 採用sqlldr定時將文字檔案載入進入資料庫SQL資料庫
- ORACLE 的載入工具SQLLDR應用OracleSQL
- 通過SQLLDR匯入LOB資料SQL
- 用sqlloader(sqlldr)裝載LOB資料SQL
- SQLLDR直接載入能否分批提交?SQL
- sqlldr經常報錯的問題SQL
- 資料庫寫入的問題資料庫
- 用Oracle sqlldr匯入文字檔案TXT 總結OracleSQL
- spring 載入不了jdbc.properties檔案的資料問題SpringJDBC
- 解決direct path read 與 direct path write問題
- sqlldr批量匯入匯出資料測試SQL