Oracle Database 10 g : 为 DBA 提供的最佳前 20 位的特性（十二到十五）

Oracle Database 10 g : 为 DBA 提供的最佳前 20 位的特性（十二）
作者 Arup Nanda 来源： OTN

第 12 周
物化视图
利用强制查询重写和新的强大的调整顾问程序 — 它们使您不再需要凭猜测进行工作 — 的引入，在 10 g 中管理物化视图变得更加容易
物化视图 (MV) — 也称为快照 — 一段时间来已经广泛使用。 MV 在一个段中存储查询结果，并且能够在提交查询时将结果返回给用户，从而不再需要重新执行查询 — 在查询要执行几次时（这在数据仓库环境中非常常见），这是一个很大的好处。物化视图可以利用一个快速刷新机制从基础表中全部或增量刷新。
假定您已经定义了一个物化视图，如下：
create materialized view mv_hotel_resv
refresh fast
enable query rewrite
as
select distinct city, resv_id, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id';
您如何才能知道已经为这个物化视图创建了其正常工作所必需的所有对象？在 Oracle 数据库 10 g 之前，这是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 过程来判断的。这些过程（在 10 g 中仍然提供）非常简要地说明一种特定的功能 — 如快速刷新功能或查询重写功能 — 可能用于上述的物化视图，但不提供如何实现这些功能的建议。相反，需要对每一个物化视图的结构进行目视检查，这是非常不实际的。
在 10 g 中，新的 DBMS_ADVISOR 程序包中的一个名为 TUNE_MVIEW 的过程使得这项工作变得非常容易：您利用 IN 参数来调用程序包，这构造了物化视图创建脚本的全部内容。该过程创建一个顾问程序任务 (Advisor Task) ，它拥有一个特定的名称，仅利用 OUT 参数就能够把这个名称传回给您。
下面是一个例子。因为第一个参数是一个 OUT 参数，所以您需要在 SQL*Plus 中定义一个变量来保存它。
SQL> -- 首先定义一个变量来保存 OUT 参数
SQL> var adv_name varchar2(20)
SQL> begin
2 dbms_advisor.tune_mview
3 (
4 :adv_name,
5 'create materialized view mv_hotel_resv refresh fast enable query rewrite as
select distinct city, resv_id, cust_name from hotels h,
reservations r where r.hotel_id = h.hotel_id');
6* end;
现在您可以在该变量中找出顾问程序的名称。
SQL> print adv_name
ADV_NAME
-----------------------
TASK_117
接下来，通过查询一个新的 DBA_TUNE_MVIEW 来获取由这个顾问程序提供的建议。务必在运行该命令之前执行 SET LONG 999999 ，因为该视图中的列语句是一个 CLOB ，默认情况下只显示 80 个字符。
select script_type, statement
from dba_tune_mview
where task_name = 'TASK_117'
order by script_type, action_id;
下面是输出：
SCRIPT_TYPE STATEMENT
-------------- ------------------------------------------------------------
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
ROWID, SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV REFRESH FAST
WITH ROWID ENABLE QUERY REWRITE AS SELECT
ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
ARUP.RESERVATIONS.HOTEL_ID GROUP BY
ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY
UNDO DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
SCRIPT_TYPE 列显示建议的性质。大多数行将要执行，因此名称为 IMPLEMENTATION 。如果接受，则需按照由 ACTION_ID 列指出的特定顺序执行建议的操作。
如果您仔细查看这些自动生成的建议，那么您将注意到它们与您自己通过目视分析生成的建议是类似的。这些建议合乎逻辑；快速刷新的存在需要在拥有适当子句（如那些包含新值的子句）的基础表上有一个 MATERIALIZED VIEW LOG 。 STATEMENT 列甚至提供了实施这些建议的确切 SQL 语句。
在实施的最后一个步骤中，顾问程序建议改变创建物化视图的方式。注意我们的例子中的不同之处：将一个 count(*) 添加到了物化视图中。因为我们将这个物化视图定义为可快速刷新的，所以必须有 count(*) ，以便顾问程序纠正遗漏。
TUNE_MVIEW 过程不仅在建议方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，还为创建相同的物化视图指出了更容易和更高效的途径。有时，顾问程序可以实际推荐多个物化视图，以使查询更加高效。
您可能会问，如果任何一个经验丰富的 DBA 都能够找出 MV 创建脚本中缺了什么，然后自己纠正它，那这还有什么用？嗯，顾问程序正是用来完成这项工作的：它是一位经验丰富、高度自觉的自动数据库管理员，它可以生成能与人的建议相媲美的建议，但有一个非常重要的不同之处：它免费工作，并且不会要求休假或加薪。这一好处使高级 DBA 解放出来，将日常的工作交给较低级的 DBA ，从而允许他们将其专业技能应用到更具有战略意义的目标上。
您还可以将顾问程序的名称作为值传递给 TUNE_MVIEW 过程中的参数，这将使用该名称而非系统生成的名称生成一个的顾问程序。
更容易的实施
既然您可以看到建议，那么您可能想实施它们。一种方式是选择列 STATEMENT ，假脱机到一个文件，然后执行该脚本文件。一种更容易的替代方法是调用附带的封装过程：
begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/
该过程调用假定您已经定义了一个目录对象，例如：
create directory mvtune_outdir as '/home/oracle/mvtune_outdir';
对 dbms_advisor 的调用将在 /home/oracle/mvtune_outdir 目录中创建一个名为 mvtune_script.sql 的文件。如果您查看一下这个文件，您将看到：
Rem SQL Access Advisor:Version 10.1.0 .1 - Production
Rem
Rem Username:ARUP
Rem Task:TASK_117
Rem Execution date:
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID C2, ARUP.HOTELS.CITY
C3, COUNT(*) M1 FROM ARUP.RESERVATIONS, ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID
= ARUP.RESERVATIONS.HOTEL_ID GROUP BY ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY;
whenever sqlerror EXIT SQL.SQLCODE
begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/
这个文件包含了您实施建议所需的一切，从而为您省去了相当大的手动创建文件的麻烦。这个自动数据库管理员又一次能够为您完成工作。
重写或退出！
至此，您一定意识到了查询重写特性有多重要和多有用。它显著地减少了 I/O 和处理，并能够更快地返回结果。
让我们基于上述例子假定一种情况。用户执行以下查询：
Select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
执行状态显示以下内容：
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
注意 consistent gets 的值，它为 6 — 一个非常低的值。这个结果基于的事实是，重写了查询来使用在三个表上创建的两个物化视图。选择不是从表中进行的，而是从物化视图中进行，从而消耗了更少的资源（如 I/O 和 CPU ）。
但如果查询重写失败了，那该怎么办？它失败的原因可能有以下几种：如果初始化参数 query_rewrite_integrity 的值被设为 TRUSTED ，且 MV 的状态是 STALE ，那么将不会重写该查询。您可以通过在查询之前在会话中设定这个值来模拟这个过程。
alter session set query_rewrite_enabled = false;
在这条命令之后，说明计划 (EXPLAIN PLAN) 显示是从所有三个表中而不是从 MV 中作出的选择。执行状态现在显示：
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
注意 consistent gets 的值：它从 6 猛增到了 16 。在实际情况下，这个结果可能无法接受，因为无法提供所需的额外资源，因此您可能想自己重写查询。在这种情况下，您可以确保如果而且只有在查询被重写的情况下，才允许进行查询。
在 Oracle9 i 数据库和更低版本中，决策是单向的：您可以禁用查询重写，但不能禁用基础表访问。不过 Oracle 数据库 10 g 提供了一种机制 — 通过一个特殊的提示 REWRITE_OR_ERROR 来实现这一目的。上述查询将利用该提示写为：
select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
注意现在的错误消息。
from hotels h, reservations r, trans t
*
ERROR at line 2:
ORA-30393:a query block in the statement did not rewrite
ORA-30393 是一种特殊类型的错误，它表示无法重写语句来使用 MV ；因此，语句失败。这种防出错功能将潜在地防止运行时间很长的查询独占系统资源。不过，请注意一个潜在的陷阱：如果 MV 之一（而不是全部）可用于重写查询，那么查询将成功。因此如果能够使用 MV_ACTUAL_SALES 但不能使用 MV_HOTEL_RESV ，那么查询将被重写，错误将不会出现。在这种情况下，执行计划将看起来像这样：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1 0 SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2 1 HASH JOIN (Cost=10 Card=80 Bytes=2080)
3 2 MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE) (Cost=2 Card=8 Bytes=104)
5 4 INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE)) (Cost=1 Card=8)
6 3 SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7 6 TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE) (Cost=3 Card=80 Bytes=480)
8 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560)
查询的确使用 MV_ACTUAL_SALES 而不是 MV_HOTEL_RESV ；因而， HOTELS 和 RESERVATIONS 表被访问。这种方法（特别是后一个表的全表扫描），无疑将使用更多的资源 — 在设计查询和 MV 时您将注意到这种情况。
虽然您可以始终利用资源管理器来控制资源使用情况，但使用该提示将防止执行查询，即使在调用资源管理器之前。资源管理器根据优化器统计数据估计所需的资源，因此是否存在足够准确的统计数据将影响这个过程。不过，重写或错误特性将停止表访问，而不管统计数据如何。
说明计划更好地进行说明
在上一个例子中，请注意说明计划输出中的行：
MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)
这种访问方法 — MAT_VIEW REWRITE — 是新增的；它显示正在访问 MV ，而非表或段。该过程立即告诉您表或 MV 是否被使用 — 即使名称没有表明段的本质。
结论
在 10 g 中，通过引入强大的新调整顾问程序 — 它们能够告诉您许多有关 MV 的设计的信息，从而使您不再需要凭猜测进行工作，管理 MV 变得更加容易。我尤其喜欢能够生成一个完整的脚本的调整建议，这种脚本可以快速实施，从而显著地节省时间和精力。强制重写或退出查询的能力在决策支持系统中会非常有帮助 — 在这种系统中必须保留资源，并且未重写的查询将不允许在数据库内随意运行。
Oracle Database 10 g : 为 DBA 提供的最佳前 20 位的特性（十四）
作者 Arup Nanda 来源： OTN
第 14 周
虚拟专用数据库
五种类型的策略、列相关策略以及列屏蔽使得 VPD 成为 DBA 的安全工具箱中一种功能更加强大的工具
虚拟专用数据库 (VPD) 也称为细粒度访问控制，它提供强大的行级安全功能。它是在 Oracle8 i 中推出的，已经受到广泛的欢迎，并且在从教育软件到金融服务等各种应用程序得到采用。
VPD 的工作方法是，通过透明地更改对数据的请求，基于一系列定义的标准向用户提供表的局部视图。在运行时，所有查询都附加了谓词，以便筛选出准许用户看到的行。例如，如果只允许用户查看帐户管理员 SCOTT 的帐户，则 VPD 设置自动地将查询：
select * from accounts;
重写为：
select * from accounts
where am_name = 'SCOTT';
DBA 在表 ACCOUNTS 上设置了一项安全策略。该策略具有一个相关函数，称为 policy function ，它返回一个用作谓词的字符串 where am_name = 'SCOTT' 。如果您不熟悉该特性的全部功能，我建议您阅读 Oracle 杂志 的文章 “ 利用 VPD 保持信息的私密性 ” 。
策略类型
生成谓词所需的重复分析是一种在某些情况下可以进行修整的开销。例如，在大部分实际情况中，谓词并不象 am_name = 'SCOTT' 那样是静态的；它基于用户的身份、用户的权限级别、用户向哪个帐户管理员进行报告等情况 , 可能更具有动态性。由策略函数创建并返回的字符串可能会具有很强的动态性，而为了保证其结果， Oracle 必须每次重新执行策略函数，既浪费资源又降低性能。在这种类型的策略中，谓词每次执行时可能会有很大的差别，该策略称为 “ 动态 ” 策略，在 Oracle9 i 数据库以及以前的版本中已经提供了这种策略。
除了保留动态策略之外， Oracle 数据库 10 g 还基于谓词的构造推出了几种新类型的策略，为提高性能提供了更好的控制： context_sensitive 、 shared_context_sensitive 、 shared_static 和 static 。现在，让我们来了解每种策略类型的意义以及如何在适当的场合中使用它们。
动态策略。 为保持向后兼容性， 10 g 中的默认策略类型为 “dynamic” — 正如 Oracle9 i 中一样。在这种情况下，对于每行以及每位用户，在每次访问表时都对策略函数进行重新求值。让我们来详细分析策略谓词：
where am_name = 'SCOTT'
忽略掉 where 子句，谓词就具有两个不同的部分：在等式操作符之前的部分 ( am_name ) 和等式操作符之后的部分 ( 'SCOTT' ) 。在大多数情况下，后面的部分更象是变量，因为它是由用户的数据提供的（如果用户是 SCOTT ，则其值为 'SCOTT' ）。在等号前面的部分是静态的。因此，即使函数不必为生成适当的谓词而对每行求出策略函数的值，由于了解前面部分的静态性以及后面部分的动态性，也可以提高性能。在 10 g 中，可以在 dbms_rls.add_policy 调用中使用 "context_sensitive" 类型的策略作为参数来实现这种方法：
policy_type => dbms_rls.context_sensitive
在另一个示例中，我们有一个称为 ACCOUNTS 的表，它拥有几列，其中一列是 BALANCE ，表示帐户余额。假设允许某个用户查看低于某特定余额的帐户，而该余额由应用程序上下文所决定。我们并不在策略函数中将此余额值固定，而是 3 是根据应用程序上下文确定，如：
create or replace vpd_pol_func
(
p_schema in varchar2,
p_table in varchar2
)
return varchar2
is
begin
return 'balance < sys_context(''vpdctx'', ''maxbal'')';
end;
应用程序上下文 VPDCTX 的属性 MAXBAL 可以在会话的前期设定，而函数在运行时可以容易地获得该数值。
请仔细注意该示例。谓词有两部分：小于号之前的部分和之后的部分。之前的部分是 “balance” 一词，它是文字符。后面的部分从某种程度而言是静态的，因为应用程序上下文变量在改变之前一直是常量。如果应用程序上下文属性不变，则整个谓词是常量，因此不需要重新执行函数。如果策略类型定义为对上下文敏感，则 Oracle 数据库 10 g 可以识别此情况以用于优化。如果在会话期间没有发生会话上下文的变化，则不重新执行该函数，从而显著提高了性能。
静态策略。 有时业务操作可以确保谓词更加静态。例如，在上下文敏感的策略类型示例中，我们将用户所见的最大余额定义为一个变量。当 web 应用程序中的 Oracle userid 由许多 web 用户共享，并且应用程序基于这些用户的权限来设置该变量（应用程序上下文）时，这种方法很有用。因此， web 用户 TAO 和 KARTHIK 都是以用户 APPUSER 连接到数据库的，二者可以在其会话中拥有两个不同的应用程序上下文的值。此时 MAXBAL 的值并不依赖于 Oracle userid ，而是依赖 TAO 和 KARTHIK 各自的会话。
在静态策略的情况下，谓词更具有可预测性，其说明如下。
LORA 和 MICHELLE 分别是 Acme Bearings 和 Goldtone Bearings 的帐户管理员。当他们连接数据库时，他们使用自己的 id ，并且只应该看到属于他们的那些行。在 Lora 方面，谓词变成 where CUST_NAME = 'ACME' ；而对于 Michelle ，则是 where CUST_NAME = 'GOLDTONE' 。在这里，谓词依赖于他们的 userid ，因此他们所创建的任何会话在应用程序上下文中始终具有相同的值。
10 g 可以利用这种情况，在 SGA 中对谓词进行高速缓存，并在会话中重用该谓词，而不必重新执行策略函数。策略函数类似于以下形式：
create or replace vpd_pol_func
(
p_schema in varchar2,
p_table in varchar2
)
return varchar2
is
begin
return 'cust_name = sys_context(''vpdctx'', ''cust_name'')';
end;
而策略定义为：
policy_type => dbms_rls.static
这种方法确保策略函数只执行一次。即使应用程序上下文在会话中改变，也从不重新执行该函数，使得此过程的速度非常快。
建议将静态策略用于在几个用户中托管应用程序的情况。在这种情况下，单个数据库拥有几个用户的数据。当每个用户登录时，登录后触发器可以设置用于策略函数的应用程序上下文的值，以便快速生成谓词。
但是，将策略定义为静态也是一把双刃剑。在以上的示例中，我们假设应用程序上下文属性 VPDCTX.CUST_NAME 的值在会话中不改变。如果这种假设不正确，将会怎样呢？如果该值改变，策略函数将不会执行，因此在谓词中将不会使用新值，而返回 错误 的结果！因此，在将策略定义为静态时要非常小心；您必须绝对确信该值不会改变。如果您不能作这种假设，则最好将策略定义为对上下文敏感。
共享策略类型。 为了重用代码并最大限度地利用已经分析过的代码，您可以决定为几个表使用通用的策略函数。例如，在上述示例中，我们可能对于不同类型的帐户拥有不同的表 — SAVINGS 和 CHECKING — 但是规则仍然是相同的：限制用户查看余额超过其授权范围的帐户。这种情况要求为 CHECKING 和 SAVINGS 表上的策略使用统一的函数。该策略创建为 context_sensitive 。
假设事件按如下顺序发生：
1. 连接会话
2. 设置应用程序上下文
3. select * from savings;
4. select * from checking;
即使应用程序上下文在第 3 步与第 4 步之间没有改变，策略函数也会重新执行，因为现在所选择的表已经不同。这不是我们所希望的情况，因为策略函数相同，不需要重新执行该函数。
10 g 中的新功能是能够在对象间共享策略。在上述示例中，您可以将这些策略的策略类型定义为：
policy_type => dbms_rls.shared_context_sensitive
将策略声明为 "shared" 可以在以上所示的情况中不再执行该函数，从而提高了性能。
选择性的列
现在设想一种情况，只有在选择了特定列时才会应用 VPD 策略。在上述示例的表 ACCOUNTS 中，各行如下所示：
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
2 TOM CONNOPHY 2000
3 ISRAEL D 1500
不允许 Michelle 查看余额超过 1600 的帐户。当她执行类似以下的查询时：
select * from accounts;
她将看到：
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
3 ISRAEL D 1500
acctno 2 的余额超过 1600 ，它已禁止显示。对于 Michelle 而言，表中只有两行，而不是三行。当她执行类似以下的查询时：
select count(*) from accounts;
该查询只计算表中的记录数，输出是二，而不是三。
但是，此时我们可以决定将安全策略稍微放松一些。在本查询中， Michelle 不能查看帐户余额等秘密数据；她只是计算表中所有记录的数目。在与安全策略一致的情况下，我们可以允许此查询计算所有记录的数目，无论是否允许她查看这些记录。如果需要这样，则在对 10 g 的 dbms_rls.add_policy 的调用中的另一个参数允许实现此功能：
sec_relevant_cols => 'BALANCE'
现在，当用户选择列 BALANCE 时，无论是显式选择还是隐含在 select * 中， VPD 策略都会介入，对行作出限制。否则将会选择表中所有的行，因为在查询中用户只选择了总计行数，而没有选择列 BALANCE 。如果将以上参数设置为所示的形式，则查询
select count(*) from accounts;
将显示三列，而不是两列。但是查询：
select * from accounts;
仍将只返回两条记录，与预期的情况相同。
列屏蔽
现在，让我们对当前的示例再增加些要求。我们不禁止显示那些余额高于阈值的行，而是希望显示所有的行，同时屏蔽那些数值超过阈值的余额列。与安全性相关的列仍然是 BALANCE 。
不允许 Michelle 看到余额超过 1600 的帐户。当她执行类似以下的查询时：
select * from accounts;
她将只看到两行， acctnos 1 和 3 。但是，我们可能希望她看到：
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
2 TOM CONNOPHY <null>
3 ISRAEL D 1500
注意，显示都所有行，但是 acctno 2 的列 BALANCE 的值显示为空（显示为 <null> ），它的余额实际上是 2000 ，超过阈值 1600 。这种方法称为 “ 列屏蔽 ” ，可以通过在对 dbms_rls.add_policy 的调用中指定参数来实现：
sec_relevant_cols_opt => dbms_rls.all_rows
在只有某些特定列值很重要的情况下，这种策略才可能非常有用，并且不需要复杂的自定义代码。它也是一种很好的变通方法，不需要对存储数据进行加密。
结论
在 Oracle 数据库 10 g 中， VPD 已经发展为一种功能非常强大的特性，它能够支持多种需求，例如基于策略有选择性地屏蔽列，只在访问特定列时应用策略等。利用应用程序特有的性质，还可以通过多种类型的策略来提高策略的性能，使得该特性适用于多种情况。
有关 VPD 和 dbms_rls 程序包的更多信息，请参阅 PL/SQL 程序包和类型参考 的 第 79 章 以及 Oracle 数据库安全指南 。您还可以阅读由我与 Don Burleson 合著的一本关于该主题的书籍 Oracle 保密安全性审计 (Rampant TechPress) 。
Oracle Database 10 g : 为 DBA 提供的最佳前 20 位的特性（十五）
作者 Arup Nanda 来源： OTN
第 15 周
段管理
用 Oracle 数据库 10 g 通过回收浪费的空间、联机重组表格和评估增长的趋势，有效地在段中进行存储管理
近来，有人要求我评估一个与 Oracle 数据库竞争的 RDBMS 。在供应商的演示过程中，观众认为 “ 最棒 ” 的特性是，对联机重组的支持 — 该产品可以联机重新部署数据块，以使段的等价物更简洁，并且不会影响当前的用户。
那时， Oracle 还没有在 Oracle9 i 数据库中提供这种功能。但是现在，有了 Oracle 数据库 10 g ，就可以轻松地联机回收浪费的空间和压缩对象 — 正好适合于起步者。
不过，在检验该特性之前，让我们看一看处理这项任务的 “ 传统的 ” 方法。
当前惯例
考虑让我们看一个段，如一张表，其中填满了块，如图 1 所示。在正常操作过程中，删除了一些行，如图 2 所示。现有就有了许多浪费的空间： (i) 在表的上一个末端和现有的块之间，以及 (ii) 在块内部，其中还有一些没有删除的行。


图 1 ：分配给该表的块。用灰色正方形表示行。
Oracle 不会释放空间以供其他对象使用，有一条简单的理由：由于空间是为新插入的行保留的，并且要适应现有行的增长。被占用的最高空间称为最高使用标记 (HWM) ，如图 2 所示。


图 2 ：行后面的块已经删除了； HWM 仍保持不变。
但是，这种方法有两个主要的问题：
• 当用户发出一个全表扫描时， Oracle 始终必须从段一直扫描到 HWM ，即使它什么也没有发现。该任务延长了全表扫描的时间。
• 当用直接路径插入行时 — 例如，通过直接加载插入（用 APPEND 提示插入）或通过 SQL*Loader 直接路径 — 数据块直接置于 HWM 之上。它下面的空间就浪费掉了。
在 Oracle9 i 及其以前的版本中，可以通过删除表，然后重建表并重新加载数据来回收空间；或通过使用 ALTER TABLE MOVE 命令把表移动到一个不同的表空间中来回收空间。这两种处理方式都必须脱机进行。另外，可以使用联机表重组特性，但是这需要至少双倍的现有表空间。
在 10 g 中，该任务已经变得微不足道了；假如您的表空间中支持自动段空间管理 (ASSM), 您现在可以缩小段、表和索引，以回收空闲块并把它们提供给数据库以作他用，让我们看看其中的缘由。
10 g 中的段管理方式
设想有一个表 BOOKINGS ，它保存有经由 Web 站点的联机登记。当一个登记确认后，就会把它存储在一个存档表 BOOKINGS_HIST 中，并从 BOOKINGS 表中删除该行。登记和确认之间的时间间隔依据客户有很大的不同，由于无法从删除的行获得足够的空间，因此许多行就插入到了表的 HWM 之上。
现在您需要回收浪费的空间。首先，准确地查明在可回收的段中浪费了多少空间。由于它是在支持 ASSM 的表空间中，您将不得不使用 DBMS_SPACE 包的 SPACE_USAGE 过程，如下所示：
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => 'BOOKINGS',
segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = ||l_full_bytes);
end;
/
输出结果如下：
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 4148 Bytes = 0
Full Blocks = 2 Bytes = 16384
这个输出结果显示有 4,148 个块，具有 75-100% 的空闲空间 (FS4) ；没有其他空闲块可用。这里仅有两个得到完全使用的块。 4,148 个块都可以回收。
接下来，您必须确保该表支持行移动。如果不支持，您可以使用如下命令来支持它：
alter table bookings enable row movement;
或通过 Administration 页上的 企业管理器 10 g 。您还要确保在该表上禁用所有基于行 id 的触发器，这是因为行将会移动，行 id 可能会发生改变。
最后，您可以通过以下命令重组该表中现有的行：
alter table bookings shrink space compact;
该命令将会在块内重新分配行，如图 3 所示，这就在 HWM 之下产生了更多的空闲块 — 但是 HWM 自身不会进行分配。


图 3 ：重组行后的表中的块。
在执行该操作后，让我们看一看空间利用率所发生的改变。使用在第一步展示的 PL/SQL 块，可以看到块现在是如何组织的：
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 1 Bytes = 0
FS4 Blocks = 0 Bytes = 0
Full Blocks = 2 Bytes = 16384
注意这里的重要改变： FS4 块（具有 75-100% 的空闲空间）的数量现在从 4,148 降为 0 。我们还看到 FS3 块（具有 50-75% 的空闲空间）的数量从 0 增加到 1 。但是，由于 HWM 没有被重置，总的空间利用率仍然是相同的。我们可以用如下命令检查使用的空间：
SQL> select blocks from user_segments where segment_name = 'BOOKINGS';
BLOCKS
---------
4224
由该表占用的块的数量 (4,224) 仍然是相同的，这是因为并没有把 HWM 从其原始位置移开。可以把 HWM 移动到一个较低的位置，并用如下命令回收空间：
alter table bookings shrink space;
注意子句 COMPACT 没有出现。该操作将把未用的块返回给数据库并重置 HWM 。可以通过检查分配给表的空间来对其进行测试：
SQL> select blocks from user_segments where segment_name = 'BOOKINGS';
BLOCKS
----------
8
块的数量从 4,224 降为 8 ；该表内所有未用的空间都返回给表空间，以让其他段使用，如图 4 所示。


图 4 ：在收缩后，把空闲块返回给数据库。
这个收缩操作完全是在联机状态下发生的，并且不会对用户产生影响。
也可以用一条语句来压缩表的索引：
alter table bookings shrink space cascade;
联机 shrink 命令是一个用于回收浪费的空间和重置 HWM 的强大的特性。我把后者（重置 HWM ）看作该命令最有用的结果，因为它改进了全表扫描的性能。
找到收缩合适选择
在执行联机收缩前，用户可能想通过确定能够进行最完全压缩的段，以找出最大的回报。只需简单地使用 dbms_space 包中的内置函数 verify_shrink_candidate 。如果段可以收缩到 1,300,000 字节，则可以使用下面的 PL/SQL 代码进行测试：
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',1300000)
then
:x := 'T';
else
:x := 'F';
end if;
end;
/
PL/SQL 过程成功完成。
SQL> print x
X
--------------------------------
T
如果目标收缩使用了一个较小的数，如 3,000 ：
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',30000)
then
:x := 'T';
else
:x := 'F';
end if;
end;
变量 x 的值被设置成 'F' ，意味着表无法收缩到 3,000 字节。
推测一下来自索引空间的需要
现在假定您将着手在一个表上，或者也许是一组表上创建一个索引的任务。除了普通的结构元素，如列和单值性外，您将不得不考虑的最重要的事情是索引的预期大小 — 必须确保表空间有足够的空间来存放新索引。
在 Oracle 数据库 9 i 及其以前的版本中，许多 DBA 使用了大量的工具（从电子数据表到独立程序）来估计将来索引的大小。在 10 g 中，通过使用 DBMS_SPACE 包，使这项任务变得极其微不足道。让我们看一看它的作用方式。
我们要求在 BOOKINGS 表的 booking_id 和 cust_name 列上创建一个索引。这个提议的索引需要多少空间呢？您所需要做的全部工作就是执行下面的 PL/SQL 脚本。
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_bookings_hist_01 on bookings_hist '||
'(booking_id, cust_name) tablespace users',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
The output is:
Used Bytes = 7501128
Allocated Bytes = 12582912
假定您想使用一些参数，而这些将参数潜在地增加了索引的大小，例如，指定 INITRANS 参数为 10 。
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_bookings_hist_01 on bookings_hist '||
'(booking_id, cust_name) tablespace users initrans 10',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
输出结果如下：
Used Bytes = 7501128
Allocated Bytes = 13631488
注意通过指定一个更高的 INITRANS ，而导致的分配字节的增加。使用这种方法可以容易地确定索引对存储空间的影响。
但是，您应该意识到两个重要的警告。第一，该过程只适用于打开了 SEGMENT SPACE MANAGEMENT AUTO 的表空间。第二，包是依据表上的统计来计算索引的估计大小。因此，对表执行相对新的统计是非常重要的。但请注意：如果没有对表的统计，不会导致使用包时出错，但会产生一个错误的结果。
估计表的大小
假定有一个名为 BOOKINGS_HIST 的表，它共有 30,000 行，各行长度较为平均，并且 PCTFREE 参数值为 20 。如果想把参数 PCT_FREE 增至 30, 表格的大小将增加多少？由于 30 是将 20 增长了 10% ，表格大小将会按 10% 的比例增长吗？这个问题不要问自己，而要问 DBMS_SPACE 包内的 CREATE_TABLE_COST 过程。下面是您能够估计大小的方法：
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_table_cost (
tablespace_name => 'USERS',
avg_row_size => 30,
row_count => 30000,
pct_free => 20,
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line('Used:'||l_used_bytes);
dbms_output.put_line('Allocated:'||l_alloc_bytes);
end;
/
输出结果如下：
Used: 1261568
Allocated: 2097152
要把表的 PCT_FREE 参数从 20 更改为 30 ，可以通过指定
pct_free => 30
我们得到了输出结果：
Used: 1441792
Allocated: 2097152
注意：使用的空间已从 1,261,568 增加到 1,441,792 ，这是由于 PCT_FREE 参数在用户数据的数据块中保留了较少的空间。增量大约有 14% ，而不是所预期的 10% 。使用这个包可以容易地计算出参数（如 PCT_FREE ）对表大小的影响，或把该表移动到一个不同的表空间中的影响。
预测段的增长
这是一个假日的周末， Acme 酒店期待有如同潮涌般的人群要求入住。作为一名 DBA ，您正设法了解这种需求，以使您能够确保有足够的空间可供使用。如何预测表的空间利用率呢？
只需询问 10 g ；您就会惊诧于它能够多么准确和聪明地为您作出预测。只需简单地发出查询 :
select * from
table(dbms_space.OBJECT_GROWTH_TREND
('ARUP','BOOKINGS','TABLE'));
函数 dbms_space.object_growth_trend() 将以 PIPELINE d 格式返回记录，这种格式可以通过 TABLE() 强制转换将其显示出来。输出如下：
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------ ----------- ----------- ------------
05-MAR-04 08.51.24 .421081 PM 8586959 39124992 INTERPOLATED
06-MAR-04 08.51.24 .421081 PM 8586959 39124992 INTERPOLATED
07-MAR-04 08.51.24 .421081 PM 8586959 39124992 INTERPOLATED
08-MAR-04 08.51.24 .421081 PM 126190859 1033483971 INTERPOLATED
09-MAR-04 08.51.24 .421081 PM 4517094 4587520 GOOD
10-MAR-04 08.51.24 .421081 PM 127469413 1044292813 PROJECTED
11-MAR-04 08.51.24 .421081 PM 128108689 1049697234 PROJECTED
12-MAR-04 08.51.24 .421081 PM 128747966 1055101654 PROJECTED
13-MAR-04 08.51.24 .421081 PM 129387243 1060506075 PROJECTED
14-MAR-04 08.51.24 .421081 PM 130026520 1065910496 PROJECTED
输出结果多次清楚显示了 BOOKINGS 表的大小，就像在 TIMEPOINT 列、 TIMESTAMP 数据类型中所显示的那样。 SPACE_ALLOC 列显示了分配给该表的字节，而 SPACE_USAGE 列显示了已使用了多少字节。该信息是由自动负载存储或 AWR （参阅本系列中的 第 6 周 ）每天进行收集的。在上面的输出中，数据是在 2004 年 3 月 9 日收集好的，就像 QUALITY 列的值 － "GOOD" 所指示的那样。那一天的空间分配和使用数字都是准确无误的。但是，以后的日子，该列的值将是 PROJECTED ，这表示空间计算是由 AWR 功能依据收集的数据进行预测的 — 不是直接从段进行收集。
注意 3 月 9 日以前的该列中的值 — 全都是 INTERPOLATED 。换句话说，该值没有真正收集或预测的值，只是简单地依据适用于任何可用数据的使用图案添加进去的。最有可能的是当时没有数据，因此不得不添加该值。
结论
有了段级操作，现在就能对段内空间如何使用进行细粒度的控制，可用来回收表内的空闲空间、联机重组表中的行以使之更压缩，以及更多操作。这些功能帮助 DBA 从诸如表重组之类的日常任务中解脱出来。联机段收缩特性在估计内部碎片和降低段的最高使用标记方面特别有用，能显著地减少全表扫描的成本。
么获取关于 SHRINK 操作的更多信息，请参阅 Oracle 数据库 SQL 参考 中的 这部分 的内容。可以在 PL/SQL 包和类型参考 的 第 88 章 中了解到有关 DBMS_SPACE 包的更多信息。为了对 Oracle 数据库 10 g 中的所有新的空间管理特性作一个全面的回顾，可以阅读技术白皮书 自我管理的数据库：积极的空间和模式对象管理 。最后， 在线 提供了一个 Oracle 数据库 10 g 空间管理的演示