SO2.INS_PROD_INS_SRV_218
生成一个dba_objects的临时表
create table system.dba_object_20180417 as select * from dba_objects;
select count(*) from system.dba_object_20180417 where status <> 'VALID';
--1、检查表是否可以在线重定义
SET SERVEROUTPUT ON
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SO2','INS_PROD_INS_SRV_218', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
--2、创建临时表及索引
set time on timing on
create table SO2.INS_PROD_INS_SRV_218_20180417
(
PROD_SRV_RELAT_ID NUMBER(14) not null,
OFFER_INST_ID NUMBER(14),
PROD_INST_ID NUMBER(14) not null,
USER_ID NUMBER(14) not null,
SERVICE_ID NUMBER(12),
STATE NUMBER(2),
DONE_CODE NUMBER(14),
CREATE_DATE DATE,
DONE_DATE DATE,
EFFECTIVE_DATE DATE not null,
EXPIRE_DATE DATE not null,
COUNTY_CODE VARCHAR2(6),
OP_ID NUMBER(12),
ORG_ID NUMBER(12),
REGION_ID VARCHAR2(6),
EFFECTIVE_DATE_TYPE NUMBER(6),
EXPIRE_DATE_TYPE NUMBER(6)
) TABLESPACE "YY_DATA"
PARTITION BY RANGE ("EXPIRE_DATE")
( partition P2013 values less than (TO_DATE('2014-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2014 values less than (TO_DATE('2015-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2015 values less than (TO_DATE('2016-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2016 values less than (TO_DATE('2017-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2017 values less than (TO_DATE('2018-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2018 values less than (TO_DATE('2019-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2019 values less than (TO_DATE('2020-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2020 values less than (TO_DATE('2021-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2021 values less than (TO_DATE('2022-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2022 values less than (TO_DATE('2023-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2023 values less than (TO_DATE('2024-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2024 values less than (TO_DATE('2025-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2025 values less than (TO_DATE('2026-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2026 values less than (TO_DATE('2027-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2027 values less than (TO_DATE('2028-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2028 values less than (TO_DATE('2029-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2029 values less than (TO_DATE('2030-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2030 values less than (TO_DATE('2031-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2031 values less than (TO_DATE('2032-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2032 values less than (TO_DATE('2033-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2033 values less than (TO_DATE('2034-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2034 values less than (TO_DATE('2035-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2035 values less than (TO_DATE('2036-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2036 values less than (TO_DATE('2037-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2037 values less than (TO_DATE('2038-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2038 values less than (TO_DATE('2039-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2039 values less than (TO_DATE('2040-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2040 values less than (TO_DATE('2041-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2041 values less than (TO_DATE('2042-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2042 values less than (TO_DATE('2043-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2043 values less than (TO_DATE('2044-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2044 values less than (TO_DATE('2045-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2045 values less than (TO_DATE('2046-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2046 values less than (TO_DATE('2047-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2047 values less than (TO_DATE('2048-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2048 values less than (TO_DATE('2049-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2049 values less than (TO_DATE('2050-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2050 values less than (TO_DATE('2051-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2051 values less than (TO_DATE('2052-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2052 values less than (TO_DATE('2053-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2053 values less than (TO_DATE('2054-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2054 values less than (TO_DATE('2055-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2055 values less than (TO_DATE('2056-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2056 values less than (TO_DATE('2057-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2057 values less than (TO_DATE('2058-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2058 values less than (TO_DATE('2059-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2059 values less than (TO_DATE('2060-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2060 values less than (TO_DATE('2061-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2061 values less than (TO_DATE('2062-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2062 values less than (TO_DATE('2063-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2063 values less than (TO_DATE('2064-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2064 values less than (TO_DATE('2065-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2065 values less than (TO_DATE('2066-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2066 values less than (TO_DATE('2067-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2067 values less than (TO_DATE('2068-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2068 values less than (TO_DATE('2069-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2069 values less than (TO_DATE('2070-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2070 values less than (TO_DATE('2071-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2071 values less than (TO_DATE('2072-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2072 values less than (TO_DATE('2073-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2073 values less than (TO_DATE('2074-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2074 values less than (TO_DATE('2075-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2075 values less than (TO_DATE('2076-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2076 values less than (TO_DATE('2077-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2077 values less than (TO_DATE('2078-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2078 values less than (TO_DATE('2079-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2079 values less than (TO_DATE('2080-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2080 values less than (TO_DATE('2081-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2081 values less than (TO_DATE('2082-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2082 values less than (TO_DATE('2083-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2083 values less than (TO_DATE('2084-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2084 values less than (TO_DATE('2085-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2085 values less than (TO_DATE('2086-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2086 values less than (TO_DATE('2087-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2087 values less than (TO_DATE('2088-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2088 values less than (TO_DATE('2089-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2089 values less than (TO_DATE('2090-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2090 values less than (TO_DATE('2091-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2091 values less than (TO_DATE('2092-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2092 values less than (TO_DATE('2093-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2093 values less than (TO_DATE('2094-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2094 values less than (TO_DATE('2095-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2095 values less than (TO_DATE('2096-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2096 values less than (TO_DATE('2097-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2097 values less than (TO_DATE('2098-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2098 values less than (TO_DATE('2099-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition P2099 values less than (TO_DATE('2100-01-01 00:00:01', 'YYYY-MM-DD hh24:mi:ss')) tablespace YY_DATA ,
partition pmax values less than (MAXVALUE) tablespace YY_DATA);
-- START THE REDEFINITION
--3、开始在线重定义
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'SO2',
orig_table => 'INS_PROD_INS_SRV_218',
int_table => 'INS_PROD_INS_SRV_218_20180417',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE
--4、复制原始表的依赖对象
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'SO2',
orig_table => 'INS_PROD_INS_SRV_218',
int_table => 'INS_PROD_INS_SRV_218_20180417',
copy_indexes => 0,--不copy 索引
copy_triggers => TRUE,
copy_constraints => FALSE,
copy_privileges => TRUE,
ignore_errors => FALSE,
num_errors => error_count,
copy_statistics => FALSE,
copy_mvlog => TRUE);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
---添加索引
alter table SO2.INS_PROD_INS_SRV_218_20180417
add constraint PK_INS_PROD_INS_SRV_218_N primary key (PROD_SRV_RELAT_ID, EFFECTIVE_DATE, EXPIRE_DATE)
using index
tablespace YY_INX;
-- Create/Recreate indexes
create index SO2.IDX_INS_PROD_INS_SRV_218_1_N on SO2.INS_PROD_INS_SRV_218_20180417 (USER_ID, OFFER_INST_ID, PROD_INST_ID)
tablespace YY_INX parallel 8;
alter index SO2.IDX_INS_PROD_INS_SRV_218_1_N noparallel;
--5、检查是否有错误
select * from DBA_REDEFINITION_ERRORS;
-- DO A FINAL SYNCHRONIZE BEFORE FINISHING THE REDEFINITION
--6、同步临时表数据（可多次执行），收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SO2',tabname=>'INS_PROD_INS_SRV_218_20180417',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,no_invalidate=>false,cascade=>true,degree => 10);
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SO2',
'INS_PROD_INS_SRV_218',
'INS_PROD_INS_SRV_218_20180417');
END;
/
-- PL/SQL procedure successfully completed.
-- FINISH THE REDEFINITION
--7、完成同步
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SO2','INS_PROD_INS_SRV_218','INS_PROD_INS_SRV_218_20180417');
-- PL/SQL procedure successfully completed.
---8、开启行迁移
alter table SO2.INS_PROD_INS_SRV_218 enable row movement;
---9、确认源表赋给其他用户的权限新表都有
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
' to ' || grantee || ';' grant_sql
from dba_tab_privs
where table_name = 'INS_PROD_INS_SRV_218';
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
' to ' || grantee || ';' grant_sql
from dba_tab_privs
where table_name = 'INS_PROD_INS_SRV_218_20180417';
---10、查看源表和新表同义词是否都存在（系列表没有同义词）
select * from dba_synonyms where table_name = 'INS_PROD_INS_SRV_218';
select * from dba_synonyms where table_name = 'INS_PROD_INS_SRV_218_20180417';
---11、对比变更前后数据库失效对应数是否一致，如果不一致对比分析
select count(*) from system.dba_object_20180417 where status <> 'VALID'
union all
select count(*) from dba_objects where status <> 'VALID';
select owner, object_name, object_type
from dba_objects
where status <> 'VALID'
and owner || '.' || object_name not in
(select owner || '.' || object_name
from system.dba_object_20180417
where status <> 'VALID');
select count(*) from system.dba_object_20180417 where status <> 'VALID'
union all
select count(*) from dba_objects where status <> 'VALID';
select owner,table_name,partitioned,row_movement,last_analyzed from dba_tables where table_name = 'INS_PROD_INS_SRV_218';
---12、删除临时表
drop table SO2.INS_PROD_INS_SRV_218_20180417 purge;
---13、再次确认统计信息是否正常，如果不正常，再次收集统计信息
ora tstat INS_PROD_INS_SRV_218
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SO2',tabname=>'INS_PROD_INS_SRV_218',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,no_invalidate=>false,cascade=>true,degree => 10);
--==============回退方案================
--取消在线重定义
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(UNAME => 'SO2',
ORIG_TABLE => 'INS_PROD_INS_SRV_218',
INT_TABLE => 'INS_PROD_INS_SRV_218_20180417',
PART_NAME => NULL);
end;
---删除临时表
drop table SO2.INS_PROD_INS_SRV_218_20180417 purge;
####原建表语句
-- Create table
create table SO2.INS_PROD_INS_SRV_218
(
PROD_SRV_RELAT_ID NUMBER(14) not null,
OFFER_INST_ID NUMBER(14),
PROD_INST_ID NUMBER(14) not null,
USER_ID NUMBER(14) not null,
SERVICE_ID NUMBER(12),
STATE NUMBER(2),
DONE_CODE NUMBER(14),
CREATE_DATE DATE,
DONE_DATE DATE,
EFFECTIVE_DATE DATE not null,
EXPIRE_DATE DATE not null,
COUNTY_CODE VARCHAR2(6),
OP_ID NUMBER(12),
ORG_ID NUMBER(12),
REGION_ID VARCHAR2(6),
EFFECTIVE_DATE_TYPE NUMBER(6),
EXPIRE_DATE_TYPE NUMBER(6)
)
tablespace YY_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 1
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SO2.INS_PROD_INS_SRV_218
add constraint PK_INS_PROD_INS_SRV_218 primary key (PROD_SRV_RELAT_ID, EFFECTIVE_DATE, EXPIRE_DATE)
using index
tablespace YY_INX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index SO2.IDX_INS_PROD_INS_SRV_218_1 on SO2.INS_PROD_INS_SRV_218 (USER_ID, OFFER_INST_ID, PROD_INST_ID)
tablespace YY_INX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);