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

分区表转换