MOVING/RENAMING DATAFILES WHILE DATABASE IS ONLINE

APPLY ONLY NON-SYSTEM TABLESPACE OR TEMP TABLESPACE

SQL> alter tablespace test offline;

Tablespace altered.

SQL>

first u copy datafile to another location 

SQL> ! cp /u02/app/oracle/oradata/test/test.dbf /u02/app/oracle/oradata/orcl/test.dbf

SQL>alter database rename file '/u02/app/oracle/oradata/test/test.dbf' to '/u02/app/oracle/oradata/orcl/test.dbf';


SQL> alter tablespace test online;

Tablespace altered.

if working properly then  delete old file from  
/u02/app/oracle/oradata/test/test.dbf location........

RESIZE TEST TABLESPACE DATAFILES

SQL> alter database datafile '/test/test01.dbf' autoextend off;

SQL> column file_name format a20

SQL> column tablespace_name format a10

SQL> select

  2              tablespace_name,

  3              file_id,

  4              file_name,

  5              dfsizeMB,

  6              hwmMB,

  7              dffreeMB,

  8              trunc((dffreeMB/dfsizeMB)*100,2) "% Free",

  9              trunc(dfsizeMB-hwmMB,2) "Resizeble"

 10     from

   (

 11   12        select

 13             df.tablespace_name tablespace_name,

 14             df.file_id file_id,

 15             df.file_name file_name,

 16             df.bytes/1024/1024 dfsizeMB,

 17             trunc((ex.hwm*(ts.block_size))/1024/1024,2) hwmMB,

 18             dffreeMB

 19        from

 20             dba_data_files df,

 21             dba_tablespaces ts,

 22        (

 23             select file_id, sum(bytes/1024/1024) dffreeMB

 24             from dba_free_space

 25             group by file_id

 26        ) free,

 27        (

 28             select file_id, max(block_id+blocks) hwm

 29             from dba_extents

           group by file_id

 30   31        ) ex

 32        where df.file_id = ex.file_id

 33        and df.tablespace_name = ts.tablespace_name

 34        and df.file_id = free.file_id (+)

 35        order by df.tablespace_name, df.file_id

 36      )

 37  /

TABLESPACE    FILE_ID FILE_NAME              DFSIZEMB      HWMMB   DFFREEMB     % Free  Resizeble

---------- ---------- -------------------- ---------- ---------- ---------- ---------- ----------

SYSAUX              3 /opt/oracle/oradata/        240      237.5        2.5       1.04        2.5

                      NMS/sysaux01.dbf

SYSTEM              1 /opt/oracle/oradata/        490     481.07        9.5       1.93       8.93

                      NMS/system01.dbf

TEST                6 /test/test01.dbf         3.9375        .69     3.3125      84.12       3.24

UNDOTBS1            2 /opt/oracle/oradata/         30      26.07    18.6875      62.29       3.93

                      NMS/undotbs01.dbf

USERS               4 /opt/oracle/oradata/          5        .44     4.5625      91.25       4.56

                      test/users01.dbf

SQL> alter database datafile '/test/test01.dbf' resize 3m;

Database altered.

SQL>

SQL> alter database datafile '/test/test01.dbf' autoextend on;

Database altered.

SQL>

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/opt/oracle/db02/nms_index.dbf'

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/opt/oracle/db02/nms_index.dbf'


SQL>
SQL>
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.
SQL> alter database datafile '/opt/oracle/db02/nms_index.dbf' offline drop;

Database altered.

SQL>
SQL>
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>