0

On an Oracle 12c database I want to reorganize a LOB segment. I used to following sql to generate the alter move command:

    select 'ALTER TABLE '||s.owner||'.'||l.table_name||' move to('||l.column_name||') store as (TABLESPACE NAME1);' 
      from dba_segments s, dba_lobs l
     where s.segment_name = l.segment_name
       and s.tablespace_name = 'NAME1'
       and segment_type='LOBSEGMENT'
       and partition_name is null;

The lob segment I want to rebuild is about 200Gb. Can I use the same tablespace the lob is in? The total free space in that tablespace needs to be 200Gb? During moving the lob is locked?

1

Can I use the same tablespace the lob is in?

Yes.

The total free space in that tablespace needs to be 200Gb?

No. Depends on the amount of data in your LOB segment. If your segment is 200 GB, but you have only 5 GB data in it (because the rest of the data was deleted), then you do not need 200 GB.

During moving the lob is locked?

Not necessarily. Starting with 12.2, alter table has the move online option:

alter table mytable move online lob (lob_column) store as (tablespace users);

The above option is not available in 12.1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.