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;
To view information about Datafiles
SQL> select * from dba_data_files;
SQL> select * from v$datafile;
To view information about Tempfiles
SQL> select * from dba_temp_files;
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;
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
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
SQL> alter tablespace users rename file ‘/u01/oracle/ica/usr01.dbf’,
‘/u01/oracle/ica/usr02.dbf’ to ‘/u02/oracle/ica/usr01.dbf’, ’/u01/oracle/ica/users02.dbf’;
4. Now bring the tablespace Online
SQL> alter tablespace users online;
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.
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:
ALTER DATABASE RENAME FILE '/u02/oracle/rbdb1/sort01.dbf', '/u02/oracle/rbdb1/user3.dbf' TO '/u02/oracle/rbdb1/temp01.dbf', '/u02/oracle/rbdb1/users03.dbf;
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