Ask Maclean

oracle中管理表

本文永久链接地址： https://www.askmac.cn/archives/oracle-manage-table.html ‎

  • 辨别各种存储数据的方法
  • 概括各种 Oracle 数据类型
  • 区分扩展的和受限的 ROWID
  • 概括行的结构
  • 创建规则表和临时表
  • 管理表内的存储结构
  • 重新组织、截断和删除表
  • 删除表内的列

存储用户数据

 

在 Oracle 数据库中有几种存储用户数据的方法：

  • 常规表
  • 分区表
  • 按索引组织的表
  • 集簇表

注：分区表、按索引组织的表和集簇表在其它课程中进行阐述。

常规表：

常规表（通常称为“表”）是存储用户数据最常用的形式。它是缺省表，并且是本课论述的重点。数据库管理员对表中行分布的控制很有限。行可能按任意顺序存储，具体顺序取决于在表中进行的操作。

 

分区表：

分区表使您可以生成可伸缩的应用程序。它具有以下特征：

  • 每个分区表有一个或多个分区，每个分区存储已分区（使用范围分区、散列分区、组合分区或列表分区）的行。
  • 分区表中的每个分区为一个段，可各自位于不同的表空间中。
  • 对于能够同时使用几个进程进行查询或操作的大型表，分区非常有用。
  • 有一些特殊的命令可用来管理一个表内的分区。

按索引组织的表：

按索引组织的表就像在一个或多个列中具有主键索引的堆表。但是，按索引组织的表并不为表和 B 树索引维护两个单独的存储空间，而是仅维护一个包含表主键和其它列值的 B 树。由于设置 PCTTHRESHOLD 值以及较长的行长度需要溢出区域，所以可能存在溢出段。

按索引组织的表为进行涉及精确匹配和范围搜索的查询，提供基于键的、对表数据的快速访问。

此外，存储要求也降低了，因为键列在表和索引中不重复。除非索引条目变得非常大，否则其余的非键列就存储在索引中；在此情况下，Oracle 服务器提供 OVERFLOW 子句来处理此问题。

集簇表：

集簇表为存储表数据提供另一种可选的方法。簇由一个表或共享相同数据块的一组表构成，它们之所以被组织在一起，是因为它们共享共同的列并且经常一起使用。

簇具有以下特征：

  • 簇有一个集簇键，用来标识需要存储在一起的多个行。
  • 集簇键可由一个或多个列组成。
  • 簇中的表具有与集簇键相对应的列。
  • 集簇是一种对使用表的应用程序透明的机制。可以象操作存储在常规表中的数据那样操作集簇表中的数据。
  • 更新集簇键中的一列可能需要移植该行。
  • 集簇键独立于主键。簇中的表可有一个主键，它可以是集簇键，也可以是另一组列。
  • 创建簇通常是为了改善性能。随机访问集簇数据更快，而对集簇表进行全表扫描通常较慢。
  • 簇会重新规范表的物理存储，但不影响其逻辑结构。

 

Oracle 内置数据类型

 

Oracle 服务器提供几种内置数据类型来存储标量数据、集合和关系。

标量数据类型：

字符数据：字符数据可以作为长度固定或长度可变的字符串存储在数据库中。

长度固定的字符数据类型（如 CHAR 和 NCHAR）存储时带有填补空格。NCHAR 是“全球化支持”包含的一种数据类型，既可以存储宽度固定字符集，也可以存储宽度可变字符集。其最大大小取决于存储一个字符所需要的字节数，上限为每行 2,000 个字节。缺省值为1 个字符或 1 个字节，具体取决于字符集。

长度可变的字符数据类型仅使用存储实际列值所需要的字节数，并且每行的大小可以不同，最大可达 4,000 字节。VARCHAR2 和 NVARCHAR2 就是长度可变的字符数据类型的例子。

 

标量数据类型（续）：

数字数据类型：Oracle 数据库中的数字始终以长度可变的数据存储。最多可以存储 38 个有效数位。数字数据类型需要：

  • 指数用 1 个字节
  • 尾数中的每两个有效数位用 1 个字节
  • 负数用 1 个字节（如果有效数位少于 38 个字节）

DATE 数据类型：Oracle 服务器将日期存储在包含七个字节的固定长度字段中。Oracle DATE 始终包括时间。

TIMESTAMP 数据类型：此数据类型存储日期和时间，包括零点几秒，最高可达 9 位小数。TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE 可以使用时区设定时间，如夏时制。TIMESTAMP 和 TIMESTAMP WITH LOCAL TIME ZONE 可用于主键，而 TIMESTAMP WITH TIME ZONE 则不能。

RAW 数据类型：可以使用此数据类型存储小型二进制数据。在网络中的计算机之间传输 RAW 数据时，或者使用 Oracle 实用程序将 RAW 数据从一个数据库移到另一个数据库时，Oracle 服务器不执行字符集转换。存储实际列值所需要的字节数大小随每行大小而异，最多为 2,000 字节。

LONG、LONG RAW 和大型对象 (LOB) 数据类型：

Oracle 为存储 LOB 提供六种数据类型：

  • CLOB 和 LONG 用于存储大型的、宽度固定的字符数据
  • NCLOB 用于存储大型的、宽度固定国家字符集数据
  • BLOB 和 LONG RAW 用于存储非结构化数据
  • BFILE 用于存储操作系统文件中的非结构化数据

LONG 和 LONG RAW 数据类型以前用于非结构化数据，如二进制图像、文档或地理信息，目前主要用于向后兼容。这两种数据类型已由 LOB 数据类型代替。LOB 数据类型与 LONG 和 LONG RAW 不同，不能互换。LOB 不支持 LONG 应用程序编程接口 (API)，反之亦然。

 

LONG、LONG RAW 和大型对象 (LOB) 数据类型（续）：

最好与旧的数据类型（LONG 和 LONG RAW）相比较来讨论 LOB 功能。在下文中，LONG 指 LONG 和 LONG RAW 数据类型，而 LOB 指所有 LOB 数据类型。

除非其大小小于 VARCHAR2 数据类型的最大大小（4,000 字节），否则，LOB 在表中存储一个定位器，而将数据存储在另一位置；LONG 则将所有数据存为一行。此外，LOB 允许将数据存储在单独的段和表空间中，或者存储在主机文件中。

LOB 支持对象类型属性（NCLOB 除外）和复制；而 LONG 不支持。

LONG 主要存储为一连串的行片段，每一块中有一个行片段指向存储在另一块中的下一行片段。因此，需要按顺序访问这些行片段。相反，LOB 通过类似文件的接口支持以片段方式随机访问数据。

ROWID 和 UROWID 数据类型：

ROWID 是一种可以和表中其它列一起查询的数据类型。它具有以下特征：

  • ROWID 是数据库中每行的唯一标识符。
  • ROWID 并不显式地作为一个列值存储。
  • 虽然 ROWID 并不直接给出一行的物理地址，但它可以用来定位行。
  • ROWID 为访问表中的行提供了最快的方法。
  • ROWID 存储在索引中来指定具有一组给定的键值的行。

在 Oracle8.1 版中，Oracle 服务器提供一种称为通用 ROWID 或 UROWID 的数据类型。它支持外表（非 Oracle 表）的 ROWID，并且可存储各种类型的 ROWID。例如：要存储按索引组织的表 (IOT) 中存储的行的 ROWID，必须使用 UROWID 数据类型。要使用 UROWID，参数 COMPATIBLE 的值必须设置为 Oracle8.1 或更高。

 

集合数据类型：

有两种集合数据类型可用来为表中的一个给定行存储重复的数据。在 Oracle8i 以前，定义和使用集合需要“对象”(Objects) 选项。下面简要论述这些类型。

变化数组 (VARRAY)：变化数组对于存储包含少量组成元素的列表（如客户的电话号码）非常有用。

VARRAY 具有以下特征：

  • 数组即一组有序的数据组成元素。
  • 一个给定数组的所有组成元素的数据类型相同。
  • 每个组成元素都有索引，即与数组中组成元素的位置相对应的编号。
  • 数组中组成元素的数目决定了数组的大小。
  • Oracle 服务器允许数组的大小可以变化，这就是它们被称为 VARRAY（意为变化数组）的原因，但在声明数组类型时必须指定最大大小。

嵌套表：嵌套表提供一种将一个表定义为另一个表内一列的方法。嵌套表可用来存储可能包含大量记录的集合（比如一个订单中的若干条目）。

嵌套表一般具有以下特征：

  • 嵌套表是一组无次序的记录或行。
  • 嵌套表中的各行结构相同。
  • 嵌套表中的行与父表分别存储，并且父表中的对应行有一个指针。
  • 嵌套表的存储特点可由数据库管理员来定义。
  • 嵌套表没有预先确定的最大大小。

关系数据类型 (REF)：

关系类型在数据库内用作指针。使用这些类型需要“对象”(Objects) 选项。这里给出一个例子：订购的每一项都可以指向或引用 PRODUCTS 表中的一行，而不必存储产品代码。

Oracle 用户定义的数据类型：

Oracle 服务器允许用户定义抽象的数据类型并在应用程序内使用这些数据类型。

 

ROWID 格式

扩展的 ROWID 在磁盘上需要 10 个字节的存储空间，并使用 18 个字符来显示。它包含下列组成元素：

  • 数据对象编号：每个数据对象（如表或索引）在创建时都分配有此编号，并且此编号在数据库中是唯一的
  • 相关文件编号：此编号对于表空间中的每个文件是唯一的
  • 块编号：表示包含此行的块在文件中的位置
  • 行编号：标识块头中行目录位置的位置

在内部，数据对象编号需要 32 位、相关文件编号需要 10 位、块编号需要 22 位、行编号
需要 16 位，加起来总共是 80 位或 10 个字节。

扩展的 ROWID 使用以 64 为基数的编码方案来显示，该方案将六个位置用于数据对象编号、三个位置用于相关文件编号、六个位置用于块编号、三个位置用于行编号。以 64 为基数的编码方案使用字符“A-Z”、“a-z”、“0-9”和“/”。共有 64 个字符，如下例所示：

 

SQL> SELECT department_id, rowid FROM hr.departments;

DEPARTMENT_ID ROWID

————- ——————

10 AAABQMAAFAAAAA6AAA

20 AAABQMAAFAAAAA6AAB

30 AAABQMAAFAAAAA6AAC

40 AAABQMAAFAAAAA6AAD

50 AAABQMAAFAAAAA6AAE

60 AAABQMAAFAAAAA6AAF

…

在本例中：

  • AAABQM 是数据对象编号
  • AAF 是相关文件编号
  • AAAAA6 是块编号
  • AAA 是 ID=10 的部分的行编号

Oracle7 和更早版本中的受限 ROWID：

Oracle8 之前的 Oracle 数据库版本使用的是受限的 ROWID 格式。受限的 ROWID 在内部仅使用六个字节，不包含数据对象编号。此格式在 Oracle7 或更早的发行版中是可以接受的，因为文件编号在一个数据库内是唯一的。因此，较早的发行版不允许数据文件数超过 1,022 个。目前，对表空间有此限制。

即使 Oracle8 通过使用与表空间相关的文件编号摆脱了这种限制，但受限 ROWID 仍用在非分区表上的非分区索引之类的对象中，其中所有索引条目指的是同一段中的行。

使用 ROWID 定位行：

因为一个段只能驻留在一个表空间中，所以，Oracle 服务器可以使用数据对象编号来确定包含某一行的表空间。

表空间中的相关文件编号用来定位文件，块编号用来定位包含该行的块，行编号用来定位该行的行目录条目。

行目录条目可以用来定位行首。

这样，ROWID 就可以用来定位一个数据库中的任意行。

 

行的结构

行数据作为长度可变的记录存储在数据库块中。通常，一个行的各列按其定义时的顺序存储，并且不存储尾随的 NULL 列。

注：对于非尾随的 NULL 列，列长度需要占用一个字节。表中的每行具有：

  • 行头：用来存储行中的列数、链接信息和行锁定状态
  • 行数据：对于每一列，Oracle 服务器存储列的长度和值（如果该列不超过 250 个字节，则需要一个字节来存储列长度；如果该列超过 250 个字节，则需要三个字节来存储列长度。列值在紧靠列长度字节后面存储。）

相邻的行之间不需要任何空格。块中的每一行在行目录中都有一个位置。目录位置指向行首。

创建表

 

CREATE TABLE hr.employees(
employee_id NUMBER(6), 
first_name	VARCHAR2(20),	
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE DEFAULT SYSDATE,
job_id	 VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER (2,2),
manager_id NUMBER(6),
department_id NUMBER(4)
);

CREATE TABLE 命令用于创建关系表或对象表。

关系表：这是存储用户数据的基本结构。

对象表：是一种将对象类型用于列定义的表。对象表是一种显式定义的表，用来存储特定类型的对象例程。

注：本课不讨论对象表。

创建表的原则：

  • 将各个表存放在单独的表空间中。
  • 使用本地管理的表空间以避免产生存储碎片。

注：有关在使用 CREATE TABLE 命令时可以定义的各种子句和参数的详细信息，请参考 Oracle9i SQL Reference 文档。

要在您自己的方案中创建关系表，您必须具有 CREATE TABLE 系统权限。要在另一个用户的方案中创建表，您必须具有 CREATE ANY TABLE 系统权限。

注：有关授予权限的详细信息，请参考“管理权限”一课。

 

下面的示例在数据字典管理的表空间中创建了一个 DEPARTMENTS 表。

SQL> CREATE TABLE hr.departments(

2  department_id NUMBER(4),

3  department_name VARCHAR2(30),

4  manager_id NUMBER(6),

5  location_id NUMBER(4))

6  STORAGE(INITIAL 200K NEXT 200K

7  PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)

8  TABLESPACE data;

以上语法是 CREATE TABLE 子句的一部分。

STORAGE 子句：

STORAGE 子句指定表的存储特性。给第一个区分配的存储空间为 200 KB。需要第二个区时，就会再创建一个 200 KB 的区（是由 NEXT 值定义的）。需要第三个区时，就会创建一个 200 KB 的区，这是因为已将 PCTINCREASE 设置为零。将可以使用的最大区数设置为 5，最小区数设置为 1。

  • MINEXTENTS：这是要分配的最小区数。
  • MAXEXTENTS：这是要分配的最大区数。如果将 MINEXTENTS 指定为一个大于 1 的值，而表空间包含多个数据文件，则这些区将分布在不同的数据文件中。
  • PCTINCREASE：这是 NEXT 区及以后的区有关区大小增长的百分比。

 

还可以在 physical_attributes_clause 中指定表的块使用参数。

  • PCTFREE：指定表内每个数据块中空间的百分比。 PCTFREE 的值必须介于 0 和 99 之间。如果值为零，表示可以通过插入新行来填充整个块。缺省值为 10。此值表示每个块中保留着 10% 的空间，用于更新现有的行以及插入新行，每个块最多可填充到 90%。
  • PCTUSED：指定为表内每个数据块维护的已用空间的最小百分比。如果一个块的已用空间低于 PCTUSED，则可在该块中插入行。PCTUSED 的值为介于 0 和 99 之间的整数，缺省值为 40。

结合 PCTFREE 和 PCTUSED 就可以确定将新行插入到现有数据块中，还是插入到新块中。这两个参数值的和必须小于或等于 100。使用这两个参数可以更有效地利用表内的空间。

  注：Oracle9i“自动段空间管理”功能可替代 PCTUSED、FREELISTS 和 FREELIST GROUPS。有关此功能的详细信息，请参考“存储结构和关系”一课。

  • INITRANS：在分配给表的每个数据块内，指定分配的初始事务处理项数。此值的范围在 1 到 255 之间，缺省值为 1 个 INITRANS：确保最小数量的并发事务处理可以更新该块。通常，应该保留此值的缺省值。
  • MAXTRANS：指定可以更新分配给表的数据块的最大并发事务处理数量。此限制不适用于查询。值的范围在 1 到 255 之间，缺省值由数据块大小的函数确定。

TABLESPACE 子句：

TABLESPACE 子句指定将要在其中创建表的表空间。示例中的表将驻留在数据表空间内。如果省略 TABLESPACE，则 Oracle 在包含该表的方案的所有者的缺省表空间中创建对象。

注：有关表空间的详细信息，请参考“管理表空间”一课。

 

  使用 Oracle Enterprise Manager 创建表

从“OEM 控制台”(OEM Console)：

注：OEM 为创建表提供了很多选项。

1.导航到“数据库”(Databases) >“方案”(Schema) >“表”(Table)。

2.单击鼠标右键，从弹出的菜单中选择“创建”(Create)。

3.输入表的信息，如表名称、表空间、所有者、列、数据类型和大小。

4.单击“创建”(Create)。

 

创建表：原则

  • 将各个表存放在单独的表空间中。
  • 使用本地管理的表空间以避免产生存储碎片。
  • 将表的标准区大小设置小一些，以减少表空间存储碎片。

将各个表存放在单独的表空间内，而不是存放在有还原段、临时段和索引的表空间内。

将表存放在本地管理的表空间内以避免产生存储碎片。

 

创建临时表

  • 使用 GLOBAL TEMPORARY 子句创建
  • CREATE GLOBAL TEMPORARY TABLE hr.employees_tempAS SELECT * FROM hr.employees;
  • 仅为事务处理或会话期间保留数据
  • 不能为数据获取 DML 锁
  • 可以为临时表创建索引、视图和触发器

可以创建临时表，来保存仅在事务处理或会话期间存在的会话专用数据。

CREATE GLOBAL TEMPORARY TABLE 命令创建针对特定事务处理或针对特定会话的临时表。对于针对特定事务处理的临时表，数据仅在该事务处理期间存在；对于针对特定会话的临时表，数据仅在该会话期间存在。会话中的数据专用于该会话。每个会话只能查看和修改自己的数据。不能为临时表的数据获取 DML 锁。控制行持续时间的子句为：

  • ON COMMIT DELETE ROWS：用来指定仅在该事务处理内可看到这些行
  • ON COMMIT PRESERVE ROWS：用来指定在整个会话期间都能看到这些行

可以为临时表创建索引、视图和触发器，还可以使用导出和导入实用程序来导出和导入临时表的定义。但是，即使您使用 ROWS 选项，也不导出任何数据。所有会话都能看到临时表的定义。

 

设置 PCTFREE 和 PCTUSED

 

设置 PCTFREE

PCTFREE 值越高，可为数据库块内的更新提供的空间就越大。如果表存在下面两种情况，则应设置一个更高的值：

  • 某些列最初为 NULL，后来更新为某个值
  • 某些列由于更新，大小可能增加

PCTFREE 的值越高，块密度就越低，即每个块容纳的行数就越少。

上面的公式确保块中有足够的空闲空间供行增长使用。

设置 PCTUSED

设置 PCTUSED 以确保只有在具备足够空间来容纳一个平均大小的行时才将块返回到空闲列表中。如果空闲列表中的某个块没有足够的空间来插入一行，Oracle 服务器将查找空闲列表中的下一个块。直到找到具备足够空间的块或者到达列表的末尾，这种线性扫描才会结束。使用给定的公式可以增加找到具有所需空闲空间的块的概率，从而缩短扫描空闲列表的时间。

注：可以使用 ANALYZE TABLE 命令估算平均行大小的值。

 

 

行移植/行迁移和行链接

 

 

行移植

如果将 PCTFREE 设置为一个较低的值，则在一个块中可能没有足够的空间来容纳更新后增长的行。出现这种情况时，Oracle 服务器就会把整个行移到一个新块，并创建一个从原块指向新位置的指针。这个进程就称为“行移植”。在移植行时，与该行相关联的输入/输出 (I/O) 性能会降低，因为 Oracle 服务器必须扫描两个数据块才能检索该数据。

行链接

如果一个行过大而任何一个块都容纳不下，就会发生行链接。如果行包含的列太长，就可能发生这种情况。在这种情况下，Oracle 服务器将该行拆分成更小的数据块，称为“行片段”。每个行片段存储在一个块中，并带有检索和组合整行所需要的指针。如果可能，可通过选择较大的块大小或将一个表拆分成包含更少列的多个表来最大限度地减少行链接。

注：在 Oracle9i 数据库性能优化 课程中将详细介绍行移植和行链接方面的信息。

更改存储和块使用参数

 

ALTER TABLE hr.employees
PCTFREE 30
PCTUSED 50
STORAGE(NEXT 500K
MINEXTENTS 2
MAXEXTENTS 100);

部分存储参数和所有块使用参数可通过 ALTER TABLE 命令加以修改。

语法：

ALTER TABLE [schema.]table

{[ storage-clause ]

[ INITRANS integer ]

[ MAXTRANS integer]}

更改存储参数的影响：

可修改的参数及其修改的影响如下所示：

  • NEXT：当 Oracle 服务器为表分配另一个区时，就会使用新的值。此后的区大小将按 PCTINCREASE 增加。

更改存储参数的影响（续）：

  • PCTINCREASE：对 PCTINCREASE 的更改将记录到数据字典中。Oracle 服务器分配下一个区时，将使用它来重新计算 NEXT。假定有这样的情况：一个表有两个区，其中 NEXT=10K，PCTINCREASE=0。如果将 PCTINCREASE 更改为 100，则要分配的第三个区为 10K，第四个区为 20K，第五个区为 40K，以此类推。
  • MINEXTENTS：MINEXTENTS 的值可以更改为任何小于或等于表中的当前区数的值。它不会对表立即产生作用，但在截断表时将用到它。
  • MAXEXTENTS：MAXEXTENTS 的值可以设置为任何大于或等于表的当前区数的值。也可以将该值设置为 UNLIMITED。

限制：

  • 表的 INITIAL 值不能修改。
  • 指定的 NEXT 的值将舍入为块大小的一个倍数，该值大于或等于指定的值。

使用 Oracle Enterprise Manager 更改存储参数

  使用 Oracle Enterprise Manager 更改存储参数

从“OEM 控制台”(OEM Console)：

1.导航到“数据库”(Databases) >“方案”(Schema) >“表”(Table)。

2.展开方案名称。

3.选择该表。

4.修改“存储”(Storage) 页中的值。注意，不能用此方法修改最小区数和初始
事务处理数。

5.单击“应用”(Apply)。

 

手动分配区

ALTER TABLE hr.employees

ALLOCATE EXTENT(SIZE 500K

DATAFILE ‘/DISK3/DATA01.DBF’);

 

 

可能需要手动对区进行分配，以便：

  • 控制一个表的区在文件之间的分配
  • 在大量加载数据前避免表的动态扩展

语法

使用下面的命令将一个区分配给一个表：

ALTER TABLE [schema.]table

ALLOCATE EXTENT [ ([SIZE integer [K|M]]

[ DATAFILE ‘filename’ ]) ]

如果忽略 SIZE，Oracle 服务器将使用 DBA_TABLES 中的 NEXT_EXTENT 大小来分配区。

在 DATAFILE 子句中指定的文件必须属于该表所属的表空间。否则，该语句就会生成错误。如果未使用 DATAFILE 子句，则 Oracle 服务器将在包含该表的表空间中的一个文件中分配区。

注：手动分配区不会影响 DBA_TABLES 中的 NEXT_EXTENT 的值。执行此命令时，Oracle 服务器不会重新计算下一个区的大小。

 

重新组织非分区表

ALTER TABLE hr.employees

MOVE TABLESPACE data1;

  • 重新组织非分区表时，将保留表的结构，但不保留
    表的内容。
  • 用于将表移到另一个表空间中或者重新组织区。

不必运行导出或导入实用程序即可移动非分区表。此外，它允许更改存储参数。该特性在以下情况下很实用：

  • 将表从一个表空间移到另一表空间
  • 重新组织表以避免行移植

移动表后，必须重建索引以避免发生以下错误：

SQL> SELECT * FROM employees WHERE id=23;

select * from employees where id=23

*

ERROR at line 1：

ORA-01502：索引 ‘HR.EMPLOYEES_ID_PK’ 或该索引的分区处于不可用状态

 

截断表truncate

TRUNCATE TABLE hr.employees;

  • 截断一个表将删除表中所有行，从而释放已使用
    的空间。
  • 对应的索引将被截断。
  • 截断一个表将删除表中所有行，从而释放已使用
    的空间。
  • 对应的索引将被截断。

 

删除表

DROP TABLE hr.department

CASCADE CONSTRAINTS;

 

如果不再需要某个表，或者要对它进行重新组织，就可以将它删除。

语法：

使用下面的命令可删除表：

DROP TABLE [schema.] table

[CASCADE CONSTRAINTS]

删除一个表后，该表所使用的区将得以释放。如果这些区是相邻的，则可以在以后某个时间自动或手动将它们合并。

如果该表是外键关系中的父表，就必须使用 CASCADE CONSTRAINTS 选项。

注：CASCADE CONSTRAINTS 选项将在“维护数据完整性”一课中详细讨论。

  使用 Oracle Enterprise Manager 删除表

从“OEM 控制台”(OEM Console)：

1.导航到“数据库”(Databases) >“方案”(Schema) >“表”(Table)。

2.展开包含要删除的表的方案。

3.展开方案名称。

4.选择该表。

5.单击鼠标右键，从弹出的菜单中选择“删除”(Remove)。

6.选择“是”(Yes) 确认删除。

 

删除列

从表中删除列：

ALTER TABLE hr.employees

DROP COLUMN comments

CASCADE CONSTRAINTS CHECKPOINT 1000;

从每行中删除列长度和数据，释放数据块中的空间。
删除大表中的一列可能需要相当长的时间。

 

可以使用 Oracle 服务器从表的行中删除列。删除列可清除未使用但可能占用大量空间的列，而不必导出或导入数据及重新创建索引和约束。

删除一列可能要用相当长的时间，因为该列的所有数据都将从表中删除。

在 Oracle8i 以前的发行版中，无法删除表中的列。

删除列时使用检查点：

删除列可能需要很长时间，并且且需要大量的还原空间。从大型表中删除列时，可以指定检查点来尽量减少还原空间的使用。在幻灯片上的示例中，每 1,000 行出现一个检查点。在操作运行完成前，该表一直被标记为 INVALID。如果操作过程中例程失败，则该表在启动后仍将处于 INVALID 状态，因此该操作必须完成。

使用下面的语句可恢复中断的删除操作：

SQL> ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

如果表处于 VALID 状态，则使用此语句将生成错误。

 

使用 Oracle Enterprise Manager 删除列：

从“OEM 控制台”(OEM Console)：

1.导航到“数据库”(Databases) >“方案”(Schema) >“表”(Table)。

2.展开包含要删除的表的方案。

3.展开方案名称。

4.选择该表。

5.单击鼠标右键，从弹出的菜单中选择“编辑/查看详细资料”
(Edit/View Details)。

6.选择要删除的列。

7.单击鼠标右键，从弹出的菜单中选择“删除列”(Drop Column)。

 

使用 UNUSED 选项

  • 将列标记为未使用：
  • ALTER TABLE hr.employees

    DROP UNUSED COLUMNS CHECKPOINT 1000;

  • 删除未使用的列：
  • 继续执行删除列操作：
  • ALTER TABLE hr.employees

    DROP COLUMNS CONTINUE CHECKPOINT 1000;

 

除将列从表中删除以外，还可以先将列标记为“未使用”，以后再删除。因为没有删除数据，所以此操作不回收磁盘空间，因而具有速度比较快的优点。被标为“未使用”的列可在以后系统活动较少时从表中删除。

未使用的列就像不属于表一样。查询时看不到未使用列中的数据。此外，在执行 DESCRIBE 命令时，也不会显示这些列的名称和数据类型。用户可以添加与未使用的列同名的新列。

如果想删除同一表中的两列，则可先将列设置为“未使用”然后再删除。在删除两列时，表中的所有行都会更新两次；但如果将这些列设置为“未使用”然后再删除，则所有的行仅更新一次。

 

确定包含未使用列的表

要确定包含未使用列的表，可以查询视图 DBA_UNUSED_COL_TABS。该查询可获取包
含未使用列的表的名称及表中标记为未使用列的数目。下面的查询显示 HR 拥有的表 EMPLOYEES 含有一个未使用的列：

SQL > SELECT * FROM dba_unused_col_tabs;

OWNER TABLE_NAME COUNT

—– ————– ——

HR EMPLOYEES 1

要确定已完成一部分 DROP COLUMN 操作的表，可查询 DBA_PARTIAL_DROP_TABS
视图。

SQL > SELECT * FROM dba_partial_drop_tabs;

OWNER TABLE_NAME COUNT

—– ————– ——

no rows selected

删除列的限制

不能执行下列操作：

  • 从对象类型表中删除列
  • 从嵌套表中删除列
  • 删除一个表中的所有列
  • 删除分区键列
  • 从 SYS 拥有的表中删除列
  • 从按索引组织的表中删除主键列

如果有未使用但未删除的 LONG 或 LONG RAW 列，将无法向表中添加 LONG 或
LONG RAW 列。（即使表的说明显示没有 LONG 或 LONG RAW 列也是如此。）

 

获取表信息

可以通过查询以下视图来获取有关表的信息：

  • DBA_TABLES
  • DBA_OBJECTS

有关表的信息可从数据字典中获取。要获取 HR 拥有的所有表的数据对象编号和表头位置，请使用下面的查询：

SQL > SELECT table_name FROM dba_tables WHERE owner = ‘HR’;

TABLE_NAME

——————-

COUNTRIES

DEPARTMENTS

DEPARTMENTS_HIST

EMPLOYEES

EMPLOYEES_HIST

JOBS

JOB_HISTORY

LOCATIONS

REGIONS

 

SQL> SELECT object_name, created

2 FROM DBA_OBJECTS

3 WHERE object_name like ‘EMPLOYEES’

4 AND owner = ‘HR’;

OBJECT_NAME CREATED

———– ———

EMPLOYEES  16-APR-01

 

练习 5：管理表

1  以用户 SYSTEM 的身份，为目前正在使用的订单输入系统创建下列表。表和列
显示如下：

注：在使用 OEM 时，一定要将 DATE_OF_DELY 设置为 NULL。

请注意，在表 ORDERS 中插入的行并不包含 DATE_OF_DELY 的值，该信息
将在履行订单后更新。请使用表空间 USERS。可以使用缺省的存储设置。

2  运行脚本 lab11_02.sql，将行插入到表中。

  3  查找哪些文件和块包含订单表的行。

提示：查询数据字典视图 DBA_EXTENTS。

4  检查 ORDERS 表使用的区数。

  5  使用缺省大小为 ORDERS 表手动分配一个区，并确认该区已按照指定添加。

6  再创建一个表 ORDERS2，作为 USERS 表空间中 ORDERS 表的副本，并且   MINEXTENTS 等于 10。检查是否已使用指定的区数创建该表。

7  截断 ORDERS 表而不释放空间，检查区数以验证区未被回收。

8  截断 ORDERS2 表并释放空间。现在该表有多少个区？

  9  运行脚本 lab11_09.sql，向 ORDERS2 表中插入一些行。

10  查看 ORDERS2 表的列。然后将 DATE_OF_DELY 列标记为 UNUSED。再次查看   ORDERS2 表的列。结果如何？

  11  删除未使用的列 DATE_OF_DELY。

12  删除 ORDERS2 表。

 

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *