DataFile Operations

               Datafile Operations

Resize:

SQL> select file_name,bytes from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

     BYTES

----------

/u03/oravis/VIS/data/xxtrainee01.dbf

 209715200

/u03/oravis/VIS/data/xxtrainee02.dbf

 209715200

SQL> alter database datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' resize 250m;

Database altered.

SQL> select file_name,bytes from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

     BYTES

----------

/u03/oravis/VIS/data/xxtrainee01.dbf

 262144000

/u03/oravis/VIS/data/xxtrainee02.dbf

 209715200

We can also decrease the size but it should have free space

Autoextensible:

SQL> select file_name,bytes,autoextensible from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

     BYTES AUT

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

/u03/oravis/VIS/data/xxtrainee01.dbf

 262144000 NO

/u03/oravis/VIS/data/xxtrainee02.dbf

 209715200 NO

SQL> alter database datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' autoextend on maxsize 250m;

Database altered.

SQL> select file_name,bytes,autoextensible from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

     BYTES AUT

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

/u03/oravis/VIS/data/xxtrainee01.dbf

 262144000 YES

/u03/oravis/VIS/data/xxtrainee02.dbf

 209715200 NO

Offline/Online:

SQL> alter database datafile '/u03/oravis/VIS/data/xxtrainee02.dbf' offline;

Database altered.

SQL> select file_name,online_status from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

ONLINE_

-------

/u03/oravis/VIS/data/xxtrainee01.dbf

ONLINE

/u03/oravis/VIS/data/xxtrainee02.dbf

RECOVER

SQL> recover datafile '/u03/oravis/VIS/data/xxtrainee02.dbf';

Media recovery complete.

SQL> select file_name,online_status from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

ONLINE_

-------

/u03/oravis/VIS/data/xxtrainee01.dbf

ONLINE

/u03/oravis/VIS/data/xxtrainee02.dbf

OFFLINE

SQL> alter database datafile '/u03/oravis/VIS/data/xxtrainee02.dbf' online;

Database altered.

SQL> select file_name,online_status from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

ONLINE

/u03/oravis/VIS/data/xxtrainee01.dbf

ONLINE

/u03/oravis/VIS/data/xxtrainee02.dbf

ONLINE

Conditions to Drop a Datafile from Tablespace:

·         It should not be the first Datafile

·         It should be Empty

Renaming Datafile : 

Relocating Data file is introduced in 12c

SQL> alter database move datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' to '/u03/oravis/VIS/data/train01.dbf';

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

/u03/oravis/VIS/data/train01.dbf

/u03/oravis/VIS/data/xxtrainee02.dbf

SQL> alter database move datafile '/u03/oravis/VIS/data/train01.dbf' to '/u03/oravis/VIS/data/xxtrainee01.dbf';

Database altered.

SQL>  select file_name from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

/u03/oravis/VIS/data/xxtrainee01.dbf

/u03/oravis/VIS/data/xxtrainee02.dbf

Relocating Datafile :

 SQL> alter database move datafile '/u03/oravis/VIS/data/xxtrainee01.dbf' to '/u03/oravis/VIS/xxtrainee01.dbf';

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

/u03/oravis/VIS/data/xxtrainee02.dbf

/u03/oravis/VIS/xxtrainee01.dbf

SQL>  alter database move datafile '/u03/oravis/VIS/xxtrainee01.dbf' to '/u03/oravis/VIS/data/xxtrainee01.dbf';

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='XXTRAINEE';

FILE_NAME

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

/u03/oravis/VIS/data/xxtrainee01.dbf

/u03/oravis/VIS/data/xxtrainee02.dbf

We can also move and rename datafile at a time by giving location Path and newName