Managing Datafiles in Oracle Database: In this article we will discuss about Managing Datafiles in Oracle Database.
Guidelines for Managing Datafiles:
Datafiles are physical files of the operating system that store the data of all logical structures in the database. They must be explicitly created for each tablespace.
Oracle Database assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it.
Creating Datafiles and Adding Datafiles to a Tablespace
You can create datafiles and associate them with a tablespace using any of the statements listed in the following table. In all cases, you can either specify the file specifications for the datafiles being created, or you can use the Oracle-managed files feature to create files that are created and managed by the database server.
Check datafiles details:
set lines 200 pages 100
col FILE_NAME for a60
select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_data_files;Output:
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ --------- --- ---------------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM AVAILABLE YES 830
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX AVAILABLE YES 590
/u01/app/oracle/oradata/ORCL/users01.dbf USERS AVAILABLE YES 6.25
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 AVAILABLE YES 105
Adding Datafiles:
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 or the current directory, depending upon your operating system. 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.
SQL>
SQL> alter tablespace users add datafile 'orakldba.dbf' size 100m;
Tablespace altered.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ --------- --- ---------------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM AVAILABLE YES 830
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX AVAILABLE YES 590
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 AVAILABLE YES 105
/u01/app/oracle/product/12.2.0.1/db_1/dbs/orakldba.dbf USERS AVAILABLE NO 100
/u01/app/oracle/oradata/ORCL/users01.dbf USERS AVAILABLE YES 6.25
SQL>
SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/ORCL/orakldba.dbf' size 100m;
Tablespace altered.
SQL>
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ --------- --- ---------------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM AVAILABLE YES 830
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX AVAILABLE YES 590
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 AVAILABLE YES 105
/u01/app/oracle/product/12.2.0.1/db_1/dbs/orakldba.dbf USERS AVAILABLE NO 100
/u01/app/oracle/oradata/ORCL/users01.dbf USERS AVAILABLE YES 6.25
/u01/app/oracle/oradata/ORCL/orakldba.dbf USERS AVAILABLE NO 100
6 rows selected.
Creating Tempfiles and Adding Tempfiles to a Tablespace:
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_temp_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ ------- --- ---------------
/u01/app/oracle/oradata/ORCL/temp01.dbf TEMP ONLINE YES 219
SQL>
SQL> alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/ORCL/temp02.dbf' size 100m AUTOEXTEND ON;
Tablespace altered.
SQL>
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_temp_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ ------- --- ---------------
/u01/app/oracle/oradata/ORCL/temp01.dbf TEMP ONLINE YES 219
/u01/app/oracle/oradata/ORCL/temp02.dbf TEMP ONLINE YES 100
Changing Datafile Size:
You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile, using the ALTER DATABASE statement. For a bigfile tablespace, you are able to perform these operations using the ALTER TABLESPACE statement.
The following example enables automatic extension for a datafile added to the users tablespace:
SQL>
SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/ORCL/orakldba.dbf' size 100m AUTOEXTEND ON;
Tablespace altered.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ --------- --- ---------------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM AVAILABLE YES 830
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX AVAILABLE YES 600
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 AVAILABLE YES 105
/u01/app/oracle/product/12.2.0.1/db_1/dbs/orakldba.dbf USERS AVAILABLE NO 100
/u01/app/oracle/oradata/ORCL/users01.dbf USERS AVAILABLE YES 6.25
/u01/app/oracle/oradata/ORCL/orakldba.dbf USERS AVAILABLE YES 100
6 rows selected.
The next example disables the automatic extension for the datafile.
SQL>
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/orakldba.dbf' AUTOEXTEND OFF;
Database altered.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ --------- --- ---------------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM AVAILABLE YES 830
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX AVAILABLE YES 600
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 AVAILABLE YES 105
/u01/app/oracle/product/12.2.0.1/db_1/dbs/orakldba.dbf USERS AVAILABLE NO 100
/u01/app/oracle/oradata/ORCL/users01.dbf USERS AVAILABLE YES 6.25
/u01/app/oracle/oradata/ORCL/orakldba.dbf USERS AVAILABLE NO 100
6 rows selected.
The following statement decreases the size of datafile /u01/app/oracle/oradata/ORCL/orakldba.dbf:
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/orakldba.dbf' RESIZE 200M;
Database altered.
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ --------- --- ---------------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM AVAILABLE YES 830
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX AVAILABLE YES 600
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 AVAILABLE YES 105
/u01/app/oracle/product/12.2.0.1/db_1/dbs/orakldba.dbf USERS AVAILABLE NO 100
/u01/app/oracle/oradata/ORCL/users01.dbf USERS AVAILABLE YES 6.25
/u01/app/oracle/oradata/ORCL/orakldba.dbf USERS AVAILABLE NO 200
6 rows selected.
Dropping Datafiles:
You use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty.
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ --------- --- ---------------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM AVAILABLE YES 830
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX AVAILABLE YES 600
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 AVAILABLE YES 105
/u01/app/oracle/product/12.2.0.1/db_1/dbs/orakldba.dbf USERS AVAILABLE NO 100
/u01/app/oracle/oradata/ORCL/users01.dbf USERS AVAILABLE YES 6.25
/u01/app/oracle/oradata/ORCL/orakldba.dbf USERS AVAILABLE NO 200
6 rows selected.
SQL>
SQL> alter tablespace USERS drop datafile '/u01/app/oracle/product/12.2.0.1/db_1/dbs/orakldba.dbf';
Tablespace altered.
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ --------- --- ---------------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM AVAILABLE YES 830
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX AVAILABLE YES 600
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 AVAILABLE YES 105
/u01/app/oracle/oradata/ORCL/users01.dbf USERS AVAILABLE YES 6.25
/u01/app/oracle/oradata/ORCL/orakldba.dbf USERS AVAILABLE NO 200
Dropping Tempfiles:
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_temp_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ ------- --- ---------------
/u01/app/oracle/oradata/ORCL/temp01.dbf TEMP ONLINE YES 219
/u01/app/oracle/oradata/ORCL/temp02.dbf TEMP ONLINE YES 100
The next example drops the tempfile temp02.dbf, which belongs to the TEMP tablespace.
SQL>
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORCL/temp02.dbf' DROP INCLUDING DATAFILES;
Database altered.
SQL>
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS, AUTOEXTENSIBLE, BYTES/1024/1024 from dba_temp_files;
FILE_NAME TABLESPACE_NAME STATUS AUT BYTES/1024/1024
------------------------------------------------------------ ------------------------------ ------- --- ---------------
/u01/app/oracle/oradata/ORCL/temp01.dbf TEMP ONLINE YES 219
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.