DM8中truncate、delete、drop操作的数据表空间释放

目录

• 一、针对普通表进行测试
• 二、针对分区表进行测试
• 三、小结

为了清楚的知道DM8的truncate操作、delete操作以及drop操作何时会释放所占用的数据表空间，分别对普通索引组织表和分区表进行相关测试，具体测试过程如下：

一、针对普通表进行测试

1、创建测试表空间

create tablespace "TEST1" datafile '/dm8/data/PROD/TEST01.dbf' size 64 autoextend off CACHE = NORMAL;
create tablespace "TEST2" datafile '/dm8/data/PROD/TEST01.dbf' size 64 autoextend off CACHE = NORMAL;
create tablespace "TEST3" datafile '/dm8/data/PROD/TEST01.dbf' size 64 autoextend off CACHE = NORMAL;
           

2、查询表空间的大小

select tablespace_name,bytes/1024/1024||'M' as "TOTAL_SIZE",user_bytes/1024/1024||'M' as "FREE_SIZE" from dba_data_files where tablespace_name in('TEST1','TEST2','TEST3');

TABLESPACE_NAME	TOTAL_SIZE	FREE_SIZE
TEST1	64M	63M
TEST2	64M	63M
TEST3	64M	63M

3、设置回滚页保留时间（便于测试）

sp_set_para_value(1,'UNDO_RETENTION',90);
 
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='UNDO_RETENTION';
           

PARA_NAME	PARA_VALUE
UNDO_RETENTION	90

4、创建测试普通表

create table test01 (id int,name varchar)STORAGE(on TEST1);
begin
   for i in 1..500000 loop
     insert into test01 values(i,i||'hello');
     if i mod 2000 = 0 then
              commit;
     end if;
   end loop;
   commit;
end;
           

create table test02 (id int,name varchar)STORAGE(on TEST2);
begin
   for i in 1..500000 loop
     insert into test02 values(i,i||'hello');
     if i mod 2000 = 0 then
              commit;
     end if;
   end loop;
   commit;
end;
           

create table test03 (id int,name varchar)STORAGE(on TEST3);
begin
   for i in 1..500000 loop
     insert into test03 values(i,i||'hello');
     if i mod 2000 = 0 then
              commit;
     end if;
   end loop;
   commit;
end;
           

5、查询当前表空间的大小

select tablespace_name,bytes/1024/1024||'M' as "TOTAL_SIZE",user_bytes/1024/1024||'M' as "FREE_SIZE" from dba_data_files where tablespace_name in('TEST1','TEST2','TEST3');

TABLESPACE_NAME	TOTAL_SIZE	FREE_SIZE
TEST1	64M	44M
TEST2	64M	44M
TEST3	64M	44M

6、对测试表test01执行delete操作，对测试表test02执行drop操作，对test03表执行truncate操作

delete from test01;
drop table test02;
truncate table test03;
commit;
           

7、查看delete操作、drop操作、truncate操作后的测试表空间的使用情况

select tablespace_name,bytes/1024/1024||'M' as "TOTAL_SIZE",user_bytes/1024/1024||'M' as "FREE_SIZE" from dba_data_files where tablespace_name in('TEST1','TEST2','TEST3');

TABLESPACE_NAME	TOTAL_SIZE	FREE_SIZE
TEST1	64M	44M
TEST2	64M	44M
TEST3	64M	63M

8、等待UNDO_RETENTION时间后，再次查询测试表空间的使用情况

select tablespace_name,bytes/1024/1024||'M' as "TOTAL_SIZE",user_bytes/1024/1024||'M' as "FREE_SIZE" from dba_data_files where tablespace_name in('TEST1','TEST2','TEST3');

TABLESPACE_NAME	TOTAL_SIZE	FREE_SIZE
TEST1	64M	63M
TEST2	64M	63M
TEST3	64M	63M

二、针对分区表进行测试

1、创建测试表空间

create tablespace "TEST1" datafile '/dm8/data/PROD/TEST01.dbf' size 64 autoextend off CACHE = NORMAL;
create tablespace "TEST2" datafile '/dm8/data/PROD/TEST02.dbf' size 64 autoextend off CACHE = NORMAL;
create tablespace "TEST3" datafile '/dm8/data/PROD/TEST03.dbf' size 64 autoextend off CACHE = NORMAL;
           

2、查询表空间的大小

select tablespace_name,bytes/1024/1024||'M' as "TOTAL_SIZE",user_bytes/1024/1024||'M' as "FREE_SIZE" from dba_data_files where tablespace_name in('TEST1','TEST2','TEST3');

TABLESPACE_NAME	TOTAL_SIZE	FREE_SIZE
TEST1	64M	63M
TEST3	64M	63M
TEST2	64M	63M

3、设置回滚页保留时间（便于测试）

sp_set_para_value(1,'UNDO_RETENTION',90);
 
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='UNDO_RETENTION';
           

PARA_NAME	PARA_VALUE
UNDO_RETENTION	90

4、创建测试普通表

create table test01(id INT,name VARCHAR(50))
partition by hash(id)
(
 partition p1 storage( on test1),
 partition p2 storage( on test1),
 partition p3 storage( on test1)
)storage( on test1);
 
begin
   for i in 1..500000 loop
     insert into test01 values(i,i||'hello');
     if i mod 2000 = 0 then
              commit;
     end if;
   end loop;
   commit;
end;
           

create table test02(id INT,name VARCHAR(50))
partition by hash(id)
(
 partition p1 storage( on test2),
 partition p2 storage( on test2),
 partition p3 storage( on test2)
)storage( on test2);
begin
   for i in 1..500000 loop
     insert into test02 values(i,i||'hello');
     if i mod 2000 = 0 then
              commit;
     end if;
   end loop;
   commit;
end;
           

create table test03(id INT,name VARCHAR(50))
partition by hash(id)
(
partition p1 storage( on test3),
partition p2 storage( on test3),
partition p3 storage( on test3)
)storage( on test3);
begin
  for i in 1..500000 loop
    insert into test03 values(i,i||'hello');
    if i mod 2000 = 0 then
             commit;
    end if;
  end loop;
  commit;
end;
           

5、查询当前表空间的大小

select tablespace_name,bytes/1024/1024||'M' as "TOTAL_SIZE",user_bytes/1024/1024||'M' as "FREE_SIZE" from dba_data_files where tablespace_name in('TEST1','TEST2','TEST3');

TABLESPACE_NAME	TOTAL_SIZE	FREE_SIZE
TEST1	64M	44M
TEST2	64M	44M
TEST3	64M	44M

6、对测试表test01执行delete操作，对测试表test02执行drop操作，对test03表执行truncate操作

delete from test01;
drop table test02;
truncate table test03;
commit;
           

7、查看delete操作、drop操作、truncate操作后的测试表空间的使用情况

select tablespace_name,bytes/1024/1024||'M' as "TOTAL_SIZE",user_bytes/1024/1024||'M' as "FREE_SIZE" from dba_data_files where tablespace_name in('TEST1','TEST2','TEST3');

TABLESPACE_NAME	TOTAL_SIZE	FREE_SIZE
TEST1	64M	44M
TEST2	64M	44M
TEST3	64M	63M

8、等待UNDO_RETENTION时间后，再次查询测试表空间的使用情况

select tablespace_name,bytes/1024/1024||'M' as "TOTAL_SIZE",user_bytes/1024/1024||'M' as "FREE_SIZE" from dba_data_files where tablespace_name in('TEST1','TEST2','TEST3');

TABLESPACE_NAME	TOTAL_SIZE	FREE_SIZE
TEST1	64M	63M
TEST2	64M	63M
TEST3	64M	63M

三、小结

从上面测试可知，对于truncate操作，不论是分区表还是普通索引组织表，测试表所占用的数据表空间会及时释放，而drop操作和delete操作对于分区表和普通索引组织表都需要超过回滚页的保留期后，测试表所占用的数据表空间才能释放出来。同样可知，DM8数据库的回滚表空间中的回滚数据是严格按照undo_retention规定的时间进行强制保留。