Renaming or Moving Oracle Data Files

>>Query datafiles which want to move.

SELECT name FROM v$datafile WHERE name LIKE '%credit%';

>>Take the tablespace that contains the datafiles offline. The database must be open.

ALTER TABLESPACE credittestdata OFFLINE NORMAL;

>> Host move datafiles

host mv /areadb2/credittestdata08.dbf /area_db/credittestdata08.dbf

>> Renaming Datafiles

ALTER TABLESPACE credittestdata RENAME DATAFILE '/areadb2/credittestdata08.dbf' to '/area_db/credittestdata08.dbf';

>>Take the tablespace that contains the datafiles Online.

ALTER TABLESPACE credittestdata ONLINE;

Resizing OR Extending an ASM Datafile in RAC

The following example shows how to Resize OR Extend a datafile to the NBLT24DATA tablespace in the +DATA ASM group.

1. Identify the datafiles for the tablespace you want to increase.

SQL> select file_name,bytes/1024/1024/1024 GB from dba_data_files where tablespace_name = 'NBLT24DATA' order by file_name;

2. Resizing OR extending the datafile on ASM.

SQL> alter database datafile '+DATA/dcpdb/datafile/nblt24data.278.914343685' resize 32g;

Tablespace altered.

Adding datafile to tablespace on ASM in RAC Environment

The following example shows how to add a datafile to the NBLT24DATA tablespace in the +DATA ASM group.

1. Find out where the current datafiles are defined. In this example the ASM group is +DATA.

SQL> select file_name,bytes/1024/1024/1024 GB from dba_data_files where tablespace_name = 'NBLT24DATA' order by file_name;

2. Add the datafile on ASM.

SQL> alter tablespace NBLT24DATA add datafile '+DATA' size 30G;

Tablespace altered.