分区表无法使用延迟创建选项.docx

刚才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_POOL 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 NOLOGGING ) PARALLEL (DEGREE 4)/这是她的产品自动生成的语句。看下，第一反应，语句的亮点有2个，一个是分区，另一个则是11gr2新加的表延迟创建功能。什么是延迟创建呢？默认创建的表不会立及分配segment，不会占用磁盘空间，这听上去也是很合理的，当第一条数据insert时才会分配segment，而且不会因为truncate而回收。Exp导出也会报错的。先看下她给的库的默认创建规则：然后试下她给的语句，发现错误是可以重现的：不过我把语句尝试分别改成下面几种情况，都可以执行：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 IMMEDIATE PCTFREE 10 INITRANS 1 MAXTRANS 255 TABLESPACE cext_data_export STORAGE ( BUFFER_POOL 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 NOLOGGING ) PARALLEL (DEGREE 4)/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_POOL DEFAULT ) NOCACHE MONITORING PARALLEL (DEGREE 4)/即证明，分区与延迟创建是不能共存的。我们立刻用工作建个分区表试试，果然，在创建分区表的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 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 PCTINCREASE 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 ;发现仍然如此，也就是工具不是按当前环境的参数，直接在建表时加入的延迟创建的选项。这是软件一个BUG。当然，这个BUG有两种情况，一种情况下没有问题，另一种情况下，就出问题了。就是普通表没事，分区表就挂了。我们下面做下实验，我直接帖sql语句了：SQL show parameter deferred_segment_creationNAME TYPE VALUE- - -deferred_segment_creation boolean TRUESQL create table t(id number);Table createdSQL select segment_name from user_segments where segment_name=T;SEGMENT_NAME-这时没结果说明是没有分配segment的。 SQL insert into t values(1);1 row insertedSQL commit;Commit completeSQL select segment_name from user_segments where segment_name=T;SEGMENT_NAME-T这里有结果说明是分配了segment的。 SQL alter system set deferred_segment_creation = false ;System alteredSQL show parameter deferred_segment_creationNAME TYPE VALUE- - -deferred_segment_creation boolean FALSESQL drop table t purge;Table droppedSQL create table t (id number);Table createdSQL select segment_name from user_segments where segment_name=T;SEGMENT_NAME-T这里有结果说明是分配了segment的。 SQL create table t1(id number)segment creation deferred;Table createdSQL select segment_name from user_segments where segment_name=T1;SEGMENT_NAME-这时没结果说明是没有分配segment的。 SQL alter system set deferred_segment_creation=true;System alteredSQL create table t2(id number)segment creation immediate;Table createdSQL show parameter deferred_segment_creationNAME TYPE VALUE- - -deferred_segment_creation boolean TRUESQL select segment_name from user_segments where segment_name=T2;SEGMENT_NAME-T2这里有结果说明是分配了segment的。 总结：在创建表时，可以加option，如segment creation immediate/deferred，这时将用该属性取代系统参数deferred_segment_creation，如果建表时未加option，则参数起作用。这里，还有一些限制条件，符合这些条件时，这些参数和表的属性就不再起作用了：1、分区表，会自己创建段；2、Sys用户创建时会自动创建段；1、先试下分区表：SQL show parameter deferred_segment_creationNAME TYPE VALUE- - -deferred_segment_creation boolean TRUESQL create table tab_2(id number,time date) 2 partition by range(time) 3 interval (numtodsinterval(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 createdSQL 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 STORAGE( 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 MAXTRANS 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 show userUSER is SYSSQL show parameter deferred_segment_creationNAME TYPE VALUE- - -deferred_segment_creation boolean TRUESQL create table tab_3(id number);Table created.SQL select segment_name from user_segments where segment_name=TAB_3;SEGMENT_NAME-TAB_3SQL create table tab_4(id number)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分区，这样只需要建立一个INTERVAL为1的分区表，并不停的插入数据，Oracle会自动扩展分区，只到分区上限Oracle会报错。但是发现这种方法不但耗时很长，而且存在bug，Oracle频繁通过递归调用来创建分区，很快就会导致系统内存耗尽，几G的内存几乎都被共享池占用，系统出现我们可爱的ORA-04031错误。而这时仅仅建立了不到10000个分区。当然利用RANGE分区，可以轻松的ADD PARTITION到20000，也不会造成错误。那么除了RANGE分区是不是就没有其他更好的方法呢，其实利用HASH分区可以更快的获得这个问题的答案。如果使用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