keren的笔记
0%

关于压测oracle库表数据清理问题

发表于 更新于

背景：在甲方公司进行应用压力测试，oracle数据库服务器的磁盘空间只提供了十几个G，对于要求吞吐量20000的应用来说，数据量之大可想而知，十几个G的空间只够压测几十分钟，更谈不上48小时的稳定性测试。因此只能从oracle数据库层面着手，数据库表按时间分区，然后创建一个job定时调用存储过程清理数据分区，保证压测的过程有足够的空间进行数据存储。

1、数据库表创建

1.1、创建表空间
1
create tablespace ts_test_data datafile '/data/oracle/test/ts_test_data.dbf' size 200M autoextend on;
1.2、创建表
1
2
3
4
5
6
7
8
9
10
11
12
create table cust_tb (
	id varchar2(64),
  name varchar2(255),
  created date
)tablespace ts_test_data
partition by range(created) interval (NUMTODSINTERVAL(10, 'MINUTE'))
STORE IN (ts_test_data)
(
  -- P01初始分区
  partition P01 values less than (to_date('2020-12-28 00:00:00','YYYY-MM-DD HH24:MI:SS'))
  tablespace ts_test_data
);

创建表根据时间字段，按照时间字段进行分区，每间隔10分钟进行一次自动分区。

2、创建清理分区存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create or replace procedure delete_partition(tb_name varchar2) is
-- 分区位置编码
max_position number;
-- 循环临时变量
cnt number;
-- 分区名称
ptn_name varchar2(64);
-- 删除分区的sql语句
sql_str varchar2(255);
begin
	-- partition_position位置从1开始自增
  select max(partition_position) into max_position from user_tab_partitions where table_name = tb_name;
  if max_position > 2 then
  	-- 每次清除位置从2到max_position-1上的表分区内容，1位置为初始创建的表分区，不能删除，最大一个位置的为最后一个，又可能正在写数据，也不删除
     for cnt in 2..(max_position-1) loop
       select partition_name into ptn_name from user_tab_partitions where table_name = tb_name and partition_position = cnt;
       sql_str := 'alter table '||tb_name||' drop partition '||ptn_name||' update global indexes';
       execute immediate sql_str;
     end loop;
     commit;
  end if;
end delete_partition;

3、创建定时job

创建job，定时调用清除分区的存储过程。

1
2
3
4
5
DECLARE
 job NUMBER;
BEGIN
	dbms_job.submit(job,'DELETE_PARTITION(''CUST_TB'');',sysdate,'sysdate+10/1440');
END;
  • job：定时job的ID。
  • DELETE_PARTITION(‘’CUST_TB’’);：调用的存储过程，此处需要注意的是传递进去的表名必须大写，因为在分区表中存储的表名为大写。
  • sysdate：job任务开始运行的时间。
  • sysdate+10/1440：每10分钟运行一次，1440为24小时（24x60），如若为10秒运行一次，则此处为86400（24x60x60）。

4、其他关联操作

  • 表空间数据文件存储释放
1
2
3
-- 可先truncate table释放表空间后再进行操作
-- resize后的大小必须大于现有储存数据的大小
ALTER DATABASE datafile '/data/oracle/test/ts_test_data.dbf' resize 200M;
  • 删除表空间及数据文件
1
drop tablespace tablespace_name including contents and datafiles;
  • 删除定时job
1
2
3
4
BEGIN
	-- 传递的参数为job的ID，可从user_jobs表查询得到
	dbms_job.remove(1);
END;
  • 删除表分区及数据
1
2
-- SYS_P663分区名称,从user_tab_partitions表查询得到
alter table table_name drop partition SYS_P663 update global indexes;