Table 6.138:
Put_file Parameters
The DBMS_FILE_TRANSFER Package
Another approach to migrate and copy
files from the file system to the
ASM is to use the Oracle supplied
PL/SQL package DBMS_FILE_TRANSFER.
The example below illustrates the
use of the package to migrate a
temporary tablespace from the file
system to the Automatic Storage
Management (ASM) using the following
steps:
§
Create and provide access to the
source and target directories - SRCDIR
and
TGTDIR. The source data file is
located on the directory
'/home/oracle.'
The
target directory is the +DATA disk
group.
SQL> CREATE
DIRECTORY srcdir as
'/home/oracle';
Directory
created.
SQL> GRANT
all ON DIRECTORY srcdir to
system;
Grant
succeeded.
SQL> CREATE
DIRECTORY tgtdir as '+DATA';
Directory
created.
SQL> GRANT
all ON DIRECTORY tgtdir to
system;
Grant
succeeded.
SQL>
§
Execute DBMS_FILE_TRANSFER.COPY_FILE
procedure
SQL> BEGIN
2
DBMS_FILE_TRANSFER.COPY_FILE(
3
'SRCDIR', 'temp_1.dbf', 'TGTDIR',
'temp_1.f');
4
END;
5
/
PL/SQL
procedure successfully
completed.
SQL>
§
Update the database data dictionary
with the new file location. The
tablespace must be either offline or
the database is on MOUNT mode.
Obtain the name
of
the copied file by querying
V$ASM_FILES.
SQL> SHUTDOWN
IMMEDIATE
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL> STARTUP
MOUNT
ORACLE
instance started.
Total System
Global Area
551165952 bytes
Fixed Size
2230232 bytes
Variable Size
352323624 bytes
Database
Buffers
192937984 bytes
Redo Buffers
3674112 bytes
Database
mounted.
SQL> ALTER
DATABASE RENAME FILE
'/home/oracle/temp_1.dbf'
2
TO '+DATA/temp_1.f';
Database
altered.
SQL> ALTER
DATABASE OPEN;
Database
altered.
SQL>
§
Verify the new location of the
migrated temporary file.
SQL> SELECT
tablespace_name, file_name
2
FROM
dba_temp_files;
TABLESPACE_NAME
FILE_NAME
--------------------
--------------------------------------------------
TEMP_2
+DATA/t1c1/tempfile/copy_file.267.846281147
TEMP_1
+DATA/temp_1.f
TEMP
+DATA/t1c1/tempfile/temp.263.846267555
SQL>