分区表转换
20160822-分区表转换-整理
登录oracle上的scp用户
conn scp
查看相关表T_IMAGE_DATA的数据
select * from T_IMAGE_DATA t
order by time
select count(*) from T_IMAGE_DATA t
execute dbms_stats.gather_table_stats('SCP', 'T_IMAGE_DATA', cascade => true);
analyze table T_IMAGE_DATA compute statistics;
select * from user_tables where table_name = 'T_IMAGE_DATA';
select * from user_tab_partitions where table_name = 'T_IMAGE_DATA';
select count(*) from T_IMAGE_DATA;
select * from user_indexes where table_name = 'T_IMAGE_DATA';
select * from user_objects
select count(*) from T_IMAGE_DATA_partion partition (PART1);
备份表T_IMAGE_DATA
-- Create table
create table T_IMAGE_DATA_bak_20160819
as select * from t_image_data
检查备份表的数据
select count(*) from T_IMAGE_DATA_BAK_20160819 t
select count(*) from T_IMAGE_DATA t
清除之前测试的数据
drop table t_image_data_p purge
drop table t_image_data_p_old purge
drop table t_image_data_partion purge
创建临时分区表T_IMAGE_DATA_partion
create table T_IMAGE_DATA_partion
PARTITION BY RANGE (time)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
(PARTITION part1
VALUES LESS THAN (TO_DATE ('07/01/2016', 'MM/DD/YYYY')))
as select * from t_image_data;
查看临时建好的表 T_IMAGE_DATA_PARTION 的数据
select * from T_IMAGE_DATA_PARTION t
order by time
select count(*) from T_IMAGE_DATA_PARTION t
execute dbms_stats.gather_table_stats('SCP', 'T_IMAGE_DATA_PARTION', cascade => true);
analyze table T_IMAGE_DATA_partion compute statistics;
select * from user_tables where table_name = 'T_IMAGE_DATA_PARTION';
select * from user_tab_partitions where table_name = 'T_IMAGE_DATA_PARTION';
select count(*) from T_IMAGE_DATA_partion;
select count(*) from T_IMAGE_DATA_partion partition (PART1);
重命名表
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Connected as scp@SCPDB01
SQL> RENAME T_IMAGE_DATA TO T_IMAGE_DATA_OLD;
Table renamed
SQL> rename T_IMAGE_DATA_PARTION to T_IMAGE_DATA;
Table renamed
SQL>
验证
select partitioned from user_tables where table_name = 'T_IMAGE_DATA_PARTION';
select partition_name from user_tab_partitions where table_name = 'T_IMAGE_DATA_PARTION';
select count(*) from SCP.T_IMAGE_DATA_partion;
select count(*) from SCP.T_IMAGE_DATA_partion partition (p4);
rename表，恢复T表的相关应用
RENAME T_IMAGE_DATA_P TO T_IMAGE_DATA_P_OLD;
rename T_IMAGE_DATA_PARTION to T_IMAGE_DATA_P;
RENAME T_IMAGE_DATA TO T_IMAGE_DATA_OLD;
rename T_IMAGE_DATA_PARTION to T_IMAGE_DATA
-- Add comments to the columns
comment on column T_IMAGE_DATA.image_id
is '影像ID';
comment on column T_IMAGE_DATA.data
is '影像数据';
comment on column T_IMAGE_DATA.time
is '时间';
原表创建语句
-- Create table
create table SCP.T_IMAGE_DATA
(
image_id NUMBER(15),
data BLOB,
time DATE
)
tablespace TBS_GMSCP
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column SCP.T_IMAGE_DATA.image_id
is '影像ID';
comment on column SCP.T_IMAGE_DATA.data
is '影像数据';
comment on column SCP.T_IMAGE_DATA.time
is '时间';
相关参考:
Oracle普通表—>分区表转换(9亿数据量)
http://www.linuxidc.com/Linux/2015-04/115720.htm
Oracle在线重定义DBMS_REDEFINITION 普通表—>分区表
http://www.linuxidc.com/Linux/2015-04/115719.htm
Oracle技术之11g分区表按时间自动创建
http://www.educity.cn/shujuku/1178190.html
Oracle回收站使用全攻略
http://www.csdn.net/article/2012-12-05/2812471-Oracle-Recycle-Bin
带有clob字段的自动分区表
http://blog.csdn.net/wll_1017/article/details/9080113
oracle非分区表，转分区表
http://blog.csdn.net/leshjmail/article/details/6524519
带有clob字段的自动分区表
http://blog.csdn.net/hijiankang/article/details/9173877/
Oracle分区表常用命令
http://www.cnblogs.com/chuncn/archive/2009/04/15/1416458.html
