As many of us know and use a technique to get rid of large chunk of data where we rename existing table, indexes, constraints, etc. Create blank original table and insert small(er) volume of data we want to keep from renamed table to recreated one. Except... This is what I ran into yesterday. Table has LOB column with named segment:
LOB(COL_NAME) STORE AS SEGMENT_NAME
As a result recreating original table fails since name SEGMENT_NAME is already taken. And the only way to change segment name I am aware of is
ALTER TABLE TABLE_NAME MOVE LOB(COL_NAME) STORE AS SEGMENT_NEW_NAME
which beats the purpose of whole exercise since moving LOB takes "forever". We ended up changing table DDL to use different segment name but this isn't always possible with vendor apps.
Does anyone know how to rename LOB segment without moving data? And if move is the only way it would be nice if Oracle would intoduce lob segment rename capability.
SY.