修改自动分区的分区名

最新推荐文章于 2024-06-21 09:52:13 发布

欣欣闹天下

最新推荐文章于 2024-06-21 09:52:13 发布

阅读量335

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/qq_40066204/article/details/113776810

版权

Oracle 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

  CREATE TABLE "DBMT"."INTERVAL2"
   (    "ID" VARCHAR2(64) DEFAULT SYS_GUID() NOT NULL ENABLE,
        "PRI_KEY" VARCHAR2(60),
        "ORG_NAME" VARCHAR2(240),
        "ORG_ID" VARCHAR2(24),
        "IVN_CODE" VARCHAR2(10),
        "INV_NAME" VARCHAR2(24),
        "MATERIAL_TYPE_NAME1" VARCHAR2(24),
        "MATERIAL_TYPE_CODE1" VARCHAR2(24),
        "MATERIAL_TYPE_NAME2" VARCHAR2(60),
        "MATERIAL_TYPE_CODE2" VARCHAR2(60),
        "MATERIAL_TYPE_NAME3" VARCHAR2(60),
        "MATERIAL_TYPE_CODE3" VARCHAR2(60),
        "SUBINVENTORY_CODE" VARCHAR2(10),
        "SUBINVENTORY_NAME" VARCHAR2(240),
        "SUBINVENTORY_TYPE" VARCHAR2(240),
        "ITEM_TYPE" VARCHAR2(20),
        "TRANSACTION_PROCESS" VARCHAR2(100),
        "BEGIN_AMOUNT" NUMBER(18,4),
        "BEGIN_QUANTITY" NUMBER(18,4),
        "PERIOD_AMOUNT" NUMBER(18,4),
        "PERIOD_QUANTITY" NUMBER(18,4),
        "END_AMOUNT" NUMBER(18,4),
        "END_QUANTITY" NUMBER(18,4),
        "SUMMARY_TIME" DATE,
        "REFERENCE1" VARCHAR2(240),
        "REFERENCE2" VARCHAR2(240),
        "REFERENCE3" VARCHAR2(240),
        "REFERENCE4" VARCHAR2(240),
        "REFERENCE5" VARCHAR2(240),
        "REFERENCE6" VARCHAR2(240),
        "REFERENCE7" VARCHAR2(240),
        "REFERENCE8" VARCHAR2(240),
        "REFERENCE9" VARCHAR2(240),
        "REFERENCE10" VARCHAR2(240),
        "REFERENCE11" VARCHAR2(240),
        "REFERENCE12" VARCHAR2(240),
        "REFERENCE13" VARCHAR2(240),
        "REFERENCE14" VARCHAR2(240),
        "REFERENCE15" VARCHAR2(240),
        "ENTITY_STATUS" VARCHAR2(50) DEFAULT 'draft',
        "SUBMIT_DATE" DATE,
        "CREATE_DATE" DATE DEFAULT sysdate,
        "INSTANCE_ID" VARCHAR2(30)
   )
  PARTITION BY RANGE ("SUMMARY_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 (PARTITION "P0"  VALUES LESS THAN (TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE);




SQL> insert into DBMT.INTERVAL2 select * from  dbmt.INTERVAL1;

已创建 7648504 行。


SQL> 
SQL> 
SQL> @seg DBMT.INTERVAL2

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME      BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------- ---------- ----------
         8 DBMT                 INTERVAL2                      SYS_P1000                      TABLE PARTITION      DATA123                    1024         63     815249
         8 DBMT                 INTERVAL2                      SYS_P1001                      TABLE PARTITION      DATA123                    1024         63     816273
         8 DBMT                 INTERVAL2                      SYS_P1002                      TABLE PARTITION      DATA123                    1024         63     817297
         8 DBMT                 INTERVAL2                      SYS_P1003                      TABLE PARTITION      DATA123                    1024         63     818321
         8 DBMT                 INTERVAL2                      SYS_P1004                      TABLE PARTITION      DATA123                    1024         63     819345
         8 DBMT                 INTERVAL2                      SYS_P1005                      TABLE PARTITION      DATA123                    1024         63     820369
         8 DBMT                 INTERVAL2                      SYS_P1006                      TABLE PARTITION      DATA123                    1024         63     821393
         8 DBMT                 INTERVAL2                      SYS_P1007                      TABLE PARTITION      DATA123                    1024         63     822417
         8 DBMT                 INTERVAL2                      SYS_P1008                      TABLE PARTITION      DATA123                    1024         63     823441
         8 DBMT                 INTERVAL2                      SYS_P1009                      TABLE PARTITION      DATA123                    1024         63     824465
         8 DBMT                 INTERVAL2                      SYS_P1010                      TABLE PARTITION      DATA123                    1024         63     825489
         8 DBMT                 INTERVAL2                      SYS_P1011                      TABLE PARTITION      DATA123                    1024         63     826513
         8 DBMT                 INTERVAL2                      SYS_P1012                      TABLE PARTITION      DATA123                    1024         63     827537
         8 DBMT                 INTERVAL2                      SYS_P1013                      TABLE PARTITION      DATA123                    1024         63     828561
         8 DBMT                 INTERVAL2                      SYS_P1014                      TABLE PARTITION      DATA123                    1024         63     829585

运行脚本修改自动分区的分区名
SQL> @aectg
修改之后的分区名如下


    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                                                            BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------------------------------------ ---------- ---------- ----------
         8 DBMT                 INTERVAL2                      P0                             TABLE PARTITION      DATA123                                                                                          1024         63    1587345
         8 DBMT                 INTERVAL2                      PART_20160701                  TABLE PARTITION      DATA123                                                                                          1024         63    1618065
         8 DBMT                 INTERVAL2                      PART_20160702                  TABLE PARTITION      DATA123                                                                                          1024         63    1588369
         8 DBMT                 INTERVAL2                      PART_20160703                  TABLE PARTITION      DATA123                                                                                          1024         63    1592465
         8 DBMT                 INTERVAL2                      PART_20160704                  TABLE PARTITION      DATA123                                                                                          1024         63    1591441
         8 DBMT                 INTERVAL2                      PART_20160705                  TABLE PARTITION      DATA123                                                                                          1024         63    1590417
         8 DBMT                 INTERVAL2                      PART_20160706                  TABLE PARTITION      DATA123                                                                                          1024         63    1589393
         8 DBMT                 INTERVAL2                      PART_20160707                  TABLE PARTITION      DATA123                                                                                          1024         63    1595537
         8 DBMT                 INTERVAL2                      PART_20160708                  TABLE PARTITION      DATA123                                                                                          1024         63    1598609
         8 DBMT                 INTERVAL2                      PART_20160709                  TABLE PARTITION      DATA123                                                                                          1024         63    1597585
         8 DBMT                 INTERVAL2                      PART_20160710                  TABLE PARTITION      DATA123                                                                                          1024         63    1593489
         8 DBMT                 INTERVAL2                      PART_20160711                  TABLE PARTITION      DATA123                                                                                          1024         63    1596561
         8 DBMT                 INTERVAL2                      PART_20160712                  TABLE PARTITION      DATA123                                                                                          1024         63    1599633
         8 DBMT                 INTERVAL2                      PART_20160713                  TABLE PARTITION      DATA123                                                                                          1024         63    1600657
         8 DBMT                 INTERVAL2                      PART_20160714                  TABLE PARTITION      DATA123                                                                                          1024         63    1594513
         8 DBMT                 INTERVAL2                      PART_20160715                  TABLE PARTITION      DATA123                                                                                          1024         63    1604753
         8 DBMT                 INTERVAL2                      PART_20160716                  TABLE PARTITION      DATA123                                                                                          1024         63    1603729
         8 DBMT                 INTERVAL2                      PART_20160717                  TABLE PARTITION      DATA123                                                                                          1024         63    1602705
         8 DBMT                 INTERVAL2                      PART_20160718                  TABLE PARTITION      DATA123                                                                                          1024         63    1606801
         8 DBMT                 INTERVAL2                      PART_20160719                  TABLE PARTITION      DATA123                                                                                          1024         63    1605777
         8 DBMT                 INTERVAL2                      PART_20160720                  TABLE PARTITION      DATA123                                                                                          1024         63    1608849