• Oracle的分区操作和修改分区主键


    1、增加一个分区
    ALTER TABLE sales
    ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )
    TABLESPACE tsx;
    增加一个列表分区
    ALTER TABLE q1_sales_by_region
    ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
    STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
    NOLOGGING;
    2、合并分区

    alter table dept coalesce partition

    3、删除分区
    ALTER TABLE sales DROP PARTITION dec98;
    ALTER INDEX sales_area_ix REBUILD(如果含有全局索引);

    4、合并分区
    ALTER TABLE four_seasons
    MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;


    Then, rebuild the local index for the affected partition.

    -- Rebuild index for quarter_two, which has been marked unusable
    -- because it has not had all of the data from Q1 added to it.
    -- Rebuilding the index will correct this.
    --
    ALTER TABLE four_seasons MODIFY PARTITION
    quarter_two REBUILD UNUSABLE LOCAL INDEXES;

    5、移动一个分区
    ALTER TABLE parts MOVE PARTITION depot2
    TABLESPACE ts094 NOLOGGING;

    6、重建一个local索引
    ALTER INDEX I_FOUR_SEASONS_L REBUILD PARTITION I_QUARTER_FOUR

    7、重命名一个分区
    ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks

    8、一个分区拆分为两个分区
    ALTER TABLE RANGE_EXAMPLE SPLIT PARTITION
    PART_1 at
    (TO_DATE(' 1994-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO ( PARTITION PART_1 tablespace st1,
    PARTITION PART_3 tablespace users)

    9、truncate一个分区
    ALTER TABLE sales TRUNCATE PARTITION dec98;
    ALTER INDEX sales_area_ix REBUILD;

    10、如果存在约束的情况,先disable约束
    ALTER TABLE sales
    DISABLE CONSTRAINT dname_sales1;
    ALTER TABLE sales TRUNCATE PARTITTION dec94;
    ALTER TABLE sales
    ENABLE CONSTRAINT dname_sales1;

    11、修改分区主键---要打开行迁移功能

    例:分区主键为330482

    alter table lymx enable row movement;--开启行迁移
    update lymx set lqbabh='e05d5fbf13684a42aaf00226093d4e9c',xzqbm='330482',fzjgbh='f4734d2a1d494987873b0b42132e63b6',gxsj=to_date('2014/01/16 14:41:39','yyyy/MM/dd hh24:mi:ss'),tjsj=to_date('2014/01/16 14:41:39','yyyy/MM/dd hh24:mi:ss')
    where zsgbh between '027978251' and '027978500';

  • 相关阅读:
    搭建你的Spring.Net+Nhibernate+Asp.Net Mvc 框架 (五)测试你的成果
    初识Asp.Net MVC2.0
    搭建你的Spring.Net+Nhibernate+Asp.Net Mvc 框架 (四)配置全攻略
    Asp.Net MVC2.0 Url 路由入门
    逝去的2010,期待平静的2011【续】
    搭建你的Spring.Net+Nhibernate+Asp.Net Mvc 框架 (六)写在后面的话
    初识Asp.Net MVC2.0【续】
    Nhibernate入门与demo
    Entity Framework快速入门CodeOnly POCO
    用友面试经历 续【最终遭拒】
  • 原文地址:https://www.cnblogs.com/lt-style/p/3668342.html
Copyright © 2020-2023  润新知