Re: Move system, temp and undotbs1?

  • From: "cichomitiko gmail" <cichomitiko@xxxxxxxxx>
  • To: "Hollis, Les" <Les.Hollis@xxxxxx>, <Surendra.Tirumala@xxxxxx>, <cemail_219@xxxxxxxxxxx>
  • Date: Fri, 1 Apr 2005 16:24:19 +0200

You're right,
just tested:

$ sqlplus '/ as sysdba'

SQL*Plus: Release - Production on Fri Apr 1 16:15:52 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';


SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1301512 bytes
Variable Size             119809016 bytes
Database Buffers          163577856 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> !cp /u01/oracle/ora10g/oradata/test/system01.dbf 

SQL> alter database rename file 
'/u01/oracle/ora10g/oradata/test/system01.dbf' to 

Database altered.

SQL> alter database open;

Database altered.


----- Original Message ----- 
From: "Hollis, Les" <Les.Hollis@xxxxxx>
To: <Surendra.Tirumala@xxxxxx>; <cichomitiko@xxxxxxxxx>; 
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, April 01, 2005 4:16 PM
Subject: RE: Move system, temp and undotbs1?

Surendra is correct.

The datafiles are NOT opened in mount state.  The Controlfile gets read
in MOUNT state.  Oracle doesn't even check the existence of the
datafiles until it tries to OPEN.

With the DB in MOUNT state, use the "alter databae rename datafile
'oldname' to 'mewname'  >This AFTER you (as wad advised) cp the file to
the new location.

You do NOT have to recreate the controlfile.

To change maxdatafiles, maxlogmembers, etc you would need to

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Sent: Friday, April 01, 2005 8:07 AM
To: cichomitiko@xxxxxxxxx; cemail_219@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Move system, temp and undotbs1?

You can move the system and undo tablespaces too.... in mount stage...

Surendra Tirumala
Database Administrator
Division of Technology Services
Education Cabinet
Commonwealth of Kentucky
Ph: (502) 564-6949x380

-----Original Message-----
From: cichomitiko gmail [mailto:cichomitiko@xxxxxxxxx]
Sent: Friday, April 01, 2005 8:44 AM
To: cemail_219@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Move system, temp and undotbs1?

You cannot move the system tablespace, you have to re-create the =
files to change the system data file's path.


To move the temp tablespace:

create tablespace temp1 datafile 'new_mount_point_data_file_name' size =

then ...

alter user <username> temporary tablespace temp1; --> for the users =
have temporary tablespace temp!

then ...

drop tablespace temp including contents and datafiles;


To move the undo tablespace:

create undo tablespace undotbs2 datafile =
size <n>;

alter system set undo_tablespace=3Dundotbs2;

drop tablespace undotbs1 including contents and datafiles;



----- Original Message -----=20
From: "J. Dex" <cemail_219@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, April 01, 2005 5:23 AM
Subject: Move system, temp and undotbs1?

> How do you move the system, temp and undotbs1 from one mount point to
> another?  I was able to move other tablespaces, but I know you can't =
> system, etc. offline?   This is a 10g database.
> Thanks.
> _________________________________________________________________
> Don=92t just search. Find. Check out the new MSN Search!
> --



Other related posts: