Search

Top 60 Oracle Blogs

Recent comments

Oracle Database Block Visualizer

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 http://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:

SQL> conn / as sysdba
Connected.
SQL> create tablespace emps_tbs
  2  datafile '/u01/app/oracle/oradata/orcl/emps_tbs01.dbf'
  3  size 64m
  4  autoextend on next 32m
  5  maxsize 1g;

Tablespace created.

SQL> conn hr/hr
Connected.
SQL> create table employees1 tablespace emps_tbs as select * from employees;

Table created.

SQL> create table employees2 tablespace emps_tbs as select * from employees;

Table created.

SQL> create table employees3 tablespace emps_tbs as select * from employees;

Table created.

SQL>

Now let’s fill those tables:

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    for i in 1..50 loop
  3	 for j in 1..10 loop
  4	   insert into employees1 select * from employees;
  5	   insert into employees2 select * from employees;
  6	   insert into employees3 select * from employees;
  7	   commit;
  8	end loop;
  9    end loop;
 10* end;
SQL> /

PL/SQL procedure successfully completed.

Now let’s play with tables a little bit </p />
</p></div>

    	  	<div class=