(8.0.22)
確認結果:
データファイルサイズは変化なし
-- データ準備
drop tablespace tbs1;
create tablespace tbs1 add datafile 'tbs1.ibd' engine=innodb;
drop table tab1;
drop table tab2;
drop table tab3;
create table tab1(col1 int primary key, col2 varchar(10)) tablespace tbs1;
create index ind11 on tab1(col2);
create table tab2(col1 int primary key, col2 varchar(10)) tablespace tbs1;
create index ind21 on tab2(col2);
create table tab3(col1 int primary key, col2 varchar(10)) tablespace tbs1;
create index ind31 on tab3(col2);
drop procedure proc1;
delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
while i < x do
set i = i + 1;
insert into tab1 values(i,rpad('x',10,'x') );
insert into tab2 values(i,rpad('x',10,'x') );
insert into tab3 values(i,rpad('x',10,'x') );
end while;
end
//
delimiter ;
start transaction;
call proc1(1000000);
commit;
-- exp/imp
mysqldump -uroot -p test tab2 > tab2.dmp
drop table tab2;
source tab2.dmp
-- 使用サイズ確認
select space,name, allocated_size/1024/1024 mbytes
from information_schema.innodb_tablespaces
where name like 'tbs1'
;
analyze table tab1;
analyze table tab2;
analyze table tab3;
select
table_name, engine, table_rows as tbl_rows,
floor( (data_length+index_length)/1024/1024) as allmb, #総容量
floor( (data_length)/1024/1024) as dmb, #データ容量
floor( (index_length)/1024/1024) as imb #インデックス容量
from information_schema.tables
where table_schema = database()
and table_name in ('tab1','tab2','tab3')
order by table_name
;
select database_name,table_name,clustered_index_size,sum_of_other_index_sizes
from mysql.innodb_table_stats
where database_name = database()
and table_name in ('tab1','tab2','tab3')
order by table_name
;
(19c)
確認方法:
3テーブルのデータをばらけるように積む。そのうち1テーブルをexp→drop→impする。
exp/imp前後でデータファイルサイズを確認し、dropで生成された隙間が再利用されるか確認。
確認結果:
dropで生成された隙間は基本的には再利用される。
ただし、ブロック数がすこし変動し、データファイルサイズが少し大きくなった。
-- データ準備
drop tablespace tbs1 including contents and datafiles;
create tablespace tbs1 datafile '/u01/app/oracle/oradata/ORCL/pdb1/tbs1_01.dbf' size 10M autoextend on maxsize unlimited;
drop table tab1 purge;
drop table tab2 purge;
drop table tab3 purge;
create table tab1(col1 int primary key, col2 varchar2(10)) tablespace tbs1;
create index ind11 on tab1(col2) tablespace tbs1;
create table tab2(col1 int primary key, col2 varchar2(10)) tablespace tbs1;
create index ind21 on tab2(col2) tablespace tbs1;
create table tab3(col1 int primary key, col2 varchar2(10)) tablespace tbs1;
create index ind31 on tab3(col2) tablespace tbs1;
declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,rpad('x',10,'x') );
insert into tab2 values(i,rpad('x',10,'x') );
insert into tab3 values(i,rpad('x',10,'x') );
commit;
end loop;
end;
/
-- exp/imp
expdp test/test@pdb1 directory=ORA_DIR dumpfile=TAB2.dmp logfile=TAB2.exp.log tables=TAB2
drop table tab2 purge;
impdp test/test@pdb1 directory=ORA_DIR dumpfile=TAB2.dmp logfile=TAB2.imp.log tables=TAB2
-- 使用サイズ確認
select segment_name,tablespace_name,segment_type,bytes/1024/1024 mbytes,blocks,extents
from user_segments
where tablespace_name = 'TBS1'
order by segment_name
;
select
d.tablespace_name,
d.mbytes "total[MB]",
NVL(f.mbytes,0) "free[MB]",
d.mbytes - NVL(f.mbytes,0) "used[MB]",
(1 - (NVL(f.mbytes,0)/d.mbytes))*100 "used_percent"
from
(SELECT tablespace_name, (SUM(bytes)/(1024*1024)) mbytes
FROM dba_data_files GROUP BY tablespace_name) d
left outer join
(SELECT tablespace_name, (SUM(bytes)/(1024*1024)) mbytes
FROM dba_free_space GROUP BY tablespace_name) f
on d.tablespace_name=f.tablespace_name
;
(13)
確認結果:
テーブル単位にファイルが分かれているため、単にデータファイルが再作成されるのみ
-- データ準備
drop tablespace tbs1;
create tablespace tbs1 owner postgres location '/var/lib/pgsql/13';
drop table tab1;
drop table tab2;
drop table tab3;
create table tab1(col1 int, col2 varchar(10)) tablespace tbs1;
create unique index tab1pk on tab1(col1) tablespace tbs1;
alter table tab1 add constraint tab1pk primary key using index tab1pk;
create index ind11 on tab1(col2) tablespace tbs1;
create table tab2(col1 int, col2 varchar(10)) tablespace tbs1;
create unique index tab2pk on tab2(col1) tablespace tbs1;
alter table tab2 add constraint tab2pk primary key using index tab2pk;
create index ind21 on tab2(col2) tablespace tbs1;
create table tab3(col1 int, col2 varchar(10)) tablespace tbs1;
create unique index tab3pk on tab3(col1) tablespace tbs1;
alter table tab3 add constraint tab3pk primary key using index tab3pk;
create index ind31 on tab3(col2) tablespace tbs1;
do $$
declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,rpad('x',10,'x'));
insert into tab2 values(i,rpad('x',10,'x'));
insert into tab3 values(i,rpad('x',10,'x'));
end loop;
end
$$
;
-- exp/imp
pg_dump -Fc -t tab2 test > tab2.dmp
drop table tab2;
pg_restore -Fc -d test tab2.dmp
-- 使用サイズ確認
\db+
\dt+ tab1
\dt+ tab2
\dt+ tab3
analyze tab1;
analyze tab2;
analyze tab3;
select oid,relname,relpages,reltuples
from pg_class
where reltablespace = (select oid from pg_tablespace where spcname = 'tbs1')
order by relname
;
select pg_size_pretty(pg_total_relation_size('tab1'));
select pg_size_pretty(pg_total_relation_size('tab2'));
select pg_size_pretty(pg_total_relation_size('tab3'));
(2019)
確認結果:
データファイルサイズは変化なし
-- データ準備
use master
alter database test remove file tbs1_01;
alter database test remove filegroup tbs1;
alter database test add filegroup tbs1;
alter database test add file
(name = tbs1_01 ,filename = 'C:\test\tbs1_01.ndf'
) to filegroup tbs1
;
use test
drop table tab1;
drop table tab2;
drop table tab3;
create table tab1(
col1 int not null primary key,
col2 varchar(10)
) on tbs1
;
create index ind11 on tab1(col2);
create table tab2(
col1 int not null primary key,
col2 varchar(10)
) on tbs1
;
create index ind21 on tab2(col2);
create table tab3(
col1 int not null primary key,
col2 varchar(10)
) on tbs1
;
create index ind31 on tab3(col2);
set nocount on
declare @i integer;
set @i = 1;
while @i <= 1000000
begin
insert into tab1 values(@i,replicate('x',10));
insert into tab2 values(@i,replicate('x',10));
insert into tab3 values(@i,replicate('x',10));
set @i = @i + 1;
end
-- exp/imp
SSMSのタスク→スクリプトの生成でスキーマとデータのスクリプトをエクスポート
※主キー以外のインデックスはエクスポートされない模様
drop table tab2;
エクスポートしたSQLを実行
-- 使用サイズ確認
select database_id,file_id,physical_name,size * 8/1024 mbyte
from sys.master_files
where database_id = DB_ID()
;
exec sp_spaceused 'dbo.tab1';
exec sp_spaceused 'dbo.tab2';
exec sp_spaceused 'dbo.tab3';
select * from sys.dm_db_partition_stats
where object_id in (object_id('dbo.tab1'),object_id('dbo.tab2'),object_id('dbo.tab3'))
order by object_name(object_id)
;