ETL基礎及常用技術,主要內容,ETL基本概念ETL常用邏輯架構ETL實施過程ETL常用技術（shell,oracle,datastage）,ETL基本概念,ETL(Extract-Transform-Load)即數據的抽取、轉換與加載。ETL是從各種原始的業務系統(異構多源)中提取數據，按照預先設計好的規則將抽取到的數據進行轉換，最后將轉換完的數據按計劃增量或全部導人到目標數據庫，成為聯機分析處理、數據挖掘的基礎。ETL為BI/DW的核心和靈魂.ETL就是一個批量數據加工的過程.,ETL常用邏輯架構,數據抽取數據清洗與轉換數據加載,ETL實施過程,數據抽取確定所有數據源來源于哪些源系統，核心系統，信貸，信用卡等定義數據接口對每個源文件及接口的每個字段進行詳細說明數據抽取方法主動抽取還是源系統提供文件，增量還是全量,每日還是每月,ETL實施過程,1.代碼標準化2.數據粒度的轉換3.根據業務規則計算確定數據清洗和轉換規則后細化mapping,編碼實現,數據清洗與轉換數據清洗1.不完整的數據2.錯誤的數據3.重復的數據數據轉換,ETL實施過程,數據加載將數據緩沖區的數據直接加載到數據庫對應的表中，如果是全量采用load方式，如果是增量則根據業務規則merge進數據庫,ETL實施過程,ETL常用技術,SHELL(unix基本操作)SQLPL/SQLPROCDATASTAGE,shell基礎,Linux中有好多種不同的shell，如bsh，csh，同其他語言一樣，可以通過我們使用任意一種文字編輯器，比如vi等來編寫我們的shell程序。程序必須以下面的行開始（必須放在文件的第一行）：#!/bin/sh符號#!用來告訴系統它后面的參數是用來執行該文件的程序。在這個程序中我們使用/bin/sh來執行程序。當編輯好腳本時，如果要執行該腳本，還必須使其可執行。要使腳本可執行：chmod+xfilename然后，可以通過輸入：./filename來執行腳本。,.,shell基礎,Vi（一）、進入vi在系統提示字符(如$、#)下敲入vi檔案名稱，vi可以自動幫你載入所要編輯的文件或是開啟一個新文件（如果該文件不存在或缺少文件名）。進入vi后螢幕左方會出現波浪符號，凡是列首有該符號就代表此列目前是空的。(二）、兩種模式如上所述，vi存在兩種模式：指令模式和輸入模式。在指令模式下輸入的按鍵將做為指令來處理：如輸入a，vi即認為是在當前位置插入字符。而在輸入模式下，vi則把輸入的按鍵當作插入的字符來處理。指令模式切換到輸入模式只需鍵入相應的輸入命令即可（如a,A），而要從輸入模式切換到指令模式，則需在輸入模式下鍵入ESC鍵，如果不曉得現在是處於什麼模式，可以多按幾次ESC，系統如發出嗶嗶聲就表示已處于指令模式下了。,.,shell基礎,Vi由指令模式進入輸入模式的指令：新增(append)a：從光標所在位置後面開始新增資料，光標後的資料隨新增資料向後移動。A：從光標所在列最後面的地方開始新增資料。插入(insert)i：從光標所在位置前面開始插入資料，光標後的資料隨新增資料向後移動。I：從光標所在列的第一個非空白字元前面開始插入資料。開始(open)o：在光標所在列下新增一列并進入輸入模式。O:在光標所在列上方新增一列并進入輸入模式。,.,shell基礎,Vi（三）、基本編輯配合一般鍵盤上的功能鍵，像是方向鍵、Insert、Delete等等，現在你應該已經可以利用vi來編輯文件了。當然vi還提供其他許許多多功能讓文字的處理更為方便。何謂編輯？一般認為是文字的新增、修改以及刪除，甚至包括文字區塊的搬移、復制等等。先這里介紹vi的如何做刪除與修改。刪除與修改文件的命令：x：刪除光標所在字符。dd：刪除光標所在的列。r：修改光標所在字元，r後接著要修正的字符。R：進入取替換狀態，新增文字會覆蓋原先文字，直到按ESC回到指令模式下為止。s：刪除光標所在字元，并進入輸入模式。S：刪除光標所在的列，并進入輸入模式。在指令模式下移動光標的基本指令是h,j,k,l。,.,shell基礎,Vi（四）、退出vi在指令模式下鍵入:q,:q!,:wq或:x(注意:號），就會退出vi。其中:wq和:x是存盤退出，而:q是直接退出，如果文件已有新的變化，vi會提示你保存文件而:q命令也會失效，這時你可以用:w命令保存文件后再用:q退出，或用:wq或:x命令退出，如果你不想保存改變后的文件，你就需要用:q!命令，這個命令將不保存文件而直接退出vi。,shell基礎,注釋#變量在shell編程中，所有的變量都由字符串組成，并且不需要對變量進行聲明。要賦值給一個變量，可以這樣寫：變量名=值取出變量值可以加一個美元符號（$）在變量前面shell腳本中的三類命令1）Unix命令2）管道,重定向和backtick3）流程控制,shell基礎unix命令,在shell腳本中可以使用任意的unix命令，這些命令通常是用來進行文件和文字操作的。常用命令語法及功能:echosometext:將文字內容打印在屏幕上ls:文件列表wclfilewc-wfilewc-cfile:計算文件行數計算文件中的單詞數計算文件中的字符數cpsourcefiledestfile:文件拷貝mvoldnamenewname:重命名文件或移動文件rmfile:刪除文件greppatternfile:在文件內搜索字符串比如：grepsearchstringfile.txtcut-bcolnumfile:指定欲顯示的文件內容范圍，并將它們輸出到標準輸出設備比如：輸出每行第5個到第9個字符cut-b5-9file.txt千萬不要和cat命令混淆，這是兩個完全不同的命令,.,shell基礎unix命令,ftp:遠程傳輸文件ftpip然后輸入用戶名密碼，put，getcatfile.txt:輸出文件內容到標準輸出設備（屏幕）上filesomefile:得到文件類型readvar:提示用戶輸入，并將輸入賦值給變量sortfile.txt:對file.txt文件中的行進行排序uniq:刪除文本文件中出現的行列比如：sortfile.txt|uniqexpr:進行數學運算Example:add2and3expr2+3find:搜索文件比如：根據文件名搜索find.-namefilename-printtee:將數據輸出到標準輸出設備(屏幕)和文件比如：somecommand|teeoutfilebasenamefile:返回不包含路徑的文件名比如：basename/bin/tux將返回tux,.,shell基礎unix命令,dirnamefile:返回文件所在路徑比如：dirname/bin/tux將返回/binheadfile:打印文本文件開頭幾行tailfile:打印文本文件末尾幾行sed:Sed是一個基本的查找替換程序。可以從標準輸入（比如命令管道）讀入文本，并將結果輸出到標準輸出（屏幕）。該命令采用正則表達式（見參考）進行搜索。不要和shell中的通配符相混淆。比如：將linuxfocus替換為LinuxFocus：cattext.file|seds/linuxfocus/LinuxFocus/newtext.fileawk:awk用來從文本文件中提取字段。缺省地，字段分割符是空格，可以使用-F指定其他分割符。catfile.txt|awk-F,print$1,$3這里我們使用，作為字段分割符，同時打印第一個和第三個字段。如果該文件內容如下：AdamBor,34,IndiaKerryMiller,22,USA命令輸出結果為：AdamBor,IndiaKerryMiller,USAtar:將文件或文件夾打包解包，tarcvfa.tarabctarxvfa.tar,shell基礎管道、重定向和backtick命令,管道(|)將一個命令的輸出作為另外一個命令的輸入。grephellofile.txt|wc-l在file.txt中搜索包含有”hello”的行并計算其行數。在這里grep命令的輸出作為wc命令的輸入重定向將命令的結果輸出到文件，而不是標準輸出（屏幕）寫入文件并覆蓋舊文件加到文件的尾部，保留舊文件內容。反短斜線（）使用反短斜線可以將一個命令的輸出作為另外一個命令的一個命令行參數。命令：V_date=date+%F,.,shell基礎流程控制命令(if),ifThen.elifThen.Else.fi大多數情況下，可以使用測試命令來對條件進行測試。比如可以比較字符串、判斷文件是否存在及是否可讀等等通常用來表示條件測試。注意這里的空格很重要。要確保方括號的空格。-fsomefile：判斷是否是一個文件-x/bin/ls：判斷/bin/ls是否存在并有可執行權限-n$var：判斷$var變量是否有值$a=$b：判斷$a和$b是否相等,.,shell基礎流程控制命令(if),字符串比較:string1=string2如果相等則為真string1!=string2如果不等則為真-nstring如果不空則為真-zstring如果為空則為真算術比較:expression1-eqexpression2如果相等則為真expression1-neexpression2如果不等則為真expression1-gtexpression2如果大于則為真expression1-geexpression2大于等于則為真expression1-ltexpression2如果小于則為真expression1-leexpression2小于等于則為真,.,shell基礎流程控制命令(if),文件:-dfile如果為目錄則為真-efile如果存在則為真(在這里要注意的是,由于歷史原因,-e選項并不可移植,所以常用的是-f選項-ffile如果為常規文件則為真-gfile如果設置了組ID則為真-rfile如果文件可讀則為真-sfile如果文件大小不為零則為真-ufile如果設置了用戶ID則為真-wfile如果文件可寫則為真-xfile如果文件可執行則為真,.,shell基礎流程控制命令,1)while.Do.Donewhile-loop將運行直到表達式測試為真。willrunwhiletheexpressionthatwetestforistrue.關鍵字break用來跳出循環。而關鍵字”continue”用來不執行余下的部分而直接跳到下一個循環。2)forvarin.do.Donefor-loop表達式查看一個字符串列表(字符串用空格分隔)然后將其賦給一個變量：3)case.in.).;Esaccase表達式可以用來匹配一個給定的字符串,.,oracle基礎數據庫安裝,Windows環境下:網上下載安裝包點擊安裝全選默認配置即可。其它機器上已有server端，可只安client端。PLSQL軟件是一個優秀的oracle工具，建議安裝unix環境下:建oracle組和用戶在oracle用戶下安裝授予其它用戶執行oracle命令的權限,.,oracle基礎數據庫的連接,Client端連接配置到oracle安裝目錄下搜索tnsnames.ora增加修改相應連接內容ORCL=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cg)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)mdb=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SID=mdb),.,oracle基礎數據庫的連接,Unix環境先設置編輯器exportEDITOR=viselectparameter,valuefromnls_database_parameterswhereparameter=NLS_CHARACTERSET;-顯示數據庫字符集NLS_LANG應與數據庫字符集一致exportNLS_LANG=“SIMPLIFIEDCHINESE.ZHS16GBK”exportNLS_DATE_FORMAT=YYYY-MM-DD-HH24.MI.SS命令行：sqlplususername/passwordserverassysdbaassysopersqlplus/assysdba-管理員進入sqlplusetl/etlmdb-普通用戶進入SQLsetlinesize300;-設置行長度SQLsetpagesize1;-設置頁長度SQLcolnameformata30;-設置列格式SQLselectsysdatefromdual;-運行語句,.,oracle基礎數據庫的連接,Plsql是一款優秀的操作oracle圖形界面軟件Sqlwindow可單條執行sqlCommandwindow可批量執行多條sql,.,oracle基礎表空間,ORACLE數據庫被劃分成稱作為表空間的邏輯區域形成ORACLE數據庫的邏輯結構。一個ORACLE數據庫能夠有一個或多個表空間,而一個表空間則對應著一個或多個物理的數據庫文件。表空間是ORACLE數據庫恢復的最小單位,容納著許多數據庫實體,如表、視圖、索引、聚簇、回退段和臨時段等。每個ORACLE數據庫均有SYSTEM表空間,這是數據庫創建時自動創建的。SYSTEM表空間必須總要保持聯機,因為其包含著數據庫運行所要求的基本信息(關于整個數據庫的數據字典、聯機求助機制、所有回退段、臨時段和自舉段、所有的用戶數據庫實體、其它ORACLE軟件產品要求的表)。一個小型應用的ORACLE數據庫通常僅包括SYSTEM表空間,然而一個稍大型應用的ORACLE數據庫采用多個表空間會對數據庫的使用帶來更大的方便。,.,oracle基礎表空間,建立本地管理表空間CREATETABLESPACEtablespace_nameDATAFILEfile_nameSIZEfile_sizeUNIFORMSIZEextent_size|AUTOALLOCATECREATETABLESPACEDATATBS01_32KDATAFILE/home/mdb/oracle/oradata/mdb/datatbs01_01.dbfSIZE100G,/home/mdb/oracle/oradata/mdb/datatbs01_02.dbfSIZE100G,/home/mdb/oracle/oradata/mdb/datatbs01_03.dbfSIZE100G,EXTENTMANAGEMENTLOCALUNIFORMSIZE32MSEGMENTSPACEMANAGEMENTAUTOblocksize32k,.,oracle基礎表空間,建立UNDO表空間CREATEUNDOTABLESPACEtablespace_nameDATAFILEfile_nameSIZEfile_sizeUNIFORMSIZEextent_size|AUTOALLOCATE建立臨時表空間:存放查詢、排序、分組等生成的臨時數據CREATETEMPORARYTABLESPACEtablespace_nameTEMPFILEfile_nameSIZEfile_sizeUNIFORMSIZEextent_size|AUTOALLOCATE,.,oracle基礎表空間,改變表空間可用性ALTERTABLESPACEtablespace_nameonline|offline改變表空間讀寫狀態ALTERTABLESPACEtablespace_nameREADWRITE|READONLY改變表空間名稱ALTERTABLESPACEtablespace_nameRENAMETOnew_tbs_name設置默認表空間ALTERDATABASEDEFAULTTABLESPACEtablespace_name-數據庫默認表空間ALTERDATABASEDEFAULTTEMPORARYtablespace_name-數據庫默認臨時表空間刪除表空間DROPTABLESPACEtablespace_nameINCLUDINGCONTENTSANDDATAFILES;,.,oracle基礎用戶管理,建立用戶createuserETLidentifiedbydefaulttablespaceETLDATA01temporarytablespaceTEMP1profileDEFAULT;給用戶授權grantconnecttoetl;grantselect_catalog_roletoetl;grantselectanytabletoetl;grantcreatesessiontoetl;grantaltersessiontoetl;grantcreatetabletoetl;grantcreateviewtoetl;grantcreatetypetoetl;grantcreatesynonymtoetl;grantcreatesequencetoetl;grantcreateproceduretoetl;,.,oracle基礎用戶管理,修改口令ALTERUSERuser_nameIDENTIFIEDBYnew_password;修改默認表空間ALTERUSERuser_nameDEFAULTTABLESPACEnew_tablespace_name;鎖定用戶ALTERUSERetlACCOUNTLOCK;解鎖用戶ALTERUSERetlACCOUNTUNLOCK;刪除用戶DROPUSERusernameCASCADE;,.,oracle基礎表建立及管理,普通建表CREATETABLEschema.table_name(column_namedatatypeDEFAULTexpr,)TABLESPACEtablespace_name;使用子查詢建表CREATETABLEschema.table_name(column_name,)TABLESPACEtablespace_nameASsubquery;建臨時表-事務臨時表當前事務內有效CREATEGLOBALTEMPORARYTABLEtable_name(column_name,)ONCOMMITDELETEROWS;-會話臨時表當前會話內有效CREATEGLOBALTEMPORARYTABLEtable_name(column_name,)ONCOMMITPRESERVEROWS;,.,oracle基礎表建立及管理,增加列ALTERTABLEtable_nameADD(columndatatypeDEFAULTexpr);修改列定義ALTERTABLEtable_nameMODIFY(columndatatypeDEFAULTexpr);刪除列ALTERTABLEtable_nameDROP(column);修改列名ALTERTABLEtable_nameRENAMECOLUMNcolumn_nametonew_column_name;增加注釋COMMENTONTABLEtable_nameIStext;COMMENTONCOLUMNtable_name.column_nameIStext;遷移非分區表的表空間ALTERTABLEtable_nameMOVEnew_tablespace_name;-表對應索引需重建,.,oracle基礎表建立及管理,截斷表-快速刪除數據釋放空間保留表結構-是DDL語句不可回退TRUNCATETABLEtable_name;-delete是DML語句可回退刪除表-PURGE是oracle10g新特性表永久刪除DROPTABLEtable_nameCASCADECONSTRAINTSPURGE;從回收站Recyclebin恢復被刪除表FLASHBACKTABLEtable_nameTOBEFOREDROP;清空回收站PURGERECYCLEBIN;查看回收站SELECT*FROMRECYCLEBIN;,.,oracle基礎表建立及管理,表SELECT*FROMUSER_TABLES;對象SELECT*FROMUSER_OBJECTS;表占用的空間selectsum(bytes)fromuser_segmentswheresegment_name=table_name;清空回收站PURGERECYCLEBIN;查看回收站SELECT*FROMRECYCLEBIN;查看表的列SELECT*FROMUSER_TAB_COLUMNS;查看注釋SELECT*FROMUSER_TAB_COMMENTS;SELECT*FROMUSER_COL_COMMENTS;查看建表語句SELECTdbms_metadata.get_ddl(TABLE,)FROMdual;SELECTdbms_metadata.get_ddl(TABLE,upper(tmpcg_ctl_paravalue),upper(etl)FROMdual;,.,oracle基礎索引,建索引CREATEINDEXUNIQUEindex_nameONtable_name(column_name,.)TABLESPACEtablespace_name;-不設表空間則建在用戶默認表空間上維護索引ALTERINDEXindex_nameREBUILDTABLESPACEtablespace_name;-可改變索引表空間顯示索引信息SELECT*FROMUSER_INDEXES;SELECT*FROMUSER_IND_COULMNS;-查找失效的非分區索引SELECT*FROMUSER_INDEXESWHERESTATUS=UNUSABLE;查看建索引語句SELECTdbms_metadata.get_ddl(INDEX,)FROMdual;SELECTdbms_metadata.get_ddl(INDEX,upper(idx_tmpcg_ctl_paravalue),upper(etl)FROMdual;,.,oracle基礎序列,序列（Sequence）是用于生成唯一數字的數據庫對象，自動生成順序遞增的序列號，可提供唯一的主鍵值，事務回滾值不會減少建序列CREATESEQUENCEseq_nameINCREMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUE;MINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE;使用序列INSERTINTOt1(id)VALUES(t1id_seq.NEXTVAL);SELECTt1id_seq.CURRVALFROMdual;刪除序列DROPSEQUENCEseq_name;顯示序列信息SELECT*FROMSEQ;查看建序列語句SELECTdbms_metadata.get_ddl(SEQUENCE,)FROMdual;,.,oracle基礎分區表,ORACLE的分區(PartitioningOption)是一種處理超大型表的技術。分區是一種“分而治之”的技術，通過將大表和索引分成可以管理的小塊，從而避免了對每個表作為一個大的、單獨的對象進行管理，為大量數據提供了可伸縮的性能。分區通過將操作分配給更小的存儲單元，減少了需要進行管理操作的時間，并通過增強的并行處理提高了性能，通過屏蔽故障數據的分區，還增加了可用性。ORACLE的分區表的劃分方法包括：按字段值進行劃分的范圍分區；按字段的HASH函數值進行的劃分HASH分區；先按范圍劃分，再按HASH劃分的復合分區；在ORACLE9i中又增強了按字段值列表進行劃分的列表(Listing)分區方法。管理員可以指定每個分區的存儲屬性，分區在宿主文件系統中的放置情況，這樣便增加了對超大型數據庫的控制粒度(granularity)。分區可以被單獨地刪除、卸出或裝入、備份、恢復，因此減少了需要進行管理操作的時間。還可以為表分區創建單獨的索引分區，從而減少了需要進行索引維護操作的時間。此外，還提供了種類繁多的局部和全局的索引技術。分區操作也可以被并行執行。分區技術還提高了數據的可用性。當部分數據由于故障或其它原因不可用時，其它分區內的數據可不受影響繼續使用。分區對應用是透明的，可以通過標準的SQL語句對分區表進行操作。Oracle的優化器在訪問數據時會分析數據的分區情況，在進行查詢時，那些不包含任何查詢數據的分區將被忽略，從而大大提高系統的性能。總之，Oracle的分區表技術能滿足高性能、高可用性、數據易管理性等綜合需求。,.,oracle基礎分區表設計原則,表的大小對于大表進行分區，將有益于大表操作的性能和大表的數據維護。通常當表的大小超過1.5GB2GB，或對于OLTP系統，表的記錄超過1000萬，都應考慮對表進行分區。數據訪問特性基于表的大部分查詢應用，只訪問表中少量的數據。對于這樣表進行分區，可充分利用分區排除無關數據查詢的特性。數據維護某些表的數據維護，經常按時間段刪除成批的數據，例如按月刪除歷史數據。對于這樣的表需要考慮進行分區，以滿足維護的需要。因為刪除（Delete）大量的數據，對系統開銷很大，有時甚至是不可接受的。只讀數據如果一個表中大部分數據都是只讀數據，通過對表進行分區，可將只讀數據存儲在只讀表空間中，對于數據庫的備份是非常有益的。并行數據操作（ParallelDML）對于經常執行并行操作（如ParallelInsert,ParallelUpdate等）的表應考慮進行分區。表的可用性當對表的部分數據可用性要求很高時，應考慮進行表分區。,.,oracle基礎分區表,范圍分區表創建腳本CREATETABLEHIS_EVT_DCC_FCMAUDI(cm_rec_llinteger,cm_tx_dtvarchar2(8),)PARTITIONBYRANGE(CM_TX_DT)(PARTITIONETL_LOAD_DATE_0508VALUESLESSTHAN(20050901)TABLESPACEetl0_r_jyrq_data_200508,PARTITIONETL_LOAD_DATE_0509VALUESLESSTHAN(20051001)TABLESPACEetl0_r_jyrq_data_200509,PARTITIONETL_LOAD_DATE_0510VALUESLESSTHAN(20051101)TABLESPACEetl0_r_jyrq_data_200510,PARTITIONETL_LOAD_DATE_0511VALUESLESSTHAN(20051201)TABLESPACEetl0_r_jyrq_data_200511,PARTITIONETL_LOAD_DATE_0512VALUESLESSTHAN(20060101)TABLESPACEetl0_r_jyrq_data_200512);CREATEUNIQUEINDEXHIS_EVT_DCC_FCMAUDI_UIDXONHIS_EVT_DCC_FCMAUDI(CM_TX_DTASC,CM_TX_LOG_NOASC,CM_TX_CODASC,CM_EC_FLGASC)LOCAL(PARTITIONETL_LOAD_DATE_0508TABLESPACEetl0_r_jyrq_idx_200508,PARTITIONETL_LOAD_DATE_0509TABLESPACEetl0_r_jyrq_idx_200509,PARTITIONETL_LOAD_DATE_0510TABLESPACEetl0_r_jyrq_idx_200510,PARTITIONETL_LOAD_DATE_0511TABLESPACEetl0_r_jyrq_idx_200511,PARTITIONETL_LOAD_DATE_0512TABLESPACEetl0_r_jyrq_idx_200512,);,.,oracle基礎分區表,HASH分區表創建腳本CREATETABLEPERSONAL_CUSTOMER_INFO（）PARTITIONBYHASH(CI-CUST-NO)PARTITIONS64TABLESPACETS_ODS_DATA;復合分區表創建腳本CREATETABLEFACT_DCC_SAACNACN_PERSONAL(txn_date,sa-acct-no)PARTITIONBYRANGE(時間字段)SUBPARTITIONBYHASH(SA-ACCT-NO)SUBPARTITIONTEMPLATE(SUBPARTITIONs1,SUBPARTITIONs2,SUBPARTITIONs3,);,.,oracle基礎分區表,查詢SELECT*FROMhis_evt_dcc_fcmaudiPARTITION(ETL_LOAD_DATE_0508);清分區數據ALTERTABLEhis_evt_dcc_fcmaudiTRUNCATEPARTITION(ETL_LOAD_DATE_0508);新增分區ALTERTABLEhis_evt_dcc_fcmaudiADDPARTITIONETL_LOAD_DATE_0601VALUESLESSTHAN(20060201)TABLESPACEetl0_r_jyrq_data_200612;刪除分區ALTERTABLEhis_evt_dcc_fcmaudiDROPPARTITIONETL_LOAD_DATE_0508;,.,oracle基礎分區表,分區信息查詢SELECT*FROMUSER_TAB_PARTITIONS;SELECT*FROMUSER_TAB_SUBPARTITIONS;SELECT*FROMUSER_IND_PARTITIONS;加上WHERESTATUS=UNUSABLE可查詢失效的分區索引SELECT*FROMUSER_IND_SUBPARTITIONS;加上WHERESTATUS=UNUSABLE可查詢失效的子分區索引SELECT*FROMUSER_SEGMENTS;,.,oracle基礎sqlldr,用法:SQLLDRkeyword=value,keyword=value,.sqlldr$1control=tb.ctlerrors=10000rows=100000bindsize=8192000readsize=8192000log=tbldr.logbad=tbldr.baddirect=true有效的關鍵字:userid-ORACLE用戶名/口令control-控制文件名log-日志文件名bad-錯誤文件名data-數據文件名discard-廢棄文件名discardmax-允許廢棄的文件的數目(全部默認)skip-要跳過的邏輯記錄的數目(默認0)load-要加載的邏輯記錄的數目(全部默認)errors-允許的錯誤的數目(默認50)rows-常規路徑綁定數組中或直接路徑保存數據間的行數(默認:常規路徑64,所有直接路徑)bindsize-常規路徑綁定數組的大小(以字節計)(默認256000)silent-運行過程中隱藏消息(標題,反饋,錯誤,廢棄,分區)direct-使用直接路徑(默認FALSE)parfile-參數文件:包含參數說明的文件的名稱parallel-執行并行加載(默認FALSE)file-要從以下對象中分配區的文件,.,oracle基礎sqlldr,控制文件LOADDATAINFILEtest.txtINTOTABLEDEPTREPLACEFIELDSTERMINATEDBY,OPTIONALLYENCLOSEDBY(DEPTNO,DNAME,LOC),.,oracle基礎文本數據導出,Oracle沒有提供將數據導出到一個文件的工具。但是我們可以用SQL*Plus的select及format數據來輸出到一個文件：setechooffnewpage0space0pagesize0feedoffheadofftrimspoolonspooloradata.txtselectcol1|,|col2|,|col3fromtab1wherecol2=XYZ;spooloff,.,oracle基礎數據庫備份導入導出,EXP:exphelp=y有三種主要的方式（完全、用戶、表）1、完全：EXPSYSTEM/MANAGERBUFFER=64000FILE=C:FULL.DMPFULL=Y如果要執行完全導出，必須具有特殊的權限2、用戶模式：EXPSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPOWNER=SONIC這樣用戶SONIC的所有對象被輸出到文件中。3、表模式：EXPSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPOWNER=SONICTABLES=(SONIC)這樣用戶SONIC的表SONIC就被導出,.,oracle基礎數據庫備份導入導出,IMP:imphelp=y具有三種模式（完全、用戶、表）1、完全：IMPsys/sys123assysdbaBUFFER=64000FILE=C:FULL.DMPFULL=Y2、用戶模式：IMPSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPFROMUSER=SONICTOUSER=SONIC這樣用戶SONIC的所有對象被導入到文件中。必須指定FROMUSER、TOUSER參數，這樣才能導入數據。3、表模式：impSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPOWNER=SONICTABLES=(SONIC)這樣用戶SONIC的表SONIC就被導入。,.,oracle基礎查看執行計劃,若有PLSQL等windows界面的工具，可點執行計劃菜單查看命令行查看執行計劃SQLexplainplanforsql語句；-并不真正執行，只是生成執行計劃例：SQLexplainplanforselect*frometlcusorgwhereecif_cus_num=12345;已解釋。SQL?/rdbms/admin/utlxplp-查看PLAN_TABLE_OUTPUT-|Id|Operation|Name|Rows|Bytes|Cost|-|0|SELECTSTATEMENT|1|279|3|1|TABLEACCESSBYINDEXROWID|ETLCUSORG|1|279|3|2|INDEXUNIQUESCAN|SYS_C007050|1|2|-,.,oracle基礎常用函數,數值型函數ABS(x)【功能】返回x的絕對值mod(x,y)【功能】返回x除以y的余數power(x,y)【功能】返回x的y次冪round(x,y)【功能】返回四舍五入后的值【參數】x,y，數字型表達式,如果y不為整數則截取y整數部分，如果y0則四舍五入為y位小數，如果y小于0則四舍五入到小數點向左第y位。trunc(x,y)【功能】返回x按精度y截取后的值【參數】x,y，數字型表達式,如果y不為整數則截取y整數部分，如果y0則截取到y位小數，如果y小于0則截取到小數點向左第y位，小數前其它數據用0表示,.,oracle基礎常用函數,字符型函數CONCAT(c1,c2)【功能】連接兩個字符串同|LOWER(c1)【功能】：將字符串全部轉為小寫UPPER(c1)【功能】將字符串全部轉為大寫LENGTH(c1)【功能】返回字符串的長度;【說明】多字節符(漢字、全角符等)，按1個字符計算LENGTHB(c1)【功能】返回字符串的長度;【說明】多字節符(漢字、全角符等)，按2個字符計算LPAD(c1,n,c2)【功能】在字符串c1的左邊用字符串c2填充，直到長度為n時為止RPAD(c1,n,c2)【功能】在字符串c1的右邊用字符串c2填充，直到長度為n時為止,.,oracle基礎常用函數,字符型函數LTRIM(c1,c2)【功能】刪除左邊出現的字符串默認為空格RTRIM(c1,c2)【功能】刪除右邊出現的字符串TRIM(c1)【功能】刪除左邊和右邊出現的空格SUBSTR(c1,n1,n2)【功能】取子字符串【說明】多字節符(漢字、全角符等)，按1個字符計算【參數】在字符表達式c1里，從n1開始取n2個字符;若不指定n2,則從第y個字符直到結束的字串.,.,oracle基礎常用函數,日期函數sysdate【功能】：返回當前日期add_months(d1,n1)【功能】：返回在日期d1基礎上再加n1個月后新的日期。last_day(d1)【功能】：返回日期d1所在月份最后一天的日期。round(d1,c1)【功能】：給出日期d1按期間(參數c1)四舍五入后的期間的第一天日期（與數值四舍五入意思相近）【參數】：d1日期型,c1為字符型(參數)，c1默認為j（即最近0點日期)【參數表】：c1對應的參數表:最近0點日期:取消參數c1或j最近的星期日：day或dy或d最近月初日期：month或mon或mm或rm最近季日期：q最近年初日期：syear或year或yyyy或yyy或yy或y(多個y表示精度)最近世紀初日期：cc或scctrunc(d1,c1)【功能】：返回日期d1所在期間(參數c1)的第一天日期,.,oracle基礎常用函數,轉換函數TO_CHAR(x,c2,C3)【功能】將日期或數據轉換為char數據類型【參數】x是一個date或number數據類型。c2為格式參數c3為NLS設置參數如果x為日期nlsparm=NLS_DATE_LANGUAGE控制返回的月份和日份所使用的語言。如果x為數字nlsparm=NLS_NUMERIC_CHARACTERS用來指定小數位和千分位的分隔符，以及貨幣符號。NLS_NUMERIC_CHARACTERS=dg,NLS_CURRENCY=string,.,oracle基礎常用函數,轉換函數TO_DATE(X,c2,c3)【功能】將字符串X轉化為日期型【參數】c2,c3,字符型，參照to_char()TO_NUMBER(X,c2,c3)【功能】將字符串X轉化為數字型【參數】c2,c3,字符型，參照to_char(),.,oracle基礎常用函數,聚組函數AVG(distinct|allx)【功能】統計數據表選中行x列的平均值。SUM(distinct|allx)【功能】統計數據表選中行x列的合計值。count(*|distinct|allx)【功能】統計數據表選中行x列的合計值。【參數】*表示對滿足條件的所有行統計，不管其是否重復或有空值(NULL)all表示對所有的值統計,默認為alldistinct只對不同的值統計，如果有參數distinct或all，需有空格與x(列)隔開，均忽略空值(NULL)。MAX(distinct|allx)【功能】統計數據表選中行x列的最大值。MIN(distinct|allx)【功能】統計數據表選中行x列的最大值。,.,oracle基礎常用函數,分析函數RANK()dense_rank()【語法】RANK()OVER(query_partition_clauseorder_by_clause)dense_RANK()OVER(query_partition_clauseorder_by_clause)【功能】聚合函數RANK和dense_rank主要的功能是計算一組數值中的排序值。【參數】dense_rank與rank()用法相當，【區別】dence_rank在并列關系是，相關等級不會跳過。rank則跳過rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名。,.,oracle基礎常用函數,其他函數NVL(expr1,expr2)【功能】若expr1為NULL，返回expr2；expr1不為NULL，返回expr1。注意兩者的類型要一致decode(條件,值1,翻譯值1,值2,翻譯值2,.值n,翻譯值n,缺省值)【功能】根據條件返回相應值【參數】c1,c2,.,cn,字符型/數值型/日期型，必須類型相同或null注：值1n不能為條件表達式,這種情況只能用casewhenthenend解決含義解釋：decode(條件,值1,翻譯值1,值2,翻譯值2,.值n,翻譯值n,缺省值)該函數的含義如下：IF條件=值1THENRETURN(翻譯值1)ELSIF條件=值2THENRETURN(翻譯值2).ELSIF條件=值nTHENRETURN(翻譯值n)ELSERETURN(缺省值)ENDIF,.,oracle基礎常用函數,其他函數casewhenthenwhenthenelseend【功能】當：時，返回對應當不為條件表達式時，與函數decode()相同，decode(,)【參數】默認為true(邏輯型)類型要與類型一致，若為字符型，則也要為字符型【注意點】1、以CASE開頭，以END結尾2、分支中WHEN后跟條件，THEN為顯示結果3、ELSE為除此之外