Oracle Schema Objects（Schema Object Storage And Type）

One characteristic of an RDBMS is the independence of physical data storage from logical data structures.

RDBMS的特点之一是物理数据与逻辑数据结构的独立性.

 

• Introduction to Schema Objects
• Schema Object Types
• Schema Object Storage
• Schema Object Dependencies
• SYS and SYSTEM Schemas

 

---

 

Introduction to Schema Objects

模式对象简介

database schema	1、In Oracle Database, a database schema is a collection of logical data structures, or schema objects. 在Oracle数据库中，数据库schema是逻辑数据结构或schema objects的集合。   2、A database schema is owned by a database user and has the same name as the user name. 一个database schema是由一个数据库用户拥有，并与用户名具有相同的名称.
Schema objects	1、Schema objects are user-created structures that directly refer to the data in the database. The database supports many types of schema objects, the most important of which are tables and indexes. Schema objects是用户创建的结构，它直接引用数据库中的数据。数据库支持多种类型的schema objects，其中最重要的是表和索引.   2、Schema objects are created and manipulated with SQL. Schema  objects是通过SQL来创建和操作的.
Database user And Schema	1、A database user has a password and various database privileges. 数据库用户有一个密码，还有各种数据库权限。   2、Each user owns a single schema, which has the same name as the user. 每个用户拥有一个单一的schema，这个schema和用户具有相同的名字。   3、The schema contains the data for the user owning the schema. Schema中包含了相应用户全部的数据。   For example, the HR user owns the HR schema, which contains schema objects such as the employees table. 例如，HR用户拥有HR模式，HR模式办好有雇员表之类的schema objects。 In a production database, the schema owner usually represents a database application rather than a person. 在生产数据库中，一个模式的拥有者通常表示一个数据库应用程序，而不是一个人。 Within a schema, each schema object of a particular type has a unique name. 在一个schema中，某个特定类型的每个schema object都有一个唯一的名称。 For example, HR.employees refers to the table employees in the HR schema. 如，HR.employee是hr模式中的employee表。

 

 

---

Schema Object Types

Schema object 类型

The most important schema objects in a relational database are tables. A table stores data in rows.

在关系数据库中，最重要的schema objects是表，表以行的形式存储数据。

1、Tables

• A table describes an entity such as employees.
• 表描述一个实体，如employees.

 

• You define a table with a table name, such as employees, and set of columns. In general, you give each column a name, a data type, and a width when you create the table.
• 使用一个表名（如employees）和一个列集来定义表，一般地，当你创建表时，应该给出每一列的列名、数据类型和列宽.
• 表=表名+列集（列名、数据类型、列宽）

 

• A table is a set of rows.
• 表是一些行的集合.

 

• A column identifies an attribute of the entity described by the table, whereas a row identifies an instance of the entity.
• 列标识实体（也就是表）的属性，而行标识实体的实例.

 

• You can optionally specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.
• 你可以选择性的为每个表列指定规则，这些规则成为完整性约束，例如“非空”就是一个完整性约束，这个约束强制每一行中的列都包含一个值.

Oracle Schema Objects——Tables——TableType

Oracle Schema Objects——Tables——TableStorage

Oracle Schema Objects——Tables——Overview of Tables

Oracle Schema Objects——Tables——Table Compression

Oracle Schema Objects——Tables——Oracle Data Type

2、Indexes

• An index is an optional data structure that you can create on one or more columns of a table.
• 索引是一个可选的数据结构，你可以在表中创建一个或者多个列上的索引.

 

• Indexes can increase the performance of data retrieval. When processing a request, the database can use available indexes to locate the requested rows efficiently.
• 索引可以提高数据检索的性能。在处理一个请求时，数据库可以使用可用索引有效地定位到请求的行.
• Indexes are useful when applications often query a specific row or range of rows.
• 当程序经常查询一个指定行或者特定范围的行时，索引很有用.

 

• Indexes are logically and physically independent of the data. Thus, you can drop and create indexes with no effect on the tables or other indexes. All applications continue to function after you drop an index.
• 索引在逻辑上和物理上是独立于数据。因此可以删除和创建索引，而对表或者其他索引没有任何影响。在删除索引后，所有应用程序可以继续运行.

Indexes are schema objects that contains an entry for each indexed row of the table or table cluster and provide direct, fast access to rows.

• 索引是一种模式对象，对于每一个被索引的表行或表簇行，索引都包含一个条目 ，以提供直接、 快速的存取。

 

• Oracle Database supports several types of index. An index-organized table is a table in which the data is stored in an index structure.

Oracle 数据库支持几种类型的索引。 一个索引组织表是一个表，其数据以一个索引结构来存储。

Oracle Schema Objects——Index

3、Partitions
Partitions are pieces of large tables and indexes. Each partition has its own name and may optionally have its own storage characteristics.

分区是大型表和索引的分片。 每个分区有其自己的名称，并可能有其自己（可选）的存储特征

 

Oracle Schema Objects——PARTITION

 

4、Views
Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data.

视图是对一个或多个表、或其他视图中的数据的自定义表示。 你可以把它们看作存储的查询。 视图实际上并不存储数据。 

Oracle Schema Objects——View

5、Sequences
A sequence is a user-created object that can be shared by multiple users to generate integers. Typically, sequences are used to generate primary key values.

序列是一个由用户创建的对象，可以被多个用户共享，用于生成整数。 通常，序列用于生成主键值。

Oracle Schema Objects——Sequences

 

6、Dimensions
A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. Dimensions are commonly used to categorize data such as customers, products, and time.

维度定义多个列集之间的父-子关系，列集中的所有列必须都来自同一个表。维度通常用于对客户、 产品、和时间之类的数据进行分类

7、Synonyms
A synonym is an alias for another schema object. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

同义词是另一个模式对象的别名。 因为同义词只是一个别名，它在数据字典中除了其定义之外，没有存储。

Oracle Schema Objects——Synonyms 

8、PL/SQL subprograms and packages
PL/SQL is the Oracle procedural extension of SQL. A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters. A PL/SQL package groups logically related PL/SQL types, variables, and subprograms.

PL/SQL 是 Oracle 对 SQL的过程化扩展。 PL/SQL 子程序是命名的PL/SQL 块，可以带参数调用。 PL/SQL 包用于将逻辑上相关的PL/SQL 类型、 变量、和子程序进行分组。

Other types of objects

 database users, roles, contexts, and directory objects

数据库用户、角色、上下文、目录对象

 

 

 

---

Schema Object Storage

Schema Object存储

1、schema object的数据如何存储

 Oracle存储（物理+逻辑）结构

schema objects that have segments 有段的模式对象 表或索引	Some schema objects store data in logical storage structures called segments. 一些schema objects将数据存储在称为段的逻辑存储结构中。 a nonpartitioned heap-organized table or an index creates a segment. 一个未分区的堆组织表或索引会创建一个段。
无段的模式对象 视图、序列	Other schema objects, such as views and sequences, consist of metadata only. have segments. 其他schema objects，像视图和序列，则只包含元数据。

2、schema object逻辑上和物理上存储在哪里？

schema object And Tablespace（表空间） 逻辑上	Oracle Database stores a schema object logically within a tablespace. Oracle数据库逻辑上将schema object存储在表空间中。   There is no relationship between schemas and tablespaces: 在schemas和表空间tablespace之间没有任何关系： a tablespace can contain objects from different schemas, 一个表空间可以包含来自不同schema的object and the objects for a schema can be contained in different tablespaces. 一个schema中的object也可以包含在不同的表空间中。
The data of each object And  data files 每个对象的数据&数据文件 物理上	The data of each object is physically contained in one or more data files. 每个对象的数据在物理上包含在一个或多个数据文件中。
Segments, Tablespaces, and Data Files   表、索引段、表空间、数据文件	The data segment for one table spans two data files, which are both part of the same tablespace. 一个表的数据段跨越2个数据文件，这2个数据文件属于同一个表空间。 A segment cannot span multiple tablespaces. 一个段不能跨越多个表空间。

 

 

---

Schema Object Dependencies

Schema Object依赖

Some schema objects reference other objects, creating schema object dependencies.

一些Schema Object会引用其他对象，就产生了依赖。

 

 

 

---

SYS and SYSTEM Schemas

 

All Oracle databases include default administrative accounts.

所有Oracle数据库都包括默认管理账户

 Administrative accounts are highly privileged and are intended only for DBAs authorized to perform tasks such as starting and stopping the database, managing memory and storage, creating and managing database users, and so on.

管理账户享有很高的特权，仅用于授权的数据库管理员执行诸如启停数据库、 管理内存和存储、 创建和管理数据库用户等任务

SYS

The administrative account SYS is automatically created when a database is created. This account can perform all database administrative functions. The SYS schema stores the base tables and views for the data dictionary. These base tables and views are critical for the operation of Oracle Database. Tables in the SYS schema are manipulated only by the database and must never be modified by any user. SYS管理帐户在创建数据库时自动创建。 此帐户可以执行所有的数据库管理功能。 SYS模式存储数据字典基表和视图。 这些基表和视图对数据库的运行至关重要。 SYS 模式中的表只由数据库操作，绝不能被任何用户修改。

SYSTEM

The SYSTEM account is also automatically created when a database is created. The SYSTEM schema stores additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to nonadministrative users. 在创建数据库时，也会自动创建 SYSTEM 帐户。 SYSTEM 模式存储其它一些用于显示管理信息的表和视图，以及用于各种数据库选项和工具的内部表和视图。 永远不要使用 SYSTEM 模式来存储非管理性用户的表。