2 Weeks!

Somehow it has already been over two weeks since I created this blog and I haven't managed to even get a second post up so I thought I would throw one up with some details on moving LOB segments in an Oracle database (since that is what I am currently doing - gotta love having to perform maintenance outside of normal work hours).

Anyway, moving a LOB segment is pretty easy. First, you need to determine what table it relates to. You can do this with the following query:

SELECT
   dl.owner,
   dl.table_name,
   dl.column_name
FROM
   dba_lobs dl
WHERE
   dl.segment_name = &log_segment_name;

or to find all LOB segments within a tablespace you can adjust the WHERE clause to look at the tablespace_name column. Once you have the table name, you simply use something similar to the following (you can omit the TABLESPACE &table_tablespace if you don't want to move the table to a new tablespace).

ALTER TABLE &table_name
  MOVE TABLESPACE &table_tablespace
  LOB(&column_name) STORE AS (TABLESPACE &lob_tablespace);

That's all there is to it. See, something useful.