Name of the Exam--1Z0-050 Oracle Database 11g: New Features for Administrators 15 Important Question From Updated Dumps given below Also See --- Oracle Certification Tracks QUESTION 1
Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL
Tuning Sets (STS) from the previous version. After changing the OPTIMIZER_FEATURE_ENABLE
parameter to 10.2.0.4 and running the SQL Performance Analyzer, you observed performance regression for a few SQL statements. What would you do with these SQL statements?
A. Set OPTIMIZER_USE_PLAN_BASELINES to FALSE to prevent the use of regressed plans.
B. Capture the plans from the previous version using STS and then load them into the stored outline.
C. Capture the plans from the previous version using STS and then load them into SQL Management Base
D. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE to prevent the plans from being loaded to the SQL plan baseline.
Correct Answer: C QUESTION 2
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. It drops all disk paths from the dga disk group.
C. All the disks in the dga disk group would be OFFLINE and the DISK_REPAIR_TIME disk attribute would
be set to 5 hours.
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.
Correct Answer: D
Evaluate the following command: SQL>ALTER SYSTEM SET db_securefile = 'IGNORE';
What is the impact of this setting on the usage of SecureFiles?
A. It forces BasicFiles to be created even if the SECUREFILE option is specified to create the LOB.
B. It forces SecureFiles to be created even if the BASICFILE option is specified to create the LOB.
C. It does not allow the creation of SecureFiles and generates an error if the SECUREFILE option is specified to create the LOB.
D. It ignores the SECUREFILE option only if a Manual Segment Space Management tablespace is used and
creates a BasicFile.
Correct Answer: A
You are managing an Oracle Database 11g ASM instance with a disk group dg01 having three disks. One of the disks in the disk group becomes unavailable because of power failure. You issued the following command to change the DISK_REPAIR_TIME attribute from 3.6 hours to 5 hours: ALTER DISKGROUP dg01 SET ATTRIBUTE 'disk_repair_time' = '5h';
To which disks in the disk group will the new value be applicable?
A. all disks in the disk group
B. all disks that are currently in OFFLINE mode
C. all disks that are not currently in OFFLINE mode
D. all disks in the disk group only if all of them are ONLINE Correct Answer: C
Which two statements about the SQL Management Base (SMB) are true? (Choose two.)
A. It contains only SQL profiles generated by SQL Tuning Advisor.
B. It stores plans generated by the optimizer using a stored outline.
C. It is part of the data dictionary and stored in the SYSAUX tablespace.
D. It is part of the data dictionary and stored in the SYSTEM tablespace.
E. It contains the statement log, the plan history, plan baselines, and SQL profiles. Correct Answer: CE
In which of the scenario will the DBA perform recovery? (Choose all that apply)
A. The alert log file is corrupted
B. One of the redo log members is corrupted
C. A database user terminates the session abnormally
D. The hard disk on which the data files is stored is corrupted
E. A tablespace is accidentally dropped
Correct Answer: DE
You issued the following command:
CREATE GLOBAL TEMPORARY TABLE admin_work_area
ON COMMIT DELETE ROWS
An index is then created on the ADMIN_WORK_AREA temporary table. Which two statements are true
regarding the TBS_T1 tablespace in the above command? (Choose two.)
A. It stores only the temporary table but not its indexes.
B. It stores both the temporary table as well as its indexes.
C. It must be a nondefault temporary tablespace for the database.
D. It can be a default or nondefault temporary tablespace for the database.
E. It must be the default temporary tablespace of the user who issues the command.
Correct Answer: BD
The Oracle Database 11g database is running in the ARCHIVELOG mode. The archived redo log files are
stored on three locations. The Flash Recovery Area is one of the locations. The details are given below:
LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive'
LOG_ARCHIVE_DEST_2 = 'SERVICE = stdb1'
DB_RECOVERY_FILE_DEST = '/u01/oradata'
Examine the following RMAN command issued to set the deletion policy for archived log files:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY
TO BACKED UP 2 TIMES TO sbt;
Which statement is true regarding what this command accomplishes?
A. All archived redo log files backed up at least twice to tape are deleted.
B. All archived redo log files backed up at least once to tape are eligible for deletion.
C. All archived redo log files backed up at least twice to tape are deleted from the flash recovery area.
D. All archived redo log files in local archiving destinations and the flash recovery area backed up at least
twice to tape are eligible for deletion.
Correct Answer: D
An index called ORD_CUSTNAME_IX has been created on the CUSTNAME column in the ORDERS table using the following command:
SQL>CREATE INDEX ord_custname_ix ON orders(custname);
The ORDERS table is frequently queried using the CUSTNAME column in the WHERE clause. You want to check the impact on the performance of the queries if the index is not available. You do not want the index to be dropped or rebuilt to perform this test. Which is the most efficient method of performing this task?
A. disabling the index
B. making the index invisible
C. making the index unusable
D. using the MONITORING USAGE clause for the index
Correct Answer: B QUESTION 9
Which tasks can be accomplished using the DBMS_LOB.SETOPTIONS procedure?
A. only encryption and compression settings for all SecureFile LOBs
B. only encryption and deduplication settings for only SecureFile CLOBs
C. deduplication, encryption, and compression settings for all SecureFile LOBs
D. deduplication, encryption, and compression settings only for SecureFile CLOBs
Correct Answer: C
Which of the following information will be gathered by the SQL Test Case Builder for the problems pertaining to SQL-related problems? (Choose all that apply.)
A. ADR diagnostic files
B. all the optimizer statistics
C. initialization parameter settings
D. PL/SQL functions, procedures, and packages
E. the table and index definitions and actual data
Correct Answer: BCE
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. A DBA cannot change the duration of the maintenance window after it is created.
C. In case of a long maintenance window, all Automated Maintenance Tasks are restarted every four hours.
D. A DBA can control the percentage of the resource allocated to the Automated Maintenance Tasks in each window.
Correct Answer: AD
You have applications that have frequently executed queries, and produce small and static result sets.
You configure the sqlnet.ora file in the client machine to set a nonzero value for the
What is the purpose of this configuration?
A. to avoid round trips to the server by enabling caching of query results in client memory
B. to improve performance by storing a copy of the data from the private SQL area of the PGA
C. to enhance the query performance by creating a cache in the client memory for sorting operations
D. to avoid the storing of query plans and results in the server by creating a cache in the client memory Correct Answer: A
You plan to use SQL Performance Analyzer to analyze the SQL workload. You created a SQL Tuning Set as a part of the workload capturing. What information is captured as part of this process? (Choose all that apply.)
A. the SQL text
B. the execution plan
C. the execution context
D. the execution frequency
E. the system change number (SCN)
Correct Answer: ACD
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 fast refresh performed after the online table redefinition is completed.
B. The materialized view should have a complete refresh performed after the online table redefinition is
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. Correct Answer: B
Evaluate the following function code:
CREATE FUNCTION get_dept_avg(dept_id NUMBER)
RESULT_CACHE RELIES_ON (EMPLOYEES)
SELECT AVG(SALARY)INTO avgs
WHERE DEPARTMENT_ID = dept_id;
Which statement is true regarding the above function?
A. The cached result becomes invalid when any structural change is done to the EMPLOYEES table.
B. If the function execution results in an unhandled exception, the exception result is also stored in the cache.
C. Each time the function is invoked in a different session, the current result in the result cache gets
D. If the function is invoked with a different parameter value, the existing result in the result cache gets
overwritten by the latest value.