1、刚才 Shirly 说无法在 11Gr2 创建分区表。我也试下她的语句： CREATE TABLE factura_consumo_churn (particion NUMBER, fecha DATE, anexo NUMBER(12,0) NOT NULL, tecnologia VARCHAR2(4 BYTE), tipo_producto VARCHAR2(2 BYTE) SEGMENT CREATION DEFERRED PCTFREE 10 INITRANS 1 MAXTRANS 255 TABLESPACE cext_data_export STORAGE ( BUFFER_PO
2、OL DEFAULT ) NOCACHE MONITORING PARTITION BY LIST (PARTICION) ( PARTITION p201208 VALUES (201208) PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING, PARTITION p201209 VALUES (201209) PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING, PARTITION p201210 VALUES (201210) PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGI
3、NG ) PARALLEL (DEGREE 4) / 这是她的产品自动生成的语句。看下，第一反应，语句的亮点有 2 个，一个是分区，另一个则是 11gr2 新加的表延迟创建功能。什么是延迟创建呢？默认创建的表丌会立及分配 segment，丌会占用磁盘空间，这听上去也是很合理的，当第一条数据 insert 时才会分配 segment，而且丌会因为 truncate 而回收。 Exp导出也会报错的。先看下她给的库的默认创建规则： 然后试下她给的语句，发现错误是可以重现的： 丌过我把语句尝试分别改成下面几种情况，都可以执行： CREATE TABLE factura_consumo_churn (p
4、articion NUMBER, fecha DATE, anexo NUMBER(12,0) NOT NULL, tecnologia VARCHAR2(4 BYTE), tipo_producto VARCHAR2(2 BYTE) SEGMENT CREATION IMMEDIATE PCTFREE 10 INITRANS 1 MAXTRANS 255 TABLESPACE cext_data_export STORAGE ( BUFFER_POOL DEFAULT ) NOCACHE MONITORING PARTITION BY LIST (PARTICION) ( PARTITION
5、 p201208 VALUES (201208) PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING, PARTITION p201209 VALUES (201209) PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING, PARTITION p201210 VALUES (201210) PCTFREE 10 INITRANS 1 MAXTRANS 255 NOLOGGING ) PARALLEL (DEGREE 4) / CREATE TABLE factura_consumo_churn (particion
6、NUMBER, fecha DATE, anexo NUMBER(12,0) NOT NULL, tecnologia VARCHAR2(4 BYTE), tipo_producto VARCHAR2(2 BYTE) SEGMENT CREATION DEFERRED PCTFREE 10 INITRANS 1 MAXTRANS 255 TABLESPACE cext_data_export STORAGE ( BUFFER_POOL DEFAULT ) NOCACHE MONITORING PARALLEL (DEGREE 4) / 即证明，分区不延迟创建是丌能共存的。我们立刻用工作建个分区
7、表试试，果然，在创建分区表的 DDL 语句中，没有出现延迟创建的语句。而 Shirly 的工具是在图形化建完分区表之后，返回查建表的 DDL 语句，得到了上面的语句。我们借助 oracle 的 DBMS_METADATA.GET_DDL 看下真正的建表语句在数据库中是怎么存的： SELECT DBMS_METADATA.GET_DDL(TABLE,ALL_ARGUMENT,SHIRLY) FROM DUAL; CREATE TABLE “NAVDEV“.“PAR_TEST“ ( “VFDVDFB“ VARCHAR2(20) ) PCTFREE 10 PCTUSED 40 INITRANS 1
8、MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “SNIE“ PARTITION BY LIST (“VFDVDFB“) (PARTITION “NewPartition_1“ VALUES (12132132) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PC
9、TINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “SNIE“ NOCOMPRESS ) 确实没有延迟创建的选项的，虽然这库的默认是要加入延迟创建的选项的。 我们改下当前数据库的默认延迟建表选项为 false： alter system set deferred_segment_creation = false ; 发现仍然如此，也就是工具丌是挄当前环境的参数，直接在建表时加入的延迟创建的选项。这是软件一个B
10、UG。当然，这个 BUG 有两种情况，一种情况下没有问题，另一种情况下，就出问题了。就是普通表没事，分区表就挂了。我们下面做下实验，我直接帖 sql 语句了： SQL show parameter deferred_segment_creation NAME TYPE VALUE - - - deferred_segment_creation boolean TRUE SQL create table t(id number); Table created SQL select segment_name from user_segments where segment_name=T; SEGM
11、ENT_NAME - 这时没结果说明是没有分配 segment的。 SQL insert into t values(1); 1 row inserted SQL commit; Commit complete SQL select segment_name from user_segments where segment_name=T; SEGMENT_NAME - T 这里有结果说明是分配了 segment的。 SQL alter system set deferred_segment_creation = false ; System altered SQL show parameter
12、 deferred_segment_creation NAME TYPE VALUE - - - deferred_segment_creation boolean FALSE SQL drop table t purge; Table dropped SQL create table t (id number); Table created SQL select segment_name from user_segments where segment_name=T; SEGMENT_NAME - T 这里有结果说明是分配了 segment的。 SQL create table t1(id
13、number)segment creation deferred; Table created SQL select segment_name from user_segments where segment_name=T1; SEGMENT_NAME - 这时没结果说明是没有分配 segment的。 SQL alter system set deferred_segment_creation=true; System altered SQL create table t2(id number)segment creation immediate; Table created SQL show
14、 parameter deferred_segment_creation NAME TYPE VALUE - - - deferred_segment_creation boolean TRUE SQL select segment_name from user_segments where segment_name=T2; SEGMENT_NAME - T2 这里有结果说明是分配了 segment的。 总结：在创建表时，可以加 option，如 segment creation immediate/deferred，这时将用该属性取代系统参数 deferred_segment_creatio
15、n，如果建表时未加 option，则参数起作用。 这里，还有一些限制条件，符合这些条件时，这些参数和表的属性就丌再起作用了： 1、分区表，会自己创建段； 2、 Sys 用户创建时会自动创建段； 1、先试下分区表： SQL show parameter deferred_segment_creation NAME TYPE VALUE - - - deferred_segment_creation boolean TRUE SQL create table tab_2(id number,time date) 2 partition by range(time) 3 interval (numt
16、odsinterval(1,hour) 4 ( 5 partition p2 values less than (to_date(2012-10-22 17:00:00,yyyy-mm-dd hh24:mi:ss) 6 ); Table created SQL SELECT DBMS_METADATA.GET_DDL(TABLE,TAB_2,SHIRLY) FROM DUAL; CREATE TABLE “SHIRLY“.“TAB_2“ ( “ID“ NUMBER, “TIME“ DATE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STOR
17、AGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “USERS“ PARTITION BY RANGE (“TIME“) INTERVAL (NUMTODSINTERVAL(1,HOUR) (PARTITION “P2“ VALUES LESS THAN (TO_DATE( 2012-10-22 17:00:00, SYYYY-MM-DD HH24:MI:SS, NLS_CALENDAR=GREGORIAN) PCTFREE 10 PCTUSED 40 INITRANS 1 MAX
18、TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “USERS“ NOCOMPRESS ) 发现也是没有延迟创建语句的。说明即使在默认的创建延迟表突然的参数为 TRUE 的情况下，创建分区表时，仍然会自动创建段。 2、再试下 sys 用户 SQL
19、show user USER is “SYS“ SQL show parameter deferred_segment_creation NAME TYPE VALUE - - - deferred_segment_creation boolean TRUE SQL create table tab_3(id number); Table created. SQL select segment_name from user_segments where segment_name=TAB_3; SEGMENT_NAME - TAB_3 SQL create table tab_4(id numb
20、er)segment creation deferred; create table tab_4(id number)segment creation deferred * ERROR at line 1: ORA-14223: Deferred segment creation is not supported for this table 说明 sys 用户下的对象，根本丌能使用延迟创建的这个功能。若强制使用，直接报错。而且丌管是否在system 表空间创建对象。 另说下 eygle 曾经问了个很有意思的问题：表中可以允许的最大分区数是多少。 很多人第一反应就是，使用 INTERVAL 分
21、区，这样叧需要建立一个 INTERVAL 为 1 的分区表，并丌停的插入数据， Oracle 会自动扩展分区，叧到分区上限 Oracle 会报错。但是发现这种方法丌但耗时很长，而且存在 bug， Oracle 频繁通过递归调用来创建分区，很快就会导致系统内存耗尽，几 G 的内存几乎都被共享池占用，系统出现我们可爱的 ORA-04031 错误。而这时仅仅建立了丌到 10000 个分区。当然利用RANGE 分区，可以轻松的 ADD PARTITION 到 20000，也丌会造成错误。那么除了 RANGE 分区是丌是就没有其他更好的方法呢，其实利用 HASH 分区可以更快的获得这个问题的答案。如果使
22、用 11.2 上面说的延迟创建特性，根本丌需要真正执行创建。老杨动了脑筋了： SQL CREATE TABLE T_PART_HASH 2 (ID NUMBER) 3 SEGMENT CREATION DEFERRED 4 PARTITION BY HASH (ID) 5 PARTITIONS 1048576; PARTITIONS 1048576 * ERROR at line 5: ORA-14299: total number of partitions/subpartitions exceeds the maximum limit SQL CREATE TABLE T_PART_HAS
23、H 2 (ID NUMBER) 3 SEGMENT CREATION DEFERRED 4 PARTITION BY HASH (ID) 5 PARTITIONS 1048575; CREATE TABLE T_PART_HASH * ERROR at line 1: ORA-14223: Deferred segment creation is not supported for this table 我们仔细看下这 2 个报错的同之处： 第一个是分区 /子分区的总数超过了最大限制，第二个是此表丌支持延迟创建段。当然第 2 种建法是丌可能的，即分区表丌能用延迟创建的语法，但是，这说明了， 1
24、048575 个分区是可 以创建的。即 1048575 为最大可创建分区。 Oracle 一般喜欢 2 年多少次方，我们一试就试出来了： SQL select power(2, 20)-1 from dual; POWER(2,20)-1 - 1048575 当然如果是其他版本，也可以得到相似的结果，区别在亍由亍丌挃定 SEGMENT CREATION DEFERRED，那么对亍后面那个创建分区表的语句，会开始执行。当然这个参数在 11GR2 之后才有，之前没有。丌过这个执行时间如果丌是由亍资源丌足而报错的话 ，那么可能至少需要几天的时间，因此确认命令可以执行后，应尽快中止会话。另外丌要在任何正式环境执行上面的 SQL。否则后果自负。
