From oracle-l-bounce@freelists.org Fri Apr 1 09:28:33 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j31FSXsr009070 for ; Fri, 1 Apr 2005 09:28:33 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j31FSMem008988 for ; Fri, 1 Apr 2005 09:28:32 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AFCEC8ACC0; Fri, 1 Apr 2005 09:26:09 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 25371-07; Fri, 1 Apr 2005 09:26:09 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2800E8AD47; Fri, 1 Apr 2005 09:26:09 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:from:to:cc:references:subject:date:mime-version:content-type:content-transfer-encoding:x-priority:x-msmail-priority:x-mailer:x-mimeole; b=SN2GYwpQA51sMdfXzgl9PLYGGnauduxGAYNUa4lzs5NTCh7YYxUzD9FPZRcqX16SQ3kViLMnK2J7RyA/G5H2yOPJ1e7GmzwgqCkxlAu86p5HH0c4xdqjavbDXxidR7rCcgwdNxXd8ayCP1bZtZ7bt2Nz7pc1sYmnY8vWewYQBYE= Message-ID: <03af01c536c6$7f051070$1a03310a@IBME1D11967173> From: "cichomitiko gmail" To: "Hollis, Les" , , Cc: References: Subject: Re: Move system, temp and undotbs1? Date: Fri, 1 Apr 2005 16:24:19 +0200 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-archive-position: 17969 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: cichomitiko@gmail.com Precedence: normal Reply-To: cichomitiko@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=ham version=2.60 X-Spam-Level: You're right, just tested: $ sqlplus '/ as sysdba' SQL*Plus: Release 10.1.0.2.0 - 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 10.1.0.2.0 - 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'; FILE_NAME -------------------------------------------------------------------------------- /u01/oracle/ora10g/oradata/test/system01.dbf 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 /u01/oracle/ora10g/oradata/test/system001.dbf SQL> alter database rename file '/u01/oracle/ora10g/oradata/test/system01.dbf' to '/u01/oracle/ora10g/oradata/test/system001.dbf'; Database altered. SQL> alter database open; Database altered. Cheers Dimitre ETNØTEAM ----- Original Message ----- From: "Hollis, Les" To: ; ; Cc: 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 recreate..... -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Surendra.Tirumala@ky.gov Sent: Friday, April 01, 2005 8:07 AM To: cichomitiko@gmail.com; cemail_219@hotmail.com Cc: oracle-l@freelists.org 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 Surendra.Tirumala@ky.gov -----Original Message----- From: cichomitiko gmail [mailto:cichomitiko@gmail.com] Sent: Friday, April 01, 2005 8:44 AM To: cemail_219@hotmail.com; oracle-l@freelists.org Subject: Re: Move system, temp and undotbs1? You cannot move the system tablespace, you have to re-create the = control=20 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 temporary tablespace temp1; --> for the users = that=20 have temporary tablespace temp! then ... drop tablespace temp including contents and datafiles; ************************************************************************ = **** *************** To move the undo tablespace: create undo tablespace undotbs2 datafile = 'new_mount_point_data_file_name'=20 size ; alter system set undo_tablespace=3Dundotbs2; drop tablespace undotbs1 including contents and datafiles; ************************************************************************ = **** **************** Cheers Dimitre ----- Original Message -----=20 From: "J. Dex" To: 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 = take > system, etc. offline? This is a 10g database. > > Thanks. > > _________________________________________________________________ > Don=92t just search. Find. Check out the new MSN Search! > http://search.msn.click-url.com/go/onm00200636ave/direct/01/ > > -- > http://www.freelists.org/webpage/oracle-l=20 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l