Menu
Doing my databases reading when I read...
Schema: Is a container for objects
Tablespace: A logical storage unit for objects
Can anyone explain the difference between these?
485
Key Differences between Database and Schema The fundamental difference between them is that the database is an organized collection of interrelated data or information about the considered object. In contrast, the schema is a logical representation or description of an entire database.
DATABASES's data are stored in logical storage units called TABLESPACES. A database may contain "one or more" tablespaces. A tablespace may contain one or more datafiles. A database's data is collectively stored in the datafiles that constitute each tablespace of the database.
The Oracle database stores a schema object logically within a tablespace. There is no relationship between database schemas and a tablespace: a tablespace can contain objects from different schemas, and objects from a database schema can be contained in different tablespaces.
A schema is a collection of objects (such as tables, indexes or procedures). Every object belongs to exactly one schema, and every schema is a part of exactly one database. (Names may not be unique, however.
A schema is a namespace - a logical thing. It is used to organize the names of database objects. It has nothing to do with the way the data is stored.
A tablespace is a physical thing. It's a container for data and has nothing to do with the logical organization of the database objects.
A single object (e.g. a table) could be spread across multiple tablespaces (depending on the DBMS being used) but it can only be defined in a single schema. The table schema_1.table_1 is a different table than schema_2.table_1 - although the "plain" name is the same, the fully qualified name is different and therefore those are two different tables.
Objects that are organized in the same schema are not necessarily stored in the same tablespace. And a single tablespace can contain objects from different schemas.
Schemas (and catalogs, which are another level of namespace) are part of the SQL language and are defined in the SQL standard.
Tablespaces are part of the physical storage and are DBMS-specific (although nearly all DBMS support a concept like that) and are not part of the SQL query language (as defined by the SQL standard). They are, however, defined and managed through vendor-specific SQL/DDL statements.
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
