Mir Sayeed Hassan – Oracle Blog

Copy OS files from one location to another by sql*plus using Oracle function UTL_FILE.FCOPY

Overview:

Create the sample 2 directories for test & create 1 file in one of the directory therefore create a user for new test or use existing user. Grant the appropriate privilege to the user and copy the files from one folder to another using the Oracle built function called “UTL_FILE.FCOPY”

Fallow the step by step procedure as shown below:

Create Directory in tmp:

[oracle@testdb tmp]$ cd copy_dir/

[oracle@testdb tmp]$ mkdir restore_dir

[oracle@testdb tmp]$ cd copy_dir/

Create a file:

[oracle@testdb copy_dir]$ vi testcopy.txt   --- ADD SOME CONTENT INIT
MIR_TESTCOPY
:wq

[oracle@testdb copy_dir]$ cat testcopy.txt
MIR_TESTCOPY

Create a new user or use exiting user as per your requirement

Here I created the new user for testing env:

SQL> create user mir identified by mir123;
User created.

SQL> grant connect,resource to mir;
Grant succeeded.

SQL> grant create any directory to mir;
Grant succeeded.

SQL> grant execute on utl_file to mir;     -----
Grant succeeded.

SQL> create directory file_src as '/tmp/copy_dir';
Directory created.

SQL> create directory restore_src as '/tmp/restore_dir';
Directory created.

SQL> exec utl_file.fcopy('FILE_SRC','testcopy.txt','RESTORE_SRC','copied_testcopy.txt');
PL/SQL procedure successfully completed.

Verify:

Exiting directory with created file:

[oracle@testdb tmp]$ cd copy_dir/

[oracle@testdb copy_dir]$ ls

testcopy.txt

[oracle@testdb copy_dir]$ cat testcopy.txt
MIR_TESTCOPY

Copied file to another directory:

[oracle@testdb copy_dir]$ cd ../restore_dir/

[oracle@testdb restore_dir]$ cat copied_testcopy.txt
MIR_TESTCOPY

===========Hence the above scenario is tested & verified in our test db===========