5_11g新特性_模式管理.docx

Oracle Database 11g：面向 DBA 和开发人员的重要特性模式管理通过显著加快并简化许多常见操作的新功能，更高效地管理数据库对象。Oracle Database 11g 包括大量特性，这些特性不仅能够简化作业，在某些情况下，还可以将某些常见的耗时操作缩减为实际上的一行代码。在本文中，您将了解其中一些特性。DDL Wait 选项Jill 是 Acme Retailers 的 DBA，她尝试更改名为 SALES 的表，为其添加一列 TAX_CODE。这是很常见的任务；她执行以下 SQL 语句：SQL alter table sales add (tax_code varchar2(10);但是，她收到了以下消息，而非“Table altered”之类的内容：alter table sales add (tax_code varchar2(10) *ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired错误消息描述的是：该表目前可能正由一个事务使用，因此要获得该表的独占锁定不太可能。当然，表的行不会永远锁定。当会话 执行提交动作后，会释放对这些行的锁定，但在此之前，由于解除锁定期间很长，其他会话可能会更新表的其他行 这样，获得表的独占锁定的时机又消失了。在典型的业务环境中，以独占方式锁定表的窗口会定期打开，但 DBA 可能无法恰好在那时执行 alter 命令。当然，Jill 也可以反复键入相同的命令，直到获得独占锁定或者失败（两者取其先）。在 Oracle Database 11g 中，Jill 有更好的选择：DDL Wait 选项。她可以执行以下命令：SQL alter session set ddl_lock_timeout = 10; Session altered.现在，如果会话中的 DDL 语句没有获得独占锁定，也不会显示错误消息。相反，它将等待 10 秒钟。在这 10 秒钟内，它将不断重试 DDL 操作，直到成功或超时（两者取其先）。如果执行以下命令：SQL alter table sales add (tax_code varchar2(10);该语句将挂起，并且不会显示错误消息。这样，Jill 就将重复尝试操作外包给了 Oracle Database 11g（就像电话通过程序重试繁忙号码），而不必反复尝试以获得难以捉摸的独占锁定可用时机。现 在，Jill 十分喜欢这个特性，并与其他所有 DBA 一起分享这个特性。由于在系统繁忙期间更改表时，每个人都遇到过相同的问题，他们都发现这个新特性非常有帮助。因此，Jill 很想知道是否可以将该行为设为默认行为，这样就不需要每次都执行 ALTER SESSION 语句？是的，可以。如果您执行 ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10，会话将在 DDL 操作期间自动等待该时间段。与任何其他 ALTER SYSTEM 语句一样，该语句可被 ALTER SESSION 语句覆盖。添加具有默认值的列尽管对该特性感到满意，Jill 还在考虑与第一个问题相关的另一个问题。她希望添加 TAX_CODE 列，但该列不能为空。显然，当她向非空表添加非空列时，还必须指定默认值“XX”。因此，她编写了以下 SQL：alter table sales add tax_code varchar2(20) default XX not null;但她在这里停下了。SALES 表十分巨大，大约有 4 亿行。她知道，在执行该语句时，Oracle 会立即添加该列，但在将控制权返回给她之前将更新所有行中的值“XX”。更新 4 亿行不仅要花费很长时间，还要填充撤销段、生成大量重做任务并产生极大的性能开销。因此，Jill 必须在“安静时段”（即，停机期间）才能进行此更改。Oracle Database 11g 中有更好的方法吗？当然有。上述语句将不会对表中的所有记录执行更新。尽管对于列值将自动设为“XX”的新记录来说，这不是问题，但当用户选择现有记录的该列时，这将返回 NULL，是吗？实际上并非如此。当用户选择现有记录的列时，Oracle 将从数据字典获取默认值并将其返回给用户。这样，您就实现了一箭双雕：可以将一个新列定义为非空并具有默认值，同时不会导致任何重做和撤销开销。真棒！虚拟列Acme 的数据库包含了一个名为 SALES 的表，如前所示。该表的结构如下：SALES_IDNUMBERCUST_IDNUMBERSALES_AMTNUMBER某些用户希望添加一个名为 SALE_CATEGORY 的列，以便根据销售量和当前客户来标识销售的类型：LOW、MEDIUM、HIGH 和 ULTRA。该列将帮助他们识别相应动作的记录，并将记录路由给相关人员以进行处理。以下是列值的逻辑：如果 sale_amt 大于：且 sale_amt 小于或等于：则 sale_category 为01000LOW10001100000MEDIUM1000011000000HIGH1000001无限ULTRA尽 管该列是重要的业务需求，但开发团队不希望更改代码来创建必要的逻辑。当然，您可以在表中添加一个名为 sale_category 的新列，然后编写一个触发器以使用上述逻辑填充该列 一个相当简单的操作。但是，由于与触发器代码的上下文切换，可能会导致性能问题。在 Oracle Database 11g 中，您不需要编写任何触发器代码。您只需添加一个虚拟列。虚拟列为您提供了灵活性，可以添加传达商业意识的列，而不增加任何复杂性或性能影响。以下是创建该表的方法：SQL create table sales 2 ( 3 sales_id number, 4 cust_id number, 5 sales_amt number, 6 sale_category varchar2(6) 7 generated always as 8 ( 9 case 10 when sales_amt 10000 and sales_amt 100000 and sales_amt insert into sales (sales_id, cust_id, sales_amt) values (1,1,100); 1 row created. SQL insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500); 1 row created. SQLinsert into sales (sales_id, cust_id, sales_amt) values (3,102,100000); 1 row created. SQL commit; Commit complete. SQL select * from sales; SALES_ID CUST_ID SALES_AMT SALE_C- - - -1 1 100 LOW2 102 1500 LOW3 102 100000 MEDIUM 3 rows selected.虚拟列值都将照常填充。即使该列未存储，您也可以将其视为表的任何其他列，甚至可以在其上创建索引。SQL create index in_sales_cat on sales (sale_category); Index created.其结果将是一个基于函数的索引。SQL select index_type 2 from user_indexes 3 where index_name = IN_SALES_CAT; INDEX_TYPE-FUNCTION-BASED NORMALSQL select column_expression 2 from user_ind_expressions 3 where index_name = IN_SALES_CAT; COLUMN_EXPRESSION-CASE WHEN SALES_AMT10000 AND SALES_AMT=100000) THEN CASE WHEN CUST_ID=101 AND CUST_ID100000 AND SALES_AMT=1000000) THEN CASE WHEN CUST_ID=101 AND CUST_ID create index in_res_guest on res (guest_id);分析完该表和索引后，如果您执行 SQL select * from res where guest_id = 101;将发现正在使用该索引： Execution Plan-Plan hash value: 1519600902 -| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 |* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |- Predicate Information (identified by operation id):- 2 - access(GUEST_ID=101)现在，使索引不可见： SQL alter index in_res_guest invisible; Index altered.现在，将显示以下内容： SQL select * from res where guest_id = 101 2 / Execution Plan-Plan hash value: 3824022422 -| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 28 | 140 (2)| 00:00:02 |* 1 | TABLE ACCESS FULL| RES | 1 | 28 | 140 (2)| 00:00:02 |- Predicate Information (identified by operation id):- 1 - filter(GUEST_ID=101)未使用索引。要使优化器再次使用索引，您必须在提示中显式命名索引： SQL select /*+ INDEX (res IN_RES_GUEST) */ res_id from res where guest_id = 101;-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 |* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |-立刻！优化器再次使用了索引。 或者，您也可以设置会话级参数以使用不可见的索引：SQL alter session set optimizer_use_invisible_indexes = true;如果您无法修改代码（如第三方应用程序中的代码），该特性将十分有用。创建索引时，可以在末尾追加子句 INVISIBLE，将索引构建为对优化器不可见。您还可以使用字典视图 USER_INDEXES 查看索引的当前设置。 SQL select visibility 2 from user_indexes 3 where index_name = IN_RES_GUEST;VISIBILITY-INVISIBLE注意，如果您重新构建该索引，则该索引将变为可见。您必须再次将其显式设为不可见。那么，该索引到底对“什么”不可见？当然，它不会对用户不可见。它只是对优化器不可见。常规数据库操作（如插入、更新和删除）将继续更新索引。创建不可见索引时应注意这一点；由于该索引，您将不会再次查看性能，但同时您在 DML 操作期间可能会付出一些代价。只读表Robin 是 Acme 数据仓库系统的开发人员，他正在考虑一个典型问题。作为 ETL 流程的一部分，几个表的更新周期有所不同。在更新时，表会按业务规则对用户开放，即使用户不应修改它们。但是，取消用户对这些表的 DML 权限不是一个可选方法。Robin 需要一个能够充当开关角色的功能，可以允许或不允许更新表。要实现这个听起来简单的操作，实际上相当困难。Robin 有哪些选择呢？一个选择是，在表上创建一个触发器，以针对 INSERT、DELETE 和 UPDATE 引发异常。执行触发器会涉及上下文切换，这会影响性能。另一个选择是，创建一个虚拟专用数据库 (VPD) 策略，始终返回 false 字符串（如“1=2”）。如果表上的 VPD 策略使用该函数，它就会返回 FALSE，并且 DML 会失败。这可能比使用触发器具有更好的性能，但用户肯定不愿意使用，因为会看到“policy function returned error”之类的错误消息。然而，在 Oracle Database 11g 中，您可以通过一个更好的方法来实现这个目标。您只需将表设为只读，如下所示：SQL alter table TRANS read only; Table altered.现在，当用户尝试执行如下所示的 DML 时： SQL delete trans;Oracle Database 11g 就会立即抛出错误： delete trans *ERROR at line 1:ORA-12081: update operation not allowed on table SCOTT.TRANS错误消息不会将操作反映到代码中，但会有目的地传递消息，而无需触发器或 VPD 策略的开销。如果您希望表可更新，则需要将其设为读/写，如下所示：SQL alter table trans read write; Table altered.现在，DML 就没有问题了： SQL update trans set amt = 1 where trans_id = 1; 1 row updated.当表仅处于只读模式时，不允许执行 DML；但您可以执行所有 DDL 操作（创建索引、维护分区等）。因此，这个特性的一个非常有用的应用就是表维护。您可以将表设为只读，执行必要的 DDL，然后再将其设为读/写。要查看表的状态，请在数据字典视图 dba_tables 中查找 read_only 列。SQL select read_only from user_tables where table_name = TRANS; REA-NO细粒度依赖跟踪该特性可以通过一个示例得到很好的解释。考虑一个名为 TRANS 的表，按如下方式创建：create table trans( trans_id number(10), trans_amt number(12,2), store_id number(2), trans_type varchar2(1)用户不应直接从该表中获取数据；他们通过视图 VW_TRANS 获取数据，该视图的创建如下所示：create or replace view vw_transasselect trans_id, trans_amt from trans;现在，视图 VW_TRANS 依赖于表 TRANS。可以使用以下查询查看依赖性：elect d.referenced_name, o.status from user_dependencies d, user_objects owhere = o.object_nameand = VW_TRANS/REFERENCED_NAME STATUS- -TRANS VALID如上所示，视图 VW_TRANS 的状态为 VALID。然后，以某种方式（如添加一列）修改基础表：alter table transadd (trans_date date);由于该视图所依赖的表发生了更改，因此该视图目前在 Oracle Database 10g 和早期版本中变为无效。现在，可以使用上述查询检查依赖性和状态：REFERENCED_NAME STATUS- -TRANS INVALID状态显示为 INVALID。因为基本上没有发生导致该视图永久无效的任何更改，所以可通过以下命令轻松地重新编译该视图：alter view vw_trans compile;那么，该视图为什么无效？答案很简单：当父对象更改时，会自动检查子对象，因为其中的某些内容也可能需要更改。但在该案例中，所作的更改是添加了一个新列。而该视图并未使用这个新列，为什么会无效呢？在 Oracle Database 11g 中，该视图不会无效。当然，依赖性仍然设为 TRANS，但现在的状态不再是 INVALID，而是 VALID！REFERENCED_NAME STATUS- -TRANS VALID由于该视图并未无效，因此该视图的所有依赖对象（如另一个视图或程序包及过程）也不会无效。该行为大大提高了应用程序的可用性，从而增强了整个体系的总体可用性。进行某些数据库更改时，无需停止应用程序。如果您更改了该视图中使用的某列（如 TRANS_AMT），该视图便会无效。这也是我们所希望的，因为所更改的列影响到了该视图。但是视图和表也需要具有高可用性，因为您还需要将它们用于其他存储对象（像过程和程序包）。考虑如下所示的程序包：create or replace package pkg_transis procedure upd_trans_amt ( p_trans_id trans.trans_id%type, p_trans_amt trans.trans_amt%type );end;/create or replace package body pkg_transis procedure upd_trans_amt ( p_trans_id trans.trans_id%type, p_trans_amt trans.trans_amt%type ) is begin update trans set trans_amt = p_trans_amt where trans_id = p_trans_id; end;end;/现在，假设您要编写一个可使事务量按指定百分比增加的函数。该函数使用程序包 pkg_trans。create or replace function adjust( p_trans_id number, p_percentage number)return booleanis l_new_trans_amt number(12);begin select trans_amt * (1 + p_percentage/100) into l_new_trans_amt from trans where trans_id = p_trans_id; pkg_trans.upd_trans_amt ( p_trans_id, p_percentage ); return TRUE;exception when OTHERS then return FALSE;end;/如果您查看该函数的状态，其应为 VALID：select statusfrom user_objectswhere object_name = ADJUST/STATUS-VALID假设您要修改程序包 pkg_trans，即添加一个新过程以更新 vendor_name 列。下面是新程序包的定义：create or replace package pkg_transis procedure upd_trans_amt ( p_trans_id trans.trans_id%type, p_trans_amt trans.trans_amt%type ); procedure upd_vendor_name ( p_trans_id trans.trans_id%type, p_vendor_name trans.vendor_name%type ); end;/create or replace package body pkg_transis procedure upd_trans_amt ( p_trans_id trans.trans_id%type, p_trans_amt trans.trans_amt%type ) is begin update trans set trans_amt = p_trans_amt where trans_id = p_trans_id; end; procedure upd_vendor_name ( p_trans_id trans.trans_id%type, p_vendor_name trans.vendor_name%type ) is begin update trans set vendor_name = p_vendor_name where trans_id = p_trans_id; end;end;重新编译该程序包后，函数 ADJUST 的状态将会怎样呢？在 Oracle Database 10g 及更低版本中，该函数（为依赖函数）将无效，如以下状态所示：STATUS-INVALID但在 Oracle Database 11g 中，如果可以通过 alter function . recompile 轻松编译，则该函数不会无效：STATUS-VALID这可极大提高可用性。函数 adjust 不会调用程序包 pkg_trans 的已更改部分，因此不需要使该函数无效，所以该函数在 Oracle Database 11g 中不会无效。但情况并不总是这样。如果通过在该程序包末尾添加新子组件来修改程序包，如上述示例中所示，则依赖存储代码不会无效。如果在程序包开头添加子组件，如下所示：create or replace package pkg_transis procedure upd_vendor_name . procedure upd_trans_amt .end;依赖存储代码 ADJUST 便会无效，与 Oracle Database 10g 及更低版本中一样。之所以出现此情况是因为，在现有过程之前插入的新过程会更改程序包中的存储区号，从而导致失效。在现有过程之后插入过程时，存储区号不会更改；只是添加了一个新的存储区号。下面是减少与依赖性有关的失效的一些一般性指导原则。 将函数和过程之类的组件添加到程序包的末尾处。 失效的一个常见原因是数据类型的更改。如果您未指定列名，则过程将采用所有列，因此任何更改都会使过程无效，即使不使用列。例如，当您使用 select * from sometable 时，会采用表的所有列。避免 select * 之类的结构、sometable%rowtype 和 insert into sometable values (.) 之类的数据类型，因为其中未提及列列表。 如果可能，请在存储代码中使用表视图。这样您可以将存储代码不使用的列添加到表中。由于视图不会无效，如上所示，因此存储代码也不会无效。 对于同义词，使用 create or replace synonym .;而不是 drop synonym .;create synonym .;这不会使过程无效。而且，如果您之前使用了联机重定义，可能会看到重定义使某些依赖对象无效。Oracle Database 11g 中不会再出现这种情况。现在，如果对象引用的列具有相同的名称和类型，联机重定义不会使对象无效。如果在重定义期间删除了某列，但过程并未使用该列，则过程不会无效。注意：在 Oracle Database 11g 第 2 版中，上述的这种失效行为有所不同。为了进行说明这一区别，我们在第 1 版数据库中创建一个表：create table trans( trans_id number(10), trans_amt number(12,2), store_id number(2), trans_type varchar2(1)接下来，创建一个触发器： create trigger tr_transbefore insert on transfor each rowdeclare l_store_id number(2);begin l_store_id := :new.store_id;end;/查看触发器的状态： SQL select status from user_objects where object_name = TR_TRANS;STATUS-VALID以某种方式修改表： SQL alter table trans add (col1 number);Table altered.现在，如果您查看触发器的状态： SQL select status from user_objects where object_name = TR_TRANS;STATUS-INVALID在第 1 版中，触发器无效，即使它与表修改无关。但在第 2 版中，触发器不会 无效，因为触发器不依赖于表的修改。（只是添加了一个新列；而现有触发器永远不会引用它。） 如果我们在第 2 版数据库上重新创建该实例并查看状态：SQL select status from user_objects where object_name = TR_TRANS;STATUS-VALID1 row selected.SQL alter table trans add (col1 number);Table altered.SQL select status from user_objects where object_name = TR_TRANS;STATUS-VALID触发器仍有效。当然，如果进行的更改影响到触发器，则情况会有所不同。例如， SQL alter table trans modify (store_id number(3);Table altered.SQL select status from user_objects where object_name = TR_TRANS;STATUS-INVALID因此，在许多情况下，表的修改（如添加新列）不会使依赖对象无效，从而提高了数据库可用性。 虚拟列上的外键（仅限第 2 版）在 11g 第 1 版中，我们看到引入了两个非常有用的重要特性。一个是前面介绍的虚拟列。另一个是基于引用完整性约束的分区（即所谓的 REF 分区），该特性允许您完全按照父表的方式对子表进行分区，即使分区列不在子表中。这 两个特性具有截然不同的好处：虚拟列允许在不造成存储列开销或不对应用程序进行更改就实现新列合并的情况下操作表；而 REF 分区允许您对表进行分区以在父子关系中利用分区修剪，而无需将这些列添加到子表中。但是，如果您要在多组相同的表上同时利用这两个特性，该怎么办呢？在 11g 第 2 版中，您可以轻松做到这一点。下面就是一个例子：表 CUSTOMERS 具有两个虚拟列 CUST_ID 和 CATEGORY，前者也用作主键，后者是分区列。表 SALES 是 CUSTOMERS 表的子表，通过 CUST_ID 实现联接。我们来看一下实际的代码。create table customers( cust_id number(14) generated always as ( DECODE(plan_id, MEDICAL,100, DENTAL,200, HOSPITAL ONLY,300, 999) | ssn | DECODE(member_type, SELF,01, SPOUSE,02, CHILD,03, 99) ) virtual, cust_name varchar2(20), ssn varchar(9), plan_id varchar2(15), member_type varchar2(10), category varchar2(1) generated always as (case when member_type = SELF then case when plan_id = MEDICAL then A else B end when member_type = SPOUSE then case when plan_id = MEDICAL then B else C end when member_type = CHILD then C else X end) virtual, constraint pk_customers primary key (cust_id)partition by list (category)( partition A values (A), partition B values (B), partition C values (C), partition DEF values (default)/我们来插入一些行，注意不要为虚拟列分配具体值。我们希望生成虚拟列。insert into insert into customers (cust_name, ssn, plan_id, member_type) values (Jill,123456789,MEDICAL,SELF)/ insert into customers (cust_name, ssn, plan_id, member_type) values (