select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
from x$ksppi x, x$ksppcv y
where x.inst_id=USERENV(‘Instance’)
and y.inst_id=USERENV(‘Instance’)
and x.indx=y.indx
and x.ksppinm like ‘%&par%’;
月份：2013年10月
SQL*Loader的使用方法
SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库 中加载数据。SQLLDR可以在极短的时间内加载数量庞大的数据。它有两种操作模式：
传统路径：（conventional path）：SQLLDR会利用SQL插入为我们加载数据。
直接路径(direct path)：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块。
利用直接路径加载，能从一个平面文件读数据，并将其直接写至格式化的数据库块，而绕过整个SQL引擎和undo生成，同时还 可能避开redo生成。要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载。
如果不带任何输入地从命令行执行SQLLDR，它会提供以下帮助：
[oracle@jy ~]$ sqlldr SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:32:22 2013 Copyright (c) 1982, 2005, Oracle. 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) 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.
要使用SQLLDR,需要有一个控制文件(control file).控制文件中包含描述输入数据的信息(如输入数据的布局、数据类型等),另外还包含有关目标表的信息.控制文件甚至还可以包含要加载的数据.在下面的例子中,我们将一步一步地建立一个简单的控制文件，并对这些命令提供必须的解释
(1) LOAD DATA
(2) INFILE *
(3) INTO TABLE DEPT
(4) FIELDS TERMINATED BY ‘,’
(5) (DEPTNO, DNAME, LOC )
(6) BEGINDATA
(7) 10,Sales,Virginia
(8) 20,Accounting,Virginia
(9) 30,Consulting,Virginia
(10) 40,Finance,Virginia
[oracle@jy ~]$ vi demo1.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia
LOAD DATA:这会告诉SQLLDR要做什么(在这个例子中,则指示要加载数据).SQLLDR还可以执行CONTINUE_LOAD,也就是继续加载.只有在继续一个多表直接路径加载时才能使用后面这个选项.
INFILE *:这会告诉SQLLDR所要加载的数据实际上包含在控制文件本身上,如第6～10行所示.也可以指定包含数据的另一个文件的文件名.如果愿意,可以使用一个命令行参数覆盖这个INFILE语句.要注意的是命令行选项总会涵盖控制文件设置.
INTO TABLE DEPT:这会告诉SQLLDR要把数据加载到哪个表中(在这个例子中,数据要加载到DEPT表中).
FIELDS TERMINATED BY ‘,’:这会告诉SQLLDR数据的形式应该是用逗号分隔的值.为SQLLDR描述输入数据的方式有数十种;这只是其中较为常用的方法之一.
(DEPTNO, DNAME, LOC):这会告诉SQLLDR所要加载的列,这些列在输入数据中的顺序以及数据类型.这是指输入流中数据的数据类型,而不是数据库中的数据类型.在这个例子中,列的数据类型默认为CHAR(255),这已经足够了.
BEGINDATA:这会告诉SQLLDR你已经完成对输入数据的描述,后面的行(第7～10行)是要加载到DEPT表的具体数据.
这个控制文件采用了最简单,最常用的格式之一:将定界数据加载到一个表.要使用这个控制文件(名为demo1.ctl),只需创建一个空的DEPT表：
sys@JINGYONG> create table dept 2 (deptno number(2) constraint dept_pk primary key, 3 dname varchar2(14), 4 loc varchar2(14) 5 ); 表已创建。
并运行以下命令：
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo1.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:43:20 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5
如果表非空，就会收到一个错误消息：
SQLLDR-601: For INSERT option, table must be empty. Error on table DEPT
这是因为,这个控制文件中几乎所有选项都取默认值,而默认的加载选项是INSERT(而不是APPEND,TRUNCATE或REPLACE).要执行INSERT,SQLLDR就认为表为空.如果想向DEPT表中增加记录,可以指定加载选项为APPEND;或者,为了替换DEPT表中的数据,可以使用REPLACE或TRUNCATE.REPLACE使用一种传统DELETE语句;因此,如果要加载的表中已经包含许多记录,这个操作可能执行得很慢.TRUNCATE则不同,它使用TRUNCATE SQL命令,通常会更快地执行,因为它不必物理地删除每一行.
每个加载都会生成一个日志文件,以上这个简单加载的日志文件如下：
[oracle@jy ~]$ cat demo1.log SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:43:20 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Control File: demo1.ctl Data File: demo1.ctl Bad File: demo1.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional Table DEPT, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTER Record 5: Rejected - Error on table DEPT, column DEPTNO. Column not found before end of logical record (use TRAILING NULLCOLS) Table DEPT: 4 Rows successfully loaded. 1 Row not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 49536 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 1 Total logical records discarded: 0 Run began on Thu Oct 03 11:43:20 2013 Run ended on Thu Oct 03 11:43:21 2013 Elapsed time was: 00:00:00.38 CPU time was: 00:00:00.09
日志文件会告诉我们关于加载的很多方面,从中可以看到我们所用的选项(默认或默认选项);可以看到读取了多少记录,加载 了多少记录等.日志文件指定了所有BAD文件和DISCARD文件的位置,甚至还会告诉我们加载用了多长时间.每个日志文件对于验 证加载是否成功至关重要,另外对于诊断错误也很有意义.如果所加载的数据导致SQL错误(也就是说,输入数据是”坏的”,并在BAD文件中建立了记录),这些错误就会记录在这个日志文件中.
如何加载定界数据
定界数据(delimited data)即用某个特定字符分隔的数据,可以用引号括起,这是当前平面文件最常见的数据格式.在大型机 上,定长,固定格式的文件可能是最可识别的文件格式,但是在UNIX和NT上,定界文件才是”标准”.
对于定界数据,最常用的格式是逗号分隔值(comma-separated values，CSV)格式.采用这种文件格式,数据中的每个字段与 下一个字段用一个逗号分隔.文本串可以用引号括起,这样就允许串本身包含逗号.如果串还必须包含引号,一般约定是使用两个引号(在下面的代码中,将使用””而不是”).要加载定界数据,相应的典型控制文件与前面第一个例子很相似,但是FIELDS TERMINATED BY 子句通常如下指定:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
它指定用逗号分隔数据字段,每个字段可以用双引号括起,如果我们把这个控制文件的最后部分修改如下:
[oracle@jy ~]$ cat demo2.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,"Virginia,USA" 20,Accounting,"Va,""USA""" 30,Consulting,Virginia 40,Finance,Virginia
使用这个控制文件运行SQLLDR时，结果如下：
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo2.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:01:03 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5
使用这个控制文件运行SQLLDR时，结果如下：
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------- 10 Sales Virginia,USA 20 Accounting Va,"USA" 30 Consulting Virginia 40 Finance Virginia
要注意以下几点：
部门10中的Virginia.USA:这是因为输入数据是”Virginia,USA”.输入数据字段必须包括在引号里才能保留数据中的逗号.否 则,数据中的这个逗号会被认为是字段结束标记,这样就会只加载Virginia,而没有USA文本.
Va,”USA”:这是因为输入数据是”Va,””USA”””.对于引号括起的串,SQLLDR会把其中”的两次出现计为一次出现.要加载一个包含可选包围字符(enclosure character)的串,必须保证这个包围字符出现两次.
另一种常用的格式是制表符定界数据(tag-delimited data),这是用制表符分隔而不是逗号分割的数据.有两种方法使用 TERMINATED BY子句来加载这种数据：
TERMINATED BY X’09′(使用十六进制格式的制表符;采用ASCII时,制表符为9)
TERMINATED BY WHITESPACE
这两种方法在实现上有很大差异,下面将会说明.还是用前面的DEPT表,我们将使用以下控制文件加载这个表:
[oracle@jy ~]$ vi demo3.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY WHITESPACE (DEPTNO, DNAME, LOC ) BEGINDATA 10 Sales Virginia
从字面上不太容易看得出来,不过要知道,在这里各部分数据之间都有两个制表符.这里的数据行实际上是:
10\t\tSales\t\tVirginia
在此\t是普通可识别的制表符转义字符.使用这个控制文件时(包含如前所示的TERMINATED BY WHITESPACE),表DEPT中的数据将是:
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------- 10 Sales Virginia
TERMINATED BY WHITESPACE会解析这个串,查找空白符(制表符,空格和换行符)的第一次出现,然后继续查找,直至找到下一 个非空白符.
另一方面,如果要使用FIELDS TERMINATED BY X’09’,如以下控制文件所示,这里稍做修改:
[oracle@jy ~]$ cat demo4.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY "X'09'" TRAILING NULLCOLS (DEPTNO,DNAME,LOC ) BEGINDATA 10 sales virginia [oracle@jy ~]$ sqlldr userid=jy/jy control=demo4.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 08:07:43 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 1
加载这样的定界数据时,很可能想逃过输入记录中的某些列.例如,你可能加载字段1,3和5,而跳过第2列和第4列.为此SQLLDR提供了FILLER关键字.这允许你映射一个输入记录中的一列,但不把它放在数据库中.例如,给定DEPT表以及先前的一个控制文件,可以修改这个控制文件,使用FILLER关键字正确地加载数据(跳过制表符):
[oracle@jy ~]$ vi demo3.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY WHITESPACE (DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC) BEGINDATA 10 Sales Virginia
所得到的表DEPT现在如下所示：
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------- 10 Sales Virginia
如何加载固定格式数据
通常会有一个有某个外部系统生成的平面文件,而且这是一个定长文件,其中包含着固定位置的数据(positional data).例 如,NAME字段位于第1～10字节,ADDRESS字段位于地11～35字节等.
这种定宽的固定位置数据是最适合SQLLDR加载的数据格式.要加载这种数据,使用SQLLDR是最快的处理方法,因为解析输入数据流相当容易.SQLLDR会在数据记录中存储固定字节的偏移量和长度,因此抽取某个给定字段相当简单.如果要加载大量数据，将其转换为一种固定位置格式通常是最好的办法.当然,定宽文件也有一个缺点,它比简单的定界文件格式可能要大得多.
要加载定宽的固定位置数据,将会在控制文件中使用POSITION关键字,例如:
[oracle@jy ~]$ vi demo5.ctl LOAD DATA INFILE * INTO TABLE DEPT (DEPTNO position(1:2),DNAME position(3:16),LOC position(17:30) ) BEGINDATA 10Accounting Virginia,USA QL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:21:04 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 2 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------- 10 Accounting Vir ginia,USA
这个控制文件没有使用FIELDS TERMINATED BY子句;而是使用了POSITION来告诉SQLLDR字段从哪里开始,到哪里结束.关于 POSITION子句有意思的是,我们可以使用重叠的位置,可以在记录中来回反复.例如,如果如下修改DEPT表:
jy@JINGYONG> alter table dept add entire_line varchar2(30); 表已更改。
并使用以下控制文件:
[oracle@jy ~]$ vi demo6.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE (DEPTNO position(1:2), DNAME position(3:16), LOC position(17:30), ENTIRE_LINE position(1:30) ) BEGINDATA 10Accounting Virginia,USA
字段ENTIRE_LINE定义的POSITION(1:30).这会从所有30字节的输入数据中抽取出这个字段的数据,而其他字段都是输入数据的子串.这个控制文件的输出如下:
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- -------------- ----------------------------- 10 Accounting Vir ginia,USA 10Accounting Virginia,USA
使用POSITION时,可以使用相对偏移量,也可以使用绝对偏移量.在前面的例子中使用了绝对偏移量,我们明确地指示了字段从 哪里开始,到哪里结束.也可以把前面的控制文件写作:
[oracle@jy ~]$ vi demo7.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE (DEPTNO position(1:2), DNAME position(*:16), LOC position(*:30), ENTIRE_LINE position(1:30) ) BEGINDATA 10Accounting Virginia,USA [oracle@jy ~]$ sqlldr userid=jy/jy control=demo7.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:25:53 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 2 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- -------------- ----------------------------- 10 Accounting Vir ginia,USA 10Accounting Virginia,USA
*指示控制文件得出上一个字段在哪里结束.因此,在这种情况下,(*:16)与(3:16)是一样的.注意,控制文件中可以混合使用相对位置和绝对位置.另外,使用*表示法时,可以把它与偏移量相加.例如,如果DNAME从DEPTNO结束之后的2个字节处开始,可以使用(*+2:16).在这个例子中,其作用就相当于使用(5:16).
POSITION子句中的结束位置必须是数据结束的绝对列位置.有时,可能指定每个字段的长度更为容易,特别是如果这些字段是连 续的(就像前面的例子一样).采用这种方式,只需告诉SQLLDR:记录从第1个字节开始,然后指定每个字段的长度就行了.这样我们就可以免于计算记录中的开始和结束偏移量,这个计算有时可能很困难.为此,可以不指定结束位置,而是指定定长记录中各个字段的长度,如下:
[oracle@jy ~]$ vi demo8.ctl REPLACE LOAD DATA INFILE * INTO TABLE DEPT (DEPTNO position(1) char(2), DNAME position(*) char(14), LOC position(*) char(14), ENTIRE_LINE position(1:30) ) BEGINDATA 10Accounting Virginia,USA [oracle@jy ~]$ sqlldr userid=jy/jy control=demo8.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:29:01 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 2 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- -------------- ----------------------------- 10 Accounting Vir ginia,USA 10Accounting Virginia,USA
在此只需告诉SQLLDR第一个字段从哪里开始及其长度.后面的每个字段都从上一个字段结束处开始,并具有指定的长度.直至最 后一个字段才需要再次指定位置,因为这个字段又要从记录起始处开始.
如何加载日期
使用SQLLDR加载日期相当简单,但是看起来这个方面经常导致混淆.你只需在控制文件中使用DATE数据类型,并指定要使用的日 期格式,这个日期格式与数据库中TO_CHAR和TO_DATE中使用的日期格式是一样的.SQLLDR会向数据应用这个日期格式,并为你完成加载.
例如,如果再把DEPT表修改如下:
jy@JINGYONG> alter table dept add last_updated date; 表已更改。
可以用以下控制文件加载它：
[oracle@jy ~]$ vi demo9.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC, ENTIRE_LINE, LAST_UPDATED date 'yyyy-mm-dd' ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 20,Accounting,Virginia,USA,1999-06-21 30,Consulting,Virginia,USA,2000-01-05 40,Finance,Virginia,USA,2001-03-15 [oracle@jy ~]$ sqlldr userid=jy/jy control=demo9.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:47:17 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ---------- -------------- -------------- ----------------------------- -------------- 10 Sales Virginia USA 01-5月 -00 20 Accounting Virginia USA 21-6月 -99 30 Consulting Virginia USA 05-1月 -00 40 Finance Virginia USA 15-3月 -01
就这么简单,只需在控制文件中应用格式,SQLLDR就会为我们完成日期转换.在某些情况下可能使用一个更强大的SQL函数更为合适.例如,如果你的输入文件包含多种不同格式的日期:有些有时间分量,有些没有;有些采用DD-MON-YYYY格式;有些格式为DD/MM/YYYY等等.
如果使用函数加载数据
在SQLLDR中使用函数很容易.要在SQLLDR脚本中向某个字段应用一个函数,只需将这个函数增加到控制文件中(用两个引号括起).例如,假设有前面的DEPT表,你想确保所加载的数据都是大写的.可以使用以下控制文件来加载:
[oracle@jy ~]$ vi demo10.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE "upper(:entire_line)", LAST_UPDATED date 'yyyy-mm-dd' ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 20,Accounting,Virginia,USA,1999-06-21 30,Consulting,Virginia,USA,2000-01-05 40,Finance,Virginia,USA,2001-03-15 "demo10.ctl" 17L, 342C written [oracle@jy ~]$ sqlldr userid=jy/jy control=demo10.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:50:46 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ---------- -------------- -------------- ----------------------------- -------------- 10 SALES VIRGINIA USA 01-5月 -00 20 ACCOUNTING VIRGINIA USA 21-6月 -99 30 CONSULTING VIRGINIA USA 05-1月 -00 40 FINANCE VIRGINIA USA 15-3月 -01
可以注意到,只需向一个绑定变量应用UPPER函数就可以很容易地将数据变为大写.要注意SQL函数可以引用任何列,而不论将 函数实际上应用于哪个列.这说明一个列可以是对两个或更多其他列应用一个函数的结果.例如,如果你想加载ENTIRE_LINE 列,可以使用SQL连接运算符.不过这种情况下这样做稍有些麻烦.现在,输入数据集中有4个数据元素.如果只是向控制文件中加入如下字符ENTIRE_LINE：
[oracle@jy ~]$ vi demo11.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED date 'yyyy-mm-dd', ENTIRE_LINE ":deptno||:dname||:loc||:last_updated" ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 20,Accounting,Virginia,USA,1999-06-21 30,Consulting,Virginia,USA,2000-01-05 40,Finance,Virginia,USA,2001-03-15 ~ ~ ~ ~ ~ "demo11.ctl" 18L, 360C written [oracle@jy ~]$ sqlldr userid=jy/jy control=demo11.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:55:32 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5
在demo11.log日志文件中可以看到:
Record 5: Rejected - Error on table DEPT, column DEPTNO. Column not found before end of logical record (use TRAILING NULLCOLS)
在此,SQLLDR告诉你,没等处理完所有列,记录中就没有数据了.这种情况下,解决方案很简单.实际上,SQLLDR甚至已经告诉了我们该怎么做:这就是使用TRAILING NULLCOLS.这样一来,如果输入记录中不存在某一列的数据,SQLLDR就会为该列绑定一个NULL值.在这种情况下,增加TRAILING NULLCOLS会导致绑定变量:ENTIRE_LINE成为NULL,所以再尝试这个控制文件：
[oracle@jy ~]$ vi demo11.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED date 'yyyy-mm-dd' ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 20,Accounting,Virginia,USA,1999-06-21 30,Consulting,Virginia,USA,2000-01-05 40,Finance,Virginia,USA,2001-03-15 [oracle@jy ~]$ sqlldr userid=jy/jy control=demo11.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 13:00:49 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ---------- -------------- -------------- ------------------------------ -------------- 10 SALES VIRGINIA 10SalesVirginia2000-05-01 01-5月 -00 20 ACCOUNTING VIRGINIA 20AccountingVirginia1999-06-21 21-6月 -99 30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 05-1月 -00 40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 15-3月 -01
之所以可以这样做,原因在于SQLLDR构建其INSERT语句的做法.SQLLDR会查看前面的控制文件,并看到控制文件中的DEPTNO, DNAME,LOC,LAST_UPDATED和ENTIRE_LINE这几列.它会根据这些列建立5个绑定变量.通常,如果没有任何函数,所建立的 INSERT语句就是:
INSERT INTO DEPT ( DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE )
VALUES (&DEPTNO,&DNAME,&LOC,&LAST_UPDATED,&ENTIRE_LINE);
然后再解析输入流,将值赋给相应的绑定变量,然后执行语句.如果使用函数,SQLLDR会把这些函数结合到INSERT语句中.在上 一个例子中,SQLLDR建立的INSERT语句如下所示：
INSERT INTO T (DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE)
VALUES ( &DEPTNO, upper(&dname), upper(&loc), &last_updated,
&deptno||&dname||&loc||&last_updated );
然后再做好准备,把输入绑定到这个语句,再执行语句.所以SQL中能做的事情都可以结合到SQLLDR脚本中.由于SQL中增加了 CASE语句,所以这样做不仅功能极为强大,而且相当容易.例如,假设你的输入文件有以下格式的日期:
HH24:MI:SS:只有一个时间;日期默认为SYSDATE.
DD/MM/YYYY:只有一个日期;时间默认为午夜0点.
HH24:MI:SS DD/MM/YYYY:日期和时间都要显式提供。
可以使用如下的一个控制文件:
[oracle@jy ~]$ vi demo12.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end" ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 12:03:03 20,Accounting,Virginia,USA,02:23:54 30,Consulting,Virginia,USA,2000-01-05 01:24:00 40,Finance,Virginia,USA,2001-03-15 [oracle@jy ~]$ sqlldr userid=jy/jy control=demo12.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 13:06:49 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 jy@JINGYONG> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; 会话已更改。 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ---------- -------------- -------------- --------------------------------------- --------------------- 10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:54 30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 2001-03-15 00:00:00
如何加载有内嵌换行符的数据
过去,如果要加载可能包含换行符的自由格式的数据,这对于SQLLDR来说很成问题.换行符是SQLLDR的默认行结束符,要加载有内嵌换行符的数据有以下方法:
加载数据,其中用非换行符的其他字符来表示换行符(例如,在文本中应该出现换行符的位置上放上串\n),并在加载时使用一个SQL函数用一个CHR(10)替换该文本.
在INFILE指令上使用FIX属性,加载一个定长平面文件.
在INFILE指令上使用VAR属性,加载一个定宽文件,在该文件使用的格式中,每一行的前几个字节指定了这一行的长度(字节数 ).
在INFILE指令上使用STR属性,加载一个变宽文件,其中用某个字符序列来表示行结束符,而不是用换行符来表示.
使用一个非换行符的字符
如果你能对如何生成输入数据加以控制,这就是一种很容易的方法.如果创建数据文件时能很容易地转换数据,这种方法就能奏 效.其思想是,就数据加载到数据库时对数据应用一个SQL函数,用某个字符串来替换换行符.下面向DEPT表再增加另一个列:
jy@JINGYONG> alter table dept add comments varchar2(4000); 表已更改。
将使用这一列来加载文本,下面是一个有内联数据的示例控制文件:
[oracle@jy ~]$vi demo13.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS "replace(:comments,'\\n',chr(10))" ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales\nOffice in Virginia 20,Accounting,Virginia,USA,02:23:54,This is the Accountin\nOffice in Virginia 30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting\nOffice in Virginia 40,Finance,Virginia,USA,2001-03-15,This is the Finance\nOffice in Virginia [oracle@jy ~]$ sqlldr userid=jy/jy control=demo13.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 14:45:48 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5
注意,调用中必须使用\\n来替换换行符,而不只是\n.这是因为\n会被SQLLDR识别为一个换行符,而且SQLLDR会把它转换为一 个换行符,而不是一个两字符的串.利用以上控制文件执行SQLLDR时,DEPT表中将加载以下数据:
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS --------------------------------------------------------------------------------------------------------------- ------------------------------------------------- 10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales Office in Virginia 20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:5 This is the Accountin Office in Virginia 30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting Office in Virginia 40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 2001-03-15 00:00:00 This is the Finance Office in Virginia
使用IFX属性
另一种可用的方法是使用FIX属性.如果使用这种方法,输入数据必须出现在定长记录中.每个记录与输入数据集中所有其他记 录的长度都相同,即有相同的字节数.对于固定位置的数据,使用FIX属性就特别适合.这些文件通常是定长输入文件.使用自由格式的定界数据时,则不太可能是一个定长文件,因为这些文件通常是变长的(这正是定界文件的关键:每一行不会不必要地过长).
使用FIX属性时,必须使用一个INFILE子句,因为FIX属性是INFILE的一个选项.另外,如果使用这个选项,数据必须在外部存储 ,而并非存储在控制文件本身.因此,假设有定长的输入记录,可以使用如下的一个控制文件:
[oracle@jy ~]$ vi demo14.ctl LOAD DATA INFILE demo14.dat "fix 80" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS ) [oracle@jy ~]$ vi demo14.dat 10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales\nOffice in Virginia 20,Accounting,Virginia,USA,02:23:54,This is the Accountin\nOffice in Virginia\n 30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting\nOffice\n 40,Finance,Virginia,USA,2001-03-15,This is the Finance\nOffice in Virginia \n
这个文件指定了一个输入数据文件(domo14.dat),这个文件中每个记录有80字节,这包括尾部的换行符(每个记录最后可能有换行符,也可能没有).在这种情况下,输入数据文件中的换行符并不是特殊字符.这只是要加载(或不加载)的另一个字符而已.要知道:记录的最后如果有换行符,它会成为这个记录的一部分.为了充分理解这一点,我们需要一个实用程序将文件的内容转储在屏幕上,以便我们看到文件中到底有什么.使用Linux(或任何Unix版本)利用od就很容易做到,这个程序可以将文件以八进制(和其他格式)转储到屏幕上.我们将使用下面的demo.dat文件.注意以下输入中的第一列实际上是八进制,所以第2行上的数字0000012是一个八进制数,不是十进制数10.由此我们可以知道所查看的文件中有哪些字节.我对这个输出进行了格式化,使得每行显示10个字符(使用-w10),所以0,12,24和36实际上就是0,10,20和30.
[oracle@jy ~]$ od -c -w10 -v demo14.dat 0000000 a l e s , V i r g i 0000012 n i a , U S A , 2 0 0000024 0 0 - 0 5 - 0 1 1 0000036 2 : 0 3 : 0 3 , T h 0000050 i s i s t h e 0000062 S a l e s \ n O f f 0000074 i c e i n V i r 0000106 g i n i a \n 2 0 , A 0000120 c c o u n t i n g , 0000132 V i r g i n i a , U 0000144 S A , 0 2 : 2 3 : 5 0000156 4 , T h i s i s 0000170 t h e A c c o u n 0000202 t i n \ n O f f i c 0000214 e i n V i r g i 0000226 n i a \n 3 0 , C o n 0000240 s u l t i n g , V i 0000252 r g i n i a , U S A 0000264 , 2 0 0 0 - 0 1 - 0 0000276 5 0 1 : 2 4 : 0 0 0000310 , T h i s i s t 0000322 h e C o n s u l t 0000334 i n g \ n O f f i c 0000346 e i n V i r g i 0000360 n i a \n 4 0 , F i n 0000372 a n c e , V i r g i 0000404 n i a , U S A , 2 0 0000416 0 1 - 0 3 - 1 5 , T 0000430 h i s i s t h e 0000442 F i n a n c e \ n 0000454 O f f i c e i n 0000466 V i r g i n i a \n 0000477
注意,在这个输入文件中,并没有用换行符(\n)来指示SQLLDRE记录在哪里结束;这里的换行符只是要加载的数据而已.SQLLDR使用FIX宽度(80字节)来得出要读取多少数据.实际上,如果查看输入数据,可以看到,输入文件中提供给SQLLDR的记录甚至并非以\n结束.部门20的记录之前的字符是一个空格,而不是换行符.
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo14.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 15:03:05 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 4
我们知道了每个记录的长度为80字节,现在就可以用前面有FIX80子句的控制文件来加载这些数据了.完成加载后,可以看到以 下结果:
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS --------------------------------------------------------------------------------------------------------------- ------------------- 10 SALES VIRGINIA10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales\nOffice in Virginia 20 ACCOUNTING VIRGINIA20AccountingVirginia02:23:54 2013-10-01 02:23:54 This is the Accountin\nOffice in Virginia\n 30 CONSULTING VIRGINIA30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting\nOffice\n 40 FINANCE VIRGINIA40FinanceVirginia2001-03-15 2001-03-15 00:00:00 This is the Finance\nOffice in Virginia \n
你可能需要截断这个数据,因为尾部的空白符会保留.可以在控制文件中使用TRIM内置SQL函数来完成截断.
Linux上 ,行结束标记就是\n(SQL中的CHR(10)).在Windows NT上,行结束标记却是\r\n(SQL中的CHR(13)||CHR(10)).一般来讲,如果使用FIX方法,就要确保是在同构平台上创建和加载文件(Linux上创建,Linux上加载;或者Windows上创建,Windows上加载)
使用VAR属性
要加载有内嵌换行符的数据,另一种方法是使用VAR属性.使用这种格式时,每个记录必须以某个固定的字节数开始,这表示这 个记录的总长度.通过使用这种格式,可以加载包含内嵌换行符的变长记录,但是每个记录的开始处必须有一个记录长度字段. 因此,如果使用如下的一个控制文件:
[oracle@jy ~]$ vi demo15.ctl LOAD DATA INFILE 'demo15.dat' "var 3" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS )
VAR 3指出每个输入记录的前3个字节是输入记录的长度,如果取以下数据文件：
[oracle@jy ~]$ cat demo15.dat 07910,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia 07820,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia 08930,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia 07440,Finance,Virginia,USA,2001-01-15,This is the Finance Office in Virginia
可以使用该控制文件来加载.在输入数据文件中有4行数据.第一行从079开始,这说明接下来79字节是第一个输入记录.这79字 节包括单词Virginia后的结束换行符.下一行从078开始,这一行有78字节的文本,依此类推.使用这种格式数据文件,可以很 容易地加载有内嵌换行符的数据.
同样,如果在使用Linux和Windows(前面的例子都在Linux上完成,其中换行符只是一个字符长),就必须调整每个记录的长度字 段.在Windows上,前例.dat文件中的长度字段应该是80,79,90和75.
使用STR属性 要加载有内嵌换行符的数据,这可能是最灵活的一种方法.通过使用STR属性,可以指定一个新的行结束符(或字符序列).就能创建一个输入数据文件,其中每一行的最后有某个特殊字符,换行符不再有特殊含义. 我更喜欢使用字符序列,通常会使用某个特殊标记,然后再加一个换行符.这样,在一个文本编辑器或某个实用程序中查看输入 数据时,就能很容易地看到行结束符,因为每个记录的最后仍然有一个换行符.STR属性以十六进制指定,要得到所需的具体十 六进制串,最容易的方法是使用SQL和UTL_RAW来生成十六进制串.例如,假设使用的是Linux平台,行结束标记是CHR(10)(换行 ),我们的特殊标记字符是一个管道符号(|),则可以写为:
jy@JINGYONG>select utl_raw.cast_to_raw('|'||chr(10)) from dual;
UTL_RAW.CAST_TO_RAW('|'||CHR(1
--------------------------------------------------------------------------------
7C0A
由此可知,在Linux上我们需要使用的STR是X’7C0A’.
在Windows上,要使用UTL_RAW.CAST_TO_RAW(‘|’||chr(13)||chr(10))
为了使用这个方法,要有以下控制文件:
[oracle@jy ~]$ vi demo16.ctl LOAD DATA INFILE demo16.dat "str X'7C0A'" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS )
因此,如果输入数据如下:
[oracle@jy ~]$ cat demo16.dat 10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia| 20,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia| 30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia| 40,Finance,Virginia,USA,2001-01-15,This is the Finance Office in Virginia|
其中,数据文件中的每个记录都以|\n结束,前面的控制文件就会正确地加载这些数据.
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo16.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 07:45:30 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 4 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS --------------------------------------------------------------------------------------------------------------- ----------------- 10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales Office in Virginia 20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:54 This is the Accounting Office in Virginia 30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting Office in Virginia 40 FINANCE VIRGINIA 40FinanceVirginia2001-01-15 2001-01-15 00:00:00 This is the Finance Office in Virginia
如果加载LOB
现在来考虑在LOB的一些方法.这不是一个LONG或LONG RAW字段,而是更可取的数据类型BLOB和CLOB.这些数据类型是Oracle 8.0及以后版本中引入的,与遗留的LONG和LONG RAW类型相比,它们支持更丰富的接口/功能集.
将分析两种加载这些字段的方法:SQLLDR和PL/SQL.
通过PL/SQL加载LOB
DBMS_LOB包的入口点为LoadFromFile,LoadBLOBFromFile和LoadCLOBFromFile.通过这些过程,我们可以使用一个BFILE(用于读取操作系统文件)来填充数据库中的BLOB或CLOB.LoadFromFile和LoadBLOBFromFile例程之间没有显著的差别,只不过后者会返回一些OUT参数,指示已经向BLOB列中加载了多少数据.不过,LoadCLOBFromFile例程还提供了一个突出的特性:字符集转换.使用LoadCLOBFromFile时,我们可以告诉数据库:这个文件将以另外某种字符集(不同于数据库正在使用的字符集)来加载,而且要执行必要的字符集转换.例如,可能有一个UTF8兼容的数据库,但是收到的要加载的文件却以WE8ISO8859P1字符集编码,或反之利用这个函数就能成功地加载这些文件.
要使用这些过程,需要在数据库中创建一个DIRECTORY对象.这个对象允许我们创建并打开BFILE(BFILE指向文件系统上数据库 服务器能访问的一个现有文件).DBMS_LOB包完全在服务器中执行.它只能看到服务器能看到的文件系统.特别是,如果你通过 网络访问Oracle,DBMS_LOB包将无法看到你的本地文件系统.
所以,需要先在数据库中创建一个DIRECTORY对象.
jy@JINGYONG> create or replace directory dir1 as '/home/oracle'; 目录已创建。 jy@JINGYONG> create or replace directory "dir2" as '/home/oracle'; 目录已创建。 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------ --------------- ----------------- SYS dir2 /home/oracle SYS DIR1 /home/oracle
我们创建的dir1在数据字典中为大写
下面,将一些数据加载到BLOB或CLOB中:
jy@JINGYONG> create table demo
2 (id int primary key,
3 theClob clob
4 );
表已创建。
[oracle@jy ~]$ echo 'Hello World!'>/home/oracle/demo.txt
jy@JINGYONG> declare
2 l_clob clob;
3 l_bfile bfile;
4 begin
5 insert into demo values(1,empty_clob()) returning theclob into l_clob;
6 l_bfile:=bfilename('DIR1','demo.txt');
7 dbms_lob.fileopen(l_bfile);
8 dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
9 dbms_lob.fileclose(l_bfile);
10 end;
11 /
PL/SQL 过程已成功完成。
jy@JINGYONG> select dbms_lob.getlength(theClob), theClob from demo;
DBMS_LOB.GETLENGTH(THECLOB) THECLOB
--------------------------- ----------------------------------------------------
13 Hello World!
在第5行我们在表中创建了一行,将CLOB设置为一个EMPTY_CLOB(),并从一个调用获取其值.除了临时LOB外,其余的LOB都住在数据库中,如果没有指向一个临时LOB的指针,或者指向一个已经在数据库中的LOB,将无法写至LOB变量.EMPTY_CLOB()不是一个NULL CLOB;而是指向一个空结构的合法指针(非NULL),它还有一个作用,可以得到一个LOB定位器,指向已锁定行中的数据.如果要选择这个值,而没有锁定底层的行,写数据就会失败,因为LOB在写之前必须锁定(不同于其他结构化数据).通过插入一行,当然我们也就锁定了这一行.如果我们要修改一个现有的行而不是插入新行,则可以使用SELECT FOR UPDATE来获取和锁定这一行.
在第6行上,我们创建了一个BFILE对象.注意,这里DIR1用的是大写,稍后就会看到,这是一个键.这是因为我们向BFILENAME() 传入了一个对象的名称,而不是对象本身.因此,必须确保这个名称与Oracle所存储的对象名称大小写匹配.
第7行打开了LOB,以便读取.
在第8行将操作系统文件/home/oracle/demo.txt的完整内容加载到刚插入的LOB定位器.这里使用DBMS_LOB.GETLENGTH()告诉LOADFROMFILE()例程要加载多少字节的BFILE(这里就是要加载全部字节).
最后,在第9行我们关闭了所打开的BFILE,CLOB已加载.
如果需要在加载文件的同时处理文件的内容,还可以在BFILE上使用DBMS_LOB.READ来读取数据.如果读取的数据实际上是文本,而不是RAW,那么使用UTL_RAW.CAST_TO_VARCHAR2会很方便.然后可以使用DBMS_LOB.WRITE或WRITEAPPEND将数据放入一个CLOB或BLOB.
通过SQLLDR加载LOB数据
现在我们来分析如何通过SQLLDR向LOB加载数据.对此方法不止一种,但是我们主要讨论两种最常用的方法:
数据内联在其他数据中。
数据外联存储(在外部存储),输入数据包含一个文件名,指示该行要加载的数据在哪个文件中.在SQLLDR术语中,这也称为二级数据文件(secondary data file，SDF).
先从内联数据谈起.
加载内联的LOB数据,这些LOB通常内嵌有换行符和其他特殊字符.
下面先来修改dept表,使COMMENTS列是一个CLOB而不是一个大的VARCHAR2字段:
jy@JINGYONG> truncate table dept; 表被截断。 jy@JINGYONG> alter table dept drop column comments; 表已更改。 jy@JINGYONG> alter table dept add comments clob; 表已更改。
例如,假设有一个数据文件(demo17.dat),它有以下内容:
[oracle@jy ~]$ cat demo17.dat 10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia| 20,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia| 30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia| 40,Finance,Virginia,USA,2001-01-15,"This is the Finance Office in Virginia, it has embedded commas and is much longer than the other comments field. If you feel the need to add double quoted text in here like this: ""You will need to double up those quotes!"" to preserve them in the string. This field keeps going for up to 1000000 bytes (because of the control file definition I used) or until we hit the magic end of record marker, the | followed by an end of line - it is right here ->"|
每个记录最后都是一个管道符号(|),后面是行结束标记.部门40的文本比其他部门的文本长得多,有多个换行符,内嵌的引号以及逗号.给定这个数据文件,可以创建一个如下的控制文件:
[oracle@jy ~]$ cat demo17.ctl LOAD DATA INFILE demo17.dat "str X'7C0A'" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS char(1000000) )
注意:这个例子在Linux上执行,Linux平台上行结束标记长度为1字节,因此可以使用以上控制文件中的STR设置.在Windows上,STR设置则必须是’7C0D0A’.
要加载这个数据文件,我们在COMMENTS列上指定了CHAR(1000000),因为SQLLDR默认所有人们字段都为CHAR(255).CHAR (1000000)则允许SQLLDR处理多达1,000,000字节的输入文本.可以把这个长度值设置为大于输入文件中任何可能文本块的大小.通过查看所加载的数据,可以看到以下结果:
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo17.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 09:08:31 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 1 Commit point reached - logical record count 2 Commit point reached - logical record count 3 Commit point reached - logical record count 4 SQL> set long 999; SQL> select comments from jy.dept; COMMENTS -------------------------------------------------------------------------------- This is the Sales Office in Virginia This is the Accounting Office in Virginia This is the Consulting Office in Virginia This is the Finance Office in Virginia, it has embedded commas and is much longer than the other comments field. If you feel the need to add double quoted text in here like this: "You will need to double up those quotes!" to preserve them in the string. This field keeps going for up to 1000000 bytes (because of the control file definition I used) or until we hit the magic end of record marker, the | followed by an end of line - it is right here ->
这里可以观察到:原来重复两次的引号不再重复,SQLLDR去除了在此放置的额外的引号.
加载外联的LOB数据,可能要把包含有一些文件名的数据文件加载在LOB中,而不是让LOB数据与结构化数据混在一起,这种情况 很常见.这提供了更大程度的灵活性,因为提供给SQLLDR的数据文件不必使用上述的4种方法之一来避开输入数据中的内嵌换行 符问题,而这种情况在大量的文本或二进制数据中会频繁出现.SQLLDR称这种额外的数据文件为LOBFILE.
SQLLDR还可以支持加载结构化数据文件(指向另外单独一个数据文件).我们可能告诉SQLLDR如何从另外这个文件分析LOB数据 ,这样就可以加载其中的一部分作为结构化数据中的每一行.这种模式的用途很有限.SQLLDR把这种外部引用的文件称为复杂二级数据文件(complex secondary data file).
LOBFILE是一种相对简单的数据文件,旨在简化LOB加载.在LOBFILE中,没有记录的概念,因此换行符不会成为问题,正是这个 性质使得LOBFILE与主要数据文件有所区别.在LOBFILE中,数据总是采用以下某种格式:
定长字段(例如,从LOBFILE加载字节100到1,000)
定界字段(以某个字符结束,或者用某个字符括起)
长度/值对,这是一个变长字段
其中最常见的类型是定界字段,实际上就是以一个文件结束符(EOF)结束.一般来讲,可能有这样一个目录,其中包含你想加载到LOB列中的文件,每个文件都要完整地放在一个BLOB中.此时,就可以使用带TERMINATED BY EOF子句的LOBFILE语句.
假设我们有一个目录,其中包含想要加载到数据库中的文件.我们想加载文件的OWNER,文件的TIME_STAMP,文件的NAME以及文 件本身.要加载数据的表如下所示:
jy@JINGYONG> create table lob_demo 2 (owner varchar2(255), 3 time_stamp date, 4 filename varchar2(255), 5 data blob 6 ); 表已创建。
在Linux上使用一个简单的ls –l来捕获输出(或者在Windows上使用dir/q/n),我们就能生成输入文件,并使用如下的一个控制文件加载:
[oracle@jy ~]$ cat demo19.ctl LOAD DATA INFILE * INTO TABLE LOB_DEMO REPLACE (owner position(14:28), time_stamp position(36:46) date "MM DD HH24:MI", filename position(48:100), data LOBFILE(filename) TERMINATED BY EOF ) BEGINDATA -rw-r--r-- 1 oracle oinstall 18432 10 3 10:42 bifile.bbd -rw-r--r-- 1 oracle oinstall 1 10 3 12:50 demo10.bad -rw-r--r-- 1 oracle oinstall 342 10 3 12:50 demo10.ctl -rw-r--r-- 1 oracle oinstall 1948 10 3 12:50 demo10.log -rw-r--r-- 1 oracle oinstall 144 10 3 12:59 demo11.bad -rw-r--r-- 1 oracle oinstall 377 10 3 13:00 demo11.ctl -rw-r--r-- 1 oracle oinstall 1913 10 3 13:00 demo11.log -rw-r--r-- 1 oracle oinstall 89 10 3 13:06 demo12.bad -rw-r--r-- 1 oracle oinstall 582 10 3 13:05 demo12.ctl -rw-r--r-- 1 oracle oinstall 2147 10 3 13:06 demo12.log -rw-r--r-- 1 oracle oinstall 790 10 3 14:45 demo13.ctl -rw-r--r-- 1 oracle oinstall 2289 10 3 14:45 demo13.log -rw-r--r-- 1 oracle oinstall 80 10 3 15:01 demo14.bad -rw-r--r-- 1 oracle oinstall 441 10 3 15:00 demo14.ctl -rw-r--r-- 1 oracle oinstall 320 10 3 15:02 demo14.dat -rw-r--r-- 1 oracle oinstall 2230 10 3 15:03 demo14.log -rw-r--r-- 1 oracle oinstall 332 10 4 05:42 demo15_bak.dat -rw-r--r-- 1 oracle oinstall 442 10 4 05:31 demo15.ctl -rw-r--r-- 1 oracle oinstall 332 10 4 05:45 demo15.dat -rw-r--r-- 1 oracle oinstall 256 10 4 05:36 demo15.dat.bak -rw-r--r-- 1 oracle oinstall 2229 10 4 05:45 demo15.log -rw-r--r-- 1 oracle oinstall 3 10 4 07:44 demo16.bad -rw-r--r-- 1 oracle oinstall 446 10 4 07:44 demo16.ctl -rw-r--r-- 1 oracle oinstall 324 10 4 07:45 demo16.dat -rw-r--r-- 1 oracle oinstall 2235 10 4 07:45 demo16.log -rw-r--r-- 1 oracle oinstall 487 10 4 09:07 demo17.ctl -rw-r--r-- 1 oracle oinstall 741 10 4 09:02 demo17.dat -rw-r--r-- 1 oracle oinstall 2321 10 4 09:08 demo17.log -rw-r--r-- 1 oracle oinstall 213 10 4 09:05 demo18.ctl -rw-r--r-- 1 oracle oinstall 665 10 4 09:05 demo18.dat -rw-r--r-- 1 oracle oinstall 1860 10 4 09:06 demo18.log -rw-r--r-- 1 oracle oinstall 1 10 3 11:43 demo1.bad -rw-r--r-- 1 oracle oinstall 177 10 3 11:43 demo1.ctl -rw-r--r-- 1 oracle oinstall 1648 10 3 11:43 demo1.log -rw-r--r-- 1 oracle oinstall 1 10 3 12:01 demo2.bad -rw-r--r-- 1 oracle oinstall 214 10 3 12:00 demo2.ctl -rw-r--r-- 1 oracle oinstall 1648 10 3 12:01 demo2.log -rw-r--r-- 1 oracle oinstall 2 10 4 07:59 demo3.bad -rw-r--r-- 1 oracle oinstall 121 10 4 07:59 demo3.ctl -rw-r--r-- 1 oracle oinstall 1525 10 4 07:59 demo3.log -rw-r--r-- 1 oracle oinstall 18 10 4 08:07 demo4.bad -rw-r--r-- 1 oracle oinstall 141 10 4 08:11 demo4.ctl -rw-r--r-- 1 oracle oinstall 1657 10 4 08:11 demo4.log -rw-r--r-- 1 oracle oinstall 137 10 3 12:20 demo5.ctl -rw-r--r-- 1 oracle oinstall 1560 10 3 12:21 demo5.log -rw-r--r-- 1 oracle oinstall 175 10 3 12:23 demo6.ctl -rw-r--r-- 1 oracle oinstall 1641 10 3 12:23 demo6.log -rw-r--r-- 1 oracle oinstall 174 10 3 12:25 demo7.ctl -rw-r--r-- 1 oracle oinstall 1641 10 3 12:25 demo7.log -rw-r--r-- 1 oracle oinstall 184 10 3 12:28 demo8.ctl -rw-r--r-- 1 oracle oinstall 1640 10 3 12:29 demo8.log -rw-r--r-- 1 oracle oinstall 1 10 3 12:47 demo9.bad -rw-r--r-- 1 oracle oinstall 290 10 3 12:47 demo9.ctl -rw-r--r-- 1 oracle oinstall 1809 10 3 12:47 demo9.log -rw-r--r-- 1 oracle oinstall 13 10 4 08:22 demo.txt -rw-r--r-- 1 oracle oinstall 547 10 3 10:44 log.bbd [oracle@jy ~]$ sqlldr userid=jy/jy control=demo19.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 10:26:54 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 56
现在,运行SQLLDR之后检查LOB_DEMO表的内容,会发现以下结果:
SQL> select owner, time_stamp, filename, dbms_lob.getlength(data) from jy.lob_demo; OWNER TIME_STAMP FILENAME DBMS_LOB.GETLENGTH(DATA) ----------------------------- -------------------------------------------- oracle oinstall 2013-10-4 9 demo17.log 2321 oracle oinstall 2013-10-4 9 demo18.dat 665 oracle oinstall 2013-10-4 9 demo18.log 1860 oracle oinstall 2013-10-3 1 demo1.log 1648 oracle oinstall 2013-10-3 1 bifile.bbd 18432 oracle oinstall 2013-10-3 1 demo10.bad 1 oracle oinstall 2013-10-3 1 demo10.ctl 342 oracle oinstall 2013-10-3 1 demo10.log 1948 oracle oinstall 2013-10-3 1 demo11.bad 144 oracle oinstall 2013-10-3 1 demo11.ctl 377 oracle oinstall 2013-10-3 1 demo12.bad 89 oracle oinstall 2013-10-3 1 demo12.ctl 582 oracle oinstall 2013-10-3 1 demo14.bad 80 oracle oinstall 2013-10-3 1 demo14.ctl 441 oracle oinstall 2013-10-4 5 demo15.dat.bak 256 oracle oinstall 2013-10-4 7 demo16.bad 3 oracle oinstall 2013-10-4 9 demo18.ctl 213 oracle oinstall 2013-10-3 1 demo1.bad 1 oracle oinstall 2013-10-3 1 demo1.ctl 177 oracle oinstall 2013-10-3 1 demo2.bad 1 oracle oinstall 2013-10-3 1 demo2.ctl 214 oracle oinstall 2013-10-4 7 demo3.bad 2 oracle oinstall 2013-10-4 7 demo3.ctl 121 oracle oinstall 2013-10-4 8 demo4.bad 18 oracle oinstall 2013-10-3 1 demo5.ctl 137 oracle oinstall 2013-10-3 1 demo6.ctl 175 oracle oinstall 2013-10-3 1 demo8.ctl 184 oracle oinstall 2013-10-3 1 demo9.bad 1 oracle oinstall 2013-10-4 8 demo.txt 13 oracle oinstall 2013-10-3 1 demo11.log 1913 oracle oinstall 2013-10-3 1 demo12.log 2147 oracle oinstall 2013-10-3 1 demo13.ctl 790 oracle oinstall 2013-10-3 1 demo13.log 2289 oracle oinstall 2013-10-3 1 demo14.dat 320 oracle oinstall 2013-10-3 1 demo14.log 2230 oracle oinstall 2013-10-4 5 demo15_bak.dat 332 oracle oinstall 2013-10-4 5 demo15.ctl 442 oracle oinstall 2013-10-4 5 demo15.dat 332 oracle oinstall 2013-10-4 7 demo16.ctl 446 oracle oinstall 2013-10-4 5 demo15.log 2229 oracle oinstall 2013-10-4 7 demo16.dat 324 oracle oinstall 2013-10-4 7 demo16.log 2235 oracle oinstall 2013-10-4 9 demo17.ctl 487 oracle oinstall 2013-10-4 9 demo17.dat 741 oracle oinstall 2013-10-3 1 demo2.log 1648 oracle oinstall 2013-10-4 7 demo3.log 1525 oracle oinstall 2013-10-4 8 demo4.ctl 141 oracle oinstall 2013-10-4 8 demo4.log 1657 oracle oinstall 2013-10-3 1 demo5.log 1560 oracle oinstall 2013-10-3 1 demo6.log 1641 oracle oinstall 2013-10-3 1 demo7.ctl 174 oracle oinstall 2013-10-3 1 demo7.log 1641 oracle oinstall 2013-10-3 1 demo8.log 1640 oracle oinstall 2013-10-3 1 demo9.ctl 290 oracle oinstall 2013-10-3 1 demo9.log 1809 oracle oinstall 2013-10-3 1 log.bbd 547
这不光适用于BLOB,也适用于CLOB.以这种方式使用SQLLDR来加载文本文件的目录会很容易.
将LOB数据加载到对象列.既然知道了如何将数据加载到我们自己创建的一个简单表中,可能会发现,有时需要将数据加载到一 个复杂的表中,其中可能有一个包含LOB的复杂对象类型(列).使用图像功能时这种情况最为常见.图像功能使用一个复杂的对象类型ORDSYS.ORDIMAGE来实现,我们需要告诉SQLLDR如何向其中加载数据.
要把一个LOB加载到一个ORDIMAGE类型的列中,首先必须对ORDIMAGE类型的结构有所了解.在SQL*Plus中使用要加载的一个目标表以及该表上的DESCRIBE,可以发现表中有一个名为IMAGE的ORDSYS.ORDIMAGE列,最终我们想在这一列中加载 IMAGE.SOURCE.LOCALDATA,只有安装并配置好interMedia,项目的例子才能正常工作;否则,数据类型ORDSYS.ORDIMAGE将是一 个未知类型:
jy@JINGYONG> create table image_load 2 (id number, 3 name varchar2(255), 4 image ordsys.ordimage 5 ); 表已创建。 SQL> desc jy.image_load Name Type Nullable Default Comments ----- --------------- -------- ------- -------- ID NUMBER Y NAME VARCHAR2(255) Y IMAGE ORDSYS.ORDIMAGE Y SQL> desc ordsys.ordimage Element Type -------------------- ---------------- SOURCE ORDSYS.ORDSOURCE HEIGHT INTEGER WIDTH INTEGER CONTENTLENGTH INTEGER FILEFORMAT VARCHAR2(4000) CONTENTFORMAT VARCHAR2(4000) COMPRESSIONFORMAT VARCHAR2(4000) MIMETYPE VARCHAR2(4000) INIT FUNCTION COPY PROCEDURE PROCESS PROCEDURE PROCESSCOPY PROCEDURE SETPROPERTIES PROCEDURE CHECKPROPERTIES FUNCTION GETHEIGHT FUNCTION GETWIDTH FUNCTION GETFILEFORMAT FUNCTION GETCONTENTFORMAT FUNCTION GETCOMPRESSIONFORMAT FUNCTION GETMETADATA FUNCTION PUTMETADATA PROCEDURE SETLOCAL PROCEDURE CLEARLOCAL PROCEDURE ISLOCAL FUNCTION GETUPDATETIME FUNCTION SETUPDATETIME PROCEDURE GETMIMETYPE FUNCTION SETMIMETYPE PROCEDURE GETCONTENTLENGTH FUNCTION GETCONTENT FUNCTION GETBFILE FUNCTION DELETECONTENT PROCEDURE GETDICOMMETADATA FUNCTION SETSOURCE PROCEDURE GETSOURCE FUNCTION GETSOURCETYPE FUNCTION GETSOURCELOCATION FUNCTION GETSOURCENAME FUNCTION IMPORT PROCEDURE IMPORTFROM PROCEDURE EXPORT PROCEDURE PROCESSSOURCECOMMAND FUNCTION OPENSOURCE FUNCTION CLOSESOURCE FUNCTION TRIMSOURCE FUNCTION READFROMSOURCE PROCEDURE WRITETOSOURCE PROCEDURE GETPROPERTIES PROCEDURE SQL> desc ordsys.ordsource Element Type -------------------- -------------- LOCALDATA BLOB SRCTYPE VARCHAR2(4000) SRCLOCATION VARCHAR2(4000) SRCNAME VARCHAR2(4000) UPDATETIME DATE LOCAL NUMBER SETLOCAL PROCEDURE CLEARLOCAL PROCEDURE ISLOCAL FUNCTION GETUPDATETIME FUNCTION SETUPDATETIME PROCEDURE SETSOURCEINFORMATION PROCEDURE GETSOURCEINFORMATION FUNCTION GETSOURCETYPE FUNCTION GETSOURCELOCATION FUNCTION GETSOURCENAME FUNCTION GETBFILE FUNCTION IMPORT PROCEDURE IMPORTFROM PROCEDURE EXPORT PROCEDURE GETCONTENTLENGTH FUNCTION GETSOURCEADDRESS FUNCTION GETLOCALCONTENT FUNCTION GETCONTENTINTEMPLOB PROCEDURE DELETELOCALCONTENT PROCEDURE OPEN FUNCTION CLOSE FUNCTION TRIM FUNCTION READ PROCEDURE WRITE PROCEDURE PROCESSCOMMAND FUNCTION
加载这种数据的控制文件可能如下所示:
[oracle@jy ~]$ cat demo20.ctl LOAD DATA INFILE * INTO TABLE image_load REPLACE FIELDS TERMINATED BY ',' (ID, NAME, file_name FILLER, IMAGE column object ( SOURCE column object ( LOCALDATA LOBFILE(file_name) TERMINATED BY EOF NULLIF file_name='NONE' ) ) ) BEGINDATA 1,psu3,psu3.jpg
这里引入了两个新构造:
COLUMN OBJECT:这会告诉SQLLDR这不是一个列名;而是列名的一部分.它不会映射到输入文件中的一个字段,只是用来构建正 确的对象列引用,从而在加载中使用.在前面的文件中有两个列对象标记,其中一个SOURCE嵌入在另一个SOURCE嵌入在 另一个IMAGE中.因此,根据我们的需要,要使用的列名是IMAGE.SOURCE.LOCALDATA.注意,我们没有加载这两个对象类型 的任何其他属性(例如,IMAGE.HEIGHT,IMAGE.CONTENTLENGTH和IMAGE.SOURCE.SRCTYPE).
NULL IF FILE_NAME = ‘NONE’:这会告诉SQLLDR,如果字段FILE_NAME包含单词NONE,则向对象列中加载一个NULL.
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo20.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 10:38:14 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 1 SQL> select * from jy.image_load ; ID NAME IMAGE --- ------- ----- 1 psu3 <Obje
使用sqlloader控制文件生成外部表创建语句的一个例子
使用sqlldr的控制文件生成创建外部表的语句
先创建一个目录
SQL> create or replace directory dir1 as '/home/oracle'; Directory created
根据sqlldr的控制文件来生成创建外部表的语句,先查看SQLLDR向DEPT表中加载批量数据的例子,如下：
[oracle@jy ~]$ cat demo21.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia
以下SQLLDR命令会为我们的外部表生成CREATE TABLE语句：
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo21.ctl external_table=generate_only SQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved.
EXTERNAL_TABLE参数有以下3个值：
NOT_USED:这是默认值.
EXECUTE:这个值说明SQLLDR不会生成并执行一个SQL INSERT语句;而是会创建一个外部表,并使用一个批量SQL语句来加载.
GENERATE_ONLY:这个值使得SQLLDR并不具体加载任何数据,而只是会生成所执行的SQL DDL和DML语句,并放到它创建的日志文 件中.
但是要注意:DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY.如果指定了DIRECT=TRUE,则会加载数据,而不会生成外部表.
使用GENERATE_ONLY时,可以在demo21.log文件中看到以下内容：
[oracle@jy ~]$ cat demo21.log SQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Control File: demo21.ctl Data File: demo21.ctl Bad File: demo21.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: External Table Table DEPT, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTER
下面为外部表生成CREATE TABLE语句：
CREATE TABLE statement for external table: ------------------------------------------------------------------------ CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT" ( "DEPTNO" NUMBER(2), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(14) )
SQLLDR已经登录到数据库;只有这样它才知道这个外部表定义中要用的具体数据类型(例如,DEPTNO是一个NUMBER(2)). SQLLDR根据数据字典来确定这些数据类型.
ORGANIZATION EXTERNAL子句告诉Oracle:这不是一个”正常”表.
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY DIR1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'DIR1':'demo21.bad'
LOGFILE 'demo21.log_xt'
READSIZE 1048576
SKIP 6
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255)
TERMINATED BY ",",
"DNAME" CHAR(255)
TERMINATED BY ",",
"LOC" CHAR(255)
TERMINATED BY ","
)
)
location
(
'demo21.ctl'
)
)REJECT LIMIT UNLIMITED
ORACEL_LOADER类型是目前支持的两种类型之一(Oracle9i中只支持这一种类型),另一种类型是ORACLE_DATAPUMP,
这是Oracle 10g及以上版本中Oracle的专用数据泵格式.这种格式不仅可以用于加载数据,也可以卸载数据.
这些访问参数显示了如何建立一个外部表,使之能像SQLLDR一样几乎以同样的方式处理文件:
RECORDS:记录默认以换行符结束,SQLLDR中的记录就是如此.
BADFILE:在刚创建的目录中建立了一个坏文件(无法处理的记录都记录到这个文件中).
LOGFILE:在当前的工作目录中记录了一个等价于SQLLDR日志文件的日志文件.
READSIZE:这是Oracle读取输入数据文件所用的默认缓冲区.在这里是1MB.如果采用专用服务器模式,这个内存来自PGA,如果 采用共享服务器模式,则来自SGA,它用于缓存输入数据文件中对应一个会话的信息.
SKIP 6:在确定了应该跳过输入文件中的多少记录.你可能会问:为什么有skip 6.是这样,在这个例子中使用了INFILE *;使用SKIP 6就是跳过控制文件本身来得到内嵌的数据.如果没有使用INFILE *,就根本不会有SKIP子句.
FIELDS TERMINATED BY:这与控制文件中的用法一样.不过,外部表增加LDRTRIM,这代表Loader TRIM.这是一种截断模式,模 拟了SQLLDR截断数据的默认做法.还有另外一些选项,包括LRTRIM,LTRIM和RTRIM,表示左截断/右截断空白符;NOTRIM表示保 留所有前导/尾随的空白符.
REJECT ROWS WITH ALL NULL FIELDS:这导致外部表会在坏文件中记录所有全空的行,而且不加载这些行.
列定义本身:这是有关所期望输入数据值的元数据.它们是所加载数据文件中的字符串,长度最多可达255个字符(SQLLDR的默 认大小),以逗号(,)结束,还可以选择用引号括起来.
location部分告诉Oracle所加载文件的文件名,在这里就是demo21.ctl,因为我们在原控制文件中使用了INFILE *.控制文件中的下一条语句是默认的INSERT,可以用于从外部表本身加载表:
INSERT statements used to load internal tables: ------------------------------------------------------------------------ INSERT /*+ append */ INTO DEPT ( DEPTNO, DNAME, LOC ) SELECT "DEPTNO", "DNAME", "LOC" FROM "SYS_SQLLDR_X_EXT_DEPT"
如果可能的话,这会执行一个逻辑上与直接路径加载等价的操作(如果可以遵循APPEND提示;如果存在触发器或外键约束,可能 不允许发生直接路径操作).
最后,在日志文件中,我们会看到一些语句,这些语句可以用于删除加载完成之后SQLLDR我我们创建的对象:
statements to cleanup objects created by previous statements: ------------------------------------------------------------------------ DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"
可能会看到日志文件中有一个CREATE DIRECTORY语句(也可能看不到).在生成外部表脚本期间,SQLLDR连接到数据库,并查询 数据字典来查看是否已经存在合适的目录.在这个例子中,由于有合适的目录,所以SQLLDR为我们选择了我们之前所创建的目录DIR1
CREATE DIRECTORY statements needed for files ------------------------------------------------------------------------ CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle'
通过sqldr生成外部全创建语句的完整语句如下:
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(14)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY DIR1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'DIR1':'demo21.bad'
LOGFILE 'demo21.log_xt'
READSIZE 1048576
SKIP 6
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255)
TERMINATED BY ",",
"DNAME" CHAR(255)
TERMINATED BY ",",
"LOC" CHAR(255)
TERMINATED BY ","
)
)
location
(
'demo21.ctl'
)
)REJECT LIMIT UNLIMITED;
执行创建外部表的语句:
jy@JINGYONG> CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT" 2 ( 3 "DEPTNO" NUMBER(2), 4 "DNAME" VARCHAR2(14), 5 "LOC" VARCHAR2(14) 6 ) 7 ORGANIZATION external 8 ( 9 TYPE oracle_loader 10 DEFAULT DIRECTORY DIR1 11 ACCESS PARAMETERS 12 ( 13 RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK 14 BADFILE 'DIR1':'demo21.bad' 15 LOGFILE 'demo21.log_xt' 16 READSIZE 1048576 17 SKIP 6 18 FIELDS TERMINATED BY "," LDRTRIM 19 REJECT ROWS WITH ALL NULL FIELDS 20 ( 21 "DEPTNO" CHAR(255) 22 TERMINATED BY ",", 23 "DNAME" CHAR(255) 24 TERMINATED BY ",", 25 "LOC" CHAR(255) 26 TERMINATED BY "," 27 ) 28 ) 29 location 30 ( 31 'demo21.ctl' 32 ) 33 )REJECT LIMIT UNLIMITED; 表已创建。
然后通过所创建的外部表向dept表插入数据:
jy@JINGYONG> INSERT /*+ append */ INTO DEPT 2 ( 3 DEPTNO, 4 DNAME, 5 LOC 6 ) 7 SELECT 8 "DEPTNO", 9 "DNAME", 10 "LOC" 11 FROM "SYS_SQLLDR_X_EXT_DEPT"; 已创建4行。 jy@JINGYONG> commit; 提交完成。
创建了日志文件
[oracle@jy ~]$ ls -lrt demo21.log_xt
-rw-r--r-- 1 oracle oinstall 737 Oct 5 00:43 demo21.log_xt
SQL> select * from jy.dept;
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS
------ -------------- -------------- ------------ ------------ ---------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
40 Finance Virginia
oracle中使用plsql来进行平面文件卸载
平面文件卸载
要把数据从一个系统移动到另一个系统,如果没有使用EXP/IMP或EXPDP/IMPDP(用于取代EXP和IMP的新数据泵),平面卸载就很有用.尽管使用EXP(DP)/IMP(DP)可以很好地将数据从一个系统移到另一个系统,但要求两个系统都是Oracle.
jy@JINGYONG> create or replace package unloader 2 authid current_user 3 as 4 /* Funaction run--unloads data from any query into a file 5 and creates a control file to reload that 6 data into another table 7 p_query=SQL query to "unload".May be virtually and query. 8 p_tname=Table to load into.Will be put into control file. 9 p_mode=REPLACE|APPEND|TRUNCATE--how to reload the data 10 p_dir=directory we will write the ctl and dat file to. 11 p_filename=name of file to write to.I will add .ctl and .dat to this 12 name 13 p_separator=field delimiter. I default this to a comma. 14 p_enclosure=what each field will be wrapped in 15 p_terminator=end of line character. We use this so we can unload 16 and reload data with newlines in it. I default to 17 "|\n"(a pipe and a newline together) and "|\r\n" on NT. 18 You need only to override this if you believe your 19 data will have that sequence in it. I ALWAYS add the 20 OS "end of line" marker to this sequence,you should not 21 */ 22 function run(p_query in varchar2, 23 p_tname in varchar2, 24 p_mode in varchar2 default 'REPLACE', 25 p_dir in varchar2, 26 p_filename in varchar2, 27 p_separator in varchar2 default ',', 28 p_enclosure in varchar2 default '"', 29 p_terminator in varchar2 default '|') 30 return number; 31 end; 32 / 程序包已创建。
注意这里使用了AUTHID CURRENT_USER.这样一来,这个包就可以在数据库上只安装一次,可由任何人用来卸载数据.要卸载数 据,只要求一点:对所卸载的表要有SELECT权限,另外对这个包有EXECUTE权限.如果这里没有使用AUTHID CURRENT_USER,则需 要这个包的所有者在要卸载的所有表上都有直接的SELECT权限.
注意:SQL会以这个例程的调用者的权限执行.不过,所有PL/SQL调用都会以所调用例程定义者的权限运行;因此,对于具有这个 包执行权限的所有人,都隐含地允许他使用UTL_FILE写至一个目录.
包体如下.我们使用UTL_FILE来写一个控制文件和一个数据文件.DBMS_SQL用于动态地处理所有查询.我们在查询中使用了一个数据类型:VARCHAR2(4000).这说明,如果LOB大于4,000字节,就不能使用这个方法来卸载LOB.不过,只需使用DBMS_LOB.SUBSTR,我们就可以使用这个方法卸载任何最多4,000字节的LOB.另外,由于我们用一个VARCHAR2作为惟一的输出数据类型,所以可以处理长度最多2,000字节的RAW(4,000个十六进制字符),除了LONG RAW和LOB外,这对其他类型都足够了.另 外,如果查询引用了一个非标量属性(一个复杂的对象类型,嵌套表等),则不能使用这个简单的实现.以下是一个90%可用的解决方案,这说明90%的情况下它都能解决问题:
create or replace package body unloader
as
g_theCursor integer default dbms_sql.open_cursor;
g_descTbl dbms_sql.desc_tab;
g_nl varchar2(2) default chr(10);
以上是这个包体中使用的一些全局变量.全局游标打开一次,即第一次引用这个包时打开,它会一起打开,直到我们注销.这就 不用每次调用这个包时都要得到一个新游标,从而避免相应的开销.G_DESCTBL是一个PL/SQL表,将保存DBMS_SQL.DESCRIBE调用的输出.G_NL是一个换行符.在需要内嵌有换行符的串中会使用这个变量.我们无需针对Windows调整这个变量,UTL_FILE会看到字符串中的CHR(10),并自动为我们将其转换为一个回车/换行符.
接下来,我们使用了一个很小的便利函数,它能将字符转换为一个十六进制数,为此使用了内置函数:
function to_hex(p_str in varchar2) return varchar2
is
begin
return to_char(ascii(p_str),'fm0x');
end;
最后,我们又创建了另一个便利函数IS_WINDOWS,它会返回TRUE或FALSE,这取决于我们所用的是否是Windows平台,如果在 Windows平台上,行结束符就是一个两字符的串,而大多数其他平台上的行结束符只是单字符.我们使用了内置DBMS_UTILITY函 数:GET_PARAMETER_VALUE,可以用这个函数读取几乎所有参数.我们获取了CONTROL_FILES参数,并查找其中是否存在\,如果 有,则说明在Windows平台上:
function is_windows return boolean
is
l_cfiles varchar2(4000);
l_dummy number;
begin
if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0) then
return instr(l_cfiles,'\')>0;
else
return FALSE;
end if;
end;
注意:IS_WINDOWS函数依赖于CONTROL_FILES参数中使用了\.要记住,其中也有可能使用/,但这极为少见.
下面的过程会创建一个控制文件来重新加载卸载的数据,这里使用了DBMS_SQL.DESCRIBE_COLUMN生成的DESCRIBE表.它会为我们处理有关操作系统的细节,如操作系统是否使用回车/换行符(用于STR属性):
procedure dump_ctl(
p_dir in varchar2,
p_filename in varchar2,
p_tname in varchar2,
p_mode in varchar2,
p_separator in varchar2,
p_enclosure in varchar2,
p_terminator in varchar2)
is
l_output utl_file.file_type;
l_sep varchar2(5);
l_str varchar2(5):=chr(10);
begin
if(is_windows) then
l_str:=chr(13)||chr(10);
end if;
l_output:=utl_file.fopen(p_dir,p_filename|\'.ctl','w');
utl_file.put_line(l_output,'load data');
utl_file.put_line( l_output, 'infile ''' ||
p_filename || '.dat'' "str x''' ||
utl_raw.cast_to_raw( p_terminator ||
l_str ) || '''"' );
utl_file.put_line( l_output, 'into table ' || p_tname );
utl_file.put_line( l_output, p_mode );
utl_file.put_line( l_output, 'fields terminated by X''' ||
to_hex(p_separator) ||
''' enclosed by X''' ||
to_hex(p_enclosure) || ''' ' );
utl_file.put_line( l_output, '(' );
for i in 1 .. g_descTbl.count loop
if ( g_descTbl(i).col_type = 12 ) then
utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
' date ''ddmmyyyyhh24miss'' ');
else
utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
' char(' ||
to_char(g_descTbl(i).col_max_len*2) ||' )' );
end if;
l_sep := ','||g_nl ;
end loop;
utl_file.put_line( l_output, g_nl || ')' );
utl_file.fclose( l_output );
end;
这是一个简单的函数,会返回一个加引号的串(使用所选择的包围字符作为引号);注意,串不只是包含字符,倘若串中还存在包围字符,还会把包围字符重复两次,从而保留这些包围字符:
function quote(p_str in varchar2,p_enclosure in varchar2)
return varchar2
is
begin
return p_enclosure||
replace(p_str,p_enclosure,p_enclosure||p_enclosure)||
p_enclosure;
end;
下面是主函数RUN.因为这个函数相当大,会列出函数的解释:
function run(p_query in varchar2,
p_tname in varchar2,
p_mode in varchar2 default 'REPLACE',
p_dir in varchar2,
p_filename in varchar2,
p_separator in varchar2 default ',',
p_enclosure in varchar2 default '"',
p_terminator in varchar2 default '|')
return number
is
l_output utl_file.file_type;
l_columnValue varchar2(4000);
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_line long;
l_datefmt varchar2(255);
l_descTbl dbms_sql.desc_tab;
begin
我们将NLS_DATE_FORMAT保存到一个变量中,从而在将数据转储到磁盘上时可以把它改为一种保留日期和时间的格式.采用这种方式,我们会保留日期的时间分量.然后建立一个异常块,从而在接收到错误时重置NLS_DATE_FORMAT:
select value into l_datefmt
from nls_session_parameters where parameter='NLS_DATE_FORMAT';
/* 设置日期格式为是一个大数字字符串避免所有的NLS问题并保留时间和日期 */
execute immediate 'alter session set nls_date_format="yyyymmddhh24miss"';
/* 设置一个异常块在出现任何错误时重新设置日期格式 */
接下来,解析并描述这个查询.将G_DESCTBL设置为L_DESCTBL来重置全局表;否则,其中会包含前一个DESCRIBE生成的数据,而不只是当前查询生成的数据.一旦完成,再调用DUMP_CTL具体创建控制文件:
begin
/*
解析和描述这个查询.将重设descTbl为了一个空表
*/
dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );
g_descTbl := l_descTbl;
dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );
/*
创建一个控制文件来重新加载数据到你所期望的表中
*/
dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,
p_enclosure, p_terminator );
/*
绑定每一个列转换为一个长度为4000字符串.我们不在乎我们获取是数字还是日期,因为它们都可以
看成是一个字符串
*/
现在可以将具体数据转储到磁盘上了.首先将每个列定义为VARCHAR2(4000)来获取数据.所有类型(NUMBER,DATE,RAW)都要 转换为VARCHAR2.在此之后,执行查询来准备获取:
for i in 1 .. l_colCnt loop
dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);
end loop;
/*
运行这个查询--忽略执行的输出,它只是来验证一个插入/更新或删除操作
*/
现在打开数据文件准备写,从查询获取所有行,并将其打印到数据文件:
l_cnt := dbms_sql.execute(g_theCursor);
/*
打开文件然后将带有分隔符的数据写入到文件中
*/
l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',32760 );
loop
exit when ( dbms_sql.fetch_rows(g_theCursor) < = 0 );
l_separator := '';
l_line := null;
for i in 1 .. l_colCnt loop
dbms_sql.column_value( g_theCursor, i,l_columnValue );
l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );
l_separator := p_separator;
end loop;
l_line := l_line || p_terminator;
utl_file.put_line( l_output, l_line );
l_cnt := l_cnt+1;
end loop;
utl_file.fclose( l_output );
最后,将日期格式设置回原来的样子(如果先前的代码由于某种原因失败了,异常块也会做这个工作),并返回:
/*
现在重新设置日期格式并将返回的行数据写入到输出文件中
*/
execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
return l_cnt;
exception
/*
如果发现任何错误会重新设置日期并重新触发错误
*/
when others then
execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
RAISE;
end;
end run;
end unloader;
执行完整的编译
SQL> create or replace package body unloader
2 as
3 g_theCursor integer default dbms_sql.open_cursor;
4 g_descTbl dbms_sql.desc_tab;
5 g_nl varchar2(2) default chr(10);
6
7 function to_hex(p_str in varchar2) return varchar2
8 is
9 begin
10 return to_char(ascii(p_str),'fm0x');
11 end;
12
13 function is_windows return boolean
14 is
15 l_cfiles varchar2(4000);
16 l_dummy number;
17 begin
18 if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0) then
19 return instr(l_cfiles,'\')>0;
20 else
21 return FALSE;
22 end if;
23 end;
24
25 procedure dump_ctl(
26 p_dir in varchar2,
27 p_filename in varchar2,
28 p_tname in varchar2,
29 p_mode in varchar2,
30 p_separator in varchar2,
31 p_enclosure in varchar2,
32 p_terminator in varchar2)
33 is
34 l_output utl_file.file_type;
35 l_sep varchar2(5);
36 l_str varchar2(5):=chr(10);
37 begin
38 if(is_windows) then
39 l_str:=chr(13)||chr(10);
40 end if;
41 l_output:=utl_file.fopen(p_dir,p_filename||'.ctl','w');
42 utl_file.put_line(l_output,'load data');
43 utl_file.put_line( l_output, 'infile ''' ||
44 p_filename || '.dat'' "str x''' ||
45 utl_raw.cast_to_raw( p_terminator ||
46 l_str ) || '''"' );
47 utl_file.put_line( l_output, 'into table ' || p_tname );
48 utl_file.put_line( l_output, p_mode );
49 utl_file.put_line( l_output, 'fields terminated by X''' ||
50 to_hex(p_separator) ||
51 ''' enclosed by X''' ||
52 to_hex(p_enclosure) || ''' ' );
53 utl_file.put_line( l_output, '(' );
54
55 for i in 1 .. g_descTbl.count loop
56 if ( g_descTbl(i).col_type = 12 ) then
57 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
58 ' date ''ddmmyyyyhh24miss'' ');
59 else
60 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
61 ' char(' ||
62 to_char(g_descTbl(i).col_max_len*2) ||' )' );
63 end if;
64 l_sep := ','||g_nl ;
65 end loop;
66 utl_file.put_line( l_output, g_nl || ')' );
67 utl_file.fclose( l_output );
68 end;
69
70 function quote(p_str in varchar2,p_enclosure in varchar2)
71 return varchar2
72 is
73 begin
74 return p_enclosure||
75 replace(p_str,p_enclosure,p_enclosure||p_enclosure)||
76 p_enclosure;
77 end;
78
79
80 function run(p_query in varchar2,
81 p_tname in varchar2,
82 p_mode in varchar2 default 'REPLACE',
83 p_dir in varchar2,
84 p_filename in varchar2,
85 p_separator in varchar2 default ',',
86 p_enclosure in varchar2 default '"',
87 p_terminator in varchar2 default '|')
88 return number
89 is
90 l_output utl_file.file_type;
91 l_columnValue varchar2(4000);
92 l_colCnt number default 0;
93 l_separator varchar2(10) default '';
94 l_cnt number default 0;
95 l_line long;
96 l_datefmt varchar2(255);
97 l_descTbl dbms_sql.desc_tab;
98 begin
99 select value into l_datefmt
100 from nls_session_parameters where parameter='NLS_DATE_FORMAT';
101 /* 设置日期格式为是一个大数字字符串避免所有的NLS问题并保留时间和日期 */
102 execute immediate 'alter session set nls_date_format="yyyymmddhh24miss"';
103 /* 设置一个异常块在出现任何错误时重新设置日期格式 */
104 begin
105 /*
106 解析和描述这个查询.将重设descTbl为了一个空表
107 */
108 dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );
109 g_descTbl := l_descTbl;
110 dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );
111 /*
112 创建一个控制文件来重新加载数据到你所期望的表中
113 */
114 dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,
115 p_enclosure, p_terminator );
116 /*
117 绑定每一个列转换为一个长度为4000字符串.我们不在乎我们获取是数字还是日期,因为它们都可以
118 看成是一个字符串
119 */
120 for i in 1 .. l_colCnt loop
121 dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);
122 end loop;
123 /*
124 运行这个查询--忽略执行的输出,它只是来验证一个插入/更新或删除操作
125 */
126 l_cnt := dbms_sql.execute(g_theCursor);
127 /*
128 打开文件然后将带有分隔符的数据写入到文件中
129 */
130 l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',32760 );
131 loop
132 exit when ( dbms_sql.fetch_rows(g_theCursor) < = 0 );
133 l_separator := '';
134 l_line := null;
135 for i in 1 .. l_colCnt loop
136 dbms_sql.column_value( g_theCursor, i,l_columnValue );
137 l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );
138 l_separator := p_separator;
139 end loop;
140 l_line := l_line || p_terminator;
141 utl_file.put_line( l_output, l_line );
142 l_cnt := l_cnt+1;
143 end loop;
144 utl_file.fclose( l_output );
145 /*
146 现在重新设置日期格式并将返回的行数据写入到输出文件中
147 */
148 execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
149 return l_cnt;
150 exception
151 /*
152 如果发现任何错误会重新设置日期并重新触发错误
153 */
154 when others then
155 execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
156 RAISE;
157 end;
158 end run;
159 end unloader;
160 /
Package body created
要运行这个代码,可以使用以下命令(要注意,当然以下代码需要你将SCOTT.EMP的SELECT权限授予某个角色,或者直接授予你 自己):
jy@JINGYONG> set serveroutput on jy@JINGYONG> create or replace directory dir1 as '/home/oracle'; 目录已创建。 jy@JINGYONG> declare 2 l_rows number; 3 begin 4 l_rows:=unloader.run 5 (p_query=>'select * from scott.emp order by empno', 6 p_tname=>'emp', 7 p_mode=>'replace', 8 p_dir=>'DIR1', 9 p_filename=>'emp', 10 p_separator=>',', 11 p_enclosure=>'"', 12 p_terminator=>'~'); 13 dbms_output.put_line(to_char(l_rows)||' rows extracted to ascii file'); 14 end; 15 / 14 rows extracted to ascii file PL/SQL 过程已成功完成。
由此生成的emp.ctl控制文件显示如下(注意,括号里粗体显示的数字并不是真的包括在文件中:加上这些数字只是为了便于引 用):
[oracle@jy ~]$ cat emp.ctl load data infile 'emp.dat' "str x'7E0A'" into table emp replace fields terminated by X'2c' enclosed by X'22' ( EMPNO char(44 ), ENAME char(20 ), JOB char(18 ), MGR char(44 ), HIREDATE date 'ddmmyyyyhh24miss' , SAL char(44 ), COMM char(44 ), DEPTNO char(44 ) )
关于这个控制文件,要注意以下几点:
第2行:使用了SQLLDR的STR特性.可以指定用什么字符或串来结束一个记录.这样就能很容易地加载有内嵌换行符的数据.串x ‘7E0A’只是换行符后面跟一个波浪号”~”.
第5行:使用了我们的分隔符和包围符.这里没有使用OPTIONALLY ENCLOSED BY,因为我们将把原数据中包围字符的所有出现都 重复两次,再把每个字段括起来.
第11行:使用了一个很大的”数值”日期格式.这有两个作用:可以避免与日期有关的所有NLS问题,还可以保留日期字段的时间分量.
从前面的代码生成的原始数据（emp.dat）文件如下：
[oracle@jy ~]$ cat emp.dat "7369","SMITH","CLERK","7902","19801217000000","800","","20"~ "7499","ALLEN","SALESMAN","7698","19810220000000","1600","300","30"~ "7521","WARD","SALESMAN","7698","19810222000000","1250","500","30"~ "7566","JONES","MANAGER","7839","19810402000000","2975","","20"~ "7654","MARTIN","SALESMAN","7698","19810928000000","1250","1400","30"~ "7698","BLAKE","MANAGER","7839","19810501000000","2850","","30"~ "7782","CLARK","MANAGER","7839","19810609000000","2450","","10"~ "7788","SCOTT","ANALYST","7566","19870419000000","3000","","20"~ "7839","KING","PRESIDENT","","19811117000000","5000","","10"~ "7844","TURNER","SALESMAN","7698","19810908000000","1500","0","30"~ "7876","ADAMS","CLERK","7788","19870523000000","1100","","20"~ "7900","JAMES","CLERK","7698","19811203000000","950","","30"~ "7902","FORD","ANALYST","7566","19811203000000","3000","","20"~ "7934","MILLER","CLERK","7782","19820123000000","1300","","10"~
emp.dat文件中要注意的问题如下:
每个字段都用包围字符括起来.
DATE卸载为很大的数字>
这个文件中的数据行按要求以一个～结束>
现在可以使用SQLLDR很容易地重新加载这个数据,你可以向SQLLDR命令行增加你认为合适的选项.
使用oracle外部表进行数据泵卸载数据
数据泵卸载
Oracle9i引入了外部表,作为向数据库中读取数据的一种方法.Oracle 10g则从另一个方向引入了这个特性,可以使用CREATE TABLE语句创建外部数据,从而由数据库卸载数据.从Oracle 10g起,这个数据从一种专用二进制格式抽取,这种格式称为数据 泵格式(Data Pump format),Oracle提供的EXPDP和IMPDP工具将数据从一个数据库移动另一个数据库所用的就是这种格式.
使用外部表卸载确实相当容易,就像使用CREATE TABLE AS SELECT语句一样简单.首先,需要一个DIRECTORY对象:
jy@JINGYONG> create or replace directory dir1 as '/home/oracle'; 目录已创建。
现在,准备使用一个简单的SELECT语句向这个目录中卸载数据,例如:
jy@JINGYONG> create table my_object_unload
2 organization external
3 (
4 type oracle_datapump
5 default directory DIR1
6 location('myobjects.dat')
7 )
8 as
9 select * from all_objects;
表已创建。
[oracle@jy ~]$ ls -lrt myobjects.dat
-rw-r----- 1 oracle oinstall 4923392 Oct 5 04:09 myobjects.dat
从上面的信息可知已经生成了myobjects.dat文件
获取外部表my_object_unload的创建语句
SQL> select dbms_metadata.get_ddl( 'TABLE', 'MY_OBJECT_UNLOAD' ) from dual;
DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
CREATE TABLE "JY"."MY_OBJECT_UNLOAD"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DIR1"
LOCATION
( 'myobjects.dat'
)
)
现在把生成的myobjects.dat文件拷贝到你要加载数据的服务器上创建目录(directory)并在要加载数据的用户下使用上面创建外部表的语句(记得要修改DEFAULT DIRECTORY “DIR1″指向你所存放myobjects.dat的目录)来创建外部表
CREATE TABLE "MY_OBJECT_UNLOAD"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DIR1"
LOCATION
( 'myobjects.dat'
)
);
/
SQL> select * from my_object_unload ;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
------- ---------------------- -------------- ---------- -------------- ------------------- ----------- ------- ------ ------------------- ------- --------- --------- ---------
SYS ICOL$ 20 2 TABLE 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_USER1 44 44 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
SYS CON$ 28 28 TABLE 2005-6-30 1 2013-3- 24 12: 2005-06-30:19:10:16 VALID N N N
SYS UNDO$ 15 15 TABLE 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
SYS C_COBJ# 29 29 CLUSTER 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_OBJ# 3 3 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
SYS PROXY_ROLE_DATA$ 25 25 TABLE 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_IND1 39 39 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_CDEF2 51 51 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_PROXY_ROLE_DATA$_1 26 26 INDEX 2005-6-30 1 2005-6- 30 19: 2005-06-30:19:10:16 VALID N N N
通过dbms_rowid.rowid_create来生成rowid
dbms_rowid.rowid_create函数,此函数可以创建一个rowid,它的语法如下
DBMS_ROWID.ROWID_CREATE (
rowid_type IN NUMBER,
object_number IN NUMBER,
relative_fno IN NUMBER,
block_number IN NUMBER,
row_number IN NUMBER)
RETURN ROWID;
rowid_type Type (restricted or extended).
Set the rowid_type parameter to 0 for a restricted ROWID. Set
it to 1 to create an extended ROWID.
If you specify rowid_type as 0, then the required object_
number parameter is ignored, and ROWID_CREATE returns a
restricted ROWID.
object_number Data object number (rowid_object_undefined for restricted).
relative_fno Relative file number.
block_number Block number in this file.
row_number Returns row number in this block.
下面是使用dbms_rowid.rowid_create的一个例子
sys@JINGYONG> select rowid, t.*,dbms_rowid.rowid_relative_fno(rowid) relative_fno ,
2 dbms_rowid.rowid_object(rowid) object_number ,
3 dbms_rowid.rowid_block_number(rowid) block_number ,
4 dbms_rowid.rowid_row_number(rowid) row_number
5 from t where rownum<2;
ROWID ID TEXT RELATIVE_FNO OBJECT_NUMBER BLOCK_NUMBER ROW_NUMBER
------------------ ---------- ------------------------------ ------------ ------------- ------------ ----------
AAANB1AABAAAPAaAAA 20 ICOL$ 1 53365 61466 0
sys@JINGYONG> select data_object_id from dba_objects where object_id=53365;
DATA_OBJECT_ID
--------------
53365
sys@JINGYONG> select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid fr
om dual;
CREATE_ROWID
------------------
AAANB1AABAAAPAaAAA
通过dbms_rowid.rowid_create(1,53365,1,61466,0)构造的rowid与原始的rowid是一样的.
参数的意思：1表示rowid的类型为扩展rowid，类型为1； data_object_id 为53365，也就是文中t表对象的id； 数据文件id为1 ，即system表空间文件；块的编号为61466号； 行数为第一行（第一行为值0） 。
通过dbms_rowid.rowid_create函数查询被锁定的具体行
查询被锁的会话和持有锁的会话，如果是exclusive锁则xidusn非零的表示已经执行并持有锁
column o_name format a10
column lock_type format a20
column object_name format a15
select rpad(oracle_username,10) o_name,
session_id sid,
decode(locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,
object_name,
all_objects.object_id,
xidusn,
xidslot,
xidsqn
from v$locked_object,all_objects
where v$locked_object.object_id=all_objects.object_id;
O_NAME SID LOCK_TYPE OBJECT_NAME OBJECT_ID XIDUSN XIDSLOT XIDSQN
---------- ------- -------------------- --------------- -------------- ---------- ------------ ----------
SYS 145 Row Exclusive T 53365 8 17 724
SYS 148 Row Exclusive T 53365 0 0 0
下面的查询可以得到被锁定的session,被锁定的对象id和row number
select sid,
row_wait_obj# object_id,
row_wait_file# file_no,
row_wait_block# block,
row_wait_row# row_num
from v$session
where row_wait_obj#=&object_id;
Enter value for object_id: 53365
old 7: where row_wait_obj#=&object_id
new 7: where row_wait_obj#=53365
SID OBJECT_ID FILE_NO BLOCK ROW_NUM
---------- -------------- ---------- ---------- ----------
148 51207 1 61466 0
如果要对应行rowid则：
sys@JINGYONG> select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid fr om dual; CREATE_ROWID ------------------ AAANB1AABAAAPAaAAA
有了rowid，具体的行就能定位了
oracle的并行管道函数
并行管道函数
这个例子中要使用两个表：T1和T2。T1是先读的表，T2表用来插入这个信息。我们要用的两个表如下：
sys@JINGYONG> create table t1 2 as 3 select object_id id,object_name text 4 from all_objects; 表已创建。 sys@JINGYONG> begin 2 dbms_stats.set_table_stats 3 (user,'T1',numrows=>100000000,numblks=>100000); 4 end; 5 / PL/SQL 过程已成功完成。 sys@JINGYONG> create table t2 2 as 3 select t1.*,0 session_id 4 from t1 5 where 1=0; 表已创建。
这里使用DBMS_STATS来骗过优化器，让它以为输入表中有10,000,000行，而且占用了100,000个数据库块。在此模拟 一个大表。第二个表T2是第一个表的一个副本，只是在结构中增加了一个SESSION_ID列。可以通过它具体看到是否发生了并行化。接下来，需要建立管道函数返回的对象类型。在这个例子中，对象类型类似于T2：
sys@JINGYONG> create or replace type t2_type 2 as object 3 ( 4 id number, 5 text varchar2(30), 6 session_id number 7 ); 8 / 类型已创建。 sys@JINGYONG> create or replace type t2_tab_type as table of t2_type; 2 / 类型已创建。
现在这个过程是一个生成行的函数。它接收数据作为输入，并在一个引用游标（ref cursor）中处理。这个函数返回一个 T2_TAB_TYPE，这就是我们刚才创建的对象类型。这是一个PARALLEL_ENABLED（启用子并行）的管道函数。在此使用了分区 （partition）子句，这就告诉Oracle：以任何最合适的方式划分或分解数据。我们不需要对数据的顺序做任何假设。
在此，我们只想划分数据。数据如何划分对于我们的处理并不重要，所以定义如下：
sys@JINGYONG> create or replace function parallel_pipelined(l_cursor in sys_refcursor) 2 return t2_tab_type 3 pipelined 4 parallel_enable(partition l_cursor by any) 5 is 6 l_session_id number; 7 TYPE type_t1_data IS TABLE OF t1%ROWTYPE INDEX BY PLS_INTEGER; 8 l_t1 type_t1_data; 9 10 begin 11 select sid into l_session_id 12 from v$mystat 13 where rownum=1; 14 loop 15 fetch l_cursor bulk collect into l_t1;--用bulk collect来一次性获取数据 16 exit when l_t1.count=0; 17 for i in 1 .. l_t1.count loop 18 pipe row(t2_type(l_t1(i).id,l_t1(i).text,l_session_id)); 19 end loop; 20 null; 21 end loop; 22 close l_cursor; 23 return; 24 end; 25 / Function created
或者用下面的过程来一行一行来获取
create or replace function parallel_pipelined( l_cursor in sys_refcursor ) return t2_tab_type pipelined parallel_enable ( partition l_cursor by any ) is l_session_id number; l_rec t1%rowtype; begin select sid into l_session_id from v$mystat where rownum =1; loop fetch l_cursor into l_rec; exit when l_cursor%notfound; pipe row(t2_type(l_rec.id,l_rec.text,l_session_id)); end loop; close l_cursor; return; end;
这样就创建了函数。我们准备并行地处理数据，让Oracle根据可用的资源来确定最合适的并行度：
SQL> insert /*+ append */ 2 into t2(id,text,session_id) 3 select * 4 from table(parallel_pipelined 5 (CURSOR(select /*+ parallel(t1) */ * 6 from t1 ) 7 )) 8 ; 50333 rows inserted SQL> commit; Commit complete
为了查看这里发生了什么，可以查询新插入的数据，并按SESSION_ID分组，先来看使用了多少个并行执行服务器，再看每个并行 执行服务器处理了多少行：
SQL> select session_id,count(*) from t2 group by session_id;
SESSION_ID COUNT(*)
---------- ----------
136 31006
145 19327
显然，对于这个并行操作的SELECT部分，我们使用了2个并行执行服务器,可以看到，Oracle对我们的过程进行了并行化
oracel分区之索引分区
索引分区
索引与表类似，也可以分区。对索引进行分区有两种可能的方法：
随表对索引完成相应的分区：这也称为局部(本地)分区索引（locally pertitioned index）。每个表分区都有一个索引分区， 而且只索引该表分区。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。
按区间对索引分区：这也称为全局分区索引（globally partitioned index）。在此，索引按区间分区（或者在Oracle 10g中该 可以按散列分区），一个索引分区可能指向任何（和所有）表分区。
对于全局分区索引，要注意实际上索引分区数可能不同于表分区数。
由于全局索引只按区间或散列分区，如果希望有一个列表或组合分区索引，就必须使用局部索引。局部索引会使用底层表相同的 机制分区。
注意 全局索引的散列分区是Oracle 10g Release 1及以后的版本中才有的新特性。在Oracle9i及以前的版本中，只能按区间进 行全局分区。
局部索引
Oracle划分了以下两类局部索引：
局部前缀索引（local prefixed index）：在这些索引中，分区键在索引定义的前几列上。例如，一个表在名为LOAD_DATE的列 上进行区间分区，该表上的局部前缀索引就是LOAD_DATE作为其索引列列表中的第一列。
局部非前缀索引（local nonprefixed index）：这些索引不以分区键作为其列列表的前几列。索引可能包含分区键列，也可能 不包含。
这两类索引都可以利用分区消除，它们都支持惟一性（只有前缀索引包含分区键）等。事实上，使用局部前缀索引的查询总允许 索引分区消除，而使用局部非前缀索引的查询可能不允许。正是由于这个原因，所以在某些人看来局部非前缀索引“更慢”，它 们不能保证分区消除（但确实可以支持分区消除）。
如果查询中将索引用作访问表的初始路径，那么从本质来讲，局部前缀索引并不比局部非前缀索引更好。也就是说，如何查询把 “扫描一个索引”作为第一步，那么前缀索引和非前缀索引之间并没有太大的差别。
分区消除行为
如果查询首先访问索引，它是否能消除分区完全取决于查询中的谓词。下面的代码创建了一个表PARTITIONED_TABLE，它在一个 数字列A上进行区间分区，使得小于2的值都在分区PART_1中，小于3的值则都在分区PART_2中：
sys@JINGYONG> create table partitioned_table 2 (a int, 3 b int, 4 data varchar2(20) 5 ) 6 partition by range(a) 7 ( 8 partition part_1 values less than(2) tablespace p1, 9 partition part_2 values less than(3) tablespace p2 10 ) 11 ; 表已创建。
然后我们创建一个局部前缀索引LOCAL_PREFIXED和一个局部非前缀索引LOCAL_NONPREFIXED。注意，非前缀索引在其定义中没有 以A作为其最前列，这是这一点使之成为一个非前缀索引：
sys@JINGYONG> create index local_prefixed on partitioned_table(a,b) local; 索引已创建。 sys@JINGYONG> create index local_nonprefixed on partitioned_table(b) local; 索引已创建。
接下来，我们向一个分区中插入一些数据，并收集统计信息：
sys@JINGYONG> insert into partitioned_table 2 select mod(rownum-1,2)+1,rownum,'x' from all_objects; 已创建50324行。 sys@JINGYONG> commit; 提交完成。 sys@JINGYONG> exec dbms_stats.gather_table_stats(user,'PARTITIONED_TABLE',estima te_percent=>100,method_opt=>'for all columns size repeat',cascade=>true); PL/SQL 过程已成功完成。
将表空间P2脱机，其中包含用于表和索引的PART_2分区：
sys@JINGYONG> alter tablespace p2 offline; 表空间已更改。
表空间P2脱机后，Oracle就无法访问这些特定的索引分区。这就好像是我们遭遇了“介质故障”，导致分区不可用。现在我们查 询这个表，来看看不同的查询需要哪些索引分区。第一个查询编写为允许使用局部前缀索引：
sys@JINGYONG> select * from partitioned_table where a=1 and b=1;
A B DATA
---------- ---------- --------------------
1 1 x
这个查询成功了，通过查看解释计划，可以看到这个查询为什么能成功。我们将使用内置包DBMS_XPLAN来查看这个查询访问了哪 些分区。输出中的PSTART (分区开始)和PSTOP（分区结束）这两列准确地显示出，这个查询要想成功需要哪些分区必须在线而且 可用：
sys@JINGYONG> delete from plan_table;
已删除0行。
sys@JINGYONG> explain plan for
2 select * from partitioned_table where a=1 and b=1;
已解释。
sys@JINGYONG> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- ---------
Plan hash value: 1622054381
--------------------------------------------------------------------------------------------------------------- ---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------- ---------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1 AND "B"=1)
已选择15行。
因此，使用LOCAL_PREFIXED的查询成功了。优化器能消除LOCAL_PREFIXED的PART_2不予考虑，因为我们在查询中指定了A=1，而 且在计划中可以清楚地看到PSTART和PSTOP都等于1.分区消除帮助了我们。不过，第二个查询却失败了
sys@JINGYONG> select * from partitioned_table where b=1; ERROR: ORA-00376: file 7 cannot be read at this time ORA-01110: data file 7: '/u01/app/oracle/product/10.2.0/oradata/jingyong/jingyon g/p201.dbf'
我们可以通过查看这个查询的执行计划看到为什么会失败
sys@JINGYONG> delete from plan_table;
已删除4行。
sys@JINGYONG> explain plan for
2 select * from partitioned_table where b=1;
已解释。
sys@JINGYONG> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------
Plan hash value: 440752652
--------------------------------------------------------------------------------------------------------------- ---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 9 | 4 (0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 9 | 4 (0)| 00:00:01 | 1 | 2 |
|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 3 (0)| 00:00:01 | 1 | 2 |
--------------------------------------------------------------------------------------------------------------- ---------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=1)
已选择15行。
在此，优化器不能不考虑LOCAL_NONPREFIXED的PART_2，为了查看是否有B=1，索引的PART_1和PART_2都必须检查。在此，局部非 前缀索引存在一个性能问题：它不能像前缀索引那样，在谓词中使用分区键。并不是说前缀索引更好，我们的意思是：要使用非 前缀索引，必须使用一个允许分区消除的查询
sys@JINGYONG> drop index local_prefixed;
索引已删除。
sys@JINGYONG> select * from partitioned_table where a=1 and b=1;
A B DATA
---------- ---------- --------------------
1 1 x
它会成功，但是正如我们所见，这里使用了先前失败的索引。该计划显示出，在此Oracle能利用分区消除，有了谓词A=1，就有 了足够的信息可以让数据库消除索引分区PART_2而不予考虑：
sys@JINGYONG> delete from plan_table;
已删除4行。
sys@JINGYONG> explain plan for
2 select * from partitioned_table where a=1 and b=1;
已解释。
sys@JINGYONG> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- ---------
Plan hash value: 904532382
--------------------------------------------------------------------------------------------------------------- ---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------- ---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=1)
3 - access("B"=1)
已选择16行。
注意PSTART和PSTOP列值为1和1.这就证明，优化器甚至对非前缀局部索引也能执行分区消除。
如果你频繁地用以下查询来查询先前的表：
select … from partitioned_table where a = :a and b = :b;
select … from partitioned_table where b = :b;
可以考虑在（b,a）上使用一个局部非前缀索引。这个索引对于前面的两个查询都是有用的。（a,b）上的局部前缀索引只对第一 个查询有用。
这里的关键是，不必对非前缀索引退避三舍，也不要认为非前缀索引是主要的性能障碍。如果你有多个如前所列的查询（可以得 益于非前缀索引），就应该考虑使用一个非前缀索引。重点是，要尽可能保证查询包含的谓词允许索引分区消除。使用前缀局部 索引可以保证这一点，使用非前缀索引则不能保证。还要考虑如何使用索引。如果将索引用作查询计划中的第一步，那么这两种 类型的索引没有多少差别。
局部索引和惟一约束
为了保证惟一性（这包括UNIQUE约束或PRIMARY KEY约束），如果你想使用一个局部索引来保证这个约束，那么分区键必须包括 在约束本身中。在我看来，这是局部索引的最大限制。Oracle只保证索引分区内部的惟一性，而不能跨分区。这说明什么呢？例 如，这意味着不能一方面在一个TIMESTAMP字段上执行区间分区，而另一方面在ID上有一个主键（使用一个局部分区索引来保证 ）。Oracle会利用全局索引来保证惟一性。
在下面的例子中，我们将创建一个区间分区表，它按一个名为LOAD_DATE的列分区，却在ID列上有一个主键。通过查看这个分表 的每一个段，就能很容易地看出到底创建了哪些对象：
sys@JINGYONG> create table partitioned
2 (load_date date,
3 id int,
4 constraint partitioned_pk primary key(id)
5 )
6 partition by range(load_date)
7 (
8 partition part_1 values less than(to_date('2000-01-01','yyyy-mm-dd')),
9 partition part_2 values less than(to_date('2001-01-01','yyyy-mm-dd'))
10 );
表已创建。
sys@JINGYONG> select segment_name,partition_name,segment_type
2 from user_segments where segment_name like'PARTITIONED%';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
-------------- -------------- ------------------
PARTITIONED PART_1 TABLE PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED_PK INDEX
PARTITIONED_PK索引没有分区.而且我们将会看到，它根本无法进行局部分区。由于认识到非惟一索引也能像惟一索引一样保证 主键，我们想以此骗过Oracle，但是可以看到这种方法也不能奏效：
sys@JINGYONG> create table partitioned
2 (timestamp date,
3 id int
4 )
5 partition by range(timestamp)
6 (
7 partition part_1 values less than
8 (to_date('2000-01-01','yyyy-mm-dd')),
9 partition part_2 values less than
10 (to_date('2001-01-01','yyyy-mm-dd'))
11 );
表已创建。
sys@JINGYONG> create index partitioned_idx on partitioned(id) local;
索引已创建。
sys@JINGYONG> select segment_name,partition_name,segment_type
2 from user_segments where segment_name like'PARTITIONED%';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
-------------------- --------------- ------------------
PARTITIONED PART_1 TABLE PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED_IDX PART_2 INDEX PARTITION
PARTITIONED_IDX PART_1 INDEX PARTITION
sys@JINGYONG> alter table partitioned
2 add constraint partitioned_pk primary key(id);
alter table partitioned
*
第 1 行出现错误:
ORA-01408: such column list already indexed
在此，Oracle试图在ID上创建一个全局索引，却发现办不到，这是因为ID上已经存在一个索引。如果已创建的索引没有分区，前 面的语句就能工作，Oracle会使用这个索引来保证约束。
为什么局部分区索引不能保证惟一性（除非分区键是约束的一部分），原因有两方面。首先，如果Oracle允许如此，就会丧失分 区的大多数好处。可用性和可扩缩性都会丧失殆尽，因为对于任何插入和更新，总是要求所有分区都一定可用，而且要扫描每一 个分区。你的分区越多，数据就会变得越不可用。另外，分区越多，要扫描的索引分区就越多，分区也会变得越发不可扩缩。这 样做不仅不能提供可用性和可扩缩性，相反，实际上 反倒会削弱可用性和可扩缩性。
另外，倘若局部分区索引能保证惟一性，Oracle就必须在事务级对这个表的插入和更新有效地串行化。这是因为，如果向PART_1 增加ID=1，Oracle就必须以某种方式防止其他人向PART_2增加ID=1。对此惟一的做法是防止别人修改索引分区PART_2，因为无法 通过对这个分区中的内容“锁定”来做到（找不出什么可以锁定）。
在一个OLTP系统中，惟一性约束必须由系统保证（也就是说，由Oracle保证），以确保数据的完整性。这意味着，应用的逻辑模 型会对物理设计产生影响。惟一性约束能决定底层的表分区机制，影响分区键的选择，或者指示你应该使用全局索引。下面将更 深入地了解全局索引。
全局索引
全局索引使用一种有别于底层表的机制进行分区。表可以按一个TIMESTAMP列划分为10个分区，而这个表上的一个全局索引可以 按REGION列划分为5个分区。与局部索引不同，全局索引只有一类，这就是前缀全局索引（prefixed global index）。如果全局 索引的索引键未从该索引的分区键开始，这是不允许的。这说明，不论用什么属性对索引分区，这些属性都必须是索引键的前几 列。
下面继续看前面的例子，这里给出一个使用全局索引的小例子。它显示全局分区索引可以用于保证主键的惟一性，这样一来，即 使不包括表的分区键，也能保证惟一性的分区索引。下面的例子创建了一个按TIMESTAMP分区的表，它有一个按ID分区的索引：
sys@JINGYONG> create table partitioned
2 (timestamp date,
3 id int
4 )
5 partition by range(timestamp)
6 (
7 partition part_1 values less than(to_date('2000-01-01','yyyy-mm-dd')),
8 partition part_2 values less than(to_date('2001-01-01','yyyy-mm-dd'))
9 );
表已创建。
sys@JINGYONG> create index partitioned_index on partitioned(id) global
2 partition by range(id)
3 (
4 partition part_1 values less than(1000),
5 partition part_2 values less than(maxvalue)
6 );
索引已创建。
注意，这个索引中使用了MAXVALUE。MAXVALUE不仅可以用于索引中，还可以用于任何区间分区表中。它表示区间的“无限上界” 。在此前的所有例子中，我们都使用了区间的硬性上界（小于< 某个值>的值）。不过，全局索引有一个需求，即最高分区（最后 一个分区）必须有一个值为MAXVALUE的分区上界。这可以确保底层表中的所有行都能放在这个索引中。
下面，在这个例子的最后，我们将向表增加主键：
sys@JINGYONG> alter table partitioned add constraint 2 partitioned_pk primary key(id); 表已更改。
从这个代码还不能明显看出Oracle在使用我们创建的索引来保证主键,所以可以试着删除这个索引来证明这一点：
sys@JINGYONG> drop index partitioned_index;
drop index partitioned_index
*
第 1 行出现错误:
ORA-02429: cannot drop index used for enforcement of unique/primary key
为了显示Oracle不允许创建一个非前缀全局索引，只需执行下面的语句：
sys@JINGYONG> create index partitioned_index2 on partitioned(timestamp,id)
2 global
3 partition by range(id)
4 (
5 partition part_1 values less than(1000),
6 partition part_2 values less than(maxvalue)
7 );
partition by range(id)
*
第 3 行出现错误:
ORA-14038: GLOBAL partitioned index must be prefixed
为了创建一个全局索引那么索引的分区键必须是全局索引中的前几列才行如下:
sys@JINGYONG> create index partitioned_index2 on partitioned(id,timestamp) 2 global 3 partition by range(id) 4 ( 5 partition part_1 values less than(1000), 6 partition part_2 values less than(maxvalue) 7 ); 索引已创建。
错误信息相当明确。全局索引必须是前缀索引。那么，要在什么时候使用全局索引呢？我们将分析两种不同类型的系统（数据仓 库和OLTP）。来看看何时可以应用全局索引
数据仓库和全局索引
原先数据仓库和全局索引是相当互斥的。数据仓库就意味着系统有某些性质，如有大量的数据出入。许多数据仓库都实现了一种 滑动窗口（sliding window）方法来管理数据，也就是说，删除表中最旧的分区，并为新加载的数据增加一个新分区。在过去（ Oracle8i及以前的版本），数据仓库系统都避免使用全局索引，对此有一个很好的原因：全局索引缺乏可用性。大多数分区操作 （如删除一个旧分区）都会使全局索引无效，除非重建全局索引，否则无法使用，这会严重地影响可用性，以前往往都是如此。
滑动窗口和索引
下面的例子实现了一个经典的数据滑动窗口。在许多实现中，会随着时间的推移向仓库中增加数据，而最旧的数据会老化。在很 多时候，这个数据会按一个日期属性进行区间分区，所以最旧的数据多存储在一个分区中，新加载的数据很可能都存储在一个新 分区中。每月的加载过程涉及：
去除老数据：最旧的分区要么被删除，要么与一个空表交换（将最旧的分区变为一个表），从而允许对旧数据进行归档。
加载新数据并建立索引：将新数据加载到一个“工作”表中，建立索引并进行验证。
关联新数据：一旦加载并处理了新数据，数据所在的表会与分区表中的一个空分区交换，将表中的这些新加载的数据变成分区表 中的一个分区（分区表会变得更大）。
这个过程会没有重复，或者执行加载过程的任何周期重复；可以是每天或每周。我们将在这一节实现这个非常典型的过程，显示 全局分区索引的影响，并展示分区操作期间可以用哪些选项来提高可用性，从而能实现一个数据滑动窗口，并维持数据的连续可 用性。
在这个例子中，我们将处理每年的数据，并加载2004和2005财政年度的数据。这个表按TIMESTAMP列分区，并创建了两个索引， 一个是ID列上的局部分区索引，另一个是TIMESTAMP列上的全局索引（这里为分区）：
sys@JINGYONG> create table partitioned
2 (timestamp date,
3 id int
4 )
5 partition by range(timestamp)
6 (
7 partition fy_2004 values less than(to_date('2005-01-01','yyyy-mm-dd')),
8 partition fy_2005 values less than(to_date('2006-01-01','yyyy-mm-dd'))
9 );
表已创建。
sys@JINGYONG> insert into partitioned partition(fy_2004)
2 select to_date('2004-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
3 from all_objects;
已创建50318行。
sys@JINGYONG> insert into partitioned partition(fy_2005)
2 select to_date('2005-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
3 from all_objects;
已创建50318行。
sys@JINGYONG> create index partitioned_idx_local on partitioned(id) local;
索引已创建。
sys@JINGYONG> create index partitioned_idx_global
2 on partitioned(timestamp) global;
索引已创建。
这就建立了我们的“仓库”表。数据按财政年度分区，而且最后两年的数据在线。这个表有两个索引：一个是LOCAL索引，另一 个是GLOBAL索引。现在正处于年末，我们想做下面的工作：
（1） 删除最旧的财政年度数据。我们不想永远地丢掉这个数据，而只是希望它老化，并将其归档。
（2） 增加最新的财政年度数据。加载、转换、建索引等工作需要一定的时间。我们想做这个工作，但是希望尽可能不影响当前 数据的可用性。
第一步是为2004财政年度建立一个看上去就像分区表的空表。我们将使用这个表与分区表中的FY_2004分区交换，将这个分区转 变成一个表，相应地使分区表中的分区为空。这样做的效果就是分区表中最旧的数据（实际上）会在交换之后被删除：
sys@JINGYONG> create table fy_2004(timestamp date,id int); 表已创建。 sys@JINGYONG> create index fy_2004_idx on fy_2004(id); 索引已创建。
对要加载的新数据做同样的工作。我们将创建并加载一个表，其结构就像是现在的分区表（但是它本身并不是分区表）：
sys@JINGYONG> create table fy_2006(timestamp date,id int);
表已创建。
sys@JINGYONG> insert into fy_2006
2 select to_date('2006-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
3 from all_objects;
已创建50325行。
sys@JINGYONG> create index fy_2006_idx on fy_2006(id) nologging;
索引已创建。
我们将当前的满分区变成一个空分区，并创建了一个包含FY_2004数据的“慢”表。而且，我们完成了使用FY_2006数据的所有必 要工作，这包括验证数据、进行转换以及准备这些数据所需完成的所有复杂任务。
现在可以使用一个交换分区来更新“活动”数据：
sys@JINGYONG> alter table partitioned 2 exchange partition fy_2004 3 with table fy_2004 4 including indexes 5 without validation; 表已更改。 sys@JINGYONG> alter table partitioned drop partition fy_2004; 表已更改。
要把旧数据“老化”，所要做的仅此而已。我们将分区变成一个满表，而将空表变成一个分区。这是一个简单的数据字典更新， 瞬时就会完成，而不会发生大量的I/O。现在可以将FY_2004表从数据库中导出（可能要使用一个可移植的表空间）来实现归档。 如果需要，还可以很快地重新关联这些数据。
接下来，我们想“滑入”（即增加）新数据：
sys@JINGYONG> alter table partitioned
2 add partition fy_2006
3 values less than(to_date('2007-01-01','yyyy-mm-dd'));
表已更改。
sys@JINGYONG> alter table partitioned
2 exchange partition fy_2006
3 with table fy_2006
4 including indexes
5 without validation;
表已更改。
sys@JINGYONG> select index_name,status from user_indexes where table_name=’PARTI
TIONED’;
INDEX_NAME STATUS
—————————— ——–
PARTITIONED_IDX_LOCAL N/A
PARTITIONED_IDX_GLOBAL UNUSABLE
sys@JINGYONG> select index_name,status from user_indexes where index_name like’F
Y%’;
INDEX_NAME STATUS
—————————— ——–
FY_2004_IDX VALID
FY_2006_IDX VALID
当然，在这个操作之后，全局索引是不可用的。由于每个索引分区可能指向任何表分区，而我们刚才取走了一个分区，并增加了 一个分区，所以这个索引已经无效了。 其中有些条目指向我们已经生成的分区，却没有任何条目指向刚增加的分区。使用了这 个索引的任何查询可能会失败而无法执行，或者如果我们跳过不可用的索引， 尽管查询能执行，但查询的性能会受到负面影响 （因为无法使用这个索引）：
sys@JINGYONG> set autotrace on explain
sys@JINGYONG> select /*+ index(partitioned partitioned_idx_global) */ count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate;
select /*+ index(partitioned partitioned_idx_global) */ count(*)
*
第 1 行出现错误:
ORA-01502: index 'SYS.PARTITIONED_IDX_GLOBAL' or partition of such index is in u
nusable state
sys@JINGYONG> select count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate;
COUNT(*)
----------
0
执行计划
----------------------------------------------------------
Plan hash value: 2869581836
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 63 (12)|00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 3 | 27 | 63 (12)|00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | PARTITIONED | 3 | 27 | 63 (12)|00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYSDATE@!-50< =SYSDATE@!)
4 - filter("TIMESTAMP"<=SYSDATE@! AND "TIMESTAMP">=SYSDATE@!-50)
Note
-----
- dynamic sampling used for this statement
因此，执行这个分区操作后，对于全局索引，我们有以下选择：
跳过索引，可以像这个例子中一样（Oracle 10g会透明地这样做），在9i中则可以通过设置会话参数 SKIP_UNUSABLE_INDEXES=TRUE来跳过索引（Oracle 10g将这个设置默认为TRUE）。但是这样一来，就丢失了索引所提供的性能提 升。让查询接收到一个错误，就像9i中一样（SKIP_UNUSABLE_INDEX设置为FALSE），在10g中，显式地请求使用提示的任何查询 都会接收到错误。要想让数据再次真正可用，必须重建这个索引。
到此为止滑动窗口过程几乎不会带来任何停机时间，但是在我们重建全局索引时，需要相当长的时间才能完成。如果查询依赖于 这些索引，在此期间它们的运行时查询 性能就会受到负面影响，可能根本不会运行，也可能运行时得不到索引提供的好处。所 有数据都必须扫描，而且要根据数据重建整个索引。如果表的大小为数百GB，这会占用相当多的资源。
活动全局索引维护
从Oracle9i开始，对于分区维护又增加了另一个选项：可以在分区操作期间使用UPDATE GLOBAL INEXES子句来维护全局索引。这 意味着，在你删除一个分区、分解一个分区以及在分区上执行任何必要的操作时，Oracle会对全局索引执行必要的修改，保证它 是最新的。由于大多数分区操作都会导致全局索引无效，这个特征对于需要提供数据连续访问的系统来说是一个大福音。你会发 现，通过牺牲分区操作的速度（但是原先重建索引后会有一个可观的不可用窗口，即不可用的停机时间相当长），可以换取100% 的数据可用性（尽管分区操作的总体响应时间会更慢）。简单地说，如果数据仓库不允许有停机时间，而且必须支持数据的滑入 滑出等数据仓库技术，这个特性就再合适不过了，但是你必须了解它带来的影响。
再来看前面的例子，如果分区操作在必要时使用了UPDATE GLOBAL INDEXES子句（在这个例子中，在ADD PARTITION语句上就没有 必要使用这个子句，因为新增加的分区中没有任何行）：
sys@JINGYONG> alter table partitioned
2 exchange partition fy_2004
3 with table fy_2004
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.
sys@JINGYONG> alter table partitioned
2 drop partition fy_2004
3 UPDATE GLOBAL INDEXES
4 /
Table altered.
sys@JINGYONG> alter table partitioned
2 add partition fy_2006
3 values less than ( to_date('01-jan-2007','dd-mon-yyyy') )
4 /
Table altered.
sys@JINGYONG> alter table partitioned
2 exchange partition fy_2006
3 with table fy_2006
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.
就会发现索引完全有效，不论在操作期间还是操作之后这个索引都是可用的：
sys@JINGYONG> select index_name, status from user_indexes; INDEX_NAME STATUS ------------------------------ -------- FY_2006_IDX VALID FY_2004_IDX VALID PARTITIONED_IDX_GLOBAL VALID PARTITIONED_IDX_LOCAL N/A 4 rows selected. sys@JINGYONG> set autotrace on explain sys@JINGYONG> select count(*) 2 from partitioned 3 where timestamp between sysdate-50 and sysdate; COUNT(*) ---------- 6750 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=9) 1 0 SORT (AGGREGATE) 2 1 FILTER 3 2 INDEX (RANGE SCAN) OF 'PARTITIONED_IDX_GLOBAL' (INDEX) (Cost=9...
但是这里要做一个权衡：我们要在全局索引结构上执行INSERT和DELETE操作的相应逻辑操作。删除一个分区时，必须删除可能指 向该分区的所有全局索引条目。执行表与分区的交换时，必须删除指向原数据的所有全局索引条目，再插入指向刚滑入的数据的 新条目。所以ALTER命令执行的工作量会大幅增加。
索引重建方法比使用UPDATE GLOBAL INDEXES子句来更新全局分区索引确实运行得更快一些,所以，UPDATE GLOBAL INDEXES是一 种允许用资源耗费的增加来换取可用性的选项。如果需要提供连续的可用性，这就是一个必要的选择。但是，你必须理解相关的 问题，并且适当地确定系统中其他组件的大小。具体地将，许多数据仓库过一段时间都会改为使用大批量的直接路径操作，而绕 过undo生成，如果允许的话，还会绕过redo生成。但是倘若使用UPDATE GLOBAL INDEXES,就不能绕过undo或redo生成。在使用这 个特性之前，需要检查确定组件大小所用的规则，从而确保这种方法在你的系统上确实能正常工作。
OLTP和全局索引
OLTP系统的特点是会频繁出现许多小的读写事务，一般来讲，在OLTP系统中，首要的是需要快速访问所需的行，而且数据完整性 很关键，另外可用性也非常重要。
在OLTP系统中，许多情况下全局索引很有意义。表数据可以按一个键（一个列键）分区。不过，你可能需要以多种不同的方式访 问数据。例如，可能会按表中的LOCATION来划分EMPLOYEE数据，但是还需要按以下列快速访问EMPLOYEE数据：
DEPARTMENT：部门的地理位置很分散。部门和位置之间没有任何关系。
EMPLOYEE_ID：尽管员工ID能确定位置，但是你不希望必须按EMPLOYEE_ID和LOCATION搜索，因为这样一来索引分区上将不能发生 分区消除。而且EMPLOYEE_ID本身必然是惟一的。
JOB_TITLE：JOB_TITLE和LOCATION之间没有任何关系。任何LOCATION上都可以出现所有JOB_TITLE值。
这里需要按多种不同的键来访问应用中不同位置的EMPLOYEE数据，而且速度至上。在一个数据仓库中，可以只使用这些键上的局 部分区索引，并使用并行索引区间扫描来快速收集大量数据。
oracel分区之表分区
分区
分区（partitioning）最早在Oracle 8.0中引入，这个过程是将一个表或索引物理地分解为多个更小、更可管理的部分。就访问 数据库的应用而言，逻辑上讲只有一个表或一个索引，但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个 独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。考虑使用分区原因是多方面的，可能是分区能提高数 据的可用性，或者是可以减少管理员的负担，另外在某些情况下，还可能提高性能。
分区只是一个工具，对索引或表进行分区时可能发生3种情况：使用这些分区表的应用可能运行得更慢；可能运行得更快；有可 能没有任何变化。
分区概述
分区有利于管理非常大的表和索引，它使用了一种“分而治之”的逻辑。分区引入了一种分区键（partition key）的概念，分 区键用于根据某个区间值（或范围值）、特定值列表或散列函数值执行数据的聚集。分区有以下好处：
（1） 提高数据的可用性：这个特点对任何类型的系统都适用，而不论系统本质上是OLTP还是仓库系统。
（2） 由于从数据库中去除了大段，相应地减轻了管理的负担。
（3） 改善某些查询的性能：主要在大型仓库环境中有这个好处，通过使用分区，可以消除很大的数据区间，从而不必考虑它们 ，相应地根本不用访问这些数据。但这在事务性系统中并不适用，因为这种系统本身就只是访问少量的数据。
（4） 可以把修改分布到多个单独的分区上，从而减少大容量OLTP系统上的竞争：如果一个段遭遇激烈的竞争，可以把它分为多 个段，这就可以成比例地减少竞争。
下面分别讨论使用分区可能带来的这些好处。
可用性的提高源自于每个分区的独立性。对象中一个分区的可用性（或不可用）并不意味着对象本身是不可用的。优化器知道有 这种分区机制，会相应地从查询计划中去除未引用的分区。在一个大对象中如果一个分区不可用，你的查询可以消除这个分区而 不予考虑，这样Oracle就能成功地处理这个查询。
为了展示这种可用性的提高，我们将建立一个散列分区表，其中有两个分区，分别在单独的表空间中。这里将创建一个EMP表， 它在EMPNO列上指定了一个分区键（EMPNO就是我们的分区键）。在这种情况下，这个结构意味着：对于插入到这个表中的每一行 ，会对EMPNO列的值计算散列，来确定这一行将置于哪个分区（及相应的表空间）中
先创建分区对应的表空间:
sys@JINGYONG> create tablespace p1 datafile '/u01/app/oracle/product/10.2.0/orad ata/jingyong/jingyong/p101.dbf' size 50M; 表空间已创建。 sys@JINGYONG> create tablespace p2 datafile '/u01/app/oracle/product/10.2.0/orad ata/jingyong/jingyong/p201.dbf' size 50M; 表空间已创建。 sys@JINGYONG> create table emp 2 (empno int, 3 ename varchar2(20) 4 ) 5 partition by hash(empno) 6 (partition part_1 tablespace p1, 7 partition part_2 tablespace p2 8 ) 9 ; 表已创建。
接下来，我们向表中插入一些数据，然后使用带分区的扩展表名检查各个分区的内容：
sys@JINGYONG> insert into emp select empno,ename from scott.emp;
已创建14行。
sys@JINGYONG> select * from emp partition(part_1);
EMPNO ENAME
---------- --------------------
7369 SMITH
7499 ALLEN
7654 MARTIN
7698 BLAKE
7782 CLARK
7839 KING
7876 ADAMS
7934 MILLER
已选择8行。
sys@JINGYONG> select * from emp partition(part_2);
EMPNO ENAME
---------- --------------------
7521 WARD
7566 JONES
7788 SCOTT
7844 TURNER
7900 JAMES
7902 FORD
已选择6行。
应该能注意到，数据的“摆放”有些随机。通过使用散列分区，我们让Oracle随机地（很可能均匀地）将数据分布到多个
分区上。我们无法控制数据要分布到哪个分区上；Oracle会根据生成的散列键值来确定。
下面将其中一个表空间脱机（例如，模拟一种磁盘出故障的情况），使这个分区中的数据不可用：
sys@JINGYONG> alter tablespace p1 offline; 表空间已更改。
接下来，运行一个查询，这个查询将命中每一个分区，可以看到这个查询失败了：
sys@JINGYONG> select * from emp;
select * from emp
*
第 1 行出现错误:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/oradata/jingyong/jingyon
g/p101.dbf'
不过，如果查询不访问脱机的表空间，这个查询就能正常工作；Oracle会消除脱机的分区而不予考虑。
sys@JINGYONG> select * from emp where empno=7844;
EMPNO ENAME
---------- --------------------
7844 TURNER
总之，只要优化器能从查询计划消除分区，它就会这么做。基于这一点，如果应用在查询中使用了分区键，就能提高这些应用的 可用性。
分区还可以通过减少停机时间来提高可用性。例如，如果有一个200GB的表，它划分为100个2GB的分区，这样就能更快地从错误 中恢复。如果某个2GB的分区遭到破坏，现在恢复的时间就只是恢复一个2GB分区所需的时间，而不是恢复一个200GB表的时间。 所以从两个方面提高了可用性：
(1) 优化器能够消除分区，这意味着许多用户可能甚至从未注意到某些数据是不可用的。
(2) 出现错误时的停机时间会减少，因为恢复所需的工作量大幅减少。
减少管理负担
之所以能减少管理负担，这是因为与在一个大对象上执行操作相比，在小对象上执行同样的操作从本质上讲更为容易、速度更快 ，而且占用的资源也更少。
例如，假设数据库中有一个100GB的索引。如果需要重建这个索引，而该索引未分区，你就必须将整个100GB的索引作为一个工作 单元来重建。尽管可以在线地重建索引，但是要完全重建完整的100GB索引，还是需要占用大量的资源。至少需要在某处有100GB 的空闲存储空间来存放索引的副本，还需要一个临时事务日志表来记录重建索引期间对基表所做的修改。另一方面，如果将索引 本身划分为100个1GB的分区，就可以一个接一个地单独重建各个索引分区。现在只需要原先所需空闲空间的100%。另外，各个索 引的重建也更快（可能是原来的100倍），需要向新索引合并的事务修改也更少（到此为止，在线索引重建期间发生的事务修改 会更少）。
另外请考虑以下情况：100GB索引的重建即将完成之前，如果出现系统或软件故障会发生什么。我们所做的全部努力都会付诸东 流。如果把问题分解，将索引划分为1GB的分区，你最多只会丢掉重建工作的1%。或者，你可能只需要重建全部聚集索引的1%， 例如，只是“最新”的数据（活动数据）需要重组，而所有“较旧”的数据（相当静态）不受影响。
最后，请考虑这样一种情况：你发现表中50%的行都是“移植”行（行链接或行迁移），可能想进修正。建立一个分区表将有利 于这个操作。为了“修正”移植行，你往往必须重建对象，在这种情况下，就是要重建一个表。如果有一个100GB的表，就需要 在一个非常大的“块”（chunk）上连续地使用ALTER TABLE MOVE来执行这个操作。另一方面，如果你有25个分区，每个分区的 大小为4GB，就可以一个接一个地重建各个分区。或者，如果你在空余时间做这个工作，而且有充足的资源，甚至可以在单独的 会话中并行地执行ALTER TABLE MOVE语句，这就很可能会减少整个操作所需的时间。对于一个未分区对象所能做的工作，分区对 象中的单个分区几乎都能做到。你甚至可能发现，移植行都集中在一个很小的分区子集中，因此，可以只重建一两个分区，而不 是重建整个表。
下面有一个小例子，展示了如何对一个有多个移植行的表进行重建。TABLE1和TABLE2都是从TABLE_T的一个10,000,000行的实例 创建的.TABLE1是一个常规的未分区表，而TABLE2是一个散列分区表，有8个分区:
sys@JINGYONG> create table table1 2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, 3 OBJECT_ID, DATA_OBJECT_ID, 4 OBJECT_TYPE, CREATED, LAST_DDL_TIME, 5 TIMESTAMP, STATUS, TEMPORARY, 6 GENERATED, SECONDARY ) 7 tablespace table1 8 as 9 select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, 10 OBJECT_ID, DATA_OBJECT_ID, 11 OBJECT_TYPE, CREATED, LAST_DDL_TIME, 12 TIMESTAMP, STATUS, TEMPORARY, 13 GENERATED, SECONDARY 14 from table_T; Table created. sys@JINGYONG> create table table2 2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, 3 OBJECT_ID, DATA_OBJECT_ID, 4 OBJECT_TYPE, CREATED, LAST_DDL_TIME, 5 TIMESTAMP, STATUS, TEMPORARY, 6 GENERATED, SECONDARY ) 7 partition by hash(id) 8 (partition part_1 tablespace table2, 9 partition part_2 tablespace table2, 10 partition part_3 tablespace table2, 11 partition part_4 tablespace table2, 12 partition part_5 tablespace table2, 13 partition part_6 tablespace table2, 14 partition part_7 tablespace table2, 15 partition part_8 tablespace table2 16 ) 17 as 18 select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, 19 OBJECT_ID, DATA_OBJECT_ID, 20 OBJECT_TYPE, CREATED, LAST_DDL_TIME, 21 TIMESTAMP, STATUS, TEMPORARY, 22 GENERATED, SECONDARY 23 from table_t; Table created.
现在，每个表都在自己的表空间中，所以我们可以很容易地查询数据字典，来查看每个表空间中已分配的空间和空闲空间：
sys@JINGYONG> select b.tablespace_name,
2 mbytes_alloc,
3 mbytes_free
4 from ( select round(sum(bytes)/1024/1024) mbytes_free,
5 tablespace_name
6 from dba_free_space
7 group by tablespace_name ) a,
8 ( select round(sum(bytes)/1024/1024) mbytes_alloc,
9 tablespace_name
10 from dba_data_files
11 group by tablespace_name ) b
12 where a.tablespace_name (+) = b.tablespace_name
13 and b.tablespace_name in ('table1','table2')
14 /
TABLESPACE MBYTES_ALLOC MBYTES_FREE
---------- ------------ -----------
TABLE1 1596 524
TABLE2 1596 524
table1和table2的大小都大约是1.6GB，每个表空间都有524MB的空闲空间。我们想创建第一个表TABLE1：
sys@JINGYONG> alter table table1 move; alter table table1 move * ERROR at line 1: ORA-01652: unable to extend temp segment by 1024 in tablespace table1
但失败了,table1表空间中要有足够的空闲空间来放下ABLE1的完整副本，同时它的原副本仍然保留，简单地说，我们需要一个很 短的时间内有大约两倍的存储空间（可能多一点，也可能少移动，这取决于重建后表的大小）。现在试图对TABLE2执行同样的操作
sys@JINGYONG> alter table table2 move; alter table table2 move * ERROR at line 1: ORA-14511: cannot perform operation on a partitioned object
这说明，Oracle在告诉我们：无法对这个“表”执行MOVE操作；我们必须在表的各个分区上执行这个操作。可以逐个地移动（相 应地重建和重组）各个分区：
sys@JINGYONG> alter table table2 move partition part_1; Table altered. sys@JINGYONG> alter table table2 move partition part_2; Table altered. sys@JINGYONG> alter table table2 move partition part_3; Table altered. sys@JINGYONG> alter table table2 move partition part_4; Table altered. sys@JINGYONG> alter table table2 move partition part_5; Table altered. sys@JINGYONG> alter table table2 move partition part_6; Table altered. sys@JINGYONG> alter table table2 move partition part_7; Table altered. sys@JINGYONG> alter table table2 move partition part_8; Table altered.
对于每个移动，只需要有足够的空闲空间来存放原来数据的1/8的副本！因此，假设有先前同样多的空闲空间，这些命令就能成 功。我们需要的临时资源将显著减少。不仅如此，如果在移动到PART_4后但在PART_5完成“移动”之前系统失败了（例如，掉电 ），我们并不会丢失以前所做的所有工作，这与执行一个MOVE语句的情况不同。前4个分区仍是“移动”后的状态，等系统恢复 时，我们可以从分区PART_5继续处理。 也可以很容易地编写一个脚本来解决上面输入8次语句的题：
sys@JINGYONG> begin 2 for x in ( select partition_name 3 from user_tab_partitions 4 where table_name = 'TABLE2' ) 5 loop 6 execute immediate 7 'alter table table2 move partition ' || 8 x.partition_name; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed.
关于分区和管理，还有一个因素需要考虑，这就是在维护数据仓库和归档中使用数据“滑动窗口”。在许多情况下，需要保证数 据在最后N个时间单位内一直在线。例如，假设需要保证最后12个月或最后5年的数据在线。如果没有分区，这通常是一个大规模 的INSERT，其后是一个大规模的DELETE。为此有相对多的DML，并且会生成大量的redo和undo。如果进行了分区，则只需做下面 的工作：
（1） 用新的月（或年，或者是其他）数据加载一个单独的表。
（2） 对这个表充分建立索引（这一步甚至可以在另一个实例中完成，然后传送到这个数据库中）。
（3） 将这个新加载（并建立了索引）的表附加到分区表的最后，这里使用一个快速DDL命令：ALTER TABLE EXCHANGE PARTITION。
（4） 从分区表另一端将最旧的分区去掉。
这样一来，现在就可以很容易地支持包含时间敏感信息的非常大的对象。数据很容易地从分区表中去除，如果不再需要它，可以 简单地将其删除；或者也可以归档到某个地方。新数据可以加载到一个单独的表中，这样在加载、建索引等工作完成之前就不会 影响分区表.
改善语句性能
分区最后一个好处体现在改进语句（SELECT、INSERT、UPDATE、DELETE、MERGE）的性能方面。我们来看两类语句，一种是修改 信息的语句，另一种是只读取信息的语句，并讨论在这种情况下可以从分区得到哪些好处。
1. 并行DML
修改数据库中数据的语句有可能会执行并行DML（parallel DML，PDML）。采用PDML时，Oracle使用多个线程或进程来执行 INSERT、UPDATE或DELETE， 而不是执行一个串行进程。在一个有充足I/O带宽的多CPU主机上，对于大规模的DML操作，速度的提 升可能相当显著。在Oracle9i以前的版本中，PDML要求必须分区。如果你的表没有分区，在先前的版本中就不能并行地执行这些 操作。如果表确实已经分区，Oracle会根据对象所有的物理分区数为对象指定一个最大并行度。从很大程度上讲，在Oracle9i及 以后版本中这个限制已经放松，只有两个突出的例外；如果希望在一个表上执行PDML，而且这个表的一个LOB列上有一个位图索 引，要并行执行操作就必须对这个表分区；另外并行度就限制为分区数。不过，总的说来，使用PDML并不一定要求进行分区。
查询性能
在只读查询（SELECT语句）的性能方面，分区对两类特殊操作起作用：
分区消除（partition elimination）：处理查询时不考虑某些数据分区。我们已经看到了一个分区消除的例子。
并行操作（parallel operation）：并行全表扫描和并行索引区间扫描就是这种操作的例子。
不过，由此得到的好处很多程度上取决于你使用何种类型的系统。
OLTP系统
在OLTP系统中，不应该把分区当作一种大幅改善查询性能的方法。实际上，在一个传统的OLTP系统中，你必须很小心地应用分区 ，提防着不要对运行时性能产生负面作用。在传统的OLTP系统中，大多数查询很可能立即返回，而且大多数数据库获取可能都通 过一个很小的索引区间扫描来完成。因此，以上所列分区性能方面可能的主要优点在OLTP系统中表现不出来。分区消除只在大对 象全面扫描时才有用，因为通过分区消除，你可以避免对对象的很大部分做全面的扫描。不过，在一个OLTP环 境中，本来就不 是大对象全面扫描（如果真是如此，则说明肯定存在严重的设计缺陷）。即使对索引进行了分区，就算是真的能在速度上有所提 高，通过扫描较小索引所得到的性能提升也是微乎其微的。如果某些查询使用了一个索引，而且它们根本无法消除任何分区，你 可能会发现，完成分区之后查询实际上运行得反而更慢了，因为你现在要扫描5、10或20个更小的索引，而不是一个较大的索引 。
尽管如此，有分区的OLTP系统确实也有可能得到效率提高。例如，可以用分区来减少竞争，从而提高并发度。可以利用分区将一 个表的修改分布到多个物理分区上。并不是只有一个表段和一个索引段，而是可以有10个表分区和20个索引分区。这就像有20个 表而不是1个表，相应地，修改期间就能减少对这个共享资源的竞争。
至于并行操作,你可能不希望在一个OLTP系统中执行并行查询。你会慎用并行操作，事实上在一个OLTP系统中，查询已经有以下 特点：即索引访问相当快，因此，分区不会让索引访问的速度有太大的高（甚至根本没有任何提高）。这并不是说要绝对避免在 OLTP系统中使用分区；而只是说不要指望通过分区来提供大幅的性能提升。尽管有些情况下分区能够改善查询的性能，但是这些 情况在大多数OLTP应用中并不成立。不过在OLTP系统中，你还是可以得到另外两个可能的好处：减轻管理负担以及有更高的可用 性。
数据仓库系统
在一个数据仓库/决策支持系统中，分区不仅是一个很强大的管理工具，还可以加快处理的速度。例如，你可能有一个大表，需 要在其中执行一个即席查询。你总是按销售定额（sales quarter）执行即席查询，因为每个销售定额包含数十万条记录，而你 有数百万条在线记录。因此，你想查询整个数据集中相当小的一部分，但是基于销售定额来索引不太可行。这个索引会指向数十 万条记录，以这种方式执行索引区间扫描会很糟糕. 处理许多查询时都要求执行一个全表扫描，但是最后却发现，一方面必须扫 描数百万条记录，但另一方面其中大多数记录并不适用于我们的查询。如果使用一种明智的分区机制，就可以按销售定额来聚集 数据，这样在查询某个给定销售定额的数据时，就可以只对这个销售定额的数据进行全面扫描。这在所有可能的解决方案中是 最佳的选择。
另外，在一个数据仓库/决策支持环境中，会频繁地使用并行查询。因此，诸如并行索引区间扫描或并行快速全面索引扫描等操 作不仅很有意义，而且对我们很有好处。我们希望充分地使用所有可用的资源，并行查询就提供了这样的一种途径。因此，在数 据仓库环境中，分区就意味着很有可能会加快处理速度。
表分区机制
目前Oracle中有4种对表分区的方法：
区间分区：可以指定应当存储在一起的数据区间。例如，时间戳在2005-06内的所有记录都存储在分区1中，时间戳在2005-06内 的所有记录都存储在分区2中，依此类推。这可能是Oracle中最常用的分区机制。
散列分区：是指在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。
列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放 在分区1中，STATUS值在（‘D’,’P’,’Q’）中的行放在分区2中，依此类推。
组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，你可以先对某些数据应用 区间分区，再在区间中根据散列或列表来选择最后的分区。
区间分区
区间分区表（range partitioned table）。下面的CREATE TABLE语句创建了一个使用RANGE_KEY_COLUMN列的区间分区表。 RANGE_KEY_COLUMN值严格小于2005-01-01的所有数据要放在分区PART_1中，RANGE_KEY_COLUMN值严格小于2006-01-01的所有数据 则放在分区PART_2中。不满足这两个条件的所有数据（例如，RANGE_KEY_COLUMN值为2007-01-01的行）将不能插入，因为它们无 法映射到任何分区：
sys@JINGYONG> create table range_example
2 (range_key_column date,
3 data varchar2(20)
4 )
5 partition by range(range_key_column)
6 (partition part_1 values less than
7 (to_date('2005-01-01','yyyy-mm-dd')),
8 partition part_2 values less than
9 (to_date('2006-01-01','yyyy-mm-dd'))
10 )
11 ;
表已创建。
sys@JINGYONG> insert into range_example values(to_date('2004-11-01','yyyy-mm-dd'
),'2004');
已创建 1 行。
sys@JINGYONG> insert into range_example values(to_date('2005-11-01','yyyy-mm-dd'
),'2005');
已创建 1 行。
sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');
已创建 1 行。
sys@JINGYONG> commit;
提交完成。
sys@JINGYONG> select * from range_example partition(part_1);
RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-04 2004
sys@JINGYONG> select * from range_example partition(part_2);
RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05 2005
01-1月 -05 2005
如果插入的日期超出上界会怎么样呢？答案是Oracle会产生一个错误：
sys@JINGYONG> insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'
),'2006');
insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'),'2006')
*
第 1 行出现错误:
ORA-14400: inserted partition key does not map to any partition
假设你想像刚才一样，将2005年和2006年的日期分别聚集到各自的分区，但是另外你还希望将所有其他日期都归入第三个分区。 利用区间分区，这可以使用MAXVALUE子句做到这一点，如下所示：
sys@JINGYONG> CREATE TABLE range_example
2 ( range_key_column date ,
3 data varchar2(20)
4 )
5 PARTITION BY RANGE (range_key_column)
6 ( PARTITION part_1 VALUES LESS THAN
7 (to_date('01/01/2005','dd/mm/yyyy')),
8 PARTITION part_2 VALUES LESS THAN
9 (to_date('01/01/2006','dd/mm/yyyy'))
10 PARTITION part_3 VALUES LESS THAN
11 (MAXVALUE)
12 )
13 /
Table created.
现在，向这个表插入一个行时，这一行肯定会放入三个分区中的某一个分区中，而不会再拒绝任何行，因为分区PART_3可以接受 不能放在PART_1或PART_2中的任何RANG_KEY_COLUMN值（即使RANGE_KEY_COLUMN值为null，也会插入到这个新分区中）。
散列分区
对一个表执行散列分区（hash partitioning）时，Oracle会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪 一个分区中。Oracle建议N是2的一个幂（2、4、8、16等），从而得到最佳的总体分布.
散列分区如何工作
散列分区设计为能使数据很好地分布在多个不同设备（磁盘）上，或者只是将数据聚集到更可管理的块（chunk）上，为表选择 的散列键应当是惟一的一个列或一组列，或者至少有足够多的相异值，以便行能在多个分区上很好地（均匀地）分布。如果你选 择一个只有4个相异值的列，并使用两个分区，那么最后可能把所有行都散列到同一个分区上，这就有悖于分区的最初目标！
在这里，我们将创建一个有两个分区的散列表。在此使用名为HASH_KEY_COLUMN的列作为分区键。Oracle会取这个列中的值，并 计算它的散列值，从而确定这一行将存储在哪个分区中：
sys@JINGYONG> create table hash_example
2 (hash_key_column date,
3 data varchar2(20)
4 )
5 partition by hash(hash_key_column)
6 (partition part_1 tablespace p1,
7 partition part_2 tablespace p2
8 )
9 ;
表已创建。
sys@JINGYONG> insert into hash_example values(to_date('2004-01-01','yyyy-mm-dd'
),'2004');
已创建 1 行。
sys@JINGYONG> insert into hash_example values(to_date('2007-01-01','yyyy-mm-dd'
),'2007');
已创建 1 行。
sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');
已创建 1 行。
sys@JINGYONG> insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'
),'2006');
已创建 1 行。
sys@JINGYONG> commit;
sys@JINGYONG> select * from hash_example partition(part_1);
HASH_KEY_COLUM DATA
-------------- --------------------
01-1月 -04 2004
01-1月 -07 2007
sys@JINGYONG> select * from hash_example partition(part_2);
HASH_KEY_COLUM DATA
-------------- --------------------
01-1月 -06 2006
01-1月 -05 2005
前面已经提到过，如果使用散列分区，你将无从控制一行最终会放在哪个分区中。Oracle会应用散列函数，并根据散列的结果来 确定行会放在哪里。如果你由于某种原因希望将某个特定行放在分区PART_1中， 就不应该使用散列分区，实际上，此时也不能 使用散列分区。行会按散列函数的“指示”放在某个分区中，也就是说，散列函数说这一行该放在哪个分区，它就会放 在哪个 分区中。如果改变散列分区的个数，数据会在所有分区中重新分布（向一个散列分区表增加或删除一个分区时，将导致所有数据 都重写，因为现在每一行可能 属于一个不同的分区）。
如果你有一个大表,而且你想对它“分而治之”，此时散列分区最有用。你不用管理一个大表，而只是管理8或16个 较小的“表 ”。从某种程度上讲，散列分区对于提高可用性也很有用，临时丢掉一个散列分区，就能访问所有余下的分区。 也许有些用户 会受到影响，但是很有可能很多用户根本不受影响，但是很有可能很多用户根本不受影响。另外，恢复的单位现在也更小了。你 不用恢复一个完整的大表；而只需恢复表中的一小部分。
列表分区
列表分区（list partitioning）是Oracle9i Release 1的一个新特性。它提供了这样一种功能，可以根据离散的值列表来指定 一行位于哪个分区。如果能根据某个代码来进行分区（如州代码或区代码），这通常很有用。例如，你可能想把Maine州（ME） 、New Hampshire州（NH）、Vermont州（VT）和Massachusetts州（MA）中所有人的记录都归至一个分区中，因为这些州相互之 间挨得很近，而且你的应用按地理位置来查询数据。类似地，你可能希望将Connecticut州（CT）、Rhode Island州（RI）和New York州（NY）的数据分组在一起。对此不能使用区间分区，因为第一个分区的区间是ME到VT，第二个区间是CT到RI。这两个区间 有重叠。而且也不能使用散列分区，因为这样你就无法控制给定行要放到哪个分区中；而要由Oracle提供的内置散列函数来控制 。利用列表分区，我们可以很容易地完成这个定制分区机制：
sys@JINGYONG> create table list_example
2 (state_cd varchar2(2),
3 data varchar2(20)
4 )
5 partition by list(state_cd)
6 (partition part_1 values('ME','NH','VT','MA'),
7 partition part_2 values('CT','RI','NY')
8 )
9 ;
表已创建。
就像区间分区一样，如果我们想插入列表分区中未指定的一个值，Oracle会向客户应用返回一个合适的错误。换句话说，没有 DEFAULT分区的列表分区表会隐含地施加一个约束（非常像表上的一个检查约束）：
sys@JINGYONG> insert into list_example values('VA','data');
insert into list_example values('VA','data')
*
第 1 行出现错误:
ORA-14400: inserted partition key does not map to any partition
如果想像前面一样把这个7个州分别聚集到各自的分区中，另外把其余的所有州代码放在第三个分区中（或者，实际上对于所插 入的任何其他行，如果STATE_CD列值不是以上7个州代码之一，就要放在第三个分区中），就可以使用VALUES（DEFAULT）子句。 在此，我们将修改表，增加这个分区（也可以在CREATE TABLE语句中使用这个子句）：
sys@JINGYONG> alter table list_example add partition part_3 values(default);
表已更改。
sys@JINGYONG> insert into list_example values('VA','data');
已创建 1 行。
sys@JINGYONG> select * from list_example partition(part_3);
ST DATA
-- --------------------
VA data
值列表中未显式列出的所有值都会放到这个（DEFAULT）分区中。关于DEFAULT的使用，有一点要注意：一旦列表分区表有一个 DEFAULT分区，就不能再向这个表中增加更多的分区了：
sys@JINGYONG> alter table list_example
2 add partition part_4 values('CA','NM');
alter table list_example
*
第 1 行出现错误:
ORA-14323: cannot add partition when DEFAULT partition exists
此时必须删除DEFAULT分区，然后增加PART_4，最后再加回DEFAULT分区。原因在于，原来DEFAULT分区可以有列表分区键值为CA 或NM的行，但增加PART_4之后，这些行将不再属于DEFAULT分区。
组合分区
最后我们会看到组合分区（composite partitioning）的一些例子，组合分区是区间分区和散列分区的组合，或者是区间分区与 列表分区的组合。
在组合分区中，顶层分区机制总是区间分区。第二级分区机制可能是列表分区或散列分区（在Oracle9i Release 1及以前的版本 中，只支持散列子分区，而没有列表分区）。有意思的是，使用组合分区时，并没有分区段，而只有子分区段。分区本身并没有 段（这就类似于分区表没有段）。数据物理的存储在子分区段上，分区成为一个逻辑容器，或者是一个指向实际子分区的容器。
在下面的例子中，我们将查看一个区间-散列组合分区。在此对区间分区使用的列集不同于散列分区使用的列集。并不是非得如 此，这两层分区也可以使用同样的列集：
sys@JINGYONG> create table composite_example
2 (range_key_column date,
3 hash_key_column int,
4 data varchar2(20)
5 )
6 partition by range(range_key_column)
7 subpartition by hash(hash_key_column) subpartitions 2
8 (
9 partition part_1
10 values less than(to_date('2005-01-01','yyyy-mm-dd'))
11 (subpartition part_1_sub_1,
12 subpartition part_1_sub_2
13 ),
14 partition part_2
15 values less than(to_date('2006-01-01','yyyy-mm-dd'))
16 (
17 subpartition part_2_sub_1,
18 subpartition part_2_sub_2
19 )
20 );
表已创建。
在区间-散列组合分区中，Oracle首先会应用区间分区规则，得出数据属于哪个区间。然后再应用散列函数，来确定数据最后要 放在哪个物理分区中
sys@JINGYONG> insert into composite_example values(to_date('2004-12-01','yyyy-mm
-dd'),2004,'2004');
已创建 1 行。
sys@JINGYONG> insert into composite_example values(to_date('2005-01-01','yyyy-mm
-dd'),2005,'2005');
已创建 1 行。
sys@JINGYONG> insert into composite_example values(to_date('2004-01-01','yyyy-mm
-dd'),2006,'2004');
已创建 1 行。
sys@JINGYONG> insert into composite_example values(to_date('2005-11-01','yyyy-mm
-dd'),2006,'2004');
已创建 1 行。
sys@JINGYONG> commit;
提交完成。
sys@JINGYONG> select * from composite_example partition(part_1);
RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-12月-04 2004 2004
01-1月 -04 2006 2004
sys@JINGYONG> select * from composite_example subpartition(part_1_sub_1);
RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-12月-04 2004 2004
sys@JINGYONG> select * from composite_example subpartition(part_1_sub_2);
RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04 2006 2004
sys@JINGYONG> select * from composite_example partition(part_2);
RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -05 2005 2005
01-11月-05 2006 2004
sys@JINGYONG> select * from composite_example subpartition(part_2_sub_1);
未选定行
sys@JINGYONG> select * from composite_example subpartition(part_2_sub_2);
RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -05 2005 2005
01-11月-05 2006 2004
因此，利用组合分区，你就能把数据先按区间分解，如果认为某个给定的区间还太大，或者认为有必要做进一步的分区消除，可 以再利用散列或列表将其再做分解。有意思的是，每个区间分区不需要有相同数目的子分区；例如，假设你在对一个日期列完成 区间分区，以支持数据净化（快速而且容易地删除所有就数据）。在2004年，CODE_KEY_COLUMN值为“奇数”的数据量与 CODE_KEY_COLUMN值为“偶数”的数据量是相等的。但是到了2005年，你发现与奇数吗相关的记录数是偶数吗相关的记录数的两 倍，所以你希望对应奇数码有更多的子分区。只需定义更多的子分区，就能相当容易地做到这一点：
sys@JINGYONG> create table composite_range_list_example
2 (range_key_column date,
3 code_key_column int,
4 data varchar2(20)
5 )
6 partition by range(range_key_column)
7 subpartition by list(code_key_column)
8 (
9 partition part_1
10 values less than(to_date('2005-01-01','yyyy-mm-dd'))
11 (subpartition part_1_sub_1 values(1,3,5,7),
12 subpartition part_1_sub_2 values(2,4,6,8)
13 ),
14 partition part_2
15 values less than(to_date('2006-01-01','yyyy-mm-dd'))
16 (subpartition part_2_sub_1 values(1,3),
17 subpartition part_2_sub_2 values(5,7),
18 subpartition part_2_sub_3 values(2,4,6,8)
19 )
20 );
表已创建。
在此，最后总共有5个分区：分区PART_1有两个子分区，分区PART_2有3个子分区。
sys@JINGYONG> insert into composite_range_list_example values(to_date('2004-01-0
1','yyyy-mm-dd'),1,'data');
已创建 1 行。
sys@JINGYONG> insert into composite_range_list_example values(to_date('2004-11-0
1','yyyy-mm-dd'),2,'data');
已创建 1 行。
sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),2,'data');
已创建 1 行。
sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),1,'data');
已创建 1 行。
sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),7,'data');
已创建 1 行。
sys@JINGYONG> commit;
提交完成。
sys@JINGYONG> select * from composite_range_list_example partition(part_1);
RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04 1 data
01-11月-04 2 data
sys@JINGYONG> select * from composite_range_list_example partition(part_2);
RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05 1 data
01-11月-05 7 data
01-11月-05 2 data
sys@JINGYONG> select * from composite_range_list_example subpartition(part_1_sub
_1);
RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04 1 data
sys@JINGYONG> select * from composite_range_list_example subpartition(part_1_sub
_2);
RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-04 2 data
sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_1);
RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05 1 data
sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_2);
RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05 7 data
sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_3);
RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05 2 data
行移动
你可能想知道，在前面所述的各种分区机制中，如果用于确定分区的列有修改会发生什么。需要考虑两种情况：
修改不会导致使用一个不同的分区；行仍属于原来的分区。这在所有情况下都得到支持。
修改会导致行跨分区移动。只有当表启用了行移动时才支持这种情况；否则，会产生一个错误。
这些行为很容易观察。在前面的例子中，我们向RANGE_EXAMPLE表的PART_1插入了三行：
sys@JINGYONG> insert into range_example values(to_date('2004-11-01','yyyy-mm-dd'
),'2004');
已创建 1 行。
sys@JINGYONG> insert into range_example values(to_date('2005-11-01','yyyy-mm-dd'
),'2005');
已创建 1 行。
sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');
已创建 1 行。
sys@JINGYONG> commit;
提交完成。
sys@JINGYONG> select * from range_example partition(part_1);
RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-04 2004
sys@JINGYONG> select * from range_example partition(part_2);
RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05 2005
01-1月 -05 2005
取其中一行，并更新其RANGE_KEY_COLUMN值，不过更新后它还能放在PART_1中：
sys@JINGYONG> update range_example
2 set range_key_column=trunc(range_key_column)
3 where range_key_column=to_date('2005-01-01','yyyy-mm-dd');
已更新 1 行。
sys@JINGYONG> commit;
提交完成。
sys@JINGYONG> select * from range_example partition(part_2);
RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05 2005
01-1月 -05 2005
不出所料，这会成功：行仍在分区PART_2中。接下来，再把RANGE_KEY_COLUMN更新为另一个值，但这次更新后的值将导致它属于 分区PART_1：
sys@JINGYONG> update range_example
2 set range_key_column=to_date('2004-12-01','yyyy-mm-dd')
3 where range_key_column=to_date('2005-01-01','yyyy-mm-dd');
update range_example
*
第 1 行出现错误:
ORA-14402: updating partition key column would cause a partition change
这会立即产生一个错误，因为我们没有显式地启用行移动。在Oracle8i及以后的版本中，可以在这个表上启用行移动（row movement），以允许从一个分区移动到另一个分区。注意 Oracle 8.0中没有行移动功能；在这个版本中，你必须先删除行，再 重新将其插入。不过，要注意这样做有一个小小的副作用；行的ROWID会由于更新而改变：
sys@JINGYONG> select rowid
2 from range_example
3 where range_key_column=to_date('2005-01-01','yyyy-mm-dd');
ROWID
------------------
AAAM++AABAAAPAqAAB
sys@JINGYONG> alter table range_example enable row movement;
表已更改。
sys@JINGYONG> update range_example
2 set range_key_column=to_date('2004-12-01','yyyy-mm-dd')
3 where range_key_column=to_date('2005-01-01','yyyy-mm-dd');
已更新 1 行。
sys@JINGYONG> select rowid
2 from range_example
3 where range_key_column=to_date('2004-12-01','yyyy-mm-dd');
ROWID
------------------
AAAM+9AABAAAPAiAAB
既然知道执行这个更新时行的ROWID会改变，所以要启用行移动，这样才允许更新分区键。
注意 在其他一些情况下，ROWID也有可能因为更新而改变。更新IOT的主键可能导致ROWID改变，该行的通用ROWID（UROWID）也 会改变。Oracle 10g的FLASHBACK TABLE命令可能改变行的ROWID，此外Oracle 10g的ALTER TABLE SHRINK命令也可能使行的 ROWID改变。
要知道，执行行移动时，实际上在内部就好像先删除了这一行，然后再将其重新插入。这会更新这个表上的索引，删除旧的索引 条目，再插入一个新条目。此时会完成DELETE再加一个INSERT的相应物理工作。不过，尽管在此执行了行的物理删除和插入，在 Oracle看来却还是一个更新，因此，不会导致INSERT和DELETE触发器触发，只有UPDATE触发器会触发。另外，由于外键约束可能 不允许DELETE的子表也不会触发DELETE触发器。不过，还是要对将完成的额外工作有所准备；行移动的开销比正常的UPDATE昂贵 得多。
表分区机制小结
一般来讲，如果将数据按某个（某些）值逻辑聚集，区间分区就很有用。基于时间的数据就是这方面经典的例子，如按“销售定 额”、“财政年度”或“月份”分区。在许多情况下，区间分区都能利用分区消除，这包括使用完全相等性和区间（小于、大于 、介于…之间等）。
如果不能按自然的区间进行分区，散列分区就很合适。例如，如果必须加载一个表，其中装满与人口普查相关的数据，可能无 法找到一个合适的属性来按这个属性完成区间分区。不过，你可能还是想得到分区提供的管理、性能和可用性提升等诸多好处 。在此，只需选择惟一的一个列或几乎惟一的一个列集，对其计算散列。这样一 来，无论有多少个分区，都能得到均匀的数据 分布。使用完全相等性或IN（value,value,…）时，散列分区对象可以利用分区消除，但是使用数据区间时，散列分区则无法利 用分区消除。
如果数据中有一列有一组离散值，而且根据应用使用这一列的方式来看，按这一列进行分区很有意义（例如，这样一来，查询 中可以轻松地利用分区消除），这种数据 就很适合采用列表分区。列表分区的经典例子包括按州或区域代码分区，实际上，一 般来讲许多“代码”性属性都很适合应用列表分区。
如果某些数据逻辑上可以进行区间分区，但是得到的区间分区还是太小，不能有效地管理，就可以使用组合分区。可以先应用区 间分区，再进一步划分各个区间，按一个散列函数或使用列表来分区。这样就能将I/O请求分布到任何给定大分区中的多个磁盘 上。另外，现在可以得到3个层次的分区消除。如果在区间分区键上查询，Oracle就能消除任何不满足条件的区间分区。如果向 查询增加散列或列表键，Oracle可以消除该区间中其他的散列或列表分区。如果只是在散列或列表键上查询（而不使用区间分区 键），Oracle就只会查询各个区间分区中的这些散列或列表子分区。
如果可以按某个属性自然地对数据完成区间分区，就应该使用区间分区，而不是散列分区或列表分区。散列和列表分 区能提供分区的许多突出优点，但是在分区消除方面都不如区间分区有用。如果所得到的区间分区太大，不能很好地管理；或者 如果你想使用所有PDML功能或对一个区间分区使用并行索引扫描，则建议在区间分区中再使用散列或列表分区。
参考:Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions