Oracle学习笔记一 10-11

10

管理Undo Data

有两种方法来管理撤消数据:

        —自动撤销管理

        —手动撤销管理

在以前的版本中，术语“撤销”被称为“回滚”。

Redo & Undo

Undo段的类型

SYSTEM:用于SYSTEM表空间中的对象

Non-SYSTEM:用于其他表空间中的对象:

        —自动模式:需要配置UNDO表空间

        —手动模式:

                Private:由单个实例获取

                Public:由任何实例获取

Deferred:在表空间脱机时使用立即的，暂时的或为了恢复的

Auto Undo Mgmt:概念

Undo数据使用Undo表空间管理。

为每个实例分配一个UNDO表空间为实例的工作负载提供足够的空间。

Oracle服务器在undo表空间中自动维护undo数据。

自动管理Mgmt: Config

在初始化文件中配置两个参数:

——UNDO_MANAGEMENT

——UNDO_TABLESPACE

至少创建一个UNDO表空间。

AUM:初始化参数

UNDO_MANAGEMENT:指定系统应该使用AUTO还是MANUAL模式UNDO_TABLESPACE:指定要使用的UNDO表空间

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE-UNDOTBS

AUM: UNDO Tablespace

使用CREATEDATABASE命令创建UNDO表空间:

CREATE DATABASE db01

UNDO TABLESPACE undo1

DATAFILE '/u01/oradata/undoldb01.dbf' SIZE 20MAUTOEXTEND ON

或者稍后使用create UNDO TABLESPACE命令创建:

CREATE UNDO TABLESPACE undo1
DATAFILE'/u01/oradata/undoldb01.dbf
SIZE 20M;

AUM :Altering an UNDO TS

ALTER TABLESPACE命令可以修改UNDO表空间。

在UNDO表空间中添加另一个数据文件的示例如下:

ALTER TABLESPACE undotbs
ADD DATAFILE '/u01/oradata/undotbs2.dbf
SIZE 30M
AUTOEXTEND ON;

AUM :Switching UNDO TS（切换UNDO TS）

您可以从使用一个UNDO表空间切换到另一个。

一个数据库一次只能被分配一个UNDO表空间。

一个实例中可以存在多个UNDO表空间，但只有一个是活动的。

使用ALTER SYSTEM命令在UNDO表空间之间进行动态切换。

ALTER SYSTEM SET UNDO TABLESPACE=UNDOTBS2;

AUM : Dropping an UNDO TS（删除）

DROP TABLESPACE命令用来删除UNDO表空间。

DROP TABLESPACE UNDOTBS2；

只有当UNDO表空间当前没有被任何实例使用时，它才能被删除。

删除一个活动的UNDO表空间。

—切换到新的UNDO表空间。

—在所有当前事务完成后删除表空间。

AUM:Other Parameters（其他参数）、

UNDO_SUPPRESS_ERRORS参数:

设置为TRUE，此参数在试图在AUTO模式下执行手动操作时抑制错误。

UNDO_RETENTION参数:

该参数用于控制为了一致性读而保留的undo数据量。

Undo Data Statistics

AUM :Sizing an UNDO TS（调整UNDO TS大小）

确定UNDo表空间的大小需要三个信息:

(UR) UNDO_RETENTION，单位为秒(UPS)每秒产生的撤消数据块数量

(DBS)开销根据扩展和文件大小而变化(db_block_size)

AUM :Altering an UNDO TS（添加UNDO TS空间）

ALTER TABLESPACE命令可以修改UNDO表空间。

在UNDO表空间中添加另一个数据文件的示例如下:
 

ALTER TABLESPACE undotbs

ADD DATAFILE'/u01/oradata/undotbs2.dbf

SIZE 30M

AUTOEXTEND ON;

Undo Data Statistics

AUM :Undo Quota（配额）

长事务和不正确编写的事务会消耗宝贵的资源。

使用undo配额，可以对用户进行分组，并为组分配最大的undo空间限制。

UNDO_POOL，一个资源管理器指令，定义资源组允许的空间量。

当一个组超过其限制时，该组不可能有新的事务，直到撤消空间被正在完成或中止的当前事务释放。

Get Undo Segment Info

可以通过以下视图查询undo段的信息:

-DBA_ROLLBACK_SEGS

动态性能视图

- VSROLLNAME

- VSROLLSTAT

- VSUNDOSTAT

- VSSESSION

- VSTRANSACTION

11

存储用户数据

Oracle内置数据类型

General Information

下面是一些关于表的一般信息

不管它们的类型是什么:一个表最多可以有1000列，尽管我不建议使用包含最大列数的设计，除非有一些迫切的需要。表的列数远少于1000列时效率最高。Oracle内部会将超过254列的行存储在彼此指向的单独行块中，并且必须重新组装以产生整个行映像。

一个表实际上可以有无限的行数，尽管您可能会遇到其他限制，以防止这种情况发生。例如，通常一个表空间最多可以有1,022个文件(尽管Oracle 10g中有新的BIGFILE表空间，也可以使您超过这些文件大小限制)。假设您有32GB的文件——也就是说，每个表空间有32,704GB。这将是2,143,289,344个块，每个块的大小为16KB。您可能能够容纳160行，每个块的大小在80到100字节之间。这将给您342,926,295,040行。但是，如果对表进行分区，则可以轻松地将这个数字乘以多次。例如，考虑一个有1,024个散列分区的表，即1024 × 342,926,295,040行。这是有限制的，但在接近这些数字之前，你会遇到其他实际限制。

一个表可以有多少个索引，就有多少列的排列(以及这些列上的函数的排列)。随着基于函数的索引的出现，理论上可以创建的索引的真实数量变得无限!但是，实际的限制将再次限制您将创建和维护的索引的实际数量。

表的数量没有限制，即使在单个数据库中也是如此。然而，实际限制将使这个数字保持在合理的范围内。您不会有数百万个表(创建和管理这么多表是不切实际的)，但您可能有数千个表。

ROWID Format（唯一确定id的标识符）

Restricted ROWID Format(不做考虑 9e之后淘汰了)

Locating a Row Using ROWID（使用ROWID定位行）

ROWID是一种数据类型，可以与表中的其他列一起查询。它具有以下特点:

ROWID是数据库中每一行的唯一标识符。

ROWID没有显式地存储为列值。

虽然ROWID不直接给出行的物理地址，但它可以用来定位行。

ROWID提供了访问表中行的最快方法。

rowid存储在索引中，以指定具有一组给定键值的行。

由于一个段只能驻留在一个表空间中，因此Oracle服务器可以通过使用数据对象号来确定包含一行的表空间。

表空间内的相对文件号用于定位文件，块号为用于定位包含该行的块，行号用于定位该行的行目录项。

行目录条目可用于定位行开头。因此，ROWID可用于定位数据库中的任何行。

Structure of a Row

行数据作为变长记录存储在数据库块中。一行的列通常按照定义的顺序存储，任何末尾的NULL列都不存储。

注意:对于非尾空列，列长度需要单个字节。表中的每一行都有:

        行头:用于存储该行的列数、链接信息和行锁状态

        行数据:对于每个列，Oracle服务器存储列长度和值(如果列需要超过250字节的存储空间，则需要一个字节来存储列长度，在这种情况下，将使用三个字节来存储列长度)。列值紧随列长度字节之后存储。)

相邻行之间不需要任何空格。块中的每一行在行目录中都有一个槽。目录槽指向该行的开头。

创建表

现在您已经知道了如何查看给定CREATE TABLE语句中可用的大多数选项，那么对于堆表来说，哪些选项是需要注意的重要选项呢?在我看来，有两个是ASSM，四个是MSSM:

自由列表:仅限sms。每个表在一个自由列表上管理它在堆中分配的块。一个表可以有多个自由列表。如果您预计许多并发用户将大量插入到表中，那么配置多个自由列表可以对性能产生重大的积极影响(以可能的额外存储为代价)。参考前面的讨论和“FREELISTS”一节中的示例，了解这种设置对性能的影响。

PCTFREE:包括ASSM和MSSM。在INSERT过程中，一个块可以被填满多少的度量。如前所述，这用于根据块当前的满程度来控制是否可以将一行添加到块中。此选项还用于控制由后续更新引起的行迁移，需要根据表的使用方式进行设置。

创建表的规则

将表放置在单独的表空间中。

使用本地管理的表空间来避免碎片空间。

为表使用少量标准区段大小以减少表空间碎片。

创建临时表

        使用GLOBAL TEMPORARY子句创建:

create using temporary table
hr.employees_temp
as selectt * from hr.employess;

表仅在事务或会话期间保留数据。

不从数据上获取DML锁。

您可以在临时表上创建索引、视图和触发器。

临时表用于在事务或会话期间保存中间结果集。保存在临时表中的数据只对当前会话可见：

        其他会话不会看到任何其他会话的数据，即使当前会话提交了数据。对于临时表来说，多用户并发性也不是问题，因为一个会话永远不能通过使用临时表来阻塞另一个会话。即使我们“锁定”临时表，它也不会阻止其他会话使用它们的临时表。正如我们在第9章中观察到的，临时表比普通表产生的重做要少得多。但是，由于它们必须为所包含的数据生成撤销信息，因此它们将生成一定数量的重做。更新和删除将产生最大的金额;插入并选择最小的数量。

临时表

Oracle的临时表与其他关系数据库中的临时表类似，主要的区别是它们是“静态”定义的。每个数据库创建一次，而不是数据库中的每个存储过程创建一次。它们始终存在—它们将作为对象存在于数据字典中，但是在会话将数据放入它们之前，它们始终显示为空。由于它们是静态定义的，因此可以创建引用临时表的视图，创建使用静态SQL引用临时表的存储过程，等等。

临时表会产生最少的重做，但它们仍然会产生一些重做，并且没有办法禁用它。重做是为回滚数据生成的，在大多数典型使用中，它可以忽略不计。如果只对临时表进行INSERT和SELECT操作，那么生成的重做量将不会明显。只有当你大量地删除或更新临时表时，你才会看到大量的重做生成。

CBO使用的统计数据可以谨慎地在临时表上生成;但是，可以使用DBMS_STATS包在临时表上设置更好的统计数据猜测集，或者由优化器在硬解析时使用动态采样动态收集统计数据。

设置PCTFREE和PCTUSED

后者舍弃

行迁移和链接

行迁移和链接

行迁移如果PCTFREE设置为较低的值，则块中可能没有足够的空间来容纳由于更新而增长的行。当这种情况发生时，Oracle服务器将整个行移动到一个新的块，并留下一个从原始块到新位置的指针。这个过程被称为行迁移。当迁移一行时，与该行相关的输入/输出(I/O)性能会下降，因为Oracle服务器必须扫描两个数据块来检索数据。

行链接

当行太大而无法放入任何块时，就会发生行链接。当行包含非常长的列时，可能会发生这种情况。在这种情况下，Oracle服务器将行分成更小的块，称为行块。每个行片段都存储在一个块中，以及检索和组装整个行所需的指针。如果可能的话，可以通过选择更大的块大小或将表分成具有更少列的多个表来最小化行链。

结构模型

存储和块参数

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

改变SP的影响

可修改的参数及修改的影响如下:

NEXT:当Oracle服务器为表分配另一个扩展时，新的值将被使用。后续的区段大小将通过PCTINCREASE增加。

PCTINCREASE:在数据字典中注册PCTINCREASE的更改。它是用于在Oracle服务器分配下一个区段时重新计算NEXT。考虑一个有两个区段的表的NEXT=10K和PCTINCREASE=0的情况。如果PCTINCREASE更改为100，则要分配的第三个区段将是10 KB第四个区段为20 KB，第五个区段为40 KB，依此类推。

MINEXTENTS: MINEXTENTS的值可以修改为小于或等于表中当前区段数的任何值。它不会立即对表产生影响，但会在表被截断时使用。

MAXEXTENTS: MAXEXTENTS的值可以设置为等于或大于表当前区段数的任何值。该值也可以设置为UNLIMITED。

手动分配区段

ALTER TABLE hr.employees
ALLOCATE EXTENT(SIZE 500k
DATAFILE '/DISK3/DATA01.DBF');

可能需要手动分配区段:

        控制表的区段跨文件的分布

        在批量加载数据之前，以避免表的动态扩展

如果省略SIZE, Oracle服务器将使用DBA_TABLES中的NEXT EXTENT大小来分配EXTENT。

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

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

非分区表重组

ALTER TABLE hr.employees
MOVE TABLESPACE date1;

重组非分区表时，保留其结构，但不保留其内容。

它用于将表移动到不同的表空间或rgz

无需运行Export或Import实用程序就可以移动非分区表。在此外，它还允许更改存储参数。这在以下情况下很有用:

        将表从一个表空间移动到另一个表空间

        重新组织表以消除行迁移

Truncating a Table

截断表是指删除表中的所有行，从而将其清空。与DELETE语句删除单个行不同，TRUNCATE TABLE语句通过释放表使用的数据页面来快速删除表中的所有数据。

TRUNCATE TABLE hr.employees;

使用该命令的效果如下:

        删除表中的所有行。

        由于TRUNCATE TABLE是一个DDL命令，因此不会生成undo数据，并且隐式地提交命令。

        相应的索引也被截断。

        被外键引用的表不能被截断。

        使用此命令时，删除触发器不会触发。

截断表将删除表中的所有行并释放已使用的空间。

相应的索引被截断。

截断表是不可撤销的，并且它会重置表的标识值并释放相关的存储空间。因此，在使用此操作时，务必谨慎，并确保如果需要，有适当的表数据备份

删除表

DROP TABLE hr.departments
CASCADE CONSTRAINTS;

删除表时，表所使用的区段将被释放。如果它们是连续的，则可以在稍后的阶段自动或手动合并它们。

如果表是外键关系中的父表，则CASCADE CONSTRAINTS选项是必需的。

注意:有关级联约束的详细信息，请参阅“维护数据完整性”一课。

删除一列

        从表中删除一列:

ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAIMTS CHECKPOINT 1000;

从每行删除列长度和数据，从而释放数据块中的空间。

在大型表中删除列需要花费相当多的时间。

Oracle服务器允许从表的行中删除列。删除列可以清除未使用的和可能需要空间的列，而无需导出或导入数据，并重新创建索引和约束。

删除列可能会花费大量时间，因为该列的所有数据都将从表中删除。

在Oracle8i之前，不可能从表中删除列。

删除列可能非常耗时，并且需要大量的撤消空间。在从大型表中删除列时，可以指定检查点以最小化撤销空间的使用。在幻灯片中的示例中，检查点每1000行出现一次。在操作完成之前，表被标记为无效。参考DBA_OBJECTS视图中的STATUS列。如果实例在操作过程中失败，则表在启动时保持INVALID状态，并且必须完成操作。

使用以下语句恢复中断的删除操作:

SQL> ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

如果表处于VALID状态，使用此方法将产生一个错误。

重命名列

        从表中重命名列:

ALTER TABLE hr.employees
RENAME COLUMN hire_date
TO start_date;

在Oracle9i Database Release 2中，对于属于关系表的列，可以使用重命名列的功能。对于具有连接索引的表，不能重命名列，在允许重命名之前，必须先删除索引。

重命名列后，函数索引和检查约束仍然有效，但是视图、触发器、域索引、函数、过程和包将失效。如果由于列名更改而导致上述重新验证失败，则必须使用新名称解决问题。

允许重命名具有物化视图的表和涉及复制的表。如果随后物化视图中出现错误，则必须修改物化视图以解决问题。语法是:

SQL>alter table [schema.]table_name
 2 rename column old_column_name
 3 TO new_column_name; 

使用 UNUSERD 标签删除列

可以将列标记为未使用，然后再将其删除，而不是从表中删除列。这样做的优点是相对较快，因为它不会因为没有删除数据而回收磁盘空间。标记为未使用的列可以在以后系统活动较少时从表中删除。

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

在删除列之前将其设置为未使用的一个示例是，当您想要删除同一表中的两个列时。删除两列时，表中的所有行都会更新两次。但是，如果将列设置为未使用，然后删除这些列，则这些行只更新一次。

命令：

#将列标记为未使用
alter table hr.employees
set unused column comments cascade
constraints;

#删除未使用的列
alter table hr.employees
drop unused colums checkpoint 1000;

#继续删除列操作
alter table hr.employees
drop columns continus checkpoint 1000;

获取表数据字典

可以通过查询以下视图获取表的信息:

DBA_TABLES

DBA_OBJECTS