1.环境信息
序号 服务器信息 操作系统 IP地址 实例名 库版本 备注
1 主库 linux xx xx Dm8
2 备库 linux xx xx Dm8
2.需求
需要将T_PUSH_LIST_API表改为分区表，以便以后清理数据
3.方案
3.1.查询表空间大小
select a.name 表空间名字,
SUM(b.max_size) 总大小M,
sum(b.total_size * b.page_size / 1024 / 1024 -
b.free_size * b.page_size / 1024 / 1024) 当前使用大小M,
sum(b.max_size) -
sum((b.total_size * b.page_size / 1024 / 1024 -
b.free_size * b.page_size / 1024 / 1024)) 当前剩余空间M,
round((1 - cast((sum(b.total_size * b.page_size / 1024 / 1024) -
sum(b.free_size * b.page_size / 1024 / 1024)) as DEC) /
sum(b.max_size)) * 100,
2) || '%' 剩余百分比
from v$tablespace a, v$datafile b
where a.id = b.group_id
group by a.name;
3.2.新建分区表
CREATE TABLE "PCDB"."T_PUSH_LIST_API_BAK"
(
"MSG_ID" NUMBER(20,0) DEFAULT NULL,
"DEV_ID" NUMBER(10,0) DEFAULT NULL,
"APP_ID" NUMBER(10,0) DEFAULT NULL,
"MSG_TYPE" NUMBER(10,0) DEFAULT NULL,
"RECEIVER_TYPE" VARCHAR(1000) DEFAULT NULL,
"RECEIVER_VALUE" CLOB DEFAULT NULL,
"API_TYPE" NUMBER(10,0) DEFAULT NULL,
"APPKEY" VARCHAR(32) NOT NULL,
"MSG_CONTENT" CLOB DEFAULT NULL,
"ITIME" DATETIME(6) DEFAULT NULL,
"PLATFORM" VARCHAR(200) DEFAULT NULL,
"ERRNO" NUMBER(10,0) DEFAULT NULL,
"TABLE_ID" NUMBER(20,0) NOT NULL,
"JSON_STRING" CLOB DEFAULT NULL,
"FLAG" NUMBER(10,0) DEFAULT '0',
"CREATE_TIME" DATETIME(6) DEFAULT NULL,
"LAST_UPDATE_TIME" TIMESTAMP(6) DEFAULT TO_TIMESTAMP('2006-01-02 15:04:05', 'yyyy-mm-dd hh24:mi:ss.ff'),
"RICH_PUSH_TYPE" NUMBER(10,0) DEFAULT '1',
"PUSH_TYPE" VARCHAR(12) DEFAULT 'default',
"MSG_TAG1" VARCHAR(20) DEFAULT NULL,
"MSG_TAG2" VARCHAR(20) DEFAULT NULL,
"GEOFENCE_ID" VARCHAR(10) DEFAULT NULL,
"OVERRIDE_MSGID" NUMBER(20,0) DEFAULT '0',
"WITHDRAW_FLAG" NUMBER(10,0) DEFAULT '0',
"IS_SCHEDULED" NUMBER(1,0) DEFAULT '0',
"OPT" NUMBER(10,0) DEFAULT '4',
CONSTRAINT "PUSH_LIST_API_IND_MSG_ID_01" UNIQUE("MSG_ID"),
CONSTRAINT "PUSH_LIST_API_TABLE_ID_01" UNIQUE("TABLE_ID"))
PARTITION BY RANGE(ITIME)
INTERVAL (NUMTOYMINTERVAL(1,'month'))
(
PARTITION p00001 VALUES LESS THAN(TO_DATE('2023-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));

COMMENT ON TABLE "PCDB"."T_PUSH_LIST_API_BAK" IS 'T_PUSH_LIST_API_BAK';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."API_TYPE" IS 'api_type';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."APPKEY" IS 'appkey';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."APP_ID" IS 'app_id';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."CREATE_TIME" IS 'create_time';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."DEV_ID" IS 'dev_id';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."ERRNO" IS 'errno';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."FLAG" IS 'flag';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."GEOFENCE_ID" IS '地理围栏id';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."ITIME" IS 'itime';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."JSON_STRING" IS 'json_string';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."LAST_UPDATE_TIME" IS 'last_update_time';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."MSG_CONTENT" IS 'msg_content';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."MSG_ID" IS 'msg_id';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."MSG_TAG1" IS '维度一：消息分类';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."MSG_TAG2" IS '维度二：消息栏目';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."MSG_TYPE" IS 'msg_type';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."OVERRIDE_MSGID" IS 'override_msgid';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."PLATFORM" IS 'platform';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."PUSH_TYPE" IS 'default:普通推送,geo:地理围栏,template:模板推送';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."RECEIVER_TYPE" IS 'receiver_type';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."RECEIVER_VALUE" IS '消息类型 0普通消息';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."RICH_PUSH_TYPE" IS '0:自定义消息；1:普通通知；2:富媒体通知';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."TABLE_ID" IS 'table_id';
COMMENT ON COLUMN "PCDB"."T_PUSH_LIST_API_BAK"."WITHDRAW_FLAG" IS 'withdraw_flag';

CREATE INDEX "PUSH_LIST_API_IND_APPID_01" ON "PCDB"."T_PUSH_LIST_API_BAK"("APP_ID" ASC) STORAGE(ON "PCDB", CLUSTERBTR) ;
CREATE INDEX "PUSH_LIST_API_IND_DEVID_01" ON "PCDB"."T_PUSH_LIST_API_BAK"("DEV_ID" ASC) STORAGE(ON "PCDB", CLUSTERBTR) ;
CREATE INDEX "PUSH_LIST_API_IND_ITIME_01" ON "PCDB"."T_PUSH_LIST_API_BAK"("ITIME" ASC) STORAGE(ON "PCDB", CLUSTERBTR) ;
3.3.确认分区表生效
select owner,table_name,partitioned from dba_tables where table_name='T_PUSH_LIST_API_BAK' and owner='PCDB';

3.4.确认索引
select owner,index_name from dba_indexes where table_name='T_PUSH_LIST_API_BAK' and owner='PCDB';

3.5.查看原表大小

3.6.查看原表行数
SQL> select count(*) from pcdb.t_push_list_api;

行号 COUNT(*)

1 493534
3.7.查看提前恢复表的行数
SQL> select count(*) from pcdb.t_push_list_api where itime < to_date('2023-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

行号 COUNT(*)

1 493128
3.8.查询无效对象
select owner,object_name,status,object_type from dba_objects where status='INVALID' and owner='PCDB';
3.9.使用迁移工具将今天之前数据迁移

3.10.rename表
rename T_PUSH_LIST_API to T_PUSH_LIST_API_20230301;
rename T_PUSH_LIST_API_BAK to T_PUSH_LIST_API;
3.11.查询今天产生的数据
select count(*) from T_PUSH_LIST_API_20230301 where itime >=to_date('2023-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
3.12.将今天产生的数据恢复
insert into T_PUSH_LIST_API select * from T_PUSH_LIST_API_20230301 where itime >=to_date('2023-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
commit;
3.13.确认各分区大小
select owner,partition_name,bytes/1024/1024||'M' from dba_segments where segment_name='T_PUSH_LIST_API' and owner='PCDB';
3.14.收集统计信息
BEGIN
declare
objtab DBMS_STATS.ObjectTab;
obj_filter_list DBMS_STATS.ObjectTab;
BEGIN
obj_filter_list(0).ownname = 'PCDB';
obj_filter_list(0).objtype = 'TABLE';
obj_filter_list(0).objname = 'T_PUSH_LIST_API';
DBMS_STATS.GATHER_SCHEMA_STATS(
'PCDB',
1.0,
FALSE,
'FOR ALL COLUMNS SIZE AUTO',
1,
'AUTO',
TRUE,
NULL,
NULL,
'GATHER',
objtab,
NULL,
TRUE,
TRUE,
obj_filter_list
);
print objtab.count;
FOR i IN 0..objtab.count-1 LOOP
print objtab(i).ownname;
print objtab(i).objtype;
print objtab(i).objname;
print objtab(i).partname;
print objtab(i).subpartname;
print '-------- ';
end loop;
END;
END;
/
3.15.备份T_PUSH_LIST_API_20230301表
dexp sysdba/SYSDBA directory=/dmbak file=t_push_list_api.dmp log=t_push_list_api1.log tables=PCDB.T_PUSH_LIST_API_20230301
3.16.删除T_PUSH_LIST_API_20230301
drop table PCDB.T_PUSH_LIST_API_20230301 purge;
3.17.确认表空间
select a.name 表空间名字,
SUM(b.max_size) 总大小M,
sum(b.total_size * b.page_size / 1024 / 1024 -
b.free_size * b.page_size / 1024 / 1024) 当前使用大小M,
sum(b.max_size) -
sum((b.total_size * b.page_size / 1024 / 1024 -
b.free_size * b.page_size / 1024 / 1024)) 当前剩余空间M,
round((1 - cast((sum(b.total_size * b.page_size / 1024 / 1024) -
sum(b.free_size * b.page_size / 1024 / 1024)) as DEC) /
sum(b.max_size)) * 100,
2) || '%' 剩余百分比
from v$tablespace a, v$datafile b
where a.id = b.group_id
group by a.name;