oracle外部表建立以及收集統計資訊以及臨時表

sql> create table trace_log(
 log_text varchar2(4000)
 )
  organization external
  (
  type oracle_loader
  default directory dumpdir
  access parameters
  (
 records delimited by newline
 nobadfile
 nodiscardfile
 nologfile
 )
 location('orcl_ora_1976.trc')
 )
 reject limit unlimited
 /

SQL> alter table trace_log location('orcl_ora_3428.trc');

 

外部表定義分析

       CREATE TABLE tablename

        (

           column1 VARCHAR2(100),

           column2 NUMBER,

           ....

        )

       ORGANIZATION  EXTERNAL

       (

           TYPE  ORACLE_LOADER

           DEFAULT  DIRECTORY "xx"

           ACCESS PARAMETERS

               (

               RECORDS DELIMITED BY  0X'0A'

               SKIP 1

               BADFILE 'bad.txt'

               FIELDS TERMINATED BY ','

               OPTIONALLY ENCLOSED BY '"'

               LRTRIM MISSING FIELD VALUES ARE NULL

               REJECT ROWS WITH ALL NULL FIELDS

               )

           LOCATION

               ("CJ_DIR":'data.txt')

       )REJECT LIMIT UNLIMITED;

 

       外部表定義的幾個重點

a.ORGANIZATIONEXTERNAL關鍵字，必須要有。以表明定義的表為外部表。

b.重要引數外部表的型別

ORACLE_LOADER：定義外部表的預設方式，只能只讀方式實現文字資料的裝載。

ORACLE_DATAPUMP：支援對資料的裝載與解除安裝，資料檔案必須為二進位制dump檔案。可以從外部表提取資料裝載到內部表，也可以從內部表解除安裝資料作為二進位制檔案填充到外部表。

c.DEFAULT DIRECTORY：預設的目錄指明瞭外部檔案所在的路徑

d.LOCATION：定義了外部表的位置

f.ACCESS PARAMETERS：描述如何對外部表進行訪問

      RECORDS關鍵字後定義如何識別資料行  

      DELIMITEDBY'XXX'——換行符，常用newline定義換行，並指明字符集。對於特殊的字元則需要單獨定義，如特殊符號，可以使用OX'十六位值'，例如tab(/t)的十六位是9，則DELIMITEDBY0X'09'； cr(/r)的十六位是d，那麼就是DELIMITEDBY0X'0D'。

      SKIP X ——跳過X行資料，有些檔案中第一行是列名，需要跳過第一行，則使用SKIP 1。

                   

FIELDS關鍵字後定義如何識別欄位，常用的如下：

        FIELDS：TERMINATEDBY'x'——欄位分割符。

        ENCLOSEDBY'x'——欄位引用符，包含在此符號內的資料都當成一個欄位。

        例如一行資料格式如："abc","a""b,""c,"。使用引數TERMINATED BY',' ENCLOSED BY'"'後，系統會讀到兩個欄位，

         第一個欄位的值是abc，第二個欄位值是a"b,"c,。

        LRTRIM ——刪除首尾空白字元。

        MISSING FIELD  VALUES  ARE  NULL——某些欄位空缺值都設為NULL。

 

對於欄位長度和分割符不確定且準備用作外部表檔案，可以使用UltraEdit、Editplus等來進行分析測試，如果檔案較大，則需要考慮將檔案分割成小檔案並從中提取資料進行測試。

       

       外部表對錯誤的處理

           REJECT LIMIT UNLIMITED

               在建立外部表時最後加入LIMIT子句，表示可以允許錯誤的發生個數。預設值為零。設定為UNLIMITED則錯誤不受限制

           BADFILE和NOBADFILE子句

               用於指定將捕獲到的轉換錯誤存放到哪個檔案。如果指定了NOBADFILE則表示忽略轉換期間的錯誤

               如果未指定該引數，則系統自動在源目錄下生成與外部表同名的.BAD檔案

               BADFILE記錄本次操作的結果，下次將會被覆蓋

           LOGFILE和NOLOGFILE子句

               同樣在accessparameters中加入LOGFILE'LOG_FILE.log'子句，則所有Oracle的錯誤資訊放入'LOG_FILE.log'中

              而NOLOGFILE子句則表示不記錄錯誤資訊到log中，如忽略該子句，系統自動在源目錄下生成與外部表同名的.LOG檔案

 

       注意以下幾個常見的問題

           1.外部表經常遇到BUFFER不足的情況，因此儘可能的增大READSIZE

           2.換行符不對產生的問題。在不同的作業系統中換行符的表示方法不一樣，碰到錯誤日誌提示如是換行符問題，可以使用

               UltraEdit開啟，直接看十六進位制

           3.特定行報錯時，檢視帶有"BAD"的日誌檔案,其中儲存了出錯的資料,用記事本開啟看看那裡出錯,是否存在於外部表定義相沖突

 

 

oracle收集統計資訊

dbms_stats.gather_table_stats('TEST','T');

ORACLE 10G以後，自動收集統計資訊，在select * from dba_scheduler_jobs裡面，時間是select * from dba_scheduler_window_groups where window_group_name in (select schedule_name from dba_scheduler_jobs)

create golbal temprary table tmpt1 on commit preserver  rows

as select * from dba_objects where 1=2;

create golbal temprary table tmpt1 on commit delete rows

as select * from dba_objects where 1=2;

----- 檢視當前日誌產生的日誌量

select a.name,b.value,b.sid from v$statname a,v$sesstat b where a.statistic# in (133,134)
and a.statistic#=b.statistic# and b.sid=(select distinct sid from v$mystat);