Oracle Database - Data Files

About

Every Oracle database has one or more physical datafiles (OS File), which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

Datafiles have the following characteristics:

One or more datafiles form a logical unit of database storage called a permanent tablespace.
A datafile can be associated with only one tablespace.
Datafiles can be defined to extend automatically when they are full.

Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.

Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the background process database writer process (DBWn).

A data file is a (contiguous|ordered) collection of extents.

Articles Related

Tablespace and data file

Tablespace	Data File	Description
EXAMPLE	EXAMPLE01.DBF	Stores the Sample Schemas, if you included them.
SYSAUX	SYSAUX01.DBF	Serves as an auxiliary tablespace to the SYSTEM tablespace. Some products and options that previously used the SYSTEM tablespace now use the SYSAUX tablespace to reduce the load on the SYSTEM tablespace.
SYSTEM	SYSTEM01.DBF	Stores the data dictionary, including definitions of tables, views, and stored procedures needed by the Oracle Database. Information in this area is maintained automatically.
TEMP	TEMP01.DBF	temporary_tablespace: Stores temporary tables and indexes created during the processing of your SQL statement. If you are running a SQL statement that involves a lot of sorting, such as the constructs GROUP BY, ORDER BY, or DISTINCT, then you may need to expand this tablespace.
UNDOTBS	UNDOTBS01.DBF	Stores undo information. The undo tablespace contains one or more undo segments that maintain transaction history that is used to roll back, or undo, changes to the database. All starter databases are configured to run in automatic undo management mode.
USERS	USERS01.DBF	Stores database objects created by database users.

Data files

Creation

A data file is created with a tablespace creation: tablespace creation

The following statement creates a tablespace named stocks that has three datafiles:

CREATE TABLESPACE stocks 
   DATAFILE '/path/stock1.dat' SIZE 10M, 
            '/path/stock2.dat' SIZE 10M,
            '/path/stock3.dat' SIZE 10M;

Add

use the apps/search/search.jsp statement

The following statement alters the stocks tablespace and adds a new datafile:

ALTER TABLESPACE stocks 
   ADD DATAFILE '/path/stock4.dat' SIZE 10M REUSE;

The file specification specifies a datafile named 'stock4.dat'. If the filename does not exist, then Oracle simply ignores the REUSE keyword (Ie reuse an existing data file)

Drop

11g:

drop tablespace test1_ts including contents and datafiles;

Data Dictionary

Oracle Enterprise Manager Database Control

Log in to the Database Control > Server > In the Storage section of the Server page, click Datafiles.

Resize

The free extent must be at the end of the data file to be able to (reduce|shrink) a datafile size.

Data File can only be shrink back to their high water mark – if there is an extent way out at the end of a file, it's not possible to shrink it.

If not, the only thing that will undo this is to recreate the database (exp, imp).

maxshrink.sql.

It will work if there is no segment in the recycle bin

set verify off

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

ONLINE

apps/search/search.jsp

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

Property

Size

Size on the OS.

select BYTES from FROM DBA_DATA_FILES

Data Files Identification

To uniquely identify a data file, Oracle Database assigns each datafile two associated file numbers:

an absolute file number

Uniquely identifies a datafile in the database. This file number can be used in many SQL statements that reference datafiles in place of using the file name. The absolute file number can be found in the FILE# column of the VDATAFILE or VTEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view.

Tempfiles are a special class of datafiles that are associated only with temporary tablespaces.

and a relative file number

Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number. In a bigfile tablespace, the relative file number is always 1024 (4096 on OS/390 platform).

Support

ORA-01157 - Cannot identify/lock data file

SQL Error: ORA-01157: cannot identify/lock data file 43 - see DBWR trace file
ORA-01110: data file 43: '/tmp/shrink_me.dbf'
01157. 00000 -  "cannot identify/lock data file %s - see DBWR trace file"
*Cause:    The background process was either unable to find one of the data
           files or failed to lock it because the file was already in use.
           The database will prohibit access to this file but other files will
           be unaffected. However the first instance to open the database will
           need to access all online data files. Accompanying error from the
           operating system describes why the file could not be identified.
*Action:   Have operating system make file available to database. Then either
           open the database or do ALTER SYSTEM CHECK DATAFILES.

Check that the file is present on the file system

ORA-00376: file 43 cannot be read at this time

ORA-00376: file 43 cannot be read at this time
ORA-01110: data file 43: '/tmp/shrink_me.dbf'
00376. 00000 -  "file %s cannot be read at this time"
*Cause:    attempting to read from a file that is not readable. Most likely
           the file is offline.
*Action:   Check the state of the file. Bring it online

The file is offline or the tablespace is also offline.

Documentation / Reference

Oracle® Database Administrator's Guide 11g Release 1 (11.1) - Guidelines for Managing Datafiles