• 数据迁移


    pkill -9 java 停止服务


    create table sic_hi_guest_201608 as select * from sic_hi_guest partition(PART201608);
    alter table sic_hi_guest drop partition PART201608;

    alter index INDEX_SIC_HI_GUEST_CERTNO_NEW rebuild;
    alter index INDEX_SIC_HI_GUEST_CHETIME_N rebuild;
    alter index INDEX_SIC_HI_GUEST_DEPIME_NEW rebuild;
    alter index INDEX_SIC_HI_GUEST_GROUNO_NEW rebuild;
    alter index INDEX_SIC_HI_GUEST_GUESNO_NEW rebuild;
    alter index INDEX_SIC_HI_GUEST_GUESTD_NEW rebuild;
    alter index INDEX_SIC_HI_GUEST_LONGINO_NEW rebuild;
    alter index INDEX_SIC_HI_GUEST_PHOTID_NEW rebuild;
    alter index INDEX_SIC_HI_GUEST_ROOMID_NEW rebuild;
    alter index PK_SIC_HI_GUEST_NEW rebuild;
    alter index SIC_HI_GUEST_DATASOURCES_NEW rebuild;
    alter index SIC_HI_GUEST_INPUTIME_NEW rebuild;

    --定期删除脚本
    [root@test oracle]# more r_sic_guest.sh
    #!/bin/bash
    today=`date -d '-2 month' +%Y%m`
    oldtable=sic_hi_guest_$today
    partable=PART$today
    fname=${partable}.sql
    newfile=$fname
    echo "drop table $oldtable CASCADE CONSTRAINTS;" >$newfile
    echo "create table $oldtable as select * from sic_hi_guest partition(${partable});" >>$newfile
    echo "alter table sic_hi_guest drop partition $partable;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_CERTNO_NEW rebuild;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_CHETIME_N rebuild;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_DEPIME_NEW rebuild;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_GROUNO_NEW rebuild;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_GUESNO_NEW rebuild;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_GUESTD_NEW rebuild;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_LONGINO_NEW rebuild;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_PHOTID_NEW rebuild;" >>$newfile
    echo "alter index INDEX_SIC_HI_GUEST_ROOMID_NEW rebuild;" >>$newfile
    echo "alter index PK_SIC_HI_GUEST_NEW rebuild;" >>$newfile
    echo "alter index SIC_HI_GUEST_INPUTIME_NEW rebuild;" >>$newfile
    echo "quit" >>$newfile
    sqlplus gpsuser/gpsuser123@gpstest @/home/oracle/$newfile >> init.log

    添加字段
    -- Add/modify columns
    alter table SIC_HI_GUEST add datasources varchar2(1) default 0;
    -- Add comments to the columns
    comment on column SIC_HI_GUEST.datasources
    is '0 BS端录入的数据 1 手机端录入数据 2 CS端录入的数据';

    创建分区索引:

    -- Create/Recreate indexes
    create index INDEX_SIC_HI_GUEST_CERTNO_NEW on SIC_HI_GUEST (CERTNO)
    local;
    create index INDEX_SIC_HI_GUEST_CHETIME_N on SIC_HI_GUEST (CHECINTIME)
    local;
    create index INDEX_SIC_HI_GUEST_DEPIME_NEW on SIC_HI_GUEST (DEPATIME)
    local;
    create index INDEX_SIC_HI_GUEST_GROUNO_NEW on SIC_HI_GUEST (GROUNO)
    local;
    create index INDEX_SIC_HI_GUEST_GUESNO_NEW on SIC_HI_GUEST (SUBSTR(GUESNO,1,14))
    local;
    create index INDEX_SIC_HI_GUEST_GUESTD_NEW on SIC_HI_GUEST (GUESTOGEID)
    local;
    create index INDEX_SIC_HI_GUEST_LONGINO_NEW on SIC_HI_GUEST (LONGRESINO)
    local;
    create index INDEX_SIC_HI_GUEST_PHOTID_NEW on SIC_HI_GUEST (PHOTID)
    local;
    create index INDEX_SIC_HI_GUEST_ROOMID_NEW on SIC_HI_GUEST (ROOMID)
    local;
    create unique index PK_SIC_HI_GUEST_NEW on SIC_HI_GUEST (GUESNO, OPERTIME_MONTH, OPERTIME_DAY)
    local;
    create index SIC_HI_GUEST_INPUTIME_NEW on SIC_HI_GUEST (INPUTIME)
    local;


    查看索引状态:
    select index_name,index_type,status from dba_indexes where table_name='SIC_HI_GUEST'

    select index_name,partition_name,status from user_ind_partitions where INDEX_NAME IN (SELECT INDEX_NAME From user_part_indexes where table_name='SIC_HI_GUEST')

    select index_name,partition_name,subpartition_name,status from user_ind_subpartitions where INDEX_NAME IN (SELECT INDEX_NAME From user_part_indexes where table_name='SIC_HI_GUEST')


    下面我手工将索引的状态修改为UNUSABLE,模拟索引破坏的情况(比如移动分区的情况)
    alter index INDEX_SIC_HI_GUEST_CERTNO_NEW unusable;

    注意:
    alter index INDEX_SIC_HI_GUEST_CHETIME_N rebuild partition PART201411
    ORA-14287:不能REBUILD(重建)组合范围分区的索引的分区


    alter index INDEX_SIC_HI_GUEST_CHETIME_N rebuild subpartition PART20141101;
    索引已更改。

    这样重建子分区的所有索引后索引才可以用。

  • 相关阅读:
    解密:腾讯如何打造一款实时对战手游
    哪是来的自尊心
    NODEJS 在Centos下面的自动启动
    nodejs的安装与配置
    基于Centos7+Nginx+Tomcat8的负载均衡服务器的搭建
    门店管理系统架构-(1)
    PHP 使用编码树,生成easyui中的tree样式
    Apache 打开网页的时候等待时间过长的解决方案
    Apache2.4开启GZIP功能
    Apache+Tomcat实现负载均衡
  • 原文地址:https://www.cnblogs.com/myibm/p/5939535.html
Copyright © 2020-2023  润新知