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

Oracle OCP 1Z0-050（51-60题）解析

QUESTION 51:

Which two statements are true with respect to the maintenance window? (Choose two.)

A. A DBA can enable or disable an individual task in all maintenance windows.

B. In case of a long maintenance window, all Automated Maintenance Tasks are restarted every four hours.

C. A DBA cannot change the duration of the maintenance window after it is created.

D. A DBA can control the percentage of the resource allocated to the Automated Maintenance Tasks in each window.

Answer: A,D

解析：

参考Oracle官方文档：

根据文档描述DBA在启用和禁用 Maintenance Task时：

Enabling and Disabling Maintenance Tasks for all Maintenance Windows

You can disable a particular automated maintenance task for all maintenance windows with a single operation. You do so by calling the DISABLE procedure of the DBMS_AUTO_TASK_ADMIN PL/SQL package without supplying the window_name argument. For example, you can completely disable the Automatic SQL Tuning Advisor task as follows:

BEGIN  dbms_auto_task_admin.disable(    client_name => 'sql tuning advisor',    operation   => NULL,    window_name => NULL);END;/

To enable this maintenance task again, use the ENABLE procedure, as follows:

BEGIN  dbms_auto_task_admin.enable(    client_name => 'sql tuning advisor',    operation   => NULL,    window_name => NULL);END;/

The task names to use for the client_name argument are listed in the DBA_AUTOTASK_CLIENT database dictionary view.

To enable or disable all automated maintenance tasks for all windows, call the ENABLE or DISABLE procedure with no arguments.

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows

By default, all maintenance tasks run in all predefined maintenance windows. You can disable a maintenance task for a specific window. The following example disables the Automatic SQL Tuning Advisor from running in the window MONDAY_WINDOW:

BEGIN  dbms_auto_task_admin.disable(    client_name => 'sql tuning advisor',     operation   => NULL,     window_name => 'MONDAY_WINDOW');END;/

About Resource Allocations for Automated Maintenance Tasks

By default, all predefined maintenance windows use the resource plan DEFAULT_MAINTENANCE_PLAN. Automated maintenance tasks run under its subplan ORA$AUTOTASK_SUB_PLAN. This subplan divides its portion of total resource allocation equally among the maintenance tasks.

DEFAULT_MAINTENANCE_PLAN defines the following resource allocations:

Consumer Group/subplan	Level 1	Level 2	Maximum Utilization Limit
ORA$AUTOTASK_SUB_PLAN	-	25%	90
ORA$DIAGNOSTICS	-	5%	90
OTHER_GROUPS	-	70%
SYS_GROUP	75%	-

In this plan, any sessions in the SYS_GROUP consumer group get priority. (Sessions in this group are sessions created by user accounts SYS and SYSTEM.) Any resource allocation that is unused by sessions in SYS_GROUP is then shared by sessions belonging to the other consumer groups and subplans in the plan. Of that allocation, 25% goes to maintenance tasks, 5% goes to background processes performing diagnostic operations, and 70% goes to user sessions. The maximum utilization limit for ORA$AUTOTASK_SUB_PLAN and ORA$DIAGNOSTICS is 90. Therefore, even if the CPU is idle, this group/plan cannot be allocated more than 90% of the CPU resources.

To reduce or increase resource allocation to the automated maintenance tasks, you make adjustments to DEFAULT_MAINTENANCE_PLAN. See for more information.

Note that as with any resource plan, the portion of an allocation that is not used by a consumer group or subplan is available for other consumer groups or subplans. Note also that the Database Resource Manager does not begin to limit resource allocations according to resource plans until 100% of CPU is being used.

Note:

Although DEFAULT_MAINTENANCE_PLAN is the default, you can assign any resource plan to any maintenance window. If you do change a maintenance window resource plan, ensure that you include the subplan ORA$AUTOTASK_SUB_PLAN and the consumer group ORA$DIAGNOSTICS in the new plan.

See Also:

for more information on resource plans.

A maintenance window is a contiguous time interval during which automated maintenance tasks are run. Maintenance windows are Oracle Scheduler windows that belong to the window group named MAINTENANCE_WINDOW_GROUP. A Scheduler window can be a simple repeating interval (such as "between midnight and 6 a.m., every Saturday"), or a more complex interval (such as "between midnight and 6 a.m., on the last workday of every month, excluding company holidays").

When a maintenance window opens, Oracle Database creates an Oracle Scheduler job for each maintenance task that is scheduled to run in that window. Each job is assigned a job name that is generated at run time. All automated maintenance task job names begin with ORA$AT. For example, the job for the Automatic Segment Advisor might be called ORA$AT_SA_SPC_SY_26. When an automated maintenance task job finishes, it is deleted from the Oracle Scheduler job system. However, the job can still be found in the Scheduler job history.

Configuring Automated Maintenance Tasks

To enable or disable specific maintenance tasks in any subset of maintenance windows, you can use the DBMS_AUTO_TASK_ADMIN PL/SQL package.

This section contains the following topics:

Enabling and Disabling Maintenance Tasks for all Maintenance Windows

Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows

Configuring Resource Allocations for Automated Maintenance Tasks

This section contains the following topics on resource allocation for maintenance windows:

About Resource Allocations for Automated Maintenance Tasks

Changing Resource Allocations for Automated Maintenance Tasks

 

QUESTION 52:

Which statements are true regarding the concept of problems and incidents in the fault diagnosability infrastructure for Oracle Database 11g? (Choose all that apply.)

A. Only the incident metadata and dumps are stored in the Automatic Diagnostic Repository (ADR).

B. The problem key is the same as the incident number.

C. The database sends an incident alert to the Oracle Enterprise Manager Database Home page.

D. Every problem has a problem key, which is a text string that describes the problem.

E. The database makes an entry into the alert log file when problems and incidents occur.

Answer: C,D,E

解析：

About Incidents and Problems

To facilitate diagnosis and resolution of critical errors, the fault diagnosability infrastructure introduces two concepts for Oracle Database: problems and incidents.

A problem is a critical error in a database instance, Oracle Automatic Storage Management (Oracle ASM) instance, or other Oracle product or component. Critical errors manifest as internal errors, such as ORA-00600, or other severe errors, such as ORA-07445 (operating system exception) or ORA-04031 (out of memory in the shared pool). Problems are tracked in the ADR. Each problem has a problem key, which is a text string that describes the problem. It includes an error code (such as ORA 600) and in some cases, one or more error parameters.

An incident is a single occurrence of a problem. When a problem (critical error) occurs multiple times, an incident is created for each occurrence. Incidents are timestamped and tracked in the Automatic Diagnostic Repository (ADR). Each incident is identified by a numeric incident ID, which is unique within the ADR. When an incident occurs, the database:

Makes an entry in the alert log.

Sends an incident alert to Oracle Enterprise Manager (Enterprise Manager).

Gathers first-failure diagnostic data about the incident in the form of dump files (incident dumps).

Tags the incident dumps with the incident ID.

Stores the incident dumps in an ADR subdirectory created for that incident.

Diagnosis and resolution of a critical error usually starts with an incident alert. Incident alerts are displayed on the Enterprise Manager Database Home page or Oracle Automatic Storage Management Home page. The Database Home page also displays in its Related Alerts section any critical alerts in the Oracle ASM instance or other Oracle products or components. After viewing an alert, you can then view the problem and its associated incidents with Enterprise Manager or with the ADRCI command-line utility.

 

QUESTION 53:

What two statements are true regarding the recommendations received from the SQL Access Advisor? (Choose two.)

A. It cannot generate recommendations that support multiple workload queries.

B. It can recommend partitioning on tables provided that the workloads have some predicates and joins on the columns of the NUMBER or DATE type.

C. It can recommend partitioning only on tables that have at least 10,000 rows.

D. It can recommend only B-tree indexes and not bitmap or function-based indexes.

Answer: B,C

 

QUESTION 54:

Which two statements about the SQL Management Base (SMB) are true? (Choose two.)

A. It stores plans generated by the optimizer using a stored outline.

B. It is part of the data dictionary and stored in the SYSTEM tablespace.

C. It is part of the data dictionary and stored in the SYSAUX tablespace.

D. It contains only SQL profiles generated by SQL Tuning Advisor.

E. It contains the statement log, the plan history, plan baselines, and SQL profiles.

Answer: C,E

解析：

参考Oracle官方文档：

15.6 SQL Management Base

The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles. To allow weekly purging of unused plans and logs, the SMB uses automatic space management.

You can also add plans manually to the SMB for a set of SQL statements. This feature is especially useful when upgrading the database from a version before Oracle Database 11g because it helps to minimize plan regressions resulting from the use of a new optimizer version.

Because the SMB is located entirely within SYSAUX, the database does not use SQL plan management and SQL tuning features when this tablespace is unavailable.

 

QUESTION 55:

Exhibit:

View the Exhibit to examine the metrics with a threshold.

Which statement is true regarding the Number of Transactions (per second) metric?

A. Oracle determines when an adaptive threshold has been breached based on the maximum value captured by the baseline.

B. The total concurrent number of threshold violations, which must occur before an alert is raised for the metric, has been set to zero.

C. Oracle uses statistical relevance to determine when an adaptive threshold has been breached for the metric.

D. The statistics for the metric values observed over the baseline time period are not examined to determine threshold values.

Answer: C

 

QUESTION 56:

Which two statements are true regarding the starting of the database instance using the following command? (Choose two.)

SQL>STARTUP UPGRADE

A. It allows only SYSDBA connections.

B. It enables all system triggers.

C. It ensures that all job queues remain active during the upgrade process.

D. It sets system initialization parameters to specific values that are required to enable database upgrade scripts to be run.

Answer: A,D

解析：

参考Oracle官方文档

在执行STARTUP UPGRADE命令时需要具有SYSDBA权限；

The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBA sessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.

You might be required to use the PFILE option to specify the location of your initialization parameter file.

 

QUESTION 57:

To generate recommendations to improve the performance of a set of SQL queries in an application, you execute the following blocks of code:

BEGIN

dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,'TASK1');

END;

/

BEGIN

dbms_advisor.set_task_parameter('TASK1','ANALYSIS_SCOPE','ALL');

dbms_advisor.set_task_parameter('TASK1','MODE','COMPREHENSIVE');

END;

/

BEGIN

dbms_advisor.execute_task('TASK1');

dbms_output.put_line(dbms_advisor.get_task_script('TASK1'));

END;

/

The blocks of code execute successfully; however, you do not get the required outcome.

What could be the reason?

A. The partial or complete workload scope needs to be associated with the task.

B. The type of structures (indexes, materialized views, or partitions) to be recommended need to be specified for the task.

C. A template needs to be associated with the task.

D. A workload needs to be associated with the task.

Answer: D

 

QUESTION 58:

You opened the encryption wallet and then issued the following command:

SQL>CREATE TABLESPACE securespace

DATAFILE '/home/user/oradata/secure01.dbf'

SIZE 150M

ENCRYPTION USING '3DES168'

DEFAULT STORAGE(ENCRYPT);

Then you closed the wallet. Later, you issued the following command to create the EMPLOYEES table in the SECURESPACE tablespace and you use the NO SALT option for the EMPID column.

What is the outcome?

A. It generates an error because the NO SALT option cannot be used with the ENCRYPT option.

B. It generates an error because the wallet is closed.

C. It creates the table but does not encrypt the data in it.

D. It creates the table and encrypts the data in it.

Answer: B

解析：

参考Oracle官方文档：

Consider Encrypting Columns That Contain Sensitive Data

You can encrypt individual table columns that contain sensitive data. Examples of sensitive data include social security numbers, credit card numbers, and medical records. Column encryption is transparent to your applications, with some restrictions.

Although encryption is not meant to solve all security problems, it does protect your data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.

Column encryption uses the transparent data encryption feature of Oracle Database, which requires that you create an Oracle wallet to store the master encryption key for the database. The wallet must be open before you can create a table with encrypted columns and before you can store or retrieve encrypted data. When you open the wallet, it is available to all sessions, and it remains open until you explicitly close it or until the database is shut down.

Transparent data encryption supports industry-standard encryption algorithms, including the following Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) algorithms:

• 3DES168
• AES128
• AES192
• AES256

You choose the algorithm to use when you create the table. All encrypted columns in the table use the same algorithm. The default is AES192. The encryption key length is implied by the algorithm name. For example, the AES128 algorithm uses 128-bit keys.

If you plan on encrypting many columns in one or more tables, you may want to consider encrypting an entire tablespace instead and storing these tables in that tablespace. Tablespace encryption, which also uses the transparent data encryption feature but encrypts at the physical block level, can perform better than encrypting many columns. Another reason to encrypt at the tablespace level is to address the following limitations of column encryption:

• If the COMPATIBLE initialization parameter set to 10.2.0, which is the minimum setting to enable transparent data encryption, data from encrypted columns that is involved in a sort or hash-join and that must be written to a temporary tablespace is written in clear text, and thus exposed to attacks. You must set COMPATIBLE to 11.1.0 or higher to ensure that encrypted data written to a temporary tablespace remains encrypted. Note that as long as COMPATIBLE is set to 10.2.0 or higher, data from encrypted columns remains encrypted when written to the undo tablespace or the redo log.
• Certain data types, such as object data types, are not supported for column encryption.
• You cannot use the transportable tablespace feature for a tablespace that includes tables with encrypted columns.
• Other restrictions, which are detailed in .

 

QUESTION 59:

Which statement about the enabling of table compression in Oracle Database 11g is true?

A. Compression can be enabled only at the table level for both direct loads and conventional DML.

B. Compression can be enabled at the table, tablespace, or partition level for direct loads only.

C. Compression can be enabled at the table, tablespace, or partition level for both direct loads and conventional DML.

D. Compression can be enabled at the table, tablespace, or partition level for conventional DML only.

Answer: C

参考Oracle官方文档：

Consider Using Table Compression

As your database grows in size to gigabytes or terabytes and beyond, consider using table compression. Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. Table compression is completely transparent to applications. It is especially useful in online analytical processing (OLAP) systems, where there are lengthy read-only operations, but can also be used in online transaction processing (OLTP) systems.

You specify table compression with the COMPRESS clause of the CREATE TABLE statement. You can enable compression for an existing table by using this clause in an ALTER TABLE statement. In this case, the only data that is compressed is the data inserted or updated after compression is enabled. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE...NOCOMPRESS statement. In this case, all data the was already compressed remains compressed, and new data is inserted uncompressed.

You can enable compression for all table operations or you can enable it for direct-path inserts only. When compression is enabled for all operations, compression occurs during all DML statements and when data is inserted with a bulk (direct-path) insert operation. To enable compression for conventional DML, you must set the COMPATIBLE initialization parameter to 11.1.0 or higher.

To enable compression for all operations you must use the COMPRESS FOR ALL OPERATIONS clause. To enable compression for direct-path inserts only, you use the COMPRESS FOR DIRECT_LOAD OPERATIONS clause. The keyword COMPRESS by itself is the same as the clause COMPRESS FOR DIRECT_LOAD OPERATIONS, and invokes the same compression behavior as previous database releases.

Adding and Dropping Columns in Compressed Tables

When you enable compression for all operations on a table, you can add and drop table columns. If you enable compression for direct-path inserts only, you cannot drop columns, and you can add columns only if you do not specify default values.

Examples

The following example enables compression for all operations on the table transaction, which is used in an OLTP application:

CREATE TABLE transaction ( ... ) COMPRESS FOR ALL OPERATIONS;

The next two examples enable compression for direct-path insert only on the sales_history table, which is a fact table in a data warehouse:

CREATE TABLE sales_history ( ... ) COMPRESS FOR DIRECT_LOAD OPERATIONS;

CREATE TABLE sales_history ( ... ) COMPRESS;

Compression and Partitioned Tables

You can enable or disable compression at the partition level. You can therefore have a table with both compressed and uncompressed partitions. If the compression settings for a table and one of its partitions disagree, the partition setting has precedence for the partition. In the following example, all partitions except the northeast partition are compressed.

CREATE TABLE sales

      (saleskey number,

       quarter number,

       product number,

       salesperson number,

       amount number(12, 2),

       region varchar2(10)) COMPRESS

   PARTITION BY LIST (region)

      (PARTITION northwest VALUES ('NORTHWEST'),

       PARTITION southwest VALUES ('SOUTHWEST'),

       PARTITION northeast VALUES  ('NORTHEAST') NOCOMPRESS,

       PARTITION southeast VALUES ('SOUTHEAST'),

       PARTITION western VALUES ('WESTERN'));

Determining If a Table Is Compressed

In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column. For partitioned tables, this column is null, and the COMPRESSION column of the *_TAB_PARTITIONS data dictionary view indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates whether the table is compressed FOR ALL OPERATIONS or for DIRECT LOAD ONLY.

SQL> SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME       COMPRESS COMPRESS_FOR

---------------- -------- ------------------

T1               DISABLED

T2               ENABLED  DIRECT LOAD ONLY

T3               ENABLED  FOR ALL OPERATIONS

 

QUESTION 60:

You are managing Oracle Database 11g with an ASM storage with high redundancy. The following command was issued to drop the disks from the dga disk group after five hours:

ALTER DISKGROUP dga OFFLINE DISKS IN FAILGROUP f2 DROP AFTER 5H;

Which statement is true in this scenario?

A. It starts the ASM fast mirror resync.

B. All the disks in the dga disk group would be OFFLINE and the DISK_REPAIR_TIME disk attribute would be set to 5 hours.

C. It drops all disk paths from the dga disk group.

D. All the disks in the dga disk group in failure group f2 would be OFFLINE and the DISK_REPAIR_TIME disk attribute would be set to 5 hours.

Answer: D

解析：

参考Oracle官方文档：

参考下面的例子：

The following example takes disk DATA_001 offline and drops it after five minutes.

ALTER DISKGROUP data OFFLINE DISK DATA_001 DROP AFTER 5m;