全部 android asp.net C/C++ cshap IOS Java javascript nodejs perl php python ruby web容器 其他 前端 数据库 第三方平台 混合式APP 网络 系统 默认分类

Oracle 在线重定义表分区

0 50

==================原始表================原始表=====================原始表

create table BUILDING_temp(building_id NUMBER(19) not null,buildingform VARCHAR2(10),city_code VARCHAR2(4))

alter table BUILDING add constraint PK_BUILDING primary key (BUILDING_ID)

==============过渡表===========================过渡表===========

create table BUILDING_temp( building_id NUMBER(19) not null, buildingform VARCHAR2(10), city_code VARCHAR2(4))partition by list(city_code)( partition P11 values('1101'), partition P12 values ('1201'), partition P13 values ('1304','1309','1303','1302','1301','1308','1307','1311','1310','1306','1305'), partition P14 values ('1405','1403','1402','1401','1404','1411','1410','1409','1408','1407','1406'), partition P15 values ('1525','1522','1509','1529','1508','1507','1506','1505','1504','1503','1502','1501'), partition P21 values ('2101','2114','2113','2112','2111','2110','2109','2108','2107','2106','2105','2104','2103','2102'), partition P22 values ('2201','2224','2208','2207','2206','2205','2204','2203','2202'), partition P23 values ('2304','2302','2301','2303','2327','2312','2311','2310','2309','2308','2307','2306','2305'), partition P31 values ('3101'), partition P32 values ('3206','3205','3204','3203','3202','3201','3213','3212','3211','3210','3209','3208','3207'), partition P33 values ('3311','3310','3309','3308','3307','3306','3305','3304','3303','3302','3301'), partition P34 values ('3413','3412','3411','3410','3408','3407','3406','3405','3404','3403','3402','3401','3418','3417','3416','3415'), partition P35 values ('3501','3509','3508','3507','3506','3505','3504','3503','3502'), partition P36 values ('3601','3611','3610','3609','3608','3607','3606','3605','3604','3603','3602'), partition P37 values ('3709','3704','3703','3701','3717','3716','3702','3708','3707','3706','3705','3715','3714','3713','3712','3711','3710'), partition P41 values ('4112','4111','4110','4109','4108','4107','4106','4105','4104','4103','4102','4101','4190','4117','4116','4115','4114','4113'), partition P42 values ('4290','4228','4213','4212','4211','4210','4209','4208','4207','4206','4205','4203','4202','4201'), partition P43 values ('4301','4331','4310','4309','4308','4307','4306','4305','4304','4303','4313','4312','4311','4302'), partition P44 values ('4401','4453','4452','4451','4420','4419','4418','4417','4416','4415','4414','4413','4412','4409','4408','4407','4406','4405','4404','4403','4402'), partition P45 values ('4513','4512','4511','4510','4509','4508','4507','4506','4505','4504','4503','4502','4501','4514'), partition P46 values ('4601','4690','4603','4602'), partition P50 values ('5001'), partition P51 values ('5134','5107','5133','5132','5120','5119','5118','5117','5116','5115','5114','5113','5111','5110','5109','5108','5106','5105','5104','5103','5101'), partition P52 values ('5201','5226','5223','5206','5205','5204','5203','5227','5202'), partition P53 values ('5331','5329','5328','5326','5325','5323','5309','5308','5307','5306','5305','5304','5303','5301','5334','5333'), partition P54 values ('5401','5426','5425','5424','5402','5422','5421'), partition P61 values ('6101','6110','6109','6108','6107','6106','6105','6104','6103','6102'), partition P62 values ('6206','6205','6204','6203','6202','6201','6230','6229','6212','6211','6210','6209','6208','6207'), partition P63 values ('6301','6328','6327','6326','6325','6323','6322','6302'), partition P64 values ('6401','6405','6404','6403','6402'), partition P65 values ('6529','6528','6527','6523','6522','6521','6502','6501','6590','6543','6542','6540','6532','6531','6530'));

alter table BUILDING_temp add constraint PK_BUILDING1 primary key (BUILDING_ID)

--验证是否可以进行在线重定义

call dbms_redefinition.can_redef_table('avmdm', 'BUILDING');======所有操作之前要先验证

--开始重定义call dbms_redefinition.start_redef_table('avmdm', 'BUILDING', 'BUILDING_temp');

--同步临时表与原始表中的数据call dbms_redefinition.sync_interim_table('avmdm', 'BUILDING', 'BUILDING_temp');

--结束重定义call dbms_redefinition.finish_redef_table('avmdm', 'BUILDING', 'BUILDING_temp');

--查询所有的数据select * from BUILDING

--查询P11分区的数据SELeCT * FROM BUILDING PARTITION(P11);

--查询P12分区的数据SELeCT * FROM BUILDING PARTITION(P12);

--查询表下的所有的分区SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='BUILDING'

--删除临时表drop table BUILDING_temp purge;

热忱回答0

要回复文章请先登录注册