The relational model
Home > Ask the Oracle Experts > Database Design and Architecture Questions & Answers > Relationship between schema and datafile in Oracle
Ask The Oracle Expert: Questions & Answers
EMAIL THIS

Relationship between schema and datafile in Oracle

Brian Peasland EXPERT RESPONSE FROM: Brian Peasland

Pose a Question
Other Oracle Categories
Meet all Oracle Experts
Become an Expert for this site


Oracle tips, scripts, and expert advice
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


>
QUESTION POSED ON: 23 May 2007

Hi,
I am no DBA and currently working on supporting Oracle ETL processes. I need to get a better explanation regarding Oracle architecture, as I do not quite get it from the manual.

The system I deal with is Oracle 10g on Sun Solaris.

I am aware that there is no relationship between tablespace and schema. I would have thought that the datafile somehow related to the schema. (I might be wrong here). I am concerned that the fact table is getting big and failing to insert is because it cannot extend the table due to no more free space. To prevent this, I would monitor the datafile where the table write. Thus, I wonder if there is a clear relationship between the schema and the datafile (physical storage). Is it because a schema is logical storage and a datafile is physical storage?

The naming of the tablespace is not related to the table name. It is using the size naming (M10A = allocate 10MB) convention.

Many thanks in advance.


>
EXPERT RESPONSE
The datafile has no relation to the schema. It does have a relation to the tablespace (which you have already defined as having no relation to the schema). A tablespace is just a logical container for those database objects that require storage space in the filesystem (these are called "segments"). The physical manifestation of the tablespace is the datafile (or datafiles) that belong to that tablespace. If you tell Oracle to put a table in a specific tablespace, that table may be in any or all datafiles that make up that tablespace. The tablespace is the logical storage here and the datafile is the physical storage. This has nothing to do with the schema because a schema's segments can be stored in one or more tablespaces and a tablespace can have one or more schema's segments. However, any one segment cannot span tablespaces. A segment must be in one and only one tablespace. But if that tablespace is made up of more than one datafile, the segment can span datafiles within that tablespace.


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


RELATED CONTENT
Database Design and Architecture
How do you size spatial tables and indexes?
Creating db links from Oracle 10g to 8i
Failed stored procedure through db link in Oracle
Using Oracle's Virtual Private Database, aka Fine-Grained Access Control
How would referential integrity work in this situation?
Recreating advanced queuing objects during database upgrade
Can I restrict data in a single schema?
How DDL and DML commands work in Oracle
When should an Oracle database be reorganized?
Can I drop a column in SYS schema?

The relational model
Happy birthday Oracle -- 30 years!
Retrieve last row inserted into table in Oracle
Oracle does not maintain order of inserted records
Why is Oracle 8 an "ORDBMS"?
Mike Ault's Oracle "good practices": Oracle design
Oracle revamps TimesTen In-Memory Database
Geeks pick the top movies of 2007
Network model for databases
Network, hierarchical and relational data models
Database vs. relational database

Oracle tutorials, tips and FAQs
How to create a database link in Oracle
Most clicked stories of 2007
How do I do that in Oracle?
Counting a row's NULL columns
How many redo log files?
How to start multiple instances in Oracle 10g
Is it possible to assign a role to a profile?
Difference between RECYCLEBIN and 'DROPPED' column
How to find transactions being committed to Oracle database
How to create a database schema in Oracle?
Oracle tutorials, tips and FAQs Research

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
E. F. Codd  (SearchOracle.com)
multidimensional database  (SearchOracle.com)
object-oriented database management system  (SearchOracle.com)
Oracle  (SearchOracle.com)
relational online analytical processing  (SearchOracle.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary



Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice

HomeNewsTopicsTipsAsk the ExpertsWebcastsWhite PapersProductsBlogs
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Reprints  |  Site Map




All Rights Reserved, Copyright 2003 - 2008, TechTarget | Read our Privacy Policy 		  TechTarget - The IT Media ROI Experts