Problem solve Get help with specific problems with your technologies, process and projects.

Relationship between schema and datafile in Oracle

In Oracle, is there a clear relationship between the schema and the datafile?

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.

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.

Dig Deeper on Oracle DBA jobs, training and certification