ODBVv2 – ghostdata busters

Some time ago I wrote a simple tool to learn about Oracle data block internals – ODBV.
The series of articles can be found here: http://blog.ora-600.pl/?s=odbv&submit= and the github repo is here: https://github.com/ora600pl/odbv

This is not a production tool but during the last session in Birmingham at UKOUG_TECH17 – where I was doing a presentation using this tool – I came to the conclusion that with a little bit of work it could be used to trace ghost data in a database.

What is ghost data? This is very simple – each time we delete something or truncate or move, Oracle database is not removing data from our datafile – the blocks are "marked" for reuse and are not associated with any logical object in a database, but our data is still there.

These days we have to think about security a lot – that’s why when you move a table to encrypted tablespace you might want to be sure, that you leave no trace of important data behind.

I propose the v2 of ODBV (written in Python3) – this is a demo version and it requires a lot to be actually useful, but for now, it works like this:

ora-600:odbv2 inter$ python3.6 odbv2.py
ODBV v2 by Kamil Stawiarski (@ora600pl | www.ora-600.pl)
ODBV v2 requires cx_Oralce - you pip to install it


Enter interactive mode: $ python3 odbv2.py -i
Or do a batch processing: $ python3 -f file_with_path_to_dbfs -c user/pass@ip:port/service -o file.html


Usage for interactive mode:
	to add a datafile for parsing and visualizing:
		ODBV> add file <>
	to generate html visualization report:
		ODBV> make html file_name.html
	to connect to Oracle for dictionary data:
		get dict user/password@ip:port/service

	Default block size is 8192 - to change it to N:
		ODBV> set blocksize N

As you can see you can use this tool in interactive or batch mode. This tool requires a cx_Oralce package for Python3 to connect to Oracle database – it is necessary to get DBA_OBJECTS contents to map blocks to existing objects and show the rest of the blocks that contents TABLE DATA or INDEX DATA as ghost blocks.

So let’s check how it works.

First of all let’s create a secured tablespace, encrypted with AES256:

SQL> administer key management create keystore '/u01/wallet' identified by "Dupiszcze";

Zmieniono magazyn kluczy.

SQL> administer key management set keystore open identified by "Dupiszcze";

Zmieniono magazyn kluczy.

SQL> administer key management set key identified by "Dupiszcze" with backup;

Zmieniono magazyn kluczy.

SQL> create tablespace secure_data
  2  datafile size 1m
  3  autoextend on next 1m
  4  maxsize 1g
  5  encryption using 'AES256'
  6  default storage (encrypt);

Utworzono przestrzen tabel.

I have 2 tables in my tablespace TBS_DUPA which is not encrypted:

SQL> select table_name
  2  from dba_tables
  3  where tablespace_name='TBS_DUPA';

TABLE_NAME
--------------------------------------------------------------------------------
EMPLOYEES_SEC
EMPLOYEES_TMP2

Now let’s move table EMPLOYEES_SEC to a new, highly secure tablespace </p />
</p></div>

    	  	<div class=