大表改造成分区表-白红宇

大表改造成分区表

阅读量：7114 次

发布时间：2019-06-28

本文共 6777 字，大约阅读时间需要 22 分钟。

首先查询用户下的大表

方法一：

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

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; -----------------在线重定义方法

转载于:https://blog.51cto.com/askjoey/1679284

你可能感兴趣的文章

VC编写的程序不能在其他机器上运行的解决方案（续）

The Receiver 4.4 - 客户端硬件解码 - 大幅度提升3D显示效能

查看>>

解决 yum时 Error: Protected multilib versions报错

windows_learn 002 用户管理和组策略

查看>>

linux中的邮件服务器笔记

查看>>

linux命令：w、who、whoami、last、lastb、lastlog、basename、mail、hostname

查看>>

Python---函数---默认参数

查看>>

collections.Counter. most_common