1、新建零时表
set time on timing on
create table RPTUSER.RPT_TMP_10086BDL_bak
(
HANDLEDATE DATE,
TEL_CALLING VARCHAR2(15)
) TABLESPACE "IS_IEF_CBT"
PARTITION BY RANGE ("HANDLEDATE")
( partition P201805 values less than (TO_DATE('2018-06-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201806 values less than (TO_DATE('2018-07-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201807 values less than (TO_DATE('2018-08-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201808 values less than (TO_DATE('2018-09-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201809 values less than (TO_DATE('2018-10-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201810 values less than (TO_DATE('2018-11-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201811 values less than (TO_DATE('2018-12-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201812 values less than (TO_DATE('2019-01-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201901 values less than (TO_DATE('2019-02-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201902 values less than (TO_DATE('2019-03-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201903 values less than (TO_DATE('2019-04-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201904 values less than (TO_DATE('2019-05-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201905 values less than (TO_DATE('2019-06-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201906 values less than (TO_DATE('2019-07-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201907 values less than (TO_DATE('2019-08-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201908 values less than (TO_DATE('2019-09-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201909 values less than (TO_DATE('2019-10-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201910 values less than (TO_DATE('2019-11-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201911 values less than (TO_DATE('2019-12-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition P201912 values less than (TO_DATE('2020-01-01 00:00:00','YYYY-MM-DD hh24:mi:ss')) tablespace IS_IEF_CBT,
partition pmax values less than (MAXVALUE) tablespace IS_IEF_CBT);
2、创建零时失效对象表
create table system.dba_object_20180820 as select * from dba_objects;
select count(*) from system.dba_object_20180820 where status <> 'VALID';
3、rename表
declare
begin
execute immediate 'alter table RPTUSER.RPT_TMP_10086BDL rename to RPT_TMP_10086BDL_20180820';
execute immediate 'alter table RPTUSER.RPT_TMP_10086BDL_bak rename to RPT_TMP_10086BDL';
end ;
/
4、对比是否有新增失效对象
select count(*) from system.dba_object_20180820 where status <> 'VALID'
union all
select count(*) from dba_objects where status <> 'VALID';
5、对比两个表的权限
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
' to ' || grantee || ';' grant_sql
from dba_tab_privs
where table_name = 'RPT_TMP_10086BDL_20180820';
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
' to ' || grantee || ';' grant_sql
from dba_tab_privs
where table_name = 'RPT_TMP_10086BDL';
6、插入表数据
insert into RPTUSER.RPT_TMP_10086BDL select * from RPTUSER.RPT_TMP_10086BDL_20180820
where HANDLEDATE >= to_date('2018-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and HANDLEDATE < to_date('2018-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
commit;
7.--收集统计信息
set line 300 pages 2000 long 999999
select 'exec dbms_stats.gather_table_stats (ownname=>''' || table_owner ||
''', tabname=>''' || table_name || ''', partname=>''' ||
partition_name ||
''',method_opt=>''FOR ALL COLUMNS SIZE 1'',estimate_percent=>10,degree=>10,cascade=>true);'
from dba_tab_partitions
where table_owner = 'RPTUSER'
and table_name = 'RPT_TMP_10086BDL'
and last_analyzed is null
order by partition_name;
exec dbms_stats.gather_table_stats (ownname=>'RPTUSER', tabname=>'RPT_TMP_10086BDL', partname=>'P201805',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>10,degree=>10,cascade=>true);
exec dbms_stats.gather_table_stats (ownname=>'RPTUSER', tabname=>'RPT_TMP_10086BDL', partname=>'P201806',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>10,degree=>10,cascade=>true);
exec dbms_stats.gather_table_stats (ownname=>'RPTUSER', tabname=>'RPT_TMP_10086BDL', partname=>'P201807',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>10,degree=>10,cascade=>true);
exec dbms_stats.gather_table_stats (ownname=>'RPTUSER', tabname=>'RPT_TMP_10086BDL', partname=>'P201808',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>10,degree=>10,cascade=>true);
--查看收集到的统计信息
ora tstat RPT_TMP_10086BDL RPTUSER
8、备份
expdp "/ as sysdba" directory=DIR_DP tables=RPTUSER.RPT_TMP_10086BDL_20180820 dumpfile=RPTUSER.RPT_TMP_10086BDL_20180820_%U.dmp compression=all filesize=12g parallel=5 logfile=RPTUSER.RPT_TMP_10086BDL_20180820.log
9、MOVE剩余数据到其他表空间
alter table RPTUSER.RPT_TMP_10086BDL_20180820 move tablespace T_WMSUSERN parallel 4;
10 rebuild 索引
ora unusable
ALTER INDEX RPTUSER.RPT_TMP_10086BDL1 REBUILD ONLINE tablespace TS_WMS_DATA;
######################################回退方案###############################################
declare
begin
execute immediate 'alter table RPTUSER.RPT_TMP_10086BDL rename to RPTUSER.RPT_TMP_10086BDL_bak';
execute immediate 'alter table RPTUSER.RPT_TMP_10086BDL_20180820 rename to RPTUSER.RPT_TMP_10086BDL';
end ;
/