Girish

• Sep 6th, 2005

 

We can change the size of the datafile.

Deepika S. Verma

• Dec 27th, 2005

 

We cannot change datafile size , if you are running short of storing space in database, then alter the tablespace and add one more datafile in tablespace

Guest

• Dec 28th, 2005

 

A data file is created when a 'create tablespace' or 'alter tablespace' command is executed. Creating a data file of size 10MB actually allocates 10MB space on the hard disk.

If a tablespace is declared to contain 100 data files, one slot per data file is reserved in the control file.

There are four types of data files -- system data files, application (or user) data files, temporary data files (used by oracle to store temp data while executing complex SQL statements) and rollback data files.

It is possible to resize a datafile. The command which will serve this purpose is

manu

• Apr 5th, 2006

 

   A  data file stores data in binary pattern. Even database stored physically in datafile.. we could not access datafile directly to access record.it is maintained ny operation system.

Suraj Sharma

• Apr 20th, 2006

 

The data physically stored in the datafile. We can associate one or more datafile for a single tablespace, but can not assign one datafile to multiple tablespaces. Datafiles only contains the committed data, but sometimes uncommitted as well. we can increase the size of the datafile, but can not shrink below the High Water Mark. Datafiles can be taken offline, read only & begin backup stage.

manish hoiyani

• Sep 14th, 2006

 

1>

datafiles comes under the physical structure of the database.

2>

It is used to store the actual data.. each  datafile can be associated with one tablespace only.. two datafiles cannot belong to different tablespace.. but two datafiles can belong to same tablespace.

3>

we cannot change the system datafiles assocaited with system tablespace.. but we can change the user datafiles associated with users tablespaces,

4>

we can resize the datafile,, we can add new datafile to the same tablespace, so whenever database runs lack of space,, either we can change the datafile size or we can add new datafile to the same tablespace..

adhiraj007

 Profile Answers by adhiraj007 

• Jan 21st, 2007

 

how we can changr the size of datafile please define a procedure in detail

Vimal

• Jan 23rd, 2007

 

THE SYNTAX TO RESIZE A DATAFILE IS: Alter Database Datafile 'datafilen_name.dbf' resize 1024M

irfanyell

 Profile Answers by irfanyell 

• Apr 24th, 2008

 

Data file is a physical storage of the database. its created when database is created or tablespace is created. you can not change the size of the datafile. but you can resize it from lower size to higher size. you can resize it from higher size to lower size ONLY WHEN YOUR DATAFILE IS EMPTY. command is alter database datafile '....path...' resize size;

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 21st, 2008

 

Oracle architecture:
                                   Database->Tablespace->Datafiles
Data base is a set of data where data are stored in form of Relational model.
Tablespace: tablespace is the logical division of Databse. Each database has atleast one tablespace.i.e SYSETEM tablespace. A tablespace belong to only one database.

Datafiles: Datafile is the physical storage of data which associated with tablespace.
One datafile must belong to one tablespace. one tablespace can have multiple datafile. Every datafile belongs to one and only tablespace.

Note: From Oracle 7.2 datafile can be resized after creation.

We can create datafile and alter the existing datafile so that it automatically increase its size when more space is neede in database. the datfile size increases in specified increments up to specified maximum.

We can check whether the datfile is autoextensiable.
Check in view DBA_DATA_FILES and check AUTOEXTEND ON clause.
sql> select * from DBA_DATA_FILES:

Increae the size of datafile by adding new datafile with tablespace:
sql> alter tablespace SYSTEM add datafile <datafilename> size 10m
        autoextend on 
        next 512 k
        maxsize 250m

We can crease or decrease the size of existing datafile:
sql> alter database datafile <datafile_name>  resize 100m;

datafile_name: '/u02/oracle/rbdb1/stuff01.dbf'

Thanks,

                                              

matabrez

 Profile Answers by matabrez 

• Jan 10th, 2011

 

Steps for changing the size of the datafile.

1. Change that datafile to offline.
eg. SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

2.
Chang the size of the datafile.

eg SQL>ALTER DATABASE RESIZE DATAFILE '/datastore/dbfilelcoatoin.dbf' resize 1gb;

3.
Again change this file to online;
eg. SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

NAGARAJ OCP

• Dec 27th, 2011

 

In data files, we cant change the extent management, type of it. But we can change the storage space for it if we need after the database (tablespace) creation.

ranjithsv18

• Oct 2nd, 2013

 

Block size available from 2k,4k,8k,16k and 32k.

As per oracle point of view Default (or) standard block size is 8k for Data files.The initialization parameter

db_block_size=8k (8192)remaining size are non-standard blok size. Extents size 64k.

(Datafile<-Segments<-Extents<-Blocks) storage unit in a datafiles. One block is full, it continues with another another block. Note:8k*8k=64k