Re: how to move datafile

From: j_beverly <j_beverly_at_yahoo.com>
Date: Mon, 04 Dec 2000 04:25:10 GMT
Message-ID: <3a2b1400.24007960_at_news.erols.com>

On Mon, 4 Dec 2000 00:20:47 +0800, "ghard" <monas_at_tm.net.my> wrote:

>please help me...
>how to move datafile to other hardisk or partation..
>
>
>

Basically, here are the steps:
** BACK UP YOUR DATABASE **
1. Take the datafile offline.
2. Use operating system commands to move and/or rename the datafile.

     (The physical file name)
3. Use ALTER TABLESPACE command to rename the datafile. 4. Bring the datafile back online.

Here are the detailed instructions from the ORACLE documentation:

---

Relocating and Renaming Datafiles in a Single Tablespace

Here is an example that illustrates the steps involved for relocating a datafile.

Assume the following conditions:

An open database has a tablespace named USERS that is made up of datafiles located on the same disk of a computer.

The datafiles of the USERS tablespace are to be relocated to a different disk drives.

You are currently connected with administrator privileges to the open database.

These are the steps:

Identify the datafile names of interest.

The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the USERS tablespace:

SELECT file_name, bytes FROM sys.dba_data_files WHERE tablespace_name = 'USERS';

FILE_NAME                                  BYTES
------------------------------------------ ----------------
/U02/ORACLE/RBDB1/USERS01.DBF              102400000
/U02/ORACLE/RBDB1/USERS02.DBF              102400000

   

Back up the database.

Before making any structural changes to a database, such as renaming and relocating the datafiles of one or more tablespaces, always completely back up the database.

Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.

Copy the datafiles to their new locations and rename them using operating system commands.

---

Note:
You can execute an operating system command to copy a file by using the HOST command.

---

Rename the datafiles within Oracle.

The datafile pointers for the files that make up the USERS tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

If the tablespace is offline but the database is open, use the ALTER TABLESPACE...RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE...RENAME FILE statement.

ALTER TABLESPACE users

    RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
                    '/u02/oracle/rbdb1/users02.dbf'
                 TO '/u03/oracle/rbdb1/users01.dbf', 
                    '/u04/oracle/rbdb1/users02.dbf';

Bring the tablespace online, or shut down and restart the database.

If the USERS tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. Received on Mon Dec 04 2000 - 05:25:10 CET