Renaming and Relocating Datafiles Script | DBMS_FILE_TRANSFER | Move Datafiles Oracle

Renaming and Relocating Datafiles Script | DBMS_FILE_TRANSFER | Move Datafiles Oracle

-- Kamil Piórek
-- kamil.piorekk@gmail.com
-- This isn't a production script and shouldn't be used on production database.
-- Usefull.

DECLARE
VN NUMBER (10);
v1 VARCHAR (3000);
TBSN VARCHAR(100)  DEFAULT 'TESTTS1';
DELSQL VARCHAR(3000);
NDIR VARCHAR (400) DEFAULT '/test';    -- NEW
BEGIN

EXECUTE IMMEDIATE 'create directory tmp2dir as '''||NDIR||''' ';

FOR rec IN (
SELECT 
  file_name,
  FILE_ID,  
  SubStr(file_name,1,(instr(file_name,'/', 1, (length (file_name) - length (replace (file_name, '/')))))-1) AS SRCDIR,
  substr (file_name, ((instr(file_name,'/', 1, (length (file_name) - length (replace (file_name, '/')))))+1) , Length(file_name)) AS FNAME,
  'ALTER TABLESPACE '||tablespace_name||' RENAME DATAFILE '''||file_name||''' TO ''' || 
        REPLACE ( file_name, (  (SubStr(file_name,1,(instr(file_name,'/', 1, (length (file_name) - length (replace (file_name, '/')))))-1))||'' ) , NDIR )||''''   AS CMD
FROM dba_data_files
  WHERE TABLESPACE_NAME = TBSN AND file_name LIKE '%TS2%'
  )
LOOP
EXECUTE IMMEDIATE 'create directory tmp1dir as '''||rec.SRCDIR||'''';

SELECT Count(*) INTO VN
FROM 
DBA_DATA_FILES WHERE FILE_ID=REC.FILE_ID ;
IF VN >=1 

  THEN 
DBMS_FILE_TRANSFER.COPY_FILE('tmp1dir',rec.FNAME, 'tmp2dir' ,rec.FNAME );
DELSQL:='ALTER TABLESPACE '|||| ';  ----- '''||rec.file_name||''' OFFLINE FOR DROP ''';
EXECUTE IMMEDIATE 'drop directory tmp1dir';
EXECUTE IMMEDIATE 'drop directory tmp2dir';

Dbms_Output.put_line(rec.CMD);
v1:=rec.CMD;
EXECUTE immediate V1;

--USUNIECIE PLIKU
EXECUTE IMMEDIATE (DELSQL);
Dbms_Output.put_line(DELSQL);
  END IF ;               
END LOOP;
END;



Kamil Piórek