《ETL基础及常用技术培训.pptx》由会员分享，可在线阅读，更多相关《ETL基础及常用技术培训.pptx（86页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1主要内容nETL基本概念nETL常用逻辑架构nETL实施过程nETL常用技术（shell,oracle,datastage）第1页/共86页2ETL基本概念 ET L(Extract-Transform-Load)即数据的抽取、转换与加载。ETL是从各种原始的业务系统(异构多源)中提取数据，按照预先设计好的规则将抽取到的数据进行转换，最后将转换完的数据按计划增量或全部导人到目标数据库，成为联机分析处理、数据挖掘的基础。ETL为BI/DW的核心和灵魂.ETL就是一个批量数据加工的过程.第2页/共86页3ETL常用逻辑架构第3页/共86页4n数据抽取n数据清洗与转换n数据加载 ETL实施过程第4
2、页/共86页5数据抽取n确定所有数据源来源于哪些源系统，核心系统，信贷，信用卡等n定义数据接口对每个源文件及接口的每个字段进行详细说明n数据抽取方法主动抽取还是源系统提供文件，增量还是全量,每日还是每月ETL实施过程第5页/共86页61.代码标准化2.数据粒度的转换3.根据业务规则计算确定数据清洗和转换规则后细化mapping,编码实现数据清洗与转换n数据清洗 1.不完整的数据 2.错误的数据 3.重复的数据 n数据转换ETL实施过程第6页/共86页7数据加载将数据缓冲区的数据直接加载到数据库对应的表中，如果是全量采用load方式，如果是增量则根据业务规则merge进数据库 ETL实施过程第7
3、页/共86页8ETL常用技术SHELL(unix基本操作)SQLPL/SQLPROCDATASTAGE第8页/共86页9shell基础Linux中有好多种不同的shell，如bsh，csh，同其他语言一样，可以通过我们使用任意一种文字编辑器，比如vi等来编写我们的shell程序。程序必须以下面的行开始（必须放在文件的第一行）：#!/bin/sh符号#!用来告诉系统它后面的参数是用来执行该文件的程序。在这个程序中我们使用/bin/sh来执行程序。当编辑好脚本时，如果要执行该脚本，还必须使其可执行。要使脚本可执行：chmod+xfilename然后，可以通过输入：./filename来执行脚本。第
4、9页/共86页10shell基础Vi（一）、进入vi在系统提示字符(如$、#)下敲入vi档案名称，vi可以自动帮你载入所要编辑的文件或是开启一个新文件（如果该文件不存在或缺少文件名）。进入vi后萤幕左方会出现波浪符号，凡是列首有该符号就代表此列目前是空的。(二）、两种模式如上所述，vi存在两种模式：指令模式和输入模式。在指令模式下输入的按键将做为指令来处理：如输入a，vi即认为是在当前位置插入字符。而在输入模式下，vi则把输入的按键当作插入的字符来处理。指令模式切换到输入模式只需键入相应的输入命令即可（如a,A），而要从输入模式切换到指令模式，则需在输入模式下键入ESC键，如果不晓得现在是处於
5、什麽模式，可以多按几次ESC，系统如发出哔哔声就表示已处于指令模式下了。第10页/共86页11shell基础Vi由指令模式进入输入模式的指令：新增(append)a：从光标所在位置後面开始新增资料，光标後的资料随新增资料向後移动。A：从光标所在列最後面的地方开始新增资料。插入(insert)i：从光标所在位置前面开始插入资料，光标後的资料随新增资料向後移动。I：从光标所在列的第一个非空白字元前面开始插入资料。开始(open)o：在光标所在列下新增一列并进入输入模式。O:在光标所在列上方新增一列并进入输入模式。第11页/共86页12shell基础Vi（三）、基本编辑配合一般键盘上的功能键，像是方
6、向键、Insert、Delete等等，现在你应该已经可以利用vi来编辑文件了。当然vi还提供其他许许多多功能让文字的处理更为方便。何谓编辑？一般认为是文字的新增、修改以及删除，甚至包括文字区块的搬移、复制等等。先这里介绍vi的如何做删除与修改。删除与修改文件的命令：x：删除光标所在字符。dd：删除光标所在的列。r：修改光标所在字元，r後接著要修正的字符。R：进入取替换状态，新增文字会覆盖原先文字，直到按ESC回到指令模式下为止。s：删除光标所在字元，并进入输入模式。S：删除光标所在的列，并进入输入模式。在指令模式下移动光标的基本指令是h,j,k,l。第12页/共86页13shell基础Vi（四
7、）、退出vi在指令模式下键入:q,:q!,:wq或:x(注意:号），就会退出vi。其中:wq和:x是存盘退出，而:q是直接退出，如果文件已有新的变化，vi会提示你保存文件而:q命令也会失效，这时你可以用:w命令保存文件后再用:q退出，或用:wq或:x命令退出，如果你不想保存改变后的文件，你就需要用:q!命令，这个命令将不保存文件而直接退出vi。第13页/共86页14shell基础注释#变量在shell编程中，所有的变量都由字符串组成，并且不需要对变量进行声明。要赋值给一个变量，可以这样写：变量名=值取出变量值可以加一个美元符号（$）在变量前面shell脚本中的三类命令1）Unix命令2）管道,
8、重定向和backtick3）流程控制第14页/共86页15shell基础unix命令在shell脚本中可以使用任意的unix命令，这些命令通常是用来进行文件和文字操作的。常用命令语法及功能:echosometext:将文字内容打印在屏幕上ls:文件列表wclfilewc-wfilewc-cfile:计算文件行数计算文件中的单词数计算文件中的字符数cpsourcefiledestfile:文件拷贝mvoldnamenewname:重命名文件或移动文件rmfile:删除文件greppatternfile:在文件内搜索字符串比如：grepsearchstringfile.txtcut-bcolnum
9、file:指定欲显示的文件内容范围，并将它们输出到标准输出设备比如：输出每行第5个到第9个字符cut-b5-9file.txt千万不要和cat命令混淆，这是两个完全不同的命令第15页/共86页16shell基础unix命令ftp:远程传输文件ftpip然后输入用户名密码，put，getcatfile.txt:输出文件内容到标准输出设备（屏幕）上filesomefile:得到文件类型readvar:提示用户输入，并将输入赋值给变量sortfile.txt:对file.txt文件中的行进行排序uniq:删除文本文件中出现的行列比如：sortfile.txt|uniqexpr:进行数学运算Examp
10、le:add2and3expr2+3find:搜索文件比如：根据文件名搜索find.-namefilename-printtee:将数据输出到标准输出设备(屏幕)和文件比如：somecommand|teeoutfilebasenamefile:返回不包含路径的文件名比如：basename/bin/tux将返回tux第16页/共86页17shell基础unix命令dirnamefile:返回文件所在路径比如：dirname/bin/tux将返回/binheadfile:打印文本文件开头几行tailfile:打印文本文件末尾几行sed:Sed是一个基本的查找替换程序。可以从标准输入（比如命令管道）
11、读入文本，并将结果输出到标准输出（屏幕）。该命令采用正则表达式（见参考）进行搜索。不要和shell中的通配符相混淆。比如：将linuxfocus替换为LinuxFocus：cattext.file|seds/linuxfocus/LinuxFocus/newtext.fileawk:awk用来从文本文件中提取字段。缺省地，字段分割符是空格，可以使用-F指定其他分割符。catfile.txt|awk-F,print$1,$3这里我们使用，作为字段分割符，同时打印第一个和第三个字段。如果该文件内容如下：AdamBor,34,IndiaKerryMiller,22,USA 命令输出结果为：AdamB
12、or,IndiaKerryMiller,USAtar:将文件或文件夹打包解包，tarcvfa.tarabctarxvfa.tar第17页/共86页18shell基础管道、重定向和 backtick命令管道(|)将一个命令的输出作为另外一个命令的输入。grephellofile.txt|wc-l在file.txt中搜索包含有”hello”的行并计算其行数。在这里grep命令的输出作为wc命令的输入重定向将命令的结果输出到文件，而不是标准输出（屏幕）写入文件并覆盖旧文件加到文件的尾部，保留旧文件内容。反短斜线（）使用反短斜线可以将一个命令的输出作为另外一个命令的一个命令行参数。命令：V_date=
13、date+%F第18页/共86页19shell基础流程控制命令(if)ifThen.elifThen.Else.fi大多数情况下，可以使用测试命令来对条件进行测试。比如可以比较字符串、判断文件是否存在及是否可读等等通常用来表示条件测试。注意这里的空格很重要。要确保方括号的空格。-fsomefile：判断是否是一个文件-x/bin/ls：判断/bin/ls是否存在并有可执行权限-n$var：判断$var变量是否有值$a=$b：判断$a和$b是否相等第19页/共86页20shell基础流程控制命令(if)字符串比较:string1=string2如果相等则为真string1!=string2如果不
14、等则为真-nstring如果不空则为真-zstring如果为空则为真算术比较:expression1-eqexpression2如果相等则为真expression1-neexpression2如果不等则为真expression1-gtexpression2如果大于则为真expression1-geexpression2大于等于则为真expression1-ltexpression2如果小于则为真expression1-leexpression2小于等于则为真第20页/共86页21shell基础流程控制命令(if)文件:-dfile如果为目录则为真-efile如果存在则为真(在这里要注意的是,由
15、于历史原因,-e选项并不可移植,所以常用的是-f选项-ffile如果为常规文件则为真-gfile如果设置了组ID则为真-rfile如果文件可读则为真-sfile如果文件大小不为零则为真-ufile如果设置了用户ID则为真-wfile如果文件可写则为真-xfile如果文件可执行则为真第21页/共86页22shell基础流程控制命令1)while.Do.Donewhile-loop将运行直到表达式测试为真。willrunwhiletheexpressionthatwetestforistrue.关键字break用来跳出循环。而关键字”continue”用来不执行余下的部分而直接跳到下一个循环。2)
16、forvarin.do.Donefor-loop表达式查看一个字符串列表(字符串用空格分隔)然后将其赋给一个变量：3)case.in.).;Esaccase表达式可以用来匹配一个给定的字符串第22页/共86页23oracle基础数据库安装Windows环境下:网上下载安装包点击安装全选默认配置即可。其它机器上已有server端，可只安client端。PLSQL软件是一个优秀的oracle工具，建议安装unix环境下:建oracle组和用户在oracle用户下安装授予其它用户执行oracle命令的权限第23页/共86页24oracle基础数据库的连接数据库的连接Client端连接配置到oracl
17、e安装目录下搜索tnsnames.ora增加修改相应连接内容ORCL=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cg)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)mdb=(DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SID=mdb)第24页/共86页25oracle基础数据库的连接数据库的连接Unix环境先设置编辑器exportEDITOR=viselectparameter,valuefromnls_database_para
18、meterswhereparameter=NLS_CHARACTERSET;-显示数据库字符集NLS_LANG应与数据库字符集一致exportNLS_LANG=“SIMPLIFIEDCHINESE.ZHS16GBK”命令行：sqlplususername/passwordserverassysdbaassysopersqlplus/assysdba-管理员进入sqlplusetl/etlmdb-普通用户进入SQLsetlinesize300;-设置行长度SQLsetpagesize1;-设置页长度SQLcolnameformata30;-设置列格式SQLselectsysdatefromdua
19、l;-运行语句第25页/共86页26oracle基础数据库的连接数据库的连接nPlsql是一款优秀的操作oracle图形界面软件Sql window 可单条执行可单条执行sqlCommand window 可批量执行多条可批量执行多条sql第26页/共86页27oracle基础表空间表空间ORACLE数据库被划分成称作为表空间的逻辑区域形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。每个ORACLE数据库均有S
20、YSTEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便。第27页/共86页28oracle基础表空间表空间建立本地管理表空间CREATETABLESPACEtablespace_nameDATAFILEfile_nameSIZEfile_sizeUNIFORMS
21、IZEextent_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第
22、28页/共86页29oracle基础表空间建立UNDO表空间CREATEUNDOTABLESPACEtablespace_nameDATAFILEfile_nameSIZEfile_sizeUNIFORMSIZEextent_size|AUTOALLOCATE建立临时表空间:存放查询、排序、分组等生成的临时数据CREATETEMPORARYTABLESPACEtablespace_nameTEMPFILEfile_nameSIZEfile_sizeUNIFORMSIZEextent_size|AUTOALLOCATE第29页/共86页30oracle基础表空间改变表空间可用性ALTERTABL
23、ESPACEtablespace_nameonline|offline改变表空间读写状态ALTERTABLESPACEtablespace_nameREADWRITE|READONLY改变表空间名称ALTERTABLESPACEtablespace_nameRENAMETOnew_tbs_name设置默认表空间ALTERDATABASEDEFAULTTABLESPACEtablespace_name-数据库默认表空间ALTERDATABASEDEFAULTTEMPORARYtablespace_name-数据库默认临时表空间删除表空间DROPTABLESPACEtablespace_nameI
24、NCLUDINGCONTENTSANDDATAFILES;第30页/共86页31oracle基础用户管理建立用户createuserETLidentifiedbydefaulttablespaceETLDATA01temporarytablespaceTEMP1profileDEFAULT;给用户授权grantconnecttoetl;grantselect_catalog_roletoetl;grantselectanytabletoetl;grantcreatesessiontoetl;grantaltersessiontoetl;grantcreatetabletoetl;grantcr
25、eateviewtoetl;grantcreatetypetoetl;grantcreatesynonymtoetl;grantcreatesequencetoetl;grantcreateproceduretoetl;第31页/共86页32oracle基础用户管理修改口令ALTERUSERuser_nameIDENTIFIEDBYnew_password;修改默认表空间ALTERUSERuser_nameDEFAULTTABLESPACEnew_tablespace_name;锁定用户ALTERUSERetlACCOUNTLOCK;解锁用户ALTERUSERetlACCOUNTUNLOCK;
26、删除用户DROPUSERusernameCASCADE;第32页/共86页33oracle基础表建立及管理普通建表CREATETABLEschema.table_name(column_namedatatypeDEFAULTexpr,)TABLESPACEtablespace_name;使用子查询建表CREATETABLEschema.table_name(column_name,)TABLESPACEtablespace_nameASsubquery;建临时表-事务临时表当前事务内有效CREATEGLOBALTEMPORARYTABLEtable_name(column_name,)ONCO
27、MMITDELETEROWS;-会话临时表当前会话内有效CREATEGLOBALTEMPORARYTABLEtable_name(column_name,)ONCOMMITPRESERVEROWS;第33页/共86页34oracle基础表建立及管理增加列ALTERTABLEtable_nameADD(columndatatypeDEFAULTexpr);修改列定义ALTERTABLEtable_nameMODIFY(columndatatypeDEFAULTexpr);删除列ALTERTABLEtable_nameDROP(column);修改列名ALTERTABLEtable_nameREN
28、AMECOLUMNcolumn_nametonew_column_name;增加注释COMMENTONTABLEtable_nameIStext;COMMENTONCOLUMNtable_name.column_nameIStext;迁移非分区表的表空间ALTERTABLEtable_nameMOVEnew_tablespace_name;-表对应索引需重建第34页/共86页35oracle基础表建立及管理截断表-快速删除数据释放空间保留表结构-是DDL语句不可回退TRUNCATETABLEtable_name;-delete是DML语句可回退删除表-PURGE是oracle10g新特性表永久
29、删除DROPTABLEtable_nameCASCADECONSTRAINTSPURGE;从回收站Recyclebin恢复被删除表FLASHBACKTABLEtable_nameTOBEFOREDROP;清空回收站PURGERECYCLEBIN;查看回收站SELECT*FROMRECYCLEBIN;第35页/共86页36oracle基础表建立及管理表SELECT*FROMUSER_TABLES;对象SELECT*FROMUSER_OBJECTS;表占用的空间selectsum(bytes)fromuser_segmentswheresegment_name=table_name;清空回收站PU
30、RGERECYCLEBIN;查看回收站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;第36页/共86页37oracle基础索引建索引CREATE IND
31、EX UNIQUE index_name ON table_name(column_name,.)TABLESPACE tablespace_name;-不设表空间则建在用户默认表空间上维护索引ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;-可改变索引表空间显示索引信息SELECT*FROMUSER_INDEXES;SELECT*FROMUSER_IND_COULMNS;-查找失效的非分区索引SELECT*FROMUSER_INDEXESWHERESTATUS=UNUSABLE;查看建索引语句SELECTdbms_metad
32、ata.get_ddl(INDEX,)FROMdual;SELECTdbms_metadata.get_ddl(INDEX,upper(idx_tmpcg_ctl_paravalue),upper(etl)FROMdual;第37页/共86页38oracle基础序列序列（Sequence）是用于生成唯一数字的数据库对象，自动生成顺序递增的序列号，可提供唯一的主键值，事务回滚值不会减少建序列CREATE SEQUENCE seq_name INCREMENT BY nSTART WITH n MAXVALUE n|NOMAXVALUE;MINVALUE n|NOMINVALUECYCLE|NOC
33、YCLECACHEn|NOCACHE;使用序列INSERT INTO t1(id)VALUES(t1id_seq.NEXTVAL);SELECTt1id_seq.CURRVALFROMdual;删除序列DROPSEQUENCEseq_name;显示序列信息SELECT*FROMSEQ;查看建序列语句SELECTdbms_metadata.get_ddl(SEQUENCE,)FROMdual;第38页/共86页39oracle基础分区表ORACLE的分区(PartitioningOption)是一种处理超大型表的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对
34、每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。ORACLE的分区表的划分方法包括：按字段值进行划分的范围分区；按字段的HASH函数值进行的划分HASH分区；先按范围划分，再按HASH划分的复合分区；在ORACLE9i中又增强了按字段值列表进行划分的列表(Listing)分区方法。管理员可以指定每个分区的存储属性，分区在宿主文件系统中的放置情况，这样便增加了对超大型数据库的控制粒度(granularity)。分区可以被单独地删除、卸出或
35、装入、备份、恢复，因此减少了需要进行管理操作的时间。还可以为表分区创建单独的索引分区，从而减少了需要进行索引维护操作的时间。此外，还提供了种类繁多的局部和全局的索引技术。分区操作也可以被并行执行。分区技术还提高了数据的可用性。当部分数据由于故障或其它原因不可用时，其它分区内的数据可不受影响继续使用。分区对应用是透明的，可以通过标准的SQL语句对分区表进行操作。Oracle的优化器在访问数据时会分析数据的分区情况，在进行查询时，那些不包含任何查询数据的分区将被忽略，从而大大提高系统的性能。总之，Oracle的分区表技术能满足高性能、高可用性、数据易管理性等综合需求。第39页/共86页40orac
36、le基础分区表设计原则表的大小对于大表进行分区，将有益于大表操作的性能和大表的数据维护。通常当表的大小超过1.5GB2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。数据访问特性基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。数据维护某些表的数据维护，经常按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。因为删除（Delete）大量的数据，对系统开销很大，有时甚至是不可接受的。只读数据如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对
37、于数据库的备份是非常有益的。并行数据操作（ParallelDML）对于经常执行并行操作（如ParallelInsert,ParallelUpdate等）的表应考虑进行分区。表的可用性当对表的部分数据可用性要求很高时，应考虑进行表分区。第40页/共86页41oracle基础分区表范围分区表创建脚本CREATETABLEHIS_EVT_DCC_FCMAUDI(cm_rec_llinteger,cm_tx_dtvarchar2(8),)PARTITIONBYRANGE(CM_TX_DT)(PARTITIONETL_LOAD_DATE_0508VALUESLESSTHAN(20050901)TABLE
38、SPACEetl0_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_LOA
39、D_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_jyr
40、q_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,);第41页/共86页42oracle基础分区表HASH分区表创建脚本CREATETABLEPERSONAL_CUSTOMER_INFO（）PARTITIONBYHASH(CI-CUST-NO)PARTITIONS
41、64TABLESPACETS_ODS_DATA;复合分区表创建脚本CREATETABLEFACT_DCC_SAACNACN_PERSONAL(txn_date,sa-acct-no)PARTITIONBYRANGE(时间字段)SUBPARTITIONBYHASH(SA-ACCT-NO)SUBPARTITIONTEMPLATE(SUBPARTITIONs1,SUBPARTITIONs2,SUBPARTITIONs3,);第42页/共86页43oracle基础分区表查询SELECT*FROMhis_evt_dcc_fcmaudiPARTITION(ETL_LOAD_DATE_0508);清分区数据
42、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;第43页/共86页44oracle基础分区表分区信息查询SELECT*FROMUSER_TAB
43、_PARTITIONS;SELECT*FROMUSER_TAB_SUBPARTITIONS;SELECT*FROMUSER_IND_PARTITIONS;加上WHERESTATUS=UNUSABLE 可查询失效的分区索引SELECT*FROMUSER_IND_SUBPARTITIONS;加上WHERESTATUS=UNUSABLE 可查询失效的子分区索引SELECT*FROMUSER_SEGMENTS;第44页/共86页45oracle基础sqlldr用法:SQLLDRkeyword=value,keyword=value,.sqlldr$1control=tb.ctlerrors=10000
44、rows=100000bindsize=8192000readsize=8192000log=tbldr.logbad=tbldr.baddirect=true有效的关键字:userid-ORACLE用户名/口令control-控制文件名log-日志文件名bad-错误文件名data-数据文件名discard-废弃文件名discardmax-允许废弃的文件的数目(全部默认)skip-要跳过的逻辑记录的数目(默认0)load-要加载的逻辑记录的数目(全部默认)errors-允许的错误的数目(默认50)rows-常规路径绑定数组中或直接路径保存数据间的行数(默认:常规路径64,所有直接路径)bind
45、size-常规路径绑定数组的大小(以字节计)(默认256000)silent-运行过程中隐藏消息(标题,反馈,错误,废弃,分区)direct-使用直接路径(默认FALSE)parfile-参数文件:包含参数说明的文件的名称parallel-执行并行加载(默认FALSE)file-要从以下对象中分配区的文件第45页/共86页46oracle基础sqlldr控制文件LOADDATAINFILEtest.txtINTOTABLEDEPTREPLACEFIELDSTERMINATEDBY,OPTIONALLYENCLOSEDBY(DEPTNO,DNAME,LOC)第46页/共86页47oracle基础
46、文本数据导出Oracle没有提供将数据导出到一个文件的工具。但是我们可以用SQL*Plus的select及format数据来输出到一个文件：setechooffnewpage0space0pagesize0feedoffheadofftrimspoolonspooloradata.txtselectcol1|,|col2|,|col3fromtab1wherecol2=XYZ;spooloff第47页/共86页48oracle基础数据库备份导入导出EXP:exphelp=y有三种主要的方式（完全、用户、表）1、完全：EXPSYSTEM/MANAGERBUFFER=64000FILE=C:FUL
47、L.DMPFULL=Y如果要执行完全导出，必须具有特殊的权限2、用户模式：EXPSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPOWNER=SONIC这样用户SONIC的所有对象被输出到文件中。3、表模式：EXPSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPOWNER=SONICTABLES=(SONIC)这样用户SONIC的表SONIC就被导出第48页/共86页49oracle基础数据库备份导入导出IMP:imphelp=y具有三种模式（完全、用户、表）1、完全：IMPsys/sys123assysdbaBUFFER=64000F
48、ILE=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就被导入。第49页/共86页50oracle基础查看执行计划若有PLSQL等windows界面的工具，可点执行计划菜单查看命令
49、行查看执行计划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|-
50、第50页/共86页51oracle基础常用函数数值型函数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表示第51页/共86页52oracle基础常用函数