Re: ALTER TABLESPACE SYSTEM RENAME DATAFILE ... ??

Luis Moreno Campos wrote:

> I have way too much space in my 2 tablespace system datafile SYS1SID.ORA and
> SYS2SID.ORA, and I want to move them from drive C to drive D - I've done it
> with other tablespaces. Mine is a Win95 PC running both Developer and
> Designer on a Personal Oracle database 7.3.3 (I also have Discoverer admin
> edition).
> I've read all Oracle documentation on ALTER TABLESPACE and ALTER DATABASE
> and it seems to me that those say it is impossible to move the system
> tablespace datafiles.
>
> But my humble years of experience taught me one thing: There are very few
> things impossible on Oracle, or have I found one?
>
> Thanks a lot. Please email me at lcampos_at_fuck.junk.mail
> substitute the domain name (fuck.junk.mail) by (cpcis.pt) and you'll get my
> real email address.
>
> Thanks in advance.
>
> Luis Campos.

Absolutely do-able. I found this in the Server Admin Manual (v7).

--

Renaming and Relocating Datafiles for Multiple Tablespaces

You can rename and relocate datafiles of one or more tablespaces with the SQL
command ALTER DATABASE with the RENAME FILE option. This option is the only
choice
if you want to rename or relocate datafiles of several tablespaces in one
operation, or rename or relocate datafiles of the SYSTEM tablespace.

To rename datafiles of several tablespaces in one operation or to rename
datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system
privilege.

To Rename and Relocate Datafiles for Multiple Tablespaces

     1. Ensure that the database is mounted but closed.

     2. Copy the datafiles to be renamed to their new locations and new names,
using the operating system.

     3. Make sure the new copies of the datafiles have different fully specified
filenames from the datafiles currently in use.

     4. Use the SQL command ALTER DATABASE to rename the file pointers in the
database's control file.

For example, the following statement renames the datafiles FILENAME 1 and
FILENAME2 to FILENAME3 and FILENAME4, respectively:

        ALTER DATABASE

           RENAME FILE 'filename1', 'filename2'

           TO 'filename3', 'filename4';

The new file must already exist; this command does not create a file. Also,
always provide complete filenames (including their paths) to properly identify
the old and
new datafiles. In particular, specify the old filename exactly as it appears in
the DBA_DATA_FILE view of the data dictionary.

Relocating Datafiles: Example

For this example, assume the following conditions:

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

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

     You are currently connected with administrator privileges to the open
database while using Server Manager.

To Relocate Datafiles

     1. 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

---------------------------

FILENAME1         102400000

FILENAME2         102400000

     Here, FILENAME1 and FILENAME2 are two fully specified filenames, each 1MB
in size.

     2. 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.

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

     4. Copy the datafiles to their new locations using operating system
commands. For this example, the existing files FILENAME1 and FILENAME2 are
copied to
     FILENAME3 and FILENAME4.

Suggestion: You can execute an operating system command to copy a file without
exiting Server Manager/LineMode by using the HOST command.

     5. Rename the datafiles within Oracle.

     The datafile pointers for the files that comprise the USERS tablespace,
recorded in the control file of the associated database, must now be changed
from
     FILENAME1 and FILENAME2 to FILENAME3 and FILENAME4, respectively.

     If the tablespace is offline but the database is open, use the Server
Manager Rename Datafiles dialog box or ALTER TABLESPACE...RENAME DATAFILE
command.
     If the database is mounted but closed, use the ALTER DATABASE...RENAME FILE
command.

     6. 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.

     7. Back up the database. After making any structural changes to a database,
always perform an immediate and complete backup.


--

hope this helps !
Srini

(to reply, change "nospam" to "chavali" in my email address.  :-)  )