Oracle OCP 1Z0-050（111-120题）解析
Oracle OCP 1Z0-050（111-120题）解析
QUESTION 111:
Sales details are being stored on a daily basis in the SALES_2007 table. A large amount of data is added to the table daily. To save disk space, you issued the following command:
ALTER TABLE sales_2007 COMPRESS FOR ALL OPERATIONS;
What would be the outcome of this command?
A. It immediately compresses all existing data as well as new data, resulting from either fresh additions or modifications to existing data.
B. It produces an error because data already exists in the table.
C. It compresses all data added or modified henceforth but the existing data in the table is not compressed immediately.
D. It produces an error because compression can be enabled at table creation only.
Answer: C
解析：
关于表压缩（Table Comprression）的内容，参考Oracle官方文档：
https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11629
Table Compression 表压缩：
当数据库大小增长到几百G或T时,可以考虑压缩表。压缩表节省空间，减少buffer cache缓存池的内存使用。 表压缩还能提高读数据时的查询速度。 但牺牲的是DML，数据装载时CPU的使用率，在OLAP系统上特点尤为突出，OLTP系统也可以使用。
可以在创建表时指定表压缩，也可以把已经建好的表改为表压缩，但只有更改后的数据是压缩的，同样也可以把一个压缩表更改为一个非压缩表，只有在更改后的数据才是非压缩的。
压缩表有两种选项：
COMPRESS FOR DIRECT_LOAD OPERATIONS：这是默认的压缩选项,即只对直接插入的数据压缩。
COMPRESS FOR ALL OPERATIONS：对所以的DML操作的数据都压缩，但COMPATIBLE参数必须设为11.1.0或更高。
示例：（说明一下示例是在Oracle 12c 上进行的，与Oracle 11g中给出的信息略有不同）
SQL> CREATE TABLE t1 (a number, b varchar2(10)) COMPRESS FOR ALL OPERATIONS;
表已创建。
SQL> CREATE TABLE t2 (a number, b varchar2(10)) COMPRESS FOR DIRECT_LOAD OPERATIONS;
表已创建。
SQL> CREATE TABLE t3 (a number, b varchar2(10)) COMPRESS;
表已创建。
SQL> CREATE TABLE t4 (a number, b varchar2(10));
表已创建。
SQL> col table_name format a10;
SQL> SELECT table_name, compression, compress_for FROM user_tables;
TABLE_NAME COMPRESSION COMPRESS_FOR
---------- ---------------- ------------------------------------------------------------
EMP DISABLED
DEPT DISABLED
T1 ENABLED ADVANCED
T2 ENABLED BASIC
T3 ENABLED BASIC
T4 DISABLED
已选择 6 行。
SQL> ALTER TABLE t4 COMPRESS FOR ALL OPERATIONS;
表已更改。
SQL> SELECT table_name, compression, compress_for FROM user_tables;
TABLE_NAME COMPRESSION COMPRESS_FOR
---------- ---------------- ------------------------------------------------------------
EMP DISABLED
DEPT DISABLED
T1 ENABLED ADVANCED
T2 ENABLED BASIC
T3 ENABLED BASIC
T4 ENABLED ADVANCED
已选择 6 行。
SQL> ALTER TABLE t4 COMPRESS FOR DIRECT_LOAD OPERATIONS;
表已更改。
SQL> SELECT table_name, compression, compress_for FROM user_tables;
TABLE_NAME COMPRESSION COMPRESS_FOR
---------- ---------------- ------------------------------------------------------------
EMP DISABLED
DEPT DISABLED
T1 ENABLED ADVANCED
T2 ENABLED BASIC
T3 ENABLED BASIC
T4 ENABLED BASIC
已选择 6 行。创建分区表后，进行Table Compress修改：
SQL> CREATE TABLE sales(
2 saleskey number,
3 quarter number,
4 product number,
5 salesperson number,
6 amount number(12,2),
7 region varchar2(10)) COMPRESS
8 PARTITION BY LIST(region)
9 ( PARTITION northwest VALUES ('NORTHWEST'),
10 PARTITION southwest VALUES ('SOUTHWEST'),
11 PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,
12 PARTITION southeast VALUES ('SOUTHEAST'));
表已创建。
SQL> SELECT table_name, compression, compress_for FROM user_tables;
TABLE_NAME COMPRESSION COMPRESS_FOR
---------- ---------------- ------------------------------------------------------------
EMP DISABLED
DEPT DISABLED
T1 ENABLED ADVANCED
T2 ENABLED BASIC
T3 ENABLED BASIC
T4 ENABLED BASIC
SALES
已选择 7 行。
SQL> select TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,COMPRESSION,COMPRESS_FOR FROM user_tab_partitions order by 3;
TABLE_NAME PARTITION_NAME PARTITION_POSITION COMPRESSION COMPRESS_FOR
---------- -------------------- ------------------ ---------------- ------------------------------------------------------------
SALES NORTHWEST 1 ENABLED BASIC
SALES SOUTHWEST 2 ENABLED BASIC
SALES NORTHEAST 3 DISABLED
SALES SOUTHEAST 4 ENABLED BASIC
SQL>
QUESTION 112:
When executing a SQL workload, you choose to generate execution plans only, without collecting execution statistics. Which two statements describe the implications of this?
(Choose two.)
A. Only the changes in the execution plan, and not performance regression, are detected.
B. It shortens the time of execution and reduces the impact on system resources.
C. It produces less accurate results of the comparison analysis.
D. It automatically calls the SQL Tuning Advisor for recommendations.
Answer: B,C
QUESTION 113:
Which dependent object will get invalidated even if it is not affected by the table redefinition?
A. packages
B. triggers
C. synonyms
D. views
Answer: B
QUESTION 114:
A PL/SQL procedure queries only those columns of a redefined table that were unchanged by the online table redefinition. What happens to the PL/SQL procedure after the online table redefinition?
A. It remains valid.
B. It becomes invalid for all options of online table redefinition but automatically gets revalidated the next time it is used.
C. It becomes invalid only if the storage parameters have been modified and it automatically gets revalidated the next time it is used.
D. It becomes invalid for all options of online table redefinition and is automatically recompiled during online redefinition of the table.
Answer: A
参考Oracle官方文档：
https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11668
QUESTION 115:
The ADMIN_EMP table has columns EMPNO, ENAME, DEPTNO, and SAL. It has a materialized view EMP_MV with a materialized log and an ENAME_IDX index on the ENAME column.
You need to perform an online table redefinition on the ADMIN_EMP table to move it from the TBS1 tablespace to the TBS2 tablespace in the same schema.
What action is required for the dependent objects when you perform online redefinition on the table?
A. The materialized view should have a complete refresh performed after the online table redefinition is completed.
B. The materialized view should have a fast refresh performed after the online table redefinition is completed.
C. The materialized view, materialized log, and the index should be dropped and re-created after the online table redefinition is complete.
D. The materialized view and materialized log should be dropped and all constraints disabled and re-created after the online table redefinition is complete.
Answer: A
参考Oracle官方文档
https://docs.oracle.com/cd/E11882_01/server.112/e25554/advmv.htm#DWHSG8250
https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#DWHSG008
QUESTION 116:
Which setting enables the baselines by default in Oracle Database 11g?
A. enabling Automated Maintenance Task using Oracle Enterprise Manager
B. setting the STATISTICS_LEVEL parameter to TYPICAL
C. adding filters to the Automatic Database Diagnostic Monitor (ADDM)
D. setting the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE
Answer: B
参考Oracle官方文档：
https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams254.htm#REFRN10214
QUESTION 117:
Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance window? (Choose all that apply.)
A. tuning each SQL statement in the order of importance
B. generating baselines that include candidate SQLs for tuning
C. generating the SQL profile
D. adding tuned SQL plans into the SQL plan baseline
E. testing and accepting the SQL profile
F. generating a list of candidate SQLs for tuning
Answer: A,C,E,F
参考Oracle官方文档：
https://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF94840
https://docs.oracle.com/cd/E11882_01/server.112/e10822/tdppt_sqltune.htm#TDPPT310
QUESTION 118:
You installed Oracle Database 11g and are performing a manual upgrade of the Oracle9i database. As a part of the upgrade process, you execute the following script:
SQL>@utlu111i.sql
Which statement about the execution of this script is true?
A. It must be executed from the Oracle Database 11g environment.
B. It must be executed only after the SYSAUX tablespace has been created.
C. It must be executed from both the Oracle Database 11g and Oracle Database 9i environments.
D. It must be executed from the environment of the database that is being upgraded.
E. It must be executed only after AUTOEXTEND is set to ON for all existing tablespaces.
Answer: D
参考Oracle官方文档：
https://docs.oracle.com/cd/E52734_01/core/IAMUP/intro_manual_upg.htm#IAMUP4854
QUESTION 119:
Exhibit #1:
Exhibit #2:
View the Exhibit exhibit1.
In the CUSTOMERS_OBE table, when the value of CUST_STATE_PROVINCE is "CA", the value of COUNTRY_ID is "US."
View the Exhibit exhibit2 to examine the commands and query plans.
The optimizer can sense 8 rows instead of 29 rows, which is the actual number of rows in the table. What can you do to make the optimizer detect the actual selectivity?
A. Change the STALE_PERCENT value for the CUSTOMERS_OBE table.
B. Set the STATISTICS_LEVEL parameter to TYPICAL.
C. Create extended statistics for the CUST_STATE_PROVINCE and CUSTOMERS_OBE columns.
D. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.
Answer: C
QUESTION 120:
Exhibit:
View the Exhibit for the object interdependency diagram.
The PRODUCTS table is used to create the PRODCAT_VW view.
PRODCAT_VW is used in the GET_DATA procedure.
GET_DATA is called in the CHECK_DATA function.
A new column PROD_QTY is added to the PRODUCTS table.
How does this impact the status of the dependent objects?
A. All dependent objects remain valid.
B. Only the view becomes invalid and gets automatically revalidated the next time it is used.
C. Only the procedure and function become invalid and get automatically revalidated the next time they are called.
D. Only the procedure and function become invalid and must be recompiled.
Answer: A
参考Oracle官方文档：
https://docs.oracle.com/cd/E11882_01/server.112/e25494/general.htm#ADMIN12492