LOBSEGMENT – Dúvidas

[diegolenhardtParticipante]

Cara, esses dias fiz uma geral numa base aqui, e fiz assim:

–1–Organizar tablespace das tabelas

select 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE WFL_DATA;'
from user_tables
where tablespace_name = 'USERS'
ORDER BY 1


–2–Organizar indices das tabelas sem LOB

select 'ALTER INDEX '||INDEX_NAME||' REBUILD TABLESPACE WFL_IDX;'
from user_indexes A
where tablespace_name = 'USERS'
and index_type 'LOB'
ORDER BY TABLE_NAME


–3–Organizar indices das tabelas com LOB

select 'ALTER TABLE '||A.TABLE_NAME||' MOVE TABLESPACE WFL_IDX LOB ('||B.COLUMN_NAME||') STORE AS (TABLESPACE WFL_IDX INDEX (TABLESPACE WFL_IDX));'
from user_indexes A, USER_TAB_COLUMNS B
where a.table_name = b.table_name
and a.tablespace_name = 'USERS'
AND b.data_type IN ('BLOB', 'CLOB')
ORDER BY a.TABLE_NAME


veja aí se ajuda..