轉帖一個在10G中文件傳輸的實例。

An Example

1.  Lets take a look at where our current files reside.

SQL> SELECT tablespace_name,file_name FROM dba_data_files;
TABLESPACE_NAME  FILE_NAME
---------------  -----------------------------------------------------
USERS            C:ORACLEPRODUCT10.1.0ORADATADATENUSERS01.DBF
SYSAUX           C:ORACLEPRODUCT10.1.0ORADATADATENSYSAUX01.DBF
UNDOTBS1         C:ORACLEPRODUCT10.1.0ORADATADATENUNDOTBS01.DBF
SYSTEM           C:ORACLEPRODUCT10.1.0ORADATADATENSYSTEM01.DBF
CUSTOMER         C:ORACLEPRODUCT10.1.0ORADATADATENCUST01.DBF

2.  Create a DIRECTORY to our source directory

SQL> CREATE DIRECTORY DBFILES AS 'C:ORACLEPRODUCT10.1.0ORADATADATEN';
Directory created.

3.  Grant READ Privileges to the source directory

SQL> GRANT READ ON DIRECTORY dbfiles TO jkoopmann;
Grant succeeded.

4.  Create a DIRECTORY to our destination directory

SQL> CREATE DIRECTORY DB_BACKUP AS 'C:ORACLEPRODUCT10.1.0ORADATADATEN_BACKUP';
Directory created.

5.  Grant WRITE Privileges to the destination directory

SQL> GRANT WRITE ON DIRECTORY db_backup TO jkoopmann;
Grant succeeded.

6.  Connect as the user you wish to copy file as

connect jkoopmann/@daten
connected.

7.  If you want to copy an existing data file attached to a tablespace, you must put it into read-only mode or take it off-line. A note here to be aware of when working with existing data files that are being used within a database, you cannot copy the data files associated with the SYSTEM, TEMPORARY, or UNDO tablespaces. This is because they cannot be taken off-line or put into read-only mode since they need to be available for read-write access by processes. In addition, the new tablespace SYSAUX in Oracle10g cannot be put into read-only mode. Because of this we will work with the CUSTOMER tablespace that I have created and copy its data file.

i.	
	SQL> ALTER TABLESPACE CUSTOMER READ ONLY;
	Tablespace altered.

-OR-

ii.	
	SQL> ALTER TABLESPACE CUSTOMER OFFLINE;
	Tablespace altered.

8.  Run the COPY_FILE procedure to copy a file to the backup directory.

BEGIN
  DBMS_FILE_TRANSFER.COPY_FILE(
        source_directory_object       =>  'DBFILES',
        source_file_name              =>  'CUST01.DBF',
        destination_directory_object  =>  'DB_BACKUP',
        destination_file_name         =>  'CUST01.DBF');
END;
/
PL/SQL procedure successfully completed.

9.  Copy of data file is done, now end backup mode for tablespace.

i.	
	SQL> ALTER TABLESPACE CUSTOMER READ WRITE;
	Tablespace altered.

-OR-

ii.	
	SQL> ALTER TABLESPACE CUSTOMER ONLINE;
	Tablespace altered.

Monitoring

If you wish to monitor the process of copy the data files you can use the new V$SESSION_LONGOPS view. This view displays those operations in your database that take longer than 6 seconds to complete. Following is a SQL statement that you can use against this view. I have given two output lines that were produced from me copying the CUSTOMER.DBF data file. The first was after the job completed and the second line was when the copy was in-stream. Of importance is the SOFAR column, which shows the total units of work completed so far. As you recall, it was stated earlier that these copies happen in 512K block units. Therefore, you can multiply the SOFAR by 512K (524,288) to get the total bytes transferred at any given time. In addition, this is a handy view to check the time this operation started, the time left, and total elapsed time for the operation to complete.

SELECT opname,target,target_desc,
       sofar,totalwork,
       to_char(start_time,'MMDDYYYY:HH24:MI:SS') start_time,
       time_remaining,elapsed_seconds
FROM v$session_longops
/

 Time Elapsed
OPNAME                       TARGET_DESC  SOFAR TOTALWORK START_TIME        Left Seconds
---------------------------- ----------- ------ --------- ----------------- ---- -------
DBMS_FILE_TRANSFER.COPY_FILE CUST01.DBF  204816    204816 04282004:07:29:33    0      21
DBMS_FILE_TRANSFER.COPY_FILE CUST01.DBF  151552    204816 04282004:07:40:54    6      16

Conclusion

This new option allows us (and Oracle) to take further control of our databases in ways I do not think we have yet considered. With the need to transport tablespaces / data files to other systems, the need to detatch ourselves from the operating system, and the need for us to seamlessly start sharing information across platforms makes this seemingly small option a very powerfull utility. Start using wisely.

原文：