SET serveroutput ON;
DECLARE
ordre_sql VARCHAR2(32000 CHAR);
nomTable VARCHAR2(200 CHAR);
nomColonne VARCHAR2(200 CHAR);
nomSchema VARCHAR2(200 CHAR) := 'JOJO';
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
FOR lob_cols IN (
SELECT table_name nomTable, column_name nomColonne FROM ALL_TAB_COLS
WHERE data_type LIKE '%LOB%'
AND owner = nomSchema
AND table_name IN (SELECT table_name FROM ALL_TABLES WHERE partitioned = 'NO' AND TEMPORARY = 'N')
)
LOOP
ordre_sql := 'ALTER TABLE '||nomSchema||'."'
|| lob_cols.nomTable
|| '" MOVE LOB('
|| lob_cols.nomColonne
|| ') STORE AS SECUREFILE (TABLESPACE JOJO_LOB01 NOcompress);';
--EXECUTE IMMEDIATE ordre_sql;
DBMS_OUTPUT.PUT_LINE(ordre_sql);
END LOOP;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('Il faut créer le tablespace JOJO_LOB01 avant d effectuer cette action');
END;
/