What is a datafile?
I would like to make a clear understanding of the datafile in the create
database. What would be the role of the datafile in the create statement?
If I place the the datafile as an option in the create DB statement, does
this mean that my data won't reside in a tablespace? And if I created a
tablespace for the tables, then what would be inside the datafile that
I defined during the create statement.
Ans1:
The datafile is the Physical File on which a Tablespace resides on.
A tablespace can span over several datafiles. A tablespace cannot
exist onless theirs a datafile unless you use raw devices as oppose to
a file system.
Tables are created and they reside on one tablespace and only one tablespace.
If you don't mention which tablespace it will use the default tablespace
that is associated to the user lauching the create table ... command.
Ans2:
The create database statement usually contains the datafile for
the system tablespace and the datafiles for the redo_log segments.
Tablespaces reside within one or more datafiles. Without the system
tablespace there is no database.
Oh, you should be sure to protect certain datafiles more than others,
the controlfiles, the redo logs, and the system tablespace datafile.
It is necessary to backup all datafiles (with certain exceptions
on the redologs), but the controlfiles and system tablespace are
more difficult to recover when lost.
Ans3:
Normally, creating a database involves two scripts that are run from
SVRMGRL. The first simply creates the SYSTEM tablespace and a datafile
associated with it as well as the REDO log files and control files(locations
of both are specified in INIT.ORA or CONFIG.ORA). The second script creates
the ROLLBACK tablespace and other secondary initial tablespaces (USER,
TOOLS, etc.) as well as rollback segments in the ROLLBACK tablespace.
If you're on a Unix system there are example scripts provided by Oracle
(CRDB.SQL and CRDB2.SQL). Look for them in the $ORACLE_HOME/svrmgr directory.
The CREATE statement should specify SYSTEM tablespace and one datafile
(see the example files described above). The SYSTEM tablespace should be
used solely for the data dictionary. All other data should be created in
separate tablespaces after the CREATE DATABASE is executed. Hope this clears
it up some.