3 - 4 minutes readManaging Data Files

What is data File?

Data files are physical files of the OS that store the data of all logical structures in the database. Data file must be created for each tablespace.

How to determine the number of dataf iles?

At least one datafile is required for the SYSTEM tablespace. We can create separate datafile for other teblespace. When we create DATABASE , MAXDATAFILES may be or not specify in create database statement clause. Oracle assassin db_files default value to 200. We can also specify the number of datafiles in init file.

When we start the oracle instance , the DB_FILES initialization parameter reserve for datafile information and the maximum number of datafile in SGA. We can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance.

Important:

• If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit. Example : if init parameter db_files set to 2 then you can not add more then 2 in your database.
• If the value of DB_FILES is too high, memory is unnecessarily consumed.
• When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

Note:

If you add new datafiles to a tablespace and do not fully specify the filenames, the database creates the datafiles in the default database directory . Oracle recommends you always specify a fully qualified name for a datafile. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files. Old files that have been previously dropped will be overwritten.

How to add datafile in execting tablespace?

alter tablespace <Tablespace_Name> add datafile ‘/…………../……./file01.dbf’ size 10m autoextend on;  

How to resize the datafile?

alter database datafile ‘/…………../……./file01.dbf’ resize 100M;

How to bring datafile online and offline?

alter database datafile ‘/…………../……./file01.dbf’ online;

alter database datafile ‘/…………../……./file01.dbf’ offline;

How to renaming the datafile in a single tablesapce?

Step:1 Take the tablespace that contains the datafiles offline. The database must be open.

alter tablespace <Tablespace_Name> offline normal;

Step:2 Rename the datafiles using the operating system.

Step:3 Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

alter tablespace <Tablespace_Name> rename datafile ‘/…../…./…./user.dbf’ to ‘/…./…./…/users1.dbf’;

Step 4: Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How to relocate datafile in a single tablesapce?

Step:1 Use following query to know the specifiec file name or size.

select file_name,bytes from dba_data_files where tablespace_name='<tablespace_name>’;

Step:2 Take the tablespace containing the datafiles offline:

alter tablespace <Tablespace_Name> offline normal;

Step:3 Copy the datafiles to their new locations and rename them using the operating system.

Step:4 Rename the datafiles within the database.

ALTER TABLESPACE <Tablespace_Name> RENAME DATAFILE

‘/u02/oracle/rbdb1/users01.dbf’, ‘/u02/oracle/rbdb1/users02.dbf’

TO ‘/u03/oracle/rbdb1/users01.dbf’,’/u04/oracle/rbdb1/users02.dbf’;

Step:5 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How to Renaming and Relocating Datafiles in Multiple Tablespaces?

Step:1 Ensure that the database is mounted but closed.

Step:2 Copy the datafiles to be renamed to their new locations and new names, using the operating system.

Step:3 Use ALTER DATABASE to rename the file pointers in the database control file.

ALTER DATABASE

RENAME FILE

‘/u02/oracle/rbdb1/sort01.dbf’,

‘/u02/oracle/rbdb1/user3.dbf’

TO ‘/u02/oracle/rbdb1/temp01.dbf’,

‘/u02/oracle/rbdb1/users03.dbf;

Step:4 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.