本文共 7796 字，大约阅读时间需要 25 分钟。

Oracle OCP 1Z0-050（25题）解析

QUESTION 25:

Which statement describes the effect of table redefinition on the triggers attached to the table?

A. All triggers on the table are invalidated and are automatically revalidated with the next DML execution on the table.

B. All triggers on the table are invalidated and must be manually recompiled before the next DML execution on the table.

C. All triggers on the table remain valid.

D. Only triggers that are affected by the changes to the structure of the table are invalidated and automatically revalidated with the next DML execution on the table.

Answer: A

解析;

参考Oracle官方文档：

Creating Dependent Objects Automatically

You use the COPY_TABLE_DEPENDENTS procedure to automatically create dependent objects on the interim table.

You can discover if errors occurred while copying dependent objects by checking the num_errors output argument. If the ignore_errors argument is set to TRUE, the COPY_TABLE_DEPENDENTS procedure continues copying dependent objects even if an error is encountered when creating an object. You can view these errors by querying the DBA_REDEFINITION_ERRORS view.

Reasons for errors include:

• A lack of system resources

• A change in the logical structure of the table that would require recoding the dependent object.

  See Example 3 in for a discussion of this type of error.

If ignore_errors is set to FALSE, the COPY_TABLE_DEPENDENTS procedure stops copying objects as soon as any error is encountered.

After you correct any errors you can again attempt to copy the dependent objects by reexecuting the COPY_TABLE_DEPENDENTS procedure. Optionally you can create the objects manually and then register them as explained in . The COPY_TABLE_DEPENDENTS procedure can be used multiple times as necessary. If an object has already been successfully copied, it is not copied again.

Creating Dependent Objects Manually

If you manually create dependent objects on the interim table with SQL*Plus or Enterprise Manager, you must then use the REGISTER_DEPENDENT_OBJECT procedure to register the dependent objects. Registering dependent objects enables the redefinition completion process to restore dependent object names to what they were before redefinition.

You would also use the REGISTER_DEPENDENT_OBJECT procedure if the COPY_TABLE_DEPENDENTS procedure failed to copy a dependent object and manual intervention is required.

You can query the DBA_REDEFINITION_OBJECTS view to determine which dependent objects are registered. This view shows dependent objects that were registered explicitly with the REGISTER_DEPENDENT_OBJECT procedure or implicitly with the COPY_TABLE_DEPENDENTS procedure. Only current information is shown in the view.

The UNREGISTER_DEPENDENT_OBJECT procedure can be used to unregister a dependent object on the table being redefined and on the interim table.

Note:

Manually created dependent objects do not have to be identical to their corresponding original dependent objects. For example, when manually creating a materialized view log on the interim table, you can log different columns. In addition, the interim table can have more or fewer dependent objects.

Results of the Redefinition Process

The following are the end results of the redefinition process:

• The original table is redefined with the columns, indexes, constraints, grants, triggers, and statistics of the interim table.
• Dependent objects that were registered, either explicitly using REGISTER_DEPENDENT_OBJECT or implicitly using COPY_TABLE_DEPENDENTS, are renamed automatically so that dependent object names on the redefined table are the same as before redefinition.

Note:

If no registration is done or no automatic copying is done, then you must manually rename the dependent objects.

• The referential constraints involving the interim table now involve the redefined table and are enabled.
• Any indexes, triggers, materialized view logs, grants, and constraints defined on the original table (prior to redefinition) are transferred to the interim table and are dropped when the user drops the interim table. Any referential constraints involving the original table before the redefinition now involve the interim table and are disabled.
• Some PL/SQL objects, views, synonyms, and other table-dependent objects may become invalidated. Only those objects that depend on elements of the table that were changed are invalidated. For example, if a PL/SQL procedure queries only columns of the redefined table that were unchanged by the redefinition, the procedure remains valid. See  for more information about schema object dependencies.

Restrictions for Online Redefinition of Tables

The following restrictions apply to the online redefinition of tables:

• If the table is to be redefined using primary key or pseudo-primary keys (unique keys or constraints with all component columns having not nullconstraints), then the post-redefinition table must have the same primary key or pseudo-primary key columns. If the table is to be redefined using rowids, then the table must not be an index-organized table.

• After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.

• Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.

• The overflow table of an index-organized table cannot be redefined online independently.

• Tables with fine-grained access control (row-level security) cannot be redefined online.

• Tables for which Flashback Data Archive is enabled cannot be redefined online. You cannot enable Flashback Data Archive for the interim table.

• Tables with BFILE columns cannot be redefined online.

• Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.

• On a system with sufficient resources for parallel execution, and in the case where the interim table is not partitioned, redefinition of a LONG column to aLOB column can be executed in parallel, provided that:

  • The segment used to store the LOB column in the interim table belongs to a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled.

  • There is a simple mapping from one LONG column to one LOB column, and the interim table has only one LOB column.

  In the case where the interim table is partitioned, the normal methods for parallel execution for partitioning apply.

• Tables in the SYS and SYSTEM schema cannot be redefined online.

• Temporary tables cannot be redefined.

• A subset of rows in the table cannot be redefined.

• Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.

• If new columns are being added as part of the redefinition and there are no column mappings for these columns, then they must not be declared NOTNULL until the redefinition is complete.

• There cannot be any referential constraints between the table being redefined and the interim table.

• Table redefinition cannot be done NOLOGGING.

• For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.

• You cannot perform online redefinition on a table that is partitioned if the table includes one or more nested tables.

• You can convert a VARRAY to a nested table with the CAST operator in the column mapping. However, you cannot convert a nested table to a VARRAY.

转载地址：http://sutdi.baihongyu.com/