数据迁移

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;
索引已更改。

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

转载于:https://www.cnblogs.com/myibm/p/5939535.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值