首页 > 代码库 > oracle普通表转分区表（在线重定义方式）

oracle普通表转分区表（在线重定义方式）

2024-08-26 03:27:26   217人阅读

1.1.TAB_TAOBAO_BILL
1.1.1检查下这张表是否可以在线重定义，无报错表示可以，报错会给出错误信息：
exec dbms_redefinition.can_redef_table(‘ycheng‘, ‘P_TAB_TAOBAO_BILL‘);
 
1.1.2.expdp导出表进行备份（这里的DATA_PUMP_1目录是/data1/dpdump）
expdp \‘\/ as sysdba\‘ directory=DATA_PUMP_1 tables=TAB_TAOBAO_BILL dumpfile=ycheng_TAB_TAOBAO_BILL0914.dmp logfile=ycheng_TAB_TAOBAO_BILL0914.log parallel=2;
 
 
1.1.3检查TAB_GS_BAG表使用的表空间使用率（这里检查表空间是否充裕，如果表空间不足，执行1.1.4，增加表空间）
SELECT D.TABLESPACE_NAME,
SPACE || ‘M‘ "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || ‘M‘ "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || ‘%‘
"USED_RATE(%)",
FREE_SPACE || ‘M‘ "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || ‘M‘ "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || ‘M‘ "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || ‘%‘ "USED_RATE(%)",
NVL (FREE_SPACE, 0) || ‘M‘ "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
 
 
1.1.4增加表空间
ALTER TABLESPACE USERS ADD DATAFILE ‘/data1/ycmain/USERS20160914.dbf‘ SIZE 31G;
 
1.2 创建YCHENG.P_TAB_TAOBAO_BILL中间表
create table P_TAB_TAOBAO_BILL
(
tx_logistic_id VARCHAR2(64) not null,
trade_no VARCHAR2(64),
bill_code VARCHAR2(64),
flag NUMBER,
create_date DATE default sysdate not null,
status NUMBER(2) default 0 not null,
rec_site VARCHAR2(30),
rec_man VARCHAR2(30),
rec_bill_man VARCHAR2(30),
rec_date DATE,
accept_date DATE,
send_site VARCHAR2(30),
send_man VARCHAR2(30),
send_date DATE,
bl_cancel NUMBER(1) default 0 not null,
cancel_date DATE,
cancel_desc VARCHAR2(215),
bl_dispart NUMBER(1) default 0 not null,
prepare_site VARCHAR2(400),
name VARCHAR2(120),
address VARCHAR2(256),
post_code VARCHAR2(20),
phone VARCHAR2(32),
mobile VARCHAR2(32),
prov VARCHAR2(64),
city VARCHAR2(64),
county VARCHAR2(64),
send_start_time DATE,
send_end_time DATE,
item_name VARCHAR2(256),
piece NUMBER,
special NUMBER,
remark VARCHAR2(512),
insurance_value NUMBER,
package_or_not NUMBER(1),
send_name VARCHAR2(120) not null,
send_address VARCHAR2(256) not null,
send_post_code VARCHAR2(20),
send_phone VARCHAR2(32),
send_mobile VARCHAR2(32),
send_prov VARCHAR2(64) not null,
send_city VARCHAR2(64) not null,
send_county VARCHAR2(64) not null,
cancel_site VARCHAR2(30),
cancel_man VARCHAR2(30),
rec_goods_man VARCHAR2(30),
message VARCHAR2(100),
customer_id VARCHAR2(64),
goods_value NUMBER(10,2),
items_value NUMBER,
companyname VARCHAR2(110),
send_companyname VARCHAR2(110),
total_weight NUMBER(10,2),
total_volume NUMBER(10,2),
pay_type VARCHAR2(10),
transport_price NUMBER default 0.0,
insurance_price NUMBER,
package_service VARCHAR2(32),
package_service_price NUMBER,
other_price NUMBER,
total_price NUMBER,
promotion VARCHAR2(500),
member_type VARCHAR2(32),
business_network_no VARCHAR2(32),
gmt_commit DATE,
gmt_updated DATE,
logistic_company_id VARCHAR2(32),
schedule_type NUMBER,
order_flag VARCHAR2(32),
tms_service_code VARCHAR2(64),
logisticproviderid VARCHAR2(20),
opt_date DATE,
opt_man VARCHAR2(64),
other7 NUMBER(10,2),
other9 VARCHAR2(30),
wmsaddress VARCHAR2(500),
other8 VARCHAR2(3),
other10 VARCHAR2(30),
logistype VARCHAR2(30),
eccompanyid VARCHAR2(64),
total_service_fee NUMBER(9,2),
buy_service_fee NUMBER(9,2),
cod_split_fee NUMBER(9,2),
type VARCHAR2(5),
ordertype NUMBER,
servicetype NUMBER,
seq NUMBER,
send_company VARCHAR2(110),
is_beyond NUMBER(1),
guid VARCHAR2(36),
cus_id VARCHAR2(36),
weight_rate NUMBER(5,2),
volume_rate NUMBER(5,2),
least_expenses NUMBER,
transport_type VARCHAR2(20),
vist_receive VARCHAR2(1),
vist_receive_price NUMBER(8,2),
delivery_type VARCHAR2(2),
delivery_price NUMBER(8,2),
cod_type VARCHAR2(2),
cod_price NUMBER(8,2),
back_signbill VARCHAR2(2),
back_signbill_price NUMBER(8,2),
wait_notifysend VARCHAR2(2),
wait_notifysend_price NUMBER(8,2),
sms_notify VARCHAR2(1),
sms_notify_price NUMBER(8,2),
fuelsurcharge VARCHAR2(1),
fuelsurcharge_price NUMBER(8,2),
duty_site VARCHAR2(30),
modifier VARCHAR2(30),
modify_site VARCHAR2(30),
modify_code VARCHAR2(30),
destination VARCHAR2(50),
favourable_bill VARCHAR2(50),
bl_order_status NUMBER(1),
online_price NUMBER(10,2),
sign_status VARCHAR2(20),
bl_insurance NUMBER(1),
creater_man VARCHAR2(30),
creater_site VARCHAR2(30),
creater_code VARCHAR2(30),
rec_bill_code VARCHAR2(30),
bl_depute NUMBER(1),
bl_tms_control NUMBER(1),
ordersource VARCHAR2(10)
)
partition by range(create_date)
interval (numtoyminterval(1,‘month‘))
(
partition TAB_TAOBAO_BILL_2014_12 values less than (TO_DATE(‘ 2015-01-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_01 values less than (TO_DATE(‘ 2015-02-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_02 values less than (TO_DATE(‘ 2015-03-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_03 values less than (TO_DATE(‘ 2015-04-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_04 values less than (TO_DATE(‘ 2015-05-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_05 values less than (TO_DATE(‘ 2015-06-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_06 values less than (TO_DATE(‘ 2015-07-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_07 values less than (TO_DATE(‘ 2015-08-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_08 values less than (TO_DATE(‘ 2015-09-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_09 values less than (TO_DATE(‘ 2015-10-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_10 values less than (TO_DATE(‘ 2015-11-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_11 values less than (TO_DATE(‘ 2015-12-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2015_12 values less than (TO_DATE(‘ 2016-01-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_01 values less than (TO_DATE(‘ 2016-02-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_02 values less than (TO_DATE(‘ 2016-03-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_03 values less than (TO_DATE(‘ 2016-04-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_04 values less than (TO_DATE(‘ 2016-05-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_05 values less than (TO_DATE(‘ 2016-06-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_06 values less than (TO_DATE(‘ 2016-07-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_07 values less than (TO_DATE(‘ 2016-08-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_08 values less than (TO_DATE(‘ 2016-09-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_09 values less than (TO_DATE(‘ 2016-10-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_10 values less than (TO_DATE(‘ 2016-11-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_11 values less than (TO_DATE(‘ 2016-12-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2016_12 values less than (TO_DATE(‘ 2017-01-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_01 values less than (TO_DATE(‘ 2017-02-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_02 values less than (TO_DATE(‘ 2017-03-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_03 values less than (TO_DATE(‘ 2017-04-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_04 values less than (TO_DATE(‘ 2017-05-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_05 values less than (TO_DATE(‘ 2017-06-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_06 values less than (TO_DATE(‘ 2017-07-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_07 values less than (TO_DATE(‘ 2017-08-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_08 values less than (TO_DATE(‘ 2017-09-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_09 values less than (TO_DATE(‘ 2017-10-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_10 values less than (TO_DATE(‘ 2017-11-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_11 values less than (TO_DATE(‘ 2017-12-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2017_12 values less than (TO_DATE(‘ 2018-01-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)),
partition TAB_TAOBAO_BILL_2018_01 values less than (TO_DATE(‘ 2018-02-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘))
）
TABLESPACE "USERS";
 
 
1.3.1.删除索引
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I";
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I1";
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I2";
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I3";
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I4";
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I6";
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I7";
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I8";
drop INDEX "YCHENG"."TAB_TAOBAO_BILL_I9";
 
 
 
1.3.2.新建索引
-- Create/Recreate indexes
create index TAB_TAOBAO_BILL_I on P_TAB_TAOBAO_BILL (BILL_CODE)
nologging local;
 
create index TAB_TAOBAO_BILL_I1 on P_TAB_TAOBAO_BILL (CREATE_DATE, REC_SITE, STATUS)
nologging local;
 
create index TAB_TAOBAO_BILL_I2 on P_TAB_TAOBAO_BILL (CREATE_DATE, SEND_SITE, STATUS)
nologging local;
 
create index TAB_TAOBAO_BILL_I3 on P_TAB_TAOBAO_BILL (CREATE_DATE, BL_DISPART, STATUS)
nologging local;
 
create index TAB_TAOBAO_BILL_I4 on P_TAB_TAOBAO_BILL (CUSTOMER_ID)
nologging local;
 
create index TAB_TAOBAO_BILL_I6 on P_TAB_TAOBAO_BILL (MOBILE)
nologging local;
 
create index TAB_TAOBAO_BILL_I7 on P_TAB_TAOBAO_BILL (SEND_MOBILE)
nologging local;
 
create index TAB_TAOBAO_BILL_I8 on P_TAB_TAOBAO_BILL (SEND_PHONE)
nologging local;
 
create index TAB_TAOBAO_BILL_I9 on P_TAB_TAOBAO_BILL (ACCEPT_DATE, LOGISTICPROVIDERID)
nologging local;
 
CREATE INDEX ind_bill_status ON P_tab_taobao_bill ("REC_BILL_MAN", "STATUS") online nologging parallel 8;
 
 
1.4.1新建立主键
-- Create/Recreate primary, unique and foreign key constraints
alter table P_TAB_TAOBAO_BILL
add constraint P_TAB_TAOBAO_BILL_P primary key (TX_LOGISTIC_ID)
using index
tablespace USERS
 
1.42授权
grant select on TAB_TAOBAO_BILL to YCQUERY;
 
 
 
1.5启动在线重定义：
exec dbms_redefinition.start_redef_table(‘ycheng‘, ‘TAB_TAOBAO_BILL‘, ‘P_TAB_TAOBAO_BILL‘);
 
1.6.查看量表数据是否一致
select count(1) from YCHENG.TAB_TAOBAO_BILL;
select count(1) from YCHENG.P_TAB_TAOBAO_BILL;
 
 
1.7.使用dbms_redefinition包的sync_interim_table模块刷新数据后，中间表也可以看到数据更改（这里，当原表数据发生改变时，可用如下刷新同步数据）
--管理员权限执行sql命令行执行,同步两边数据
exec dbms_redefinition.sync_interim_table(‘ycheng‘, ‘TAB_TAOBAO_BILL‘, ‘P_TAB_TAOBAO_BILL‘);
--管理员权限执行sql命令行执行
 
 
 
1.8.结束在线重定义
--管理员权限执行sql命令行执行，结束重定义
exec dbms_redefinition.finish_redef_table(‘ycheng‘, ‘TAB_TAOBAO_BILL‘, ‘P_TAB_TAOBAO_BILL‘);
--管理员权限执行sql命令行执行
 
 
1.9.验证数据
select count(1) from YCHENG.TAB_TAOBAO_BILL;
select count(1) from YCHENG.P_TAB_TAOBAO_BILL;
 
 
1.10.查看各分区数据是否正确
select PARTITION_NAME from user_tab_partitions where table_name = ‘TAB_TAOBAO_BILL‘;
select * from TAB_TAOBAO_BILL partition(TAB_TAOBAO_BILL_2016_12);
 
 
 
1.11.删掉中间表
drop table P_TAB_TAOBAO_BILL;
 
1.12.修改主键
alter table "YCHENG"."TAB_TAOBAO_BILL" drop constraint P_TAB_TAOBAO_BILL_P cascade drop index;
 
 
-- Create/Recreate primary, unique and foreign key constraints
alter table TAB_TAOBAO_BILL
add constraint TAB_TAOBAO_BILL_P primary key (TX_LOGISTIC_ID)
using index
tablespace USERS
 
 
1.13 收集TAB_TAOBAO_BILL分区表及相关对象的优化器统计信息
需要用到sys用户在SQLPLUS下进行操作
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>‘YCHENG‘,TABNAME=>‘TAB_TAOBAO_BILL‘,ESTIMATE_PERCENT=>100,METHOD_OPT=>‘FOR ALL COLUMNS SIZE AUTO‘,DEGREE=>2,NO_INVALIDATE=>FALSE,CASCADE=>TRUE);
 

oracle普通表转分区表（在线重定义方式）


联系
我们