Oracle – Reclaiming LOB space after deletion (CLOB, BLOB)

To reclaim space after a deleting LOBs, use the following commands. XXXXX is my tablename, data is my column name for the lob, in pink the size of the table.

1) Check size of the table XXXXX

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'XXXXX';

>> XXXXX DATA SYS_LOB0000025315C00007$$ 1073741824

2) Delete, in my case, I just clean up all files in my table.


>> 183 rows deleted.
>> committed.

3) Ask Oracle to shrink the tablespace used by the LOB column ‘data’. Have a coffee break.


>> table XXXXX altered.

4) Check size again:

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'XXXXX';

>> XXXXX DATA SYS_LOB0000025315C00007$$ 65536


SqlFiddle – A way to share SQL snippets so that they can be tested in the browser

A couple of days ago, I stumbled upon SQLFiddle, a little magical website that allows you to upload a DBSchema and run queries on it, live in the browser. It also allows to share them with other users.

In the course of my work, I’ve often come across a table that has an identifier, a timestamp and a value, and these rows must be mapped to one new row containing multiple identifiers. Two typical examples of that are two identifiers defining a minimum and a maximum value timeseries that must be put into a single row entry and an entry that is built from three identified timeseries representing value, volatility and mean reversion parameters of a stochastic process.

In my earlier blog post, Oracle 10g – Pivoting data, I provided two different sql scripts to solve the first case min/max time series. You can find the SQLFiddles here:

Decode with subquery and max 

Decode in one query and max 

You can even view your query’s execution plan, which is a really nice touch. I’ll take the time to share some more oracle sql statements next time in an SQLFiddle.

Oracle 10g – Pivoting data

Oracle 10g doesn’t have the Pivot function. To convert a common type a data (Date, Id, Value) into a list of (Date, Value of Id1, Value of Id2, …), if you know the number of columns (and their names), you can a decode function (basically a SQL IF or CASE) function to do it.

Demonstration below with the code to build the table and test it.

Insert into TESTTABLE (DATETIME,SCENARIOI,MYVALUE) values (to_date('01.10.12 00:00','DD.MM.RR HH24:MI'),1,10);
Insert into TESTTABLE (DATETIME,SCENARIOI,MYVALUE) values (to_date('01.10.12 00:00','DD.MM.RR HH24:MI'),2,20);
Insert into TESTTABLE (DATETIME,SCENARIOI,MYVALUE) values (to_date('01.10.12 01:00','DD.MM.RR HH24:MI'),1,15);
Insert into TESTTABLE (DATETIME,SCENARIOI,MYVALUE) values (to_date('01.10.12 01:00','DD.MM.RR HH24:MI'),2,20);

The created and populated table: 

To extract the data in a matrix format, with one column per scenario, we can use either of the queries below:

select datetime,
 max(decode(scenarioi,1, MyValue)),
 max(decode(scenarioi,2, MyValue)) from testtable
group by datetime
order by 1;
SELECT DateTime, MAX(One) as Sc1, MAX(Two) as Sc2
 FROM (SELECT datetime,
 decode(scenarioi, 1, MyValue) as One,
 decode(scenarioi, 2, MyValue) as Two
 FROM testtable

An example output of the query: 

It would be nice to have a query that could automatically select a number of columns and convert them, it seems that requires some PLSQL, but sadly I’ve not been able to figure it out yet. If anyone has such an example, just leave a comment.