Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Some non-DBA users in your database have been granted ANY TABLE system privileges and they are able to access data dictionary base tables. You decide to restrict their access to data dictionary objects. Which method would you adopt to achieve this objective?
A. Revoke the RESOURCE role from the users.
B. Set the value of the OS_ROLES parameter to TRUE.
C. Use Database Resource Manager to restrict user access to objects.
D. Grant ANY TABLE system privileges again without ADMIN OPTION.
E. Set the value of the O7_DICTIONARY_ACCESSIBILITY parameter to FALSE.
Answer: E
Q2. Which statements listed below describe the data dictionary views?
1. These are stored in the SYSTEM tablespace
2. These are the based on the virtual tables
3. These are owned by the SYS user
4. These can be queried by a normal user only if O7_DICTIONARY_ACCESSIBLILITY parameter is set to TRUE
5. The V$FIXED_TABLE view can be queried to list the names of these views
A. 1 and 3
B. 2,3 and 5
C. 1,2, and 5
D. 2,3,4 and 5
Answer: A
Q3. You have statistics collected for some selected tables. Your requirement is that the statistics for the tables and all dependent indexes must not be overwritten by further statistics collection until a certain point of time. How would you achieve this?
A. Lock statistics for the tables.
B. Change STALE_PERCENT to zero for the tables.
C. Set the TIMED_STATISTICS parameter to TRUE.
D. Set the STATISTICS_LEVEL parameter to BASIC.
E. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
Answer: A
Q4. Which two files in the database can be configured for automatic backups by using the autobackup feature in Recovery Manager (RMAN)? (Choose two.)
A. Data Files
B. Control Files
C. Parameter Files
D. Online Redo Log Files
E. Server Parameter File
Answer: BE
Q5. The HR user creates a stand-alone procedure as follows and grants the EXECUTE privilege on the procedure to many database users:
CREATE OR REPLACE PROCEDURE create_dept ( v_deptno NUMBER, v_dname VARCHAR2,
v_mgr NUMBER, v_loc NUMBER)
BEGIN
INSERT INTO hr.departments VALUES (v_deptno, v_dname, v_mgr, v_loc);
END;
The users having permission to execute the procedure are able to insert records into the
DEPARTMENTS table even though they do not have the INSERT privilege on the table. You want only those users who have privileges on the DEPARTMENTS table to be able to execute the procedure successfully.
What would you suggest to the PL/SQL developers to achieve this?
A. Create the procedure with definer's right.
B. Create the procedure with invoker's right.
C. Grant the EXECUTE privilege with GRANT OPTION on the procedure to selected users.
D. Create the procedure as part of a PL/SQL package and grant the EXECUTE privilege on the package to selected users.
Answer: B
Q6. In a new installation of Oracle Database 11g, you perform these activities:
1. Organize software and data on different subdirectories to avoid poor performance.
2. Use consistent naming conventions for database files.
3. Separate administrative information pertaining to the database in different directories.
Which option corresponds to the type of activities you performed?
A. Oracle Managed Files
B. Oracle Grid Architecture
C. Optimal Flexible Architecture
D. Oracle database architecture
E. Automatic Storage Management
Answer: C
Q7. View the Exhibit and examine the attributes of an undo tablespace. In an OLTP system, the user SCOTT has started a query on a large table in the peak transactional hour that performs bulk inserts. The query runs for more than 15 minutes and then SCOTT receives the following error:
ORA-01555: snapshot too old What could be the reason for this error?
Exhibit:
A. The query is unable to get a read-consistent image.
B. There is not enough space in Flash Recovery Area.
C. There is not enough free space in the flashback archive.
D. The query is unable to place data blocks in undo tablespace.
Answer: A
Q8. You have recently collected statistics on certain objects of a schema in your database. But you observe suboptimal execution plans for the queries on these objects after two days of statistics collection. The optimizer statistics retention period is set to its default value.
Which action would help to use the previous set of statistics on the objects?
A. Restore statistics from statistics history.
B. Reduce the optimizer statistics retention period by 2 days.
C. Set the OPTIMIZER_PENDING_STATISTICS parameter to TRUE.
D. Reduce the Automatic Workload Repository (AWR) retention period by 2 days.
Answer: A
Q9. Which two operations can be performed on an external table? (Choose two.)
A. Create a view on the table
B. Create an index on the table
C. Create a synonym on the table
D. Add a virtual column to the table
E. Update the table using the UPDATE statement
F. Delete rows in the table using the DELETE command
Answer: AC
Q10. Which task would you perform before you run Oracle Universal Installer (OUI) in silent or suppressed mode for an installation?
A. Run the root.sh script.
B. Create the oraInst.loc file.
C. Create the tnsnames.ora file.
D. Run the oraInstRoot.sh script.
Answer: B
Q11. You have issued a SHUTDOWN ABORT command to bring down your database instance.
Consider the steps that will be performed later when you open the database:
1. SGA is allocated.
2. Control file is read.
3. Redo log files are opened.
4. Instance recovery is started.
5. Background processes are started.
6. Data file headers are checked for consistency.
7. Server parameter file or the initialization parameter file is read.
Which option has the correct order in which these steps occur?
A. 7, 1, 5, 2, 3, 6, 4
B. 1, 2, 3, 7, 5, 6, 4
C. 7, 1, 4, 5, 2, 3, 6
D. 1, 7, 5, 4, 2, 3, 6
Answer: A
Q12. Which statement is true about the UNDO_RETENTION parameter when retention guarantee is not enabled?
A. It is the time period after which the undo data becomes obsolete.
B. It is the time period after which the committed undo data would be transferred to a temporary tablespace.
C. It is the minimum time period up to which the committed undo data would be retained if free undo space is available.
D. It is the time period after which the undo data is transferred to the Flash Recovery Area to provide read consistency.
Answer: C
Q13. Your database is functional with a peak load for the last one hour. You want to preserve the performance statistics collected during this period to be used for comparison when you analyze the performance of the database in the future.
What action would you take to achieve this task?
A. Insert finding directives for ADDM tasks in the future.
B. Create a baseline on a pair of snapshots spanning the peak-load period.
C. Decrease the snapshot interval in the AWR to collect more snapshots during the peakload period.
D. Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid automatic purging of snapshots.
Answer: B
Q14. View the Exhibits and examine lock waits. Users HR and SH complain that their transactions on one of the application tables, EMP, are waiting for response.
Which action would you take to release the lock and enable users HR and SH to continue with their transactions?
07 (exhibit):
A. Kill the session of the user SCOTT with session ID118.
B. Issue manual checkpoint using the ALTER SYSTEM command.
C. Modify the profile used by user SCOTT to reduce the CONNECT_TIME limit.
D. Flush the Shared Pool to remove the SQL statement causing "wait" in memory.
Answer: A
Q15. You are managing an Oracle Database 11g database. You configured the database to run in ARCHIVELOG mode.
Which two statements are true in this scenario? (Choose two.)
A. You must shut down the database instance to perform the backups.
B. You must configure the Flash Recovery Area (FRA) for the database.
C. You can perform complete database backups without closing the database.
D. All the previous closed database backups including control files become invalid afteryou configure the database to ARCHIVELOG mode.
Answer: CD