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
- Oracle Database - Buffer cache
- Oracle Database - TableSpace
- Oracle Database - (Logical) Segment (Database Object Storage)
- Oracle Database - Physical Database Structures (Files)
- Oracle Database - Temporary Datafiles (tempfiles)
- Oracle Database - Temporary Tablespace
- Oracle Database - Physical I/O
- Oracle Database - (Logical) Extent (Unit of Storage)
- Oracle Apex - Workspace
- Oracle Database - Control Files
- More ...
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
See also: Oracle database - File (Common Specification for data file, control file, )
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.
See also Oracle Database - (Fragmented) (Unused|Free) space (Reclaim|Shrink|resize).
If not, the only thing that will undo this is to recreate the database (exp, imp).
It will work if there is no segment in the recycle bin
set verify offcolumn value new_val blksizeselect value from v$parameter where name = 'db_block_size'/column file_name format a50 word_wrappedcolumn 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 reportcompute sum of savings on reportselect 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 ) savingsfrom dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) bwhere a.file_id = b.file_id(+)/column cmd format a75 word_wrappedselect 'alter database datafile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmdfrom dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) bwhere a.file_id = b.file_id(+) and ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0/ONLINE
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;Property
Size
Size on the OS.
select BYTES from FROM DBA_DATA_FILESData 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 fileORA-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 timeORA-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 onlineThe file is offline or the tablespace is also offline.
