Oracle Database - Data Files

Card Puncher Data Processing

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:

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.

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).

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





Discover More
Apex Find Workspace
Oracle Apex - Workspace

A workspace is a virtual private database allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private. Each workspace...
Db File Sequential Read
Oracle Database - db file sequential read Wait Event

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read. From SQL Developer,...
Db File Scattered Read
Oracle Database - 'db file scattered read' wait event

db file scattered read is a wait event. It's a multiblock read into many discontinuous SGA buffers This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting...
Oracle Segment Extent Data Block
Oracle Database - (Fragmented) (Unused|Free) space (Reclaim|Shrink|resize)

Used and free space. A data file contains ordered extents (by block Id). One segments is made up of one of several extents (not ordered, contiguous). Over time, updates and deletes on objects within...
Oracle Segment Extent Data Block
Oracle Database - (Logical) Extent (Unit of Storage)

An extent is a group of contiguous data blocks. An extent can contain data from only one data file. An extent is a logical unit of database storage space allocation made up of a number of (contiguous|ordered)...
Oracle Segment Extent Data Block
Oracle Database - (Logical) Segment (Database Object Storage)

A segment is a logical storage structure that contains data structure (segment type) of database objects. For example: each table's data is stored in its own data segment, while each index's data...
Sql Developer Autotrace
Oracle Database - Autotrace

Autotrace is: a system variable of sql plus can also be found in SQL Developer AUTOTRACE provides you: an execution plan (such as explain plan) and some important regarding its actual execution....
Card Puncher Data Processing
Oracle Database - Buffer cache

For many types of operations, Oracle Database uses the buffer cache to store data blocks read from disk. Oracle Database bypasses the buffer cache for particular operations, such as sorting and parallel...
Card Puncher Data Processing
Oracle Database - Control Files

Every Oracle database has a control file. Every time an instance of an Oracle database is started, its control file identifies the physical structure: the datafiles, the tempfiles, and redo...
Card Puncher Data Processing
Oracle Database - Database Writer Process (DBWn)

Database writer process (DBWn) is a background process that writes buffers in the database buffer cache to data files. Modified or new data is not necessarily written to a datafile immediately. To reduce...



Share this page:
Follow us:
Task Runner