To properly move the datafile around or rename the datafile, follow this guide: Login to SQLPlus. SQL> alter tablespace USERS rename to PSAPUSER; Renaming datafiles of a single offline tablespace: While the database is open, put the tablespace offline, rename the datafile at the operating system level, rename the datafile at the database level and finally take the tablespace online again. Use the ALTER DATABASE command to update the control file with the new file location(s) as follows. ALTER TABLESPACE OFFLINE; 9.
Completed: create tablespace ReadOnly_TBS datafile '+DATA' size 100M Sun Jan 26 09:30:34 2014 alter tablespace ReadOnly_TBS read only Converting block 0 to version 10 format Completed: alter tablespace ReadOnly_TBS read only Sun Jan 26 09:35:51 2014 alter tablespace readonly_tbs rename to … The following statement drops the empty data file: ALTER TABLESPACE tbs_03 DROP DATAFILE 'tbs_f04.dbf'; It can be used to add a new data file to, or to drop a data file … take the tablespace offline, rename file, alter database to update the controlfile, bring tablespace back on line. In many situations, a datafile or logfile must be renamed inside Oracle. This is traditional approach to move/rename SYSTEM, SYSAUX or users tablespace. ALTER [UNDO] TABLESPACE tablespace_name NDB only: {ADD | DROP} DATAFILE 'file_name' [INITIAL_SIZE [=] size] [WAIT] InnoDB and NDB: [RENAME TO tablespace_name] InnoDB only: [SET {ACTIVE | INACTIVE}] [ENCRYPTION [=] {'Y' | 'N'}] InnoDB and NDB: [ENGINE [=] engine_name]. ; Shutdown the database instance with SHUTDOWN command. How to rename or move a datafile in oracle 12015 views Less than a minute 3 If you are in 12c version ,then renaming a datafile can be done online, without making the datafile offline. How to Rename or Move Datafiles and Logfiles. ALTER TABLESPACE old_tbs RENAME to new_tbs; Example 2: Transport a tablespace between two databases. Connect as SYS DBA with CONNECT / AS SYSDBA command. Step I: Take the tablespace containing the datafile offline. Another great new feature in tablespace management is rename tablespace. This statement is used with NDB and InnoDB tablespaces. When you rename a in the primary database, the change won’t be reflected on standby database, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO. In the following example (see figure 3.2), you cannot transport a tablespace TBS01 from database A to database B in the previous release of Oracle server because database B also has a tablespace called TBS01.
Whereas the contents of the file remain valid, you need to define a new physical name or location for it. One step only is required for any of these actions and the database remains entirely available in read and write for users, without any data loss. 1) alter TABLESPACE_CONTAINING_DATAFILE offline; 2) rename the file 3) ALTER DATABASE to rename the file 4) alter TABLESPACE_CONTAINING_DATAFILE online; done.
no recovery or anything like that. You must then rename the data files in the control file to point to the new location by using the SQL statement ALTER DATABASE... RENAME. Oracle Training from Don Burleson The best on site " Oracle training classes" are just a phone call away!You can get personalized Oracle training by Donald Burleson, right at your shop! Assume the following conditions: Consider scenario where I want to move SYSTEM and SYSAUX tablespace to new location. Procedure for Relocating Datafiles in a Single Tablespace.
Here is a sample procedure for relocating a datafile.