Resize/Add/Move/Rename/Drop datafile and temporary datafile in Oracle

Resize a datafile and tempfile:

SQL> alter database datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf' resize 50M;

SQL> alter database tempfile '/u09/oradata/TEST/test.tmp_tbspace.data.01.dbf' resize 50M;

Add a datafile:

SQL> alter tablespace tst_tbspace1 add datafile '/u10/oradata/TEST/test.tst_tbspace.data.02.dbf' size 2000M;

SQL> alter tablespace tmp_tbspace add tempfile '/u09/oradata/TEST/test.tmp_tbspace.data.02.dbf' size 2000M;


Steps to move and/or rename datafile in oracle:

1. Shutdown the database and exit out of the SQL prompt - Database level step

Ex: SQL> shutdown

2. At the unix prompt copy or move the datafile from current location to new location using the OS copy (cp) command - OS level step

Ex: $ mv /u10/oradata/TEST/test.tbspace.data.01.dbf /u01/oradata/TEST/test.tst_tbspace.data.01.dbf

you change the location and also rename the file at a time if you need to.

Ex: $ mv /u10/oradata/TEST/test.tst_tbspace.data.01.dbf /u01/oradata/TEST/test.tst_tbspace.datafile.02.dbf

3. Open a SQL session and startup mount the database - Database level step

Ex: SQL> startup mount

4. Rename the datafile using oracle rename command - Database level step

Ex: SQL> alter database rename file '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf'
                 to '/u01/oradata/TEST/test.tbspace.data.01.dbf';

5. Open the database - Database level step

Ex: SQL> alter database open;

                       (or)

      SQL> shutdown

      SQL> startup

Drop datafile:

SQL>  alter database datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf' offline drop;

(or)

SQL> alter tablespace tst_tbspace drop datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf';