DATAFILE&TABLESPACES

                               DATAFILE MANAGEMENT

                              -----------------------------------------------
A datafile is a file that correspondens with a tablespace. One
datafile can be used by one tablespace, but one tablespace can
has more than one datafiles. An Oracle databae include of a 
number of physical files called datafile.
Datafiles are mandatory files inside a database.
datafiles are used to stored information of enduser (or)businessdata.
Universaly accepted naming convention for datafile .dbf.
Datafiles are Physical files.
Datafiles are made of oracle blocks where 
Each block size would be 2k,4k,8k.16k,32k  default 8k.
The blocksize is determined by  the parameter db_block_size .
For better managebility of datafilesm oracle introduced 
tablespace concept.
Tablespace is logical entity with has physical logica structure.
Tablespace is collection of physical datafiles and logical tables.
As a end users are always store data in tables.
oracle internally store those tables data into datafile blocks 
physically.
Tablespace is a collection of one or more datafiles

Tablespace are devided into 2 types.

MANDATORY TABLESPACE.
UNMANDATORY TABLESPACE.

1>MANADATORY TABLESPACE:
-------------------------------------------
This tablespaces are mandatory because without  these 
tablespace we cannot create the database.
we need to specify these during database creation.
In 9i system is mandatory and 10g system,sysaux
11g system,sysaux,undotablespace

SYSTEM TABLESPACE:
----------------------------------
This will the first tablespace which is created inside during a 
database creation.
which  ever the first datafile which specified create database 
command that datafile will be placed assigned to system tablespace.
we cannot change this tablespace name ever.
system tablespace contains datadictionary tables.

TO KNOW THE CURRENT EXISTING IN THE TABLESPACES:
SQL>select  tablespace_name,status from dba_tablespaces;

By default tablespace status is online i.e datafile is open.
By default we will be create a tablespace is READ WRITE mode.
If tablespace is in READ WRITE mode all the tables and datafiles 
present under the tablespace will be READWRITE mode.
----------------------------------------------------------
SQL>select name,enabled from v$datafile;
----------------------------------------------------------
------------------------------
SYSAUX TABLESPACE:
--------------------------
It is new manadatory tablespace from 10g.
it is an auxiliary tablespace for the system and it is introduced 
by the burden of system tablespace.
From 10g all metadata table which are introduced will be 
automatically stored in sysaux tablespaces.
To know the all new features information which are supported 
by sysaux tablespace.
we need to query           V$SYSAAUX_OCCUPANTS
we cannot drop sysaux tablespace.
sysaux tablespace cannot made read only because it is exist 
inside a database it is always in READWRITE mode.
we cannot rename this one.
sysaux tablespace can be made offline.


---------------------------------------------
UNDO TABLESPACE:
------------------------------------
From g it is mandatory tablespace.
It is used basically support rollback operations it holds data only.

UNMANDATORY TABLESPACES::

------------------------------------------
These tablespaces holds enduser data interms of table
we create these tablespace while creating database or even 
after creating a database.

SQL>create tablespace tablespacename datafile'------' size=memory;

SYNTAX TO ADD DATAFILE:
------------------------------------------
SQL>alter tablespace  tablespacename add datafile 'path location' size=memory;

RENAMING TABLESPACE:
SQL>alter tablespace existing tablespacename rename to newname;
note:To rename a tablespace status must online

when tablespace is empty this syntax use
SQL>drop tablespace tablepacename;

SQL>drop tablespace tablespacename including contents;
only tablespace will be deleted not datafile.

SQL>drop tablespace tablespacename including contents and datafiles;
physical datafiles also deleted.