Oracle | # Move Objects From Tablespace | Move Tablespace LOB | Index Move | Table Move Oracle

SELECT   -- move from tablespace --
case when bytes/(1024*1024*1024) > 1 then (
CASE WHEN segment_type LIKE 'TABLE'
                THEN 'ALTER TABLE '||v1.OWNER||'.'||v1.SEGMENT_NAME||' MOVE TABLESPACE <newtbs> PARALLEL 4;' --owner,segment_name,tablespace_name,segment_type
            WHEN  segment_type LIKE 'INDEX'
                THEN 'ALTER INDEX '||v1.OWNER||'.'||v1.SEGMENT_NAME||' REBUILD TABLESPACE <newtbs> PARALLEL 4 ;'
            WHEN  segment_type LIKE 'LOBSEGMENT'
                THEN 'ALTER TABLE  '||lob.owner||'.'||lob.table_name||' MOVE LOB('||lob.column_name||') STORE AS (TABLESPACE <newtbs>) PARALLEL 4 ;'
       END )
  when  bytes/(1024*1024*1024) <= 0.5 then  (
CASE WHEN segment_type LIKE 'TABLE'
                THEN 'ALTER TABLE '||v1.OWNER||'.'||v1.SEGMENT_NAME||' MOVE TABLESPACE <newtbs>;' --owner,segment_name,tablespace_name,segment_type
            WHEN  segment_type LIKE 'INDEX'
                THEN 'ALTER INDEX '||v1.OWNER||'.'||v1.SEGMENT_NAME||' REBUILD TABLESPACE <newtbs>;'
            WHEN  segment_type LIKE 'LOBSEGMENT'
                THEN 'ALTER TABLE  '||lob.owner||'.'||lob.table_name||' MOVE LOB('||lob.column_name||') STORE AS (TABLESPACE <newtbs>) ;'
       END
       )
       END as DDL    
    from dba_segments v1
        left join dba_lobs lob on v1.segment_name=lob.segment_name
         where v1.tablespace_name like 'USERS'
            and v1.owner like 'Yrownr%'
            and SEGMENT_TYPE NOT IN ('LOBINDEX')
            and v1.segment_name not like 'BIN$%'
                order by v1.owner ;