Recently I wanted to demonstrate to some people on my training, how Oracle database maintains blocks in a datafile – what happens after truncate, truncate with drop storage clause, delete, regular insert, direct path insert and so on…
I didn’t find any tool for that so I’ve written my own. It’s core code is based on my previous database block research – project RICO https://blog.ora-600.pl/2015/09/03/rico/
Tool is very small and simple and right now it supports only regular blocks with data and no compression.
And this is how it works – let’s create a tablespace and 3 new tables in HR schema:
3 | SQL> create tablespace emps_tbs |
4 | 2 datafile '/u01/app/oracle/oradata/orcl/emps_tbs01.dbf' |
6 | 4 autoextend on next 32m |
13 | SQL> create table employees1 tablespace emps_tbs as select * from employees; |
17 | SQL> create table employees2 tablespace emps_tbs as select * from employees; |
21 | SQL> create table employees3 tablespace emps_tbs as select * from employees; |
Now let’s fill those tables:
7 | 4 insert into employees1 select * from employees; |
8 | 5 insert into employees2 select * from employees; |
9 | 6 insert into employees3 select * from employees; |
16 | PL/SQL procedure successfully completed. |
Now let’s play with tables a little bit 🙂
1 | To check the physical layout of blocks in a datafile we have get data_object_id of all tables: |
4 | 1 select object_id, data_object_id, object_name |
6 | 3* where object_name like 'EMPLOYEES_' |
9 | OBJECT_ID DATA_OBJECT_ID OBJECT_NAME |
10 | ---------- -------------- ------------------------------ |
11 | 94033 94033 EMPLOYEES3 |
12 | 94032 94032 EMPLOYEES2 |
13 | 94031 94031 EMPLOYEES1 |
Now we can prepare parameter file for my tool:
And this is how we use it (I use print screens to show you colors :))
And below visualization you can find legend:
Well, that's it. If you want to play with it, you can find this tool, compiled for Linux x86_64 here: http://ora-600.pl/oinstall/odbv.x86_64
I will use it in the future to explain some stuff 🙂 Enjoy!
