大表改造成分区表

原创

viking714 2015-07-28 17:04:25 ©著作权

©著作权归作者所有：来自51CTO博客作者viking714的原创作品，请联系作者获取转载授权，否则将追究法律责任

首先查询用户下的大表

方法一：
select table_name,num_rows from user_tables order by num_rows desc;

（按表的行数排序）
方法二：

create or replace function count_rows(table_name in varchar2)
       return number
is
       num_rows number;
       stmt varchar2(2000);
begin
       stmt := 'select count(*) from '||table_name ;
       execute immediate stmt into num_rows;
       return num_rows;
end;

select table_name,count_rows(table_name) nrows from user_tables order by nrows desc;

方法三：
（与上两种略有差异，这个是按表大小查询）
select * from
(select owner,segment_name,segment_type,sum(bytes)/1024/1024 object_size
from dba_segments where owner='GUOYU' group by owner,segment_name,segment_type order by object_size desc)
where rownum < 20 and segment_type='TABLE';

--------------------------------------------------------

---------------------------------------------------------

create table CUSTOMER_SALESINFO_DETAILN
(
ID             VARCHAR2(40) not null,
SALESID        VARCHAR2(40),
SALESDATE      DATE,
PRODUCTCODE    VARCHAR2(20),
QUANT          NUMBER(10,2),
RETAILPRICE    NUMBER(10,2),
AMOUNT         NUMBER(10,2),
BARCODE        VARCHAR2(18),
MINI_UNIT      VARCHAR2(18),
DISCOUNTAMOUNT NUMBER(10,2),
PRODUCT_TYPE   VARCHAR2(18)
)
partition by range (SALESDATE)
(
   partition sales_1402 values less than (to_date('2014-03-01','yyyy-mm-dd'))
,partition sales_1403 values less than (to_date('2014-04-01','yyyy-mm-dd'))
,partition sales_1404 values less than (to_date('2014-05-01','yyyy-mm-dd'))
,partition sales_1405 values less than (to_date('2014-06-01','yyyy-mm-dd'))
,partition sales_1406 values less than (to_date('2014-07-01','yyyy-mm-dd'))
,partition sales_1407 values less than (to_date('2014-08-01','yyyy-mm-dd'))
,partition sales_1408 values less than (to_date('2014-09-01','yyyy-mm-dd'))
,partition sales_1409 values less than (to_date('2014-10-01','yyyy-mm-dd'))
,partition sales_1410 values less than (to_date('2014-11-01','yyyy-mm-dd'))
,partition sales_1411 values less than (to_date('2014-12-01','yyyy-mm-dd'))
,partition sales_1412 values less than (to_date('2015-01-01','yyyy-mm-dd'))
,partition sales_1501 values less than (to_date('2015-02-01','yyyy-mm-dd'))
,partition sales_1502 values less than (to_date('2015-03-01','yyyy-mm-dd'))
,partition sales_1503 values less than (to_date('2015-04-01','yyyy-mm-dd'))
,partition sales_1504 values less than (to_date('2015-05-01','yyyy-mm-dd'))
,partition sales_1505 values less than (to_date('2015-06-01','yyyy-mm-dd'))
,partition sales_1506 values less than (to_date('2015-07-01','yyyy-mm-dd'))
,partition sales_1507 values less than (to_date('2015-08-01','yyyy-mm-dd'))
,partition sales_1508 values less than (to_date('2015-09-01','yyyy-mm-dd'))
,partition sales_1509 values less than (to_date('2015-10-01','yyyy-mm-dd'))
,partition sales_1510 values less than (to_date('2015-11-01','yyyy-mm-dd'))
,partition sales_1511 values less than (to_date('2015-12-01','yyyy-mm-dd'))
,partition sales_1512 values less than (to_date('2016-01-01','yyyy-mm-dd'))
,partition sales_1601 values less than (to_date('2016-02-01','yyyy-mm-dd'))
,partition sales_1602 values less than (to_date('2016-03-01','yyyy-mm-dd'))
,partition sales_1603 values less than (to_date('2016-04-01','yyyy-mm-dd'))
,partition sales_1604 values less than (to_date('2016-05-01','yyyy-mm-dd'))
,partition sales_1605 values less than (to_date('2016-06-01','yyyy-mm-dd'))
,partition sales_1606 values less than (to_date('2016-07-01','yyyy-mm-dd'))
,partition sales_1607 values less than (to_date('2016-08-01','yyyy-mm-dd'))
,partition sales_1608 values less than (to_date('2016-09-01','yyyy-mm-dd'))
,partition sales_1609 values less than (to_date('2016-10-01','yyyy-mm-dd'))
,partition sales_1610 values less than (to_date('2016-11-01','yyyy-mm-dd'))
,partition sales_1611 values less than (to_date('2016-12-01','yyyy-mm-dd'))
,partition sales_1612 values less than (to_date('2017-01-01','yyyy-mm-dd'))
,partition sales_max values less than (maxvalue)
)
tablespace POS_TBS
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);

-- Create/Recreate primary, unique and foreign key constraints
alter table CUSTOMER_SALESINFO_DETAILN
add primary key (ID)
using index
tablespace POS_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------

方法一：（在线重定义）

检查下这张表是否可以在线重定义，无报错表示可以，报错会给出错误信息：
SQL> exec dbms_redefinition.can_redef_table('guoyu', 'CUSTOMER_SALESINFO_DETAIL');
PL/SQL procedure successfully completed

启动在线重定义：
SQL> exec dbms_redefinition.start_redef_table('guoyu','CUSTOMER_SALESINFO_DETAIL','CUSTOMER_SALESINFO_DETAILN');
PL/SQL procedure successfully completed

使用dbms_redefinition包的sync_interim_table模块刷新数据后，中间表也可以看到数据更改。
SQL> exec dbms_redefinition.sync_interim_table('guoyu', 'IM_CUSTOMER_SALESINFO_DETAIL','CUSTOMER_SALESINFO_DETAILN');
PL/SQL procedure successfully completed

结束在线重定义
SQL> exec dbms_redefinition.finish_redef_table('guoyu', 'IM_CUSTOMER_SALESINFO_DETAIL','CUSTOMER_SALESINFO_DETAILN');
PL/SQL procedure successfully completed

-----如果执行一次在线重定义没成功有可能再检查就会失败，有可能是在源表上建了物化视图的原因，删除物化视图也许可行。
           drop materialized view log on CUSTOMER_SALESINFO_DETAIL;
----如果重定义中间出错可以执行
      dbms_redifinition.abort_redef_table（'guoyu','CUSTOMER_SALESINFO_DETAIL','CUSTOMER_SALESINFO_DETAILN'）
---这个存储过程可以删除在线重定义产生的临时对象。

方法二：
1、插入数据
insert into CUSTOMER_SALESINFO_DETAILN select * from CUSTOMER_SALESINFO_DETAIL;
commit；

2、直接重命名会提示ORA-26563: renaming this table is not allowed，要删除在表上的物化视图。
drop materialized view log on CUSTOMER_SALESINFO_DETAIL;

alter table IM_CUSTOMER_SALESINFO_DETAIL rename to CUSTOMER_SALESINFO_DETAILO;
--然后再重命名

alter table IM_CUSTOMER_SALESINFO_DETAILN rename to CUSTOMER_SALESINFO_DETAIL;

-------------------------------------
------------------------------------------

创建索引：

局部索引
-- Create/Recreate indexes
drop index SALESDETAIL_DATE;
create index SALESDETAIL_DATE on CUSTOMER_SALESINFO_DETAIL (SALESDATE) local
tablespace POS_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
drop index SALESDETAIL_SALEID;
create index SALESDETAIL_SALEID on CUSTOMER_SALESINFO_DETAIL (SALESID) local
tablespace POS_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);

测试：

select count(id) from CUSTOMER_SALESINFO_DETAIL partition(sales_1502);
select count(*) from CUSTOMER_SALESINFO_DETAIL;
查看各分区数据是否正确
SQL> select table_name, partition_name from user_tab_partitions where table_name = 'IM_CUSTOMER_SALESINFO_DETAIL';

都没问题后，删除原表
drop table CUSTOMER_SALESINFO_DETAILO purge; ---------------insert方法
drop table CUSTOMER_SALESINFO_DETAILN purge; -----------------在线重定义方法