Tuesday, October 2, 2012

How to move lobsegment and lobindex to a different Tablespace

You can move a LOBSEGMENT with the

ALTER TABLE owner.table_name MOVE LOB (column_name) STORE AS (tablespace_name)

command.
You cannot specify a tablespace for the LOBINDEX -- it is automatically created and moved with the LOBSEGMENT.

The mapping between a Table's LOB column and it's LOBSEGMENT (or vice versa , if you start with a LOBSEGMENT and want to know which Table it belongs to) is ALL/DBA/USER_LOBS where
table_name and column_name are available with segment_name. You can even identify the LOBINDEX from index_name in the same view.

Note : "small" LOBs stored inline (ie in the row itself) are not in a seperate LOBSEGMENT at all. That is called STORAGE IN ROW and is the default for LOBs of 4000bytes or less.

No comments:

Post a Comment