-
-
Agrandir datafile avant import
define TBS='SIM_SIM_LOB01'
define DFSZ='10000M'
select 'Alter database datafile '''||FILE_NAME||''' resize '||'&DFSZ'||';' from dba_data_files where TABLESPACE_NAME like '&TBS' order by FILE_NAME; -
Déplacer des objets de type LOB dans le bon tabespace (LOB)
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;
/ -
Change permission using UT_FILE
https://jenniferlinca.wordpress.com/2008/03/28/using-utl_file-how-file-permissions-are-determined-working-sample/