[오라클] LOB TABLESPACE MOVE 쿼리 생성

select 'alter table OWNER.' || TABLE_NAME || ' move lob(' || PATH# || ') store as (tablespace MOVETS);'

from (

select table_name,substr(max(sys_connect_by_path(column_name,',')),2) as path#

from (

select table_name,column_name,row_number() over(partition by table_name  order by column_name) as ord

from DBA_TAB_COLUMNS

where TABLE_NAME 

in (

select table_name

from DBA_TABLES

where tablespace_name = 'ORIGTS'

)

and DATA_TYPE like '%LOB%'

order by OWNER,TABLE_NAME,COLUMN_NAME

) a

 start with ORD =1 connect by prior ORD = ORD -1 and prior  TABLE_NAME = TABLE_NAME

 group by table_name

) a

;