Oracle Database 11g Certified Master Upgrade Sample Questions:

1. Every resource plan must contain an allocation to which consumer group?

A) BASE_GROUP
B) DEFAULT_GROUP
C) OTHER_GROUPS
D) SYS_GROUP
E) LOW_GROUP

2. Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines. (Choose two.)

A) Automatic Workload Repository (AWR) snapshots
B) Cursor cache
C) SQL Tuning Set
D) Stored outline

3. While Monitoring the space usage in your database that is in ARCHIVELOG mode you observed that the flash recovery area does not have enough free space to accommodate any more files and you do not have necessary permissions to add more space to it.
Identify the two events that can occur in the event of a log switch? (Choose two.)

A) The Oracle database server deletes a file that is on the obsolete file list to make free space in the flash recovery area
B) The log switch hangs occur for transactions until free space is available in the flash recovery area
C) The database instance status is implicitly changed to RESTRICTED mode and file creations to the flash recovery area are prevented
D) An entry is created in the alert log file and the database instance continues to function normally

4. You performed the RMAN database backup with the KEEP option. Which two statements are true regarding this backup? (Choose two.)

A) The KEEP option is an attribute of an individual backup piece.
B) The backup contains data files, the server parameter file, and the control file even if the control file autobackup is disabled.
C) The KEEP option overrides the configured retention policy.
D) The backup contains only data files and archived redo log files.

5. The INV_HISTORY table is created using the command:
SQL>CREATE TABLE INV_HISTORY
(inv_no NUMBER(3),
inv_date DATE,
inv_amt NUMBER(10,2))
partition by range (inv_date)
interval (numtoyminterval(1,'month'))
(partition p0 values less than (to_date('01-01-2005','dd-mm-yyyy')),
partition p1 values less than (to_date('01-01-2006','dd-mm-yyyy')));
The following data has been inserted into the INV_HISTORY table :
INV_NO INV_DATE INV_AMT 1 30-dec-2004 1000 2 30-dec-2005 2000 3 1-feb-2006 3000 4 1-mar-2006 4000 5 1-apr-2006 5000
You would like to store the data belonging to the year 2006 in a single partition and issue the command:
SQL> ALTER TABLE inv_history MERGE PARTITIONS FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')), FOR(TO_DATE('15-apr-2006')) INTO PARTITION sys_py;
What would be the outcome of this command?

A) It produces an error because the date values specified in the merge do not match the date values stored in the table.
B) It executes successfully, and the transition point is set to '1-apr-2006'.
C) It executes successfully, and the transition point is set to '15-apr-2006'.
D) It produces an error because the partitions specified for merging are not adjacent.

Solutions: