
	Home > Ask the Oracle Database / Applications Experts > Oracle 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

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 Del.icio.us

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.

Digg This!

StumbleUpon

Del.icio.us

RELATED CONTENT

	Oracle database design and architecture
	How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table
	Can I install an Oracle client on Windows 7?
	How to use the Oracle Database Upgrade Assistant (DBUA)
	Can I specify Oracle column order in my database table?
	Can I have a single Oracle 11g RAC instance across multiple databases?
	How to use the Oracle export utility to duplicate database structure
	How to choose the primary key columns in an Oracle table
	Understanding the data archiving definition
	How to plan Oracle Grid Control Repository maintenance
	What Linux flavor should I use for my Linux proof of concept in Oracle?

Oracle tutorials, tips and FAQs

Part 2: Maintaining your place in the race -- ERP project management

Oracle virtualization introduction guide

Revving your engines: Tuning up your ERP project plan

Oracle Openworld 2009: Here's what to expect

Oracle OpenWorld 2009 Special Report

SearchOracle.com podcasts

Do I need to buy an Oracle license for a development database?

Special Report: Collaborate '09

IOUG on 2009 Collaborate conference: what's hot, what's not

How to create a database link in Oracle

Oracle tutorials, tips and FAQs Research

Oracle database design and architecture

How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table

Can I install an Oracle client on Windows 7?

How to use the Oracle Database Upgrade Assistant (DBUA)

Can I specify Oracle column order in my database table?

Can I have a single Oracle 11g RAC instance across multiple databases?

How to use the Oracle export utility to duplicate database structure

How to choose the primary key columns in an Oracle table

Understanding the data archiving definition

Review: Oracle's 11g R2 database has some good and bad

Scaling an Oracle database: What is the best strategy for you?

RELATED GLOSSARY TERMS

Terms from Whatis.com − the technology online dictionary

10g (SearchOracle.com)

delimiter (SearchOracle.com)

extent (SearchOracle.com)

field (SearchOracle.com)

flexfield (SearchOracle.com)

foreign key (SearchOracle.com)

Oracle (SearchOracle.com)

row (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

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.

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
Operating Systems IT Management Networking Database	Security Servers and Storage Applications and Development Career Center

Oracle White Papers: Fusion Middleware

SEARCH

TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.


TechTarget Corporate Web Site \| Media Kits \| Site Map All Rights Reserved, Copyright 2003 - 2009, TechTarget \| Read our Privacy Policy