Oracle Copying Files Using the Database Server

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

The DBMS_FILE_TRANSFER package helps you copy binary files to a different location on the same server or transfer files between Oracle databases.

Both the source and destination files should be of the same type, either operating system files or ASM files.

The maximum file size is 2 terabytes, and the file must be in multiples of 512 bytes.

You can monitor the progress of all your file-copy operations using the V$SESSION_LONGOPS view.

Copying Files on a Local System

CREATE DIRECTORY source_dir AS
'/u01/app/oracle';
CREATE DIRECTORY dest_dir AS
'/u01/app/oracle/example';
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
SOURCE_FILE_NAME => 'exm_old.txt',
DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
DESTINATION_FILE_NAME => 'exm_new.txt');
END;

Transferring a File to a Different Database

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
SOURCE_FILE_NAME => 'exm_old.txt',
DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
DESTINATION_FILE_NAME => 'exm_new.txt'
DESTINATION_DATABASE => 'US.ACME.COM');
END;

In order to transfer a file the other way around, you must replace the PUT_FILE procedure with the GET_FILE procedure.

If you are copying a database datafile, do not forget to make it READ ONLY before you start to copy.

You can monitor copying progress using V$SESSION_LONGOPS view.

Dropping Partitioned Table

In previous versions, if you drop a partitioned table, Oracle removes all the partitions at once. This led to a time and resource consuming process.

In Oracle Database 10g Release 2, when you drop a partitioned table, partitions are dropped one by one.

Because each partition is dropped individually, fewer resources are required than when the table is dropped as a whole.

Dropping Empty Datafiles

In Oracle 10g release 2, empty datafiles can be dropped ALTER TABLESPACE test DROP DATAFILE 'hr1.dbf';

You cannot drop non-empty datafiles

ORA-03262: the file is non-empty

You cannot drop first file in tablespace

ORA-03263: cannot drop the first file of tablespace HR

Renaming Temporary Files

In Oracle 10.2 temporary files can be renamed.

ALTER DATABASE TEMPFILE 'temp1.dbf' OFFLINE
$ mv temp1.dbf temp2.dbf
ALTER DATABASE RENAME FILE 'temp1.dbf' TO
'temp2.dbf'
ALTER DATABASE TEMPFILE 'temp1.dbf' ONLINE