oracle sql and dba tutorial logo


Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step

Oracle SQL

Tutorial for Oracle SQL

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions

Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.

MySQL Tutorial

Learn MySQL step by step










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;

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;

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


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;

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

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:

    RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
             TO '/u02/oracle/rbdb1/temp01.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




HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats

Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved