Moving oracle tables with lob columns to another tablespace

I have encountered a problem with oracle database that it does not reuse lob data. So, if you have 5000 rows of some lob data, and you delete them from the table, then insert the same 5000 rows, you will have 10000 rows of consumed space. That could be done easily with shrinking the table but the default tablespace segment space management (SSM) on oracle 10g is manual, so it does not allow row movement and table shrinking.

First, you create a tablespace with ASSM, that is automatic segment space management:

CREATE TABLESPACE NEW_TABLESPACE DATAFILE
'/home/oracle/product/10.2.0/db_1/dbs/new_tablespace.dbf' SIZE 16109M AUTOEXTEND ON NEXT
1000M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Now we can proceed with table movement and after that lob_segment movement. There are some steps we should follow:

  1. Move table
  2. Rebuild indexes (and move them to the new tablespace if you wish so)
  3. Move the lob segment
  4. Enable row movement
  5. Shrink the table and lob segment

Here is the sql needed, of course, you will need to change and/or add indexes that reside on the current table.

--moving the table to the new tablespace
alter table table_name move tablespace new_tablespace;

--rebuilding the index(es) and moving them to the new tablespace
alter index index_name rebuild tablespace new_tablespace;

--moving the lobsegment(s) to the new tablespace
alter table table_name move lob (lob_column) store as
(tablespace new_tablespace);

--shrinking the table and lobsegment
alter table table_name enable row movement;
alter table table_name shrink space cascade;
alter table table_name modify lob (lob_column) (shrink space);

Table and the lob segment will shrink during migration, but just in case you need further space shrinkage i have included the scripts needed.

Datediff function in Oracle

As i am working on Oracle database at my day job, i have found something that is by default on sqlserver but not in oracle. That is the datediff function that given the input of two dates and an identifier like day, month or year returns integer with the requested number.

So to cut the long story short, i have written my own datediff function for oracle use. You can request more return types in the comments, and i will be glad to add them. Here is the code:

create or replace function datediff(v_type VARCHAR2, date_from DATE, date_to DATE) return integer as
v_diff INTEGER;
begin
v_diff :=
  case upper(v_type)
    when 'MONTH' then
      months_between(date_to, date_from)
    when 'DAY' then
      date_to - date_from
    when 'YEAR' then
      trunc(months_between(date_to, date_from)/12)
    when 'HH' then
    (date_to - date_from) * 24
    when 'MM' then
    (date_to - date_from) * 24 * 60
    when 'SS' then
    (date_to - date_from) * 24 * 60 * 60
  end;
return abs(v_diff);
end;

As you can see, the type parameters are: MONTH for difference in months, DAY for difference in days and YEAR for difference in years respectively.

I hope oracle will see the light and include this function in any of the future releases.

UPDATE: As my friend LILkillaBEE has mentioned that he could be in a need of hour/minute/second difference, i have added that too, so, this should be the final implementation.

New parameters are HH for hour, MM for minute and SS for seconds difference.