データブロック確認

• Oracle

https://taityo-diary.hatenablog.jp/entry/2015/08/17/170644

create table tab23(col1 int,col2 varchar2(100));
insert into tab23 values(1,'XXXXXXXXXXXXXXXXXXXXXXX');
commit;
alter system checkpoint;

select
dbms_rowid.rowid_to_absolute_fno(rowid, 'TEST', 'TAB23') file_id,
dbms_rowid.rowid_block_number(rowid) block_id
from tab23;

alter session set tracefile_identifier = 'datadump';

alter system dump datafile 4 block 2829;
alter system dump datafile 4 block MIN 2829 block MAX 2829;

• MySQL

調べた限りない模様

• PostgreSQL

(10)

【1】pageinspect
http://pgsqldeepdive.blogspot.com/2012/12/postgresql_16.html

create extension pageinspect;
\x

drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1);

select * from get_raw_page('tab1',0);
select * from page_header(get_raw_page('tab1',0));
select * from heap_page_items(get_raw_page('tab1',0));
select lp,lp_off,lp_flags,lp_len,t_xmin,t_xmax from heap_page_items(get_raw_page('tab1',0));

【2】pg_filedump
http://pgsqldeepdive.blogspot.com/2012/12/pgfiledump.html

-- 1.Postgresソースコンパイル

yum install gcc zlib-devel readline-devel

cd /usr/local/src
wget https://ftp.postgresql.org/pub/source/v10.6/postgresql-10.6.tar.gz
tar xvfz postgresql-10.6.tar.gz

chown -R postgres:postgres postgresql-10.6
su postgres
cd postgresql-10.6
./configure
make
sudo make install

-- 2.pg_filedumpインストール

su - postgres
tar xvzf pg_filedump-REL_10_1-280ce13.tar.gz
cd pg_filedump-REL_10_1-280ce13

export PATH=$PATH:/usr/local/pgsql/bin

make

sudo bash -c 'PATH=$PATH:/usr/local/pgsql/bin make install'

--3. 動作確認

drop table tab1;
drop table tab2;

create table tab1(col1 int);
create table tab2(col1 int) with ( fillfactor = 50);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

insert into tab1 select a from generate_series(1,1000) a;
insert into tab2 select a from generate_series(1,1000) a;

analyze tab1;
analyze tab2;

sudo systemctl restart postgresql-10
※なぜか一度サービス再起動しないとpg_filedump実行時にエラーとなる

/usr/pgsql-10/bin/oid2name
/usr/pgsql-10/bin/oid2name -d test -i

/usr/local/pgsql/bin/pg_filedump -a /var/lib/pgsql/10/data/base/32768/140043
/usr/local/pgsql/bin/pg_filedump -a /var/lib/pgsql/10/data/base/32768/140046

/usr/local/pgsql/bin/pg_filedump -R 1 1 /var/lib/pgsql/10/data/base/32768/140043
/usr/local/pgsql/bin/pg_filedump -R 1 1 /var/lib/pgsql/10/data/base/32768/140046

• SQL Server

(2014)

http://memorandom-nishi.hatenablog.jp/entry/2017/02/26/174358

sp_helptext [sys.fn_PhysLocCracker]

select * from dbo.tab1
cross apply sys.fn_PhysLocCracker(%%physloc%%) as fplc
order by
fplc.file_id,
fplc.page_id,
fplc.slot_id

select [dpa].[page_level] as [page_level],
[dpa].[allocated_page_page_id] as [page_id],
[i].[name] as [index_name],
[dpa].[page_type_desc],
[dpa].[previous_page_page_id],
[dpa].[next_page_page_id]
from sys.dm_db_database_page_allocations( db_id('test'),
object_id('dbo.tab1'),
1,
null,
'detailed'
) as [dpa]
inner join sys.indexes as [i]
on [dpa].[object_id] = [i].[object_id]
and [dpa].[index_id] = [i].[index_id]
where [dpa].[page_level] is not null
order by [dpa].[page_level] desc,
[dpa].[allocated_page_page_id]

(2019)

drop table tab1;
create table tab1(col1 int not null primary key);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
select * from tab1;

select t1.col1, t3.*
from tab1 t1
cross apply sys.fn_PageResCracker(%%physloc%%) t2
cross apply sys.dm_db_page_info(DB_ID(), t2.file_id, t2.page_id, 'DETAILED') t3
;