Explain the relationship among database, tablespace and data file.What is schema

Showing Answers 1 - 21 of 21 Answers

vipul

  • Jun 9th, 2005
 

database contain atleast one tablespace and tablespace must have atleast one datafile

  Was this answer useful?  Yes

Ravi Prakash Pichika

  • Jun 11th, 2005
 

Databases, tablespaces and datafiles are closely related, but they have important differences: 
 
--- A Oracle Database consists of one or more tablespaces 
--- Each Table space in an Oracle database consists of one or more files called datafiles. 
--- A database's data is collectively stored in the datafiles that constitute each tablespace of the database.

soccer_champ

  • Jun 18th, 2005
 

When a database user is created, a corresponding schema with the same name is created for that user. A schema is a named collection of objects that include Tables, Triggers, constraints, Indexes, Views etc. A user can only be associated with one schema, and that is the same name as the user's. Username and schema are often used interchangeably.

RAVI SALUJA

  • Jan 10th, 2007
 

There is a very close relationship between database, tablespaces and datafiles

Technically --> A database is linked with one or more tablespaces

and a tablespace is linked with one or more datafile

One datafile has not linked with two or more tablespaces means one datafile is linked with only one tablespace, and this is the same case in relation of database and tablespace -- > one tablespace is not linked with one or more databases but a database has one or more tablespaces

Thanks

  Was this answer useful?  Yes

fdg28

  • Jun 9th, 2008
 

Database : Collection of data is called database.

Tablespace : An Oracle Database consists of one or more logical storage units called tablespace, which collectively use to store all the database's data.

DataFile : Each tablespace in an Oracle Database consists of one or more datafiles which are used to store data physically.

Schema : A schema is a named collection of objects that include Tables, Triggers, constraints, Indexes, Views etc. A user can only be associated with one schema, and that is the same name as the user's. Username and schema are often used interchangeably.

A database consists of one or many tablespaces.  A database has one-to-many relationship with tablespaces.  That is, one database can have many tablespaces, but a given tablespace can belong to only one database.  Similary, a tablespace has one-to-many relationship with datafiles.  That is, a tablespace can have one or many datafiles but a given datafile can belong to only one tablespace.
 
Here is an example:

select * from v$database;
This will give you the name of the database, dbid, created, log_mode,  etc.  You will see only one row.

select *
from v$tablespace;
This will give TS#, tablespace_name, etc.  You will see many rows.  TS# is primary key here.

select *
from v$datafile;
This will give you the datafiles name, TS#, where TS# here is a foreign key referencing the TS# of the v$tablespace table.  Also, you can see name column where the complete path is available as
/u5/fdgre2/fergdb/horm_data_01.dbf
/u5/fdgre2/fergdb/horm_data_02.dbf  ...... etc.


  So the short and correct answer is:

A database has one-to-many relationship with tablespace.
A tablesapce has one-to-many relationship with datafile.

Ram Srinivasan

  Was this answer useful?  Yes

ammupriyaa

  • Jul 24th, 2008
 

Table Space : The table space is useful for storing the data in the
database. When a database is created two table spaces are created.

    a)  System Table Space : This data file stores
all the tables related to the system and dba tables


    b) User Table Space : This data file stores all the
user related tables. We should have separate table spaces for storing the tables
and indexes so that the access is fast.

Data Files : Every Oracle Data Base has one or more physical data files.
They store the data for the database. Every data-file is associated with only
one database. Once the Data file is created the size cannot change. To increase
the size of the database to store more data we have to add data file.

  Was this answer useful?  Yes

imran44

  • Jan 9th, 2009
 

The database is actually a collection of objects, which are made up of tablespaces and each tablespace must have datafile associate with it. The tablespace cannot be created without datafile. The tablespaces are two kind physical and logical.
The logical made up of Segments,extents and blocks, and physical made of OS blocks where the actual data exist and smallest unit unit of the database.

In Oracle schema is a user, who can owned objects and owned by others users. The database comes with sys and system schemas which are default schemas, later on many other users or schemas can be created on needed bases.

  Was this answer useful?  Yes

hmounir

  • Dec 27th, 2009
 

The datafiles in an Oracle database are grouped together into one or more tablespaces. An Oracle tablespace consists of one or more datafiles; a datafile can be a part of one and only one tablespace.

Schema can own any type of object in the database: Indexes, Sequences, Views, so forth.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions