[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.這次好在表不是太大,否則後果很嚴重,對生產系統影響不大.總之年關做事還是小心為上.