To rename or relocate datafiles belonging to a Single Tablespace do the following. Take the tablespace offline. Rename or Relocate the datafiles using operating system command. Give the ALTER TABLESPACE with RENAME DATAFILE option to change the filenames within the Database. Bring the tablespace Online.
- First take the tablespace offline ALTER TABLESPACE TBS1 OFFLINE; If someone is using the tablespace, then you can't take it offline! You must kill all sessions using it. The time needed to take it offline, depends on the size of tablespace, a 10Mbytes tablespace may take 1 sec, a 1TByte tablespace may take some minutes so be patient.
- ALTER TABLESPACE oldtbs RENAME to newtbs; Example 2: Transport a tablespace between two databases. 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.
- Finally, bring the data files within the tablespace back online: SQL alter tablespace users online; If you want to rename data files from multiple tablespaces in one operation, you can use the ALTER DATABASE RENAME FILE statement (instead of the ALTER TABLESPACE.RENAME DATAFILE statement). The following example renames several data files in.
- Alter tablespace add rename datafile alter tablespace read write read only drop from csc 454 at depaul university.
View information and rename tablespaces and datafiles
Viewing Information about Tablespaces and Datafiles
Oracle has provided many Data dictionaries to view information about tablespaces and datafiles. Some of them are:
To view information about Tablespaces in a database give the following query
SQL> select * from dba_tablespaces
SQL> select * from v$tablespace;
SQL> select * from v$tablespace;
To view information about Datafiles
SQL> select * from dba_data_files;
SQL> select * from v$datafile;
SQL> select * from v$datafile;
To view information about Tempfiles
SQL> select * from dba_temp_files;
SQL> select * from v$tempfile;
SQL> select * from v$tempfile;
To view information about free space in datafiles
SQL> select * from dba_free_space;
To view information about free space in tempfiles
SQL> select * from V$TEMP_SPACE_HEADER;
Renaming or Relocating Datafiles belonging to a Single Tablespace
You can rename datafiles to either change their names or relocate them.
To rename or relocate datafiles belonging to a Single Tablespace do the following.
1. Take the tablespace offline
2. Rename or Relocate the datafiles using operating system command
3. Give the ALTER TABLESPACE with RENAME DATAFILE option to change the filenames within the Database.
4. Bring the tablespace Online
For Example suppose you have a tablespace users with the following datafiles
/u01/oracle/ica/usr01.dbf’
/u01/oracle/ica/usr02.dbf’
/u01/oracle/ica/usr02.dbf’
Now you want to relocate /u01/oracle/ica/usr01.dbf’ to ‘/u02/oracle/ica/usr01.dbf’ and want to rename ‘/u01/oracle/ica/usr02.dbf’ to ‘/u01/oracle/ica/users02.dbf’ then follow the given the steps
1. Bring the tablespace offline
SQL> alter tablespace users offline;
2. Copy the file to new location using o/s command.
$ cp /u01/oracle/ica/usr01.dbf /u02/oracle/ica/usr01.dbf’
Rename the file ‘/u01/oracle/ica/usr02.dbf’ to ‘/u01/oracle/ica/users02.dbf’ using o/s command.
$ mv /u01/oracle/ica/usr02.dbf /u01/oracle/ica/users02.dbf
3. Now start SQLPLUS and type the following command to rename and relocate these files
4. Now bring the tablespace Online
SQL> alter tablespace users online;
Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces
Alter Tablespace Rename Datafile Mysql
You can rename and relocate datafiles in one or more tablespaces using the ALTER DATABASE RENAME FILE statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege
To rename datafiles in multiple tablespaces, follow these steps.
1. Ensure that the database is mounted but closed.
Alter Tablespace Rename Datafile Access
2. Copy the datafiles to be renamed to their new locations and new names, using the operating system..
3. Use ALTER DATABASE to rename the file pointers in the database control file.
For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:
Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.
4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.
5. Start the Database