Geek Answers Handbook

Database schema and database table space?

Doing reading my databases while reading ...

Scheme: container for objects

Tablespace: logical storage unit for objects

Can someone explain the difference between the two?

+6
source share
3 answers

Schema - namespace - is a logical thing. It is used to organize database object names. It has nothing to do with how data is stored.

Table space is a physical thing. It is a container for data and has nothing to do with the logical organization of database objects.

One object (for example, a table) can be distributed across several table spaces (depending on the DBMS used), but it can be defined in only one scheme. The schema_1.table_1 table is a different table than schema_2.table_1 - although the same name is the same, the full name is different, and therefore these are two different tables.

Objects that are organized in the same layout are not necessarily stored in the same table space. And one tablespace can contain objects from different schemas.

Schemas (and directories, which are another level of the namespace) are part of the SQL language and are defined in the SQL standard.

Table spaces are part of the physical storage and are specific to the DBMS (although almost all DBMSs support this concept) and are not part of the SQL query language (as defined by the SQL standard). However, they are defined and managed through vendor-specific SQL / DDL statuses.

+11
source

A schema manages logical structures.
Although Tablespaces work with the physical data files that make up the database.

From the Oracle documentation:

Scheme :
Scheme is a collection of database objects. The schema belongs to the database user and has the same name as this user. Schema objects are logical structures that directly refer to database data. Schema objects include structures such as tables , views, and indexes . (There is no connection between the table space and the schema. Objects in the same schema can be in different table spaces, and the table space can contain objects from different schemas.)

Tabular :
The database is divided into one or more logical storage units called table units. Table spaces are divided into logical storage units called segments, which are further divided into extents. Extents are a collection of adjacent blocks. Table space size is the size of the data files that make up the table space. Database size is the collective size of the table spaces that make up the database.

You can increase the database in three ways:

 Add a datafile to a tablespace Add a new tablespace Increase the size of a datafile
0
source

There is no connection between schemes and table spaces: a table space can contain objects from different schemes, and objects for a scheme can be contained in different table spaces.

FROM ORACLE DOCUMENTATION. https://docs.oracle.com/cd/B10500_01/server.920/a96524/c11schem.htm

0
source

More articles:


All Articles