1 minutes readHow to copy a datafile from ASM to a file system by using dbms package

Step 1. Log onto the target database that is local to the ASM instance as the sys user.

Step 2. create source directory within the target database.
SQL> create or replace directory SOURCE_DIR as ‘+DB_DATA/indian/datafile/’;
Directory created.

(In this example +DGROUP1/V10ASM/datafile/ is the source directory where the datafile is located and where you wish to copy the file from.)

Step 3. create destination directory within database.
SQL> create or replace directory ORACLE_DEST as ‘/export/home/oracle/oracle’;
Directory created.

(In this example /restore is the destination directory where the datafile is to be copied to.)

Step 4. Execute the dbms_file_transfer package.
SQL> BEGINdbms_file_transfer.copy_file(source_directory_object =>’SOURCE_DIR’, source_file_name => ‘users.267.736431563’,destination_directory_object => ‘ORACLE_DEST’,destination_file_name => ‘anup.dbf’);
END;
/
PL/SQL procedure successfully completed.

The file has now been copied to the /restore directory.

RF: 428893.1