ADD Datafile to Tablespace:
ALTER TABLESPACE DATA02
ADD DATAFILE '/oradata1/THRP/DATA02_02.dbf'
SIZE 100M;
ALTER TABLESPACE STATE_DATA01
add datafile '/data/state_datafiles/STATE_DATA01_01.dbf'
size 1G AUTOEXTEND ON NEXT 100M;
ALTER Datafile:
ALTER database datafile '/oradata1/THRP/DATA02_02.dbf' AUTOEXTEND ON MAXSIZE 20G;
alter database datafile '/oradata1/THRP/DATA02_02.dbf' AUTOEXTEND ON NEXT 50M;
The datafiles 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. Every Oracle database has one or more physical datafiles.
The datafile is divided into smaller units called data blocks. The data of logical database structures, such as tables and indexes, is physically located in the blocks of the datafiles allocated for a database.
Datafiles hold the following characteristics:
- One or more physical datafiles form a logical database storage unit called a tablespace.
- Datafiles can have certain characteristics set to let them automatically extend when the database runs out of space.
The first block of every datafile is the header. The header includes important information such as file size, block size, tablespace, and creation timestamp. Whenever the database is opened, Oracle checks to see that the datafile header information matches the information stored in the control file. If it does not, then recovery is necessary. Oracle reads the data in a datafile during normal operation and stores it in the buffer cache.
For example, assume that a user wants to access some data in a table. If the requested information is not already in the buffer/memory cache, Oracle reads it from the appropriate datafiles and stores it 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 database writer process (DBWn) background process.
