Oracle Dump Workshop I

Oracle 1z0-042 Oracle Database 10g: Administration I

1. You have a text file that maintains information on thousands of items. The end-user application
requires the transfer of that information into a table in the database. What would you use to
achieve this task? (Choose two.)
A. Data Pump
B. SQL*Loader
C. External table
D. Oracle Text
E. Oracle Import
Answer: B,C

2. Which step do you need to perform to enable a user with the SYSDBA privilege to log in as
SYSDBA in iSQL*Plus?
A. The user must be granted the database administrator (DBA) privilege.
B. Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and
grant the webDba role to the user.
C. The user must be listed in the password file for the authentication.
D. No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
Answer: B

3. You specified extent management as local for a tablespace. How will it affect space management
in the tablespace?
A. The tablespace will be system managed and the users cannot specify the extent size.
B. Free extents will be managed by the data dictionary tables.
C. All the extents will be of the same size.
D. Bitmap will be used to record free and allocated extents.
Answer: D

4. Which two operations require undo data? (Choose two.)
A. recovering from failed transactions
B. recording a transaction to redo log files
C. rolling back a transaction
D. committing a transaction
E. rolling forward during instance recovery
Answer: A,C

5. The employee IDs of employees who have made sales in the company are transferred from the
EMPLOYEES table to the BONUS table with a default bonus value. Later, the human resources
department decides to give bonuses to employees as per the following conditions:
1. Employees with a salary of $8,000 or less should receive a bonus.
2. Employees who have not made sales get a bonus of 1% of their salary.
3. Employees who already made sales get an increase in their bonus equal to 1% of their salary.
What would you do to implement these changes in one step?
A. Use SQL*Loader utility.
B. Use the MERGE statement.
C. Use a multitable insert operation.
D. Use a correlated UPDATE statement.
Answer: B

6. The ST_INFO master table has millions of rows that are updated very rarely. It has a
STATE_CODE column that contains the value for 25 states. The table is frequently queried on the
STATE_CODE column. Which type of index would you suggest to improve the query
performance?
A. bitmap
B. B-tree
C. function based
D. reverse key
Answer: A

7. You want to monitor and control the resource usage by sessions. You want to be warned
automatically when more than 100 sessions are opened with your database. What action would
you take to achieve this?
A. Set the warning threshold for the Current Logons Count metric.
B. Set the limits in the profiles used by users.
C. Use the Database Resource Manager.
D. Modify the SESSIONS initialization parameter.
Answer: A

8. User Scott has updated the salary of one of the employees in the EMPLOYEES table and has not
committed the transaction. What are the two types of locks that this scenario would lead to?
(Choose two.)
A. a shareable table lock for the table containing the row
B. null lock on the table containing the row
C. exclusive table-level lock for the table containing the row
D. null lock on the row being updated
E. ROW SHARE lock for the row being updated
F. shared row-exclusive lock for the row being updated
G. ROW EXCLUSIVE lock for the row being updated
Answer: A,G

9. Which statement regarding the contents of the V$PARAMETER view is true?
A. displays only the list of default values
B. displays the current contents of the server parameter file
C. displays the currently in effect parameter values
D. displays the list of all the parameter files of a database
E. displays only the list of all advanced parameters
F. displays only the list of all basic parameters
Answer: C

10. Redo log files are not multiplexed in your database. Redo log blocks are corrupted in group 2, and
archiving has stopped. All the redo logs are filled and database activity is halted. Database writer
has written everything to disk. Which command would you execute to proceed further?
A. ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
B. RECOVER LOGFILE BLOCK GROUP 2;
C. ALTER DATABASE CLEAR LOGFILE GROUP 2;
D. ALTER DATABASE DROP LOGFILE GROUP 2;
E. ALTER DATABASE RECOVER LOGFILE GROUP 2;
Answer: A

11. Which three statements are true regarding the fine-grained auditing (FGA)? (Choose three.)
A. FGA is possible on SELECT statements only.
B. The audit trail for FGA is stored in the AUD_LOG$ table.
C. FGA includes the SQL statement used by the user as part of the audit event entry.
D. FGA enables a SQL predicate to define when to audit an event.
E. The audit trail for FGA is stored in the FGA_LOG$ table.
F. FGA audits DELETE statements only when audit columns are specified.
Answer: C,D,E

12. Your database instance is started using the server parameter file (SPFILE). Control files are
multiplexed and stored on different disks. Because of a disk failure, you lost one of these control
files. You replaced the damaged disk. What is the correct sequence of steps that you would follow
to recover the control file?
1. Shut down the instance, if not already done.
2. Copy one of the remaining control files to a new location.
3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new
location of the control files.
4. Start up the database instance to the NOMOUNT stage.
5. Recover the database to the point of failure of the control file.
6. Open the database.
A. 2,4,3,4,5
B. 5,2,3,4
C. 4,5,6,2,3
D. 1,2,4,3,5
Answer: D

13. You executed the STARTUP MOUNT command to start your database. For which database
operation do you need to start the database in the MOUNT state?
A. dropping a user in your database
B. dropping a tablespace in your database
C. enabling or disabling redo log archiving
D. renaming the control files
E. re-creating the control files, after you lost all the control files in your database
Answer: C

14. View the Exhibit to see the source and target databases.
You have created a database link, devdb.us.oracle.com, between the databases PRODDB and
DEVDB. You want to import schema objects of the HR user using Oracle Data Pump from the
development database, DEVDB, to the production database, PRODDB. You execute the following
command on the target database server:
$impdp system/manager directory = DB_DATA
dumpfile = schemas.dat
schemas = hr
flashback_time = 2004-02-03 09:00
The command fails, displaying the following error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read
ORA-27037: unable to obtain file status
What would you do to overcome the error?

A. remove the dumpfile option in the command
B. remove the flashback_time option in the command
C. remove the dumpfile option and add the network_link = devdb.us.oracle.com option in the
command
D. add the user, SYSTEM, to the schemas option in the command
E. remove the schemas option and add the network_link = devdb.us.oracle.com option in the
command
F. add network_link = devdb.us.oracle.com option in the command
Answer: C

15. You want to create a tablespace with the following specifications:
1. The tablespace extends automatically.
2. Used and free extents should be managed by bitmaps.
3. Default PCTUSED attribute is set to 60.
4. All the extents would be of size 1 MB.
Which three options would you choose to create the tablespace? (Choose three.)
A. tablespace with segment space management as automatic
B. tablespace with AUTOEXTEND enabled
C. tablespace with a uniform extent allocation of 1 MB
D. tablespace with dictionary-managed extents
E. tablespace with segment space management as manual
Answer: B,C,E

16. Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON;
Session altered
SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees;
Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp;
Table dropped.
What happens in this scenario?
A. The table is moved to the SYSTEM tablespace.
B. The table is renamed and remains in the TBSFD tablespace.
C. The table is moved to the SYSAUX tablespace.
D. The table is removed from the database permanently.
Answer: B

17. Which two statements are true about the roles in the Oracle database? (Choose two.)
A. A role cannot be assigned external authentication.
B. Roles can be granted to other roles.
C. Roles are owned by the SYS user.
D. A role can be granted to itself.
E. A role can contain both system and object privileges.
Answer: B,E

18. The user SCOTT executes the following command successfully to increase the salary values in
one of his sessions:

SQL> UPDATE emp SET sal=sal*1.15 WHERE deptno=20;
Before SCOTT ends the transaction, user HR who has the privileges on EMP table executes a
query to fetch the salary details but finds the old salary values instead of the increased values.
Why does HR still see the old data?
A. because of redo data from redo log file
B. because of data from a temporary tablespace
C. because of undo data from the undo tablespace
D. because of data from database buffer cache
Answer: C

19. In your database, the snapshot interval is set to 10 minutes for the Automatic Workload Repository
(AWR). The database instance is running for the past 30 days.
View the Exhibit to examine the Automatic Database Diagnostic Monitor (ADDM) task.
The ADDM task is performed every 10 minutes, except the last task. What could be the reason for
this?

A. The snapshot retention period for the AWR was increased before the last task.
B. The STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to TYPICAL
before the last task.
C. The optimizer statistics was collected manually by using the DBMS_STATS package before the
last task.
D. The optimizer statistics for few objects in the database were locked before the last task.
Answer: B

20. You plan to use static database registration for a new listener when you create it. What could be
the two reasons for this? (Choose two.)
A. The Oracle Enterprise Manager is to be used to monitor an Oracle9i database.
B. More than one database is to be registered with the listener.
C. The listener is not configured on the default port of 1521 and the instance is not configured to
register with a nondefault port.
D. The users will connect the database by using the host naming method.
E. The database that is to be registered with the listener is configured in shared server mode.
Answer: A,C

21. You want to protect your database from single point of failure by multiplexing the control file. Your
database is started using SPFILE. You have to perform the following tasks:
1. Shut down the database instance.
2. Copy the control file to new location.
3. Configure the CONTROL_FILES parameter.
4. Restart the instance.
Select the correct order in which these tasks need to be performed.
A. 3,2,1,4
B. 3,1,2,4
C. 2,3,1,4
D. 1,2,3,4
Answer: B

22. Which two statements are true regarding the database in ARCHIVELOG mode? (Choose two.)
A. Archiving information is written to the data files and redo log files.
B. You can perform complete database backups without closing the database.
C. Online redo log files have to be multiplexed before putting the database in ARCHIVELOG
mode.
D. All the previous database backups become invalid after you configure the database to
ARCHIVELOG mode.  
E. You have to shut down the database to perform the backups.
Answer: B,D

23. Which three statements are true about the stages of database startup? (Choose three.)
A. Data files and redo log files can be renamed at the MOUNT stage.
B. Control files are required to bring the database to the NOMOUNT stage.
C. Data files and online redo log files are checked for consistency while opening the database.
D. Data files and redo log files are made available to users at the OPEN stage.
E. Control files are read at the OPEN stage for the location of data files.
Answer: A,C,D

24. Which is the memory area that is created when a dedicated server process is started, and
contains data and control information for that server process?
A. SGA
B. Streams Pool
C. Shared Pool
D. PGA
Answer: D

25. The HR user owns the EMP table. The HR user grants privileges to the SCOTT user by using this
command:
SQL> GRANT SELECT,INSERT,UPDATE ON emp TO scott WITH GRANT OPTION;
The SCOTT user executes this command to grant privileges to the JIM user:
SQL> GRANT SELECT,INSERT,UPDATE ON hr.emp TO jim;
Now the HR user decides to revoke privileges from JIM by using this command:
SQL> REVOKE SELECT,INSERT,UPDATE ON emp FROM jim;
Which statement is true after HR issues the REVOKE command?
A. The command fails because SCOTT still has privileges with him.
B. Only HR can perform SELECT, INSERT, and UPDATE operations on the EMP table.
C. The command fails because HR cannot revoke the privileges from JIM.
D. The command succeeds and privileges are revoked from JIM.
Answer: C

26. View the Exhibit.
Which statement regarding the dept and emp tables is true?

A. When you delete a row from the dept table, automatically the corresponding rows are updated
with null values in the emp table.
B. When you delete a row from the dept table, automatically the corresponding rows are deleted
from the emp table.
C. When you delete a row from the emp table, automatically the corresponding rows are updated
with null values in the dept table.  
D. When you delete a row from the emp table, automatically the corresponding rows are deleted
from the dept table.
E. When you delete a row from the emp table, you would receive a constraint violation error.
F. When you delete a row from the dept table, you would receive a constraint violation error.
Answer: B

27. You created a response file and want to check it before starting installations in silent mode. You
started installation of Oracle software in interactive mode by providing the response file. In the
middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not
consistent with the response file. What action would you take to detect the cause of this behavior?
A. Refer to the alert log file for information regarding the actions performed by OUI during
installation.
B. Refer to the contents of the oraInst.loc file to verify the steps performed by OUI.
C. Compare the contents of the installActions.log file with that of the response file.
D. Compare the contents of the install.log file with the response file.
Answer: C

28. In which situation would you use the Oracle Shared Server configuration?
A. when performing export and import using Oracle Data Pump
B. when performing batch processing and bulk loading operation in a data warehouse environment
C. in an online transaction processing (OLTP) system where large number of client sessions are
idle most of the time
D. when performing backup and recovery operations using Oracle Recovery Manager
Answer: C

29. You find that the database performance degrades while you backup the PROD database using
Recovery Manager (RMAN). The PROD database is running in shared server mode. The
database instance is currently using 60% of total operating system memory. You suspect the
shared pool fragmentation to be the reason. Which action would you consider to overcome the
performance degradation?
A. Increase the total System Global Area (SGA) size to increase memory hits.
B. Increase Database Buffer Cache size to increase cache hits.
C. Increase Shared Pool size to cache more PL/SQL objects.
D. Configure Java Pool to cache the java objects.
E. Configure Streams Pool to enable parallel processing.
F. Configure Large Pool to be used by RMAN and shared server.
Answer: F

30. View the Exhibit to see the structure of the EMPLOYEES and DEPARTMENTS tables.
Your organization plans to dissolve the department with department ID 30. You execute the
following command to delete rows from the DEPARTMENTS table:
SQL>delete from DEPARTMENTS
where DEPT_ID = 30;
The command fails and displays the following error:
ERROR at line 1:
ORA-02292: integrity constraint (HR.SYS_C005374) violated - child record found
Which two actions would you take to overcome this error? (Choose two.)

A. first, delete rows from the EMPLOYEES table for department id 30 and then delete the rows
from the DEPARTMENTS table for department id 30  
B. first, drop the EMPLOYEES table and then delete the rows from the DEPARTMENTS table
C. first, drop the DEPARTMENTS table and then delete the rows from the EMPLOYEES table
D. alter the foreign key constraint to include the on delete cascade option
E. first, delete all of the rows from EMPLOYEES table and then delete the rows from the
DEPARTMENTS table for department id 30
F. alter the foreign key constraint to include the cascade option
Answer: A,D

31. Which two statements are true about the primary key constraint in a table? (Choose two.)
A. It is not possible to disable the primary key constraint.
B. The primary key constraint can be imposed by combining more than one column.
C. The non-deferrable primary key constraint creates an unique index on the primary key column if
it is not already indexed.
D. The primary key constraint can be referred by only one foreign key constraint.
E. It is possible to have more than one primary key constraint in a single table.
Answer: B,C

32. You are working on an online transaction processing (OLTP) system. You notice that a PL/SQL
procedure got executed twice at 2:00 p.m. This has incorrectly updated the EMP_SAL table. How
would you revert the table to its state at 2:00 p.m.?
A. Perform point-in-time recovery to 2:00 p.m.
B. Issue the rollback statement with system change number (SCN).
C. Restore the entire database from the recent backup and open it.
D. Use Flashback Table feature to revert the changes.
Answer: D

33. While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g
software, you are prompted to run orainstRoot.sh script. What does this script accomplish?
A. It creates the Inventory pointer file.  
B. It modifies the Unix kernel parameters to match Oracle's requirement.
C. It creates the Oracle user for installation.
D. It creates the base directory.
E. It creates the pointer file.
Answer: A

34. You executed the following command to back up your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Which initialization parameter is used to specify the location of the trace file?
A. BACKUP_DUMP_DEST
B. USER_DUMP_DEST
C. TRACE_DUMP_DEST
D. CORE_DUMP_DEST
E. BACKGROUND_DUMP_DEST
Answer: B

35. You require the Oracle server to manage the undo segments and space among various active
sessions automatically. You created an undo tablespace, UNDO_TBS1, in your database. Which
two additional steps would you perform to achieve this? (Choose two.)
A. Create an initial undo segment in the undo tablespace.
B. Enable the retention guarantee for the undo tablespace.
C. Set the UNDO_TABLESPACE parameter to UNDO_TBS1.
D. Set the UNDO_MANAGEMENT initialization parameter to AUTO.
E. Set the UNDO_RETENTION parameter to 900 or more.
Answer: C,D

36. Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON;  
Session altered
SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees;
Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp;
Table dropped.
What happens in this scenario?
A. The table is moved to the SYSTEM tablespace.
B. The table is removed from the database permanently.
C. The table is moved to the SYSAUX tablespace.
D. The table is renamed and remains in the TBSFD tablespace.
Answer: D

37. You perform differential incremental level 1 backups of your database on each working day and
level 0 backup on Sundays. Which two statements are true about the differential incremental
backups? (Choose two.)
A. The backup performed on each working day contains all the blocks that have changed since the
last level 0 backup.
B. The backup performed on Sundays contains all the blocks that have changed since the last
level 1 backup.
C. The backup performed on Sundays contains all the blocks that have ever been use in the
database.
D. The backup performed on each working day contains all the blocks that have changed since
the last level 0 or level 1 backup.
Answer: C,D

38. Which two statements about Flashback Query are true? (Choose two.)
A. It helps in row-level recovery from user errors.
B. It is generated by using the redo log files.
C. It fails when undo data pertaining to the transaction is overwritten.
D. The database has to be opened with the resetlogs option after performing Flashback Query.
E. It can be performed to recover ALTER TABLE statements
Answer: A,C

39. You want to refer the employee personal information stored in operating system(OS) files in
EMPLOYEE table. You plan to add a new column to EMPLOYEE table to achieve this. Which data
type would you use for the new column?
A. CLOB
B. LONG RAW
C. BFILE
D. BLOB
Answer: C

40. A user complains that he gets the following error message repeatedly after executing some SQL
statements. The error message forces the user to log off from and log on to the database to
continue his work.
ORA-02392: exceeded session limit on CPU usage, you are being logged off
Which action would you take to increase the session limit on CPU usage?
A. Modify the object privileges assigned to the user.
B. Modify the system privileges assigned to the users.
C. Modify the roles assigned to the users.
D. Modify the value for the RESOURCE_LIMIT parameter in the parameter file.
E. Modify the profile assigned to the user.
Answer: E

41. You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;  
What is the effect of this command?
A. One audit record is created for the whole session if user SCOTT successfully drops one or
more tables in his session.
B. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE
privilege to other users in his session.
C. One audit record is created for each successful DROP TABLE command executed in the
session of SCOTT.
D. One audit record is created for each successful DROP TABLE command executed by any user
to drop tables owned by SCOTT.
E. One audit record is created for every session when any user successfully drops a table owned
by SCOTT.
Answer: A

42. You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A. One audit record is created for each successful DROP TABLE command executed in the
session of SCOTT.
B. One audit record is created for each successful DROP TABLE command executed by any user
to drop tables owned by SCOTT.
C. One audit record is created for the whole session if user SCOTT successfully drops one or
more tables in his session.
D. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE
privilege to other users in his session.
E. One audit record is created for every session when any user successfully drops a table owned
by SCOTT.
Answer: C

43. The SCOTT user has an index on the ITEM_DESC column of the ITEM table. As part of the year-
ending task, SCOTT updates the ITEM_DESC column for most of the rows in the ITEM table. How
does this change to the table affect the index?

A. An update in a leaf row takes place.
B. The index becomes invalid after the update.
C. The leaf block containing the row to be updated is marked as invalid.
D. A row in the leaf block of the index for the key value is deleted and inserted.
Answer: D

44. Due to media failure you lost one of the data files belonging to the USERS tablespace, and the
tablespace is not available to use. Which statement regarding the status of the database is true?
A. The database gets shut down automatically.
B. The database remains open.
C. The status of the database depends on the status of the USERS tablespace.
D. The database gets dismounted.
E. The database instance gets aborted.
F. The database becomes read only.
Answer: B

45. Which three descriptions are correct about the effects of the TRUNCATE command on a table?
(Choose three.)
A. Delete triggers on the table are fired during the execution of the TRUNCATE command.
B. The high-water mark (HWM) is set to point to the first useable data block in the table segment.
C. The corresponding indexes for the table are also truncated.
D. Very little or no undo data is generated during the execution of the TRUNCATE command.
E. The child table is truncated when the TRUNCATE command is applied on the parent table.
Answer: B,C,D

46. Your alert log file has the following information:
Tue May 25 17:43:38 2004
ORA-00060: Deadlock detected. More info in file
/u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.  
What would you do to ensure that database is still running correctly?
A. examine the trace file and kill the session that caused the deadlock
B. examine the trace file for details and remember that deadlocks are resolved automatically by
Oracle database
C. examine the trace file for details and ask the user who caused the deadlock to roll back the
transaction
D. examine the trace file for details and use the Undo Advisor to rollback the transaction that
caused the deadlock
E. examine the trace file and kill both the sessions responsible for the deadlock
Answer: B

47. When you try to start the Database Control by using the emctl start dbconsole command the
following error is displayed:
TZ set to America/New_york
OC4J Configuration issue.
/u01/app/oracle/product/10.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_orcl.us.oracle.com not found.
Which two environment variables do you need to set appropriately to avoid such errors, and start
the Database Control successfully? (Choose two.)
A. ORACLE_SID
B. ORACLE_BASE
C. NLS_LANG
D. ORACLE_HOME
E. LD_LIBRARY_PATH
Answer: A,D

48. Your database is functional with peak load for one hour. You want to preserve the performance
statistics collected during this period so that they can be used for comparison while analyzing the
performance of the database in the future. What action would you take to achieve this task?
A. Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid
the automatic purging of the snapshots.  
B. Set the STATISTICS_LEVEL initialization parameter to ALL.
C. Create a baseline on a pair of snapshots that have the statistics of the peak-load period in the
database.
D. Decrease the snapshot interval in the AWR to collect more number of snapshots during the
peak-load period.
Answer: C

49. Your database is having two control files, three redo log file groups with two members in each
group. Failure of which file would cause an instance to shut down?
A. one of the redo log members
B. any control file
C. loss of the initialization parameter file
D. any archive log file
E. any data file belonging to the default permanent tablespace
Answer: B

50. View the Exhibit.
The DBA creates a new user as follows:
SQL> CREATE USER manfus IDENTIFIED BY manfus TEMPORARY TABLESPACE temp;
User manfus creates a table as follows:
SQL> CREATE TABLE material(id NUMBER(3));
In which tablespace will the material table be created?

A. TEMP
B. SYSAUX
C. PROD
D. SYSTEM
E. USERS
Answer: E

51. You are working on the 24X7 database with high transaction volume, to ensure faster instance
recovery on your database you set the FAST_START_MTTR_TARGET initialization parameter to
a very low value. What effect it will have on the database?
A. The overall database performance would be degraded.  
B. The database performance would be enhanced.
C. The mean time to recover (MTTR) would be increased.
D. The redo log files would be get filled more frequently.
Answer: A

52. Which two statements are true about simple views? (Choose two.)
A. Views can be created as read only.
B. Data manipulation language (DML) operations cannot be performed on views.
C. Views are data segments like tables.
D. Views can be created on the basis of more than one table.
Answer: A,D

53. Your database is configured with the following parameters related to SGA:
SGA_TARGET=256MB
SHARED_POOL_SIZE=32MB
DB_CACHE_SIZE=100MB
LARGE_POOL_SIZE=0
JAVA_POOL_SIZE=0
STREAMS_POOL_SIZE=0
Which two statements are true about the configuration? (Choose two.)
A. 124 MB (256 minus 132) of memory is available for use by all the manually sized components.
B. 124 MB (256 minus 132) of memory is available for use by all the manually and automatically
sized components.
C. The shared pool and the default buffer pool will not be sized smaller than 32 MB and 100 MB,
respectively.
D. The shared pool and the default buffer pool will not be sized bigger than 32 MB and 100 MB,
respectively.
E. The SGA_TARGET value cannot be sized smaller than 100 MB.
Answer: B,C

54. Which three statements are true regarding the logical structure of the Oracle database? (Choose
three.)
A. A data block is the smallest unit of I/O for data files.
B. Each segment contains one or more extents.
C. Multiple tablespaces can share single data file.
D. Each data block in the database always corresponds to one OS block.
E. It is possible to have tablespaces of different block sizes in a database.
Answer: A,B,E

55. View the Exhibit and examine the output.
Which statement describes the conclusion?

A. The dictionary cache is consuming more space than the library cache.
B. Preparing indexes on the tables used in the SQL statements would improve the library cache
performance.
C. The shared pool size should be increased to accommodate the SQL statements.
D. The users should use bind variables instead of literals in the query.
Answer: D

56. Which two statements about Flashback Query are true? (Choose two.)
A. The database has to be opened with the resetlogs option after performing Flashback Query.
B. It can be performed to recover ALTER TABLE statements
C. It fails when undo data pertaining to the transaction is overwritten.
D. It helps in row-level recovery from user errors.
E. It is generated by using the redo log files.  
Answer: C,D

57. Which two are valid locking levels that are used by transactions in an Oracle database? (Choose
two.)
A. object level
B. block level
C. schema level
D. row level
E. database level
Answer: A,D

58. Your database is in NOARCHIVELOG mode and a logswitch happens every 20 minutes. On
Thursday morning, media failure resulted in corruption of a data file belonging to the TOOLS
tablespace. Backups are taken on every Sunday. What would you do in this situation?
A. restore the data files from backup and perform cancel-based recovery
B. restore the data files from backup and perform a complete recovery
C. restore only the corrupted data file and perform tablespace recovery
D. recover using Oracle Flashback Database technology
E. restore the entire database from the most recent backup and start the instance and open the
database
Answer: E

59. As a result of performance analysis, you created an index on the prod_name column of the
prod_det table, which contains about ten thousand rows. Later, you updated a product name in the
table. How does this change affect the index?
A. The index becomes invalid when you make any updates.
B. The index will be updated automatically at commit.
C. A leaf row in the index will be deleted and inserted.
D. A leaf will be marked as invalid.
E. An update in a leaf row takes place.

Answer: C

60. The session of user SCOTT receives the following error after executing an UPDATE command on
the EMP table:
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
On investigation, you find that a session opened by user JIM has a transaction that caused the
deadlock.
Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.)
A. SCOTT has to reexecute the last command in the transaction after he commits the transaction.
B. SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
C. It is terminated after receiving the error and JIM can continue with his transaction.
D. The transaction in it is terminated after receiving the error and JIM can continue with his
transaction.
Answer: A,B

61. You want to create a new optimized database for your transactional production environment to be
used by a financial application. While creating the database, you want the Oracle software to take
care of all basic settings to optimize the database performance. Which method would you use to
achieve this objective?
A. Use Enterprise Manager to create a new database with the Online Transaction Processing
(OLTP) option.
B. Use the CREATE DATABASE .. command to create the database with Oracle-managed files.
C. Use the CREATE DATABASE .. command to create the database with Automatic Storage
Management (ASM) file system.
D. Use Database Configuration Assistant (DBCA) to create the database with Transaction
Processing template.
E. Use the Database Configuration Assistant (DBCA) to create the database with Oracle-managed
files.
Answer: D

62. The UNDO_RETENTION parameter in your database is set to 1000 and undo retention is not
guaranteed. Which statement regarding retention of undo data is correct?
A. Undo data will be retained in the UNDO tablespace for 1,000 seconds, then it gets moved to the
TEMPORARY tablespace to provide read consistency.
B. Undo data will be stored permanently after 1,000 seconds.
C. Undo data becomes obsolete after 1,000 seconds.
D. Undo data gets refreshed after every 1,000 seconds.
E. Committed undo data would be retained for 1,000 seconds if free undo space is available.
Answer: E

63. Your database is open, and you plan to perform Recovery Manager (RMAN) backups. Which
three statements are true about these backups? (Choose three.)
A. The backups would be possible only if the database is running in ARCHIVELOG mode.
B. The backups would be consistent.
C. The backups need not be restored during recovery in case of a media failure.
D. The backups need to be restored and database has to be recovered in case of a media failure.
E. The backups would be possible only if the database is running in NOARCHIVELOG mode.
F. The backups would be inconsistent.
Answer: A,D,F

64. While setting up an Oracle database for one of your critical applications, you want to ensure that
the database is backed up at regular intervals without your intervention. What should you do to
achieve the objective?
A. schedule the database backup using DBMS_JOB package after creating the database
B. schedule the database backup using Database Configuration Assistant (DBCA) while creating
the database
C. schedule the database backup using Recovery Manager (RMAN) commands after creating the
database
D. configure the database to run in ARCHIVELOG mode  
E. configure the Flash Recovery Area to enable automatic database backup
Answer: B

65. You have a large amount of historical data in an operating system file. Some analysts in your
organization need to query this data. The file is too large to load into your current database. Which
is the most effective method to accomplish the task?
A. Load the data into your database by using the PARALLEL clause.
B. Upgrade the hardware/memory to accommodate the data.
C. Use an external table so you can have the metadata available in your database, but leave the
data in the operating system files.
D. Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
Answer: C

66. The junior DBA in your organization has accidentally deleted the alert log file. What will you do to
create new alert log file?
A. Create the new text file file as ALERT.LOG.
B. You have to recover the alert log file from the valid backup.
C. No action required.The file would be created automatically by the instance.
D. Change the value for the BACKGROUND_DUMP_DEST parameter.
Answer: C

67. Users of HR schema complain about slower-than-normal performance. On investigation the DBA
found that maintenance was recently performed on some of the tables. The DBA traced the query
that takes longer than normal to execute.
View the Exhibit exhibit_before.
After the DBA resolves the problem, the query performs normally.
View the Exhibit exhibit_after.
What action would the DBA have taken to resolve the performance problem?

A. moved the indexes associated with the EMPLOYEES table to the same tablespace where the
EMPLOYEES table exists
B. moved the EMPLOYEES table in to a locally managed tablespace
C. moved the EMPLOYEES table to another location in the same tablespace
D. analyzed the EMPLOYEES table to collect the current statistics
E. reorganized the associated indexes for the EMPLOYEES table that were in an unusable state
Answer: E

68. You are creating a locally managed tablespace to meet the following requirements:
All the extents should be of the same size.
The data should be spread across two data files.
A bitmap should be used to record the free space within the allocated extents.
Which three options would you choose? (Choose three.)
A. set PCTFREE and PCTUSED to 50
B. set segment space management to Automatic
C. specify extent allocation as Automatic
D. specify extent allocation as Uniform
E. use the RESIZE clause while creating the tablespace
F. create the tablespace as smallfile tablespace
G. create the tablespace as bigfile tablespace
Answer: B,D,F

69. View the Exhibit to observe the roles assigned to the SCOTT user.
Which statement is true about the assignment of the SELECT_CATALOG_ROLE role to the
SCOTT user?

A. The user needs to enable the role explicitly.
B. The user can start using the role immediately.
C. The user can grant the role to other users.
D. The user cannot use the role at all.
Answer: A

70. Which two statement about Automatic Storage Management (ASM) are true? (Choose two.)
A. ASM can be used to store trace files, alert log files, and the server parameter file (SPFILE).
B. ASM supports the Oracle database and operating system files.
C. ASM provides automatic load balancing across all ASM disks.
D. ASM provides mirroring on file by file basis.
Answer: C,D

71. A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the
ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was
disabled. What are the two effects of this command? (Choose two.)
A. It does not validate the existing data in the table.
B. It prevents insert, update, and delete operations on the table while the constraint is in the
process of being enabled.
C. It enables the constraint to be enforced at the end of each transaction.
D. It fails if any existing row violates the constraint.
Answer: B,D

72. Which three pieces of information are to be mandatorily provided while creating a new listener
using Enterprise Manager Database Control? (Choose three.)
A. the port used by the listener
B. the database services to be registered with the listener
C. the server name where the listener runs
D. the log file and trace file destination for the listener
E. the protocol used by the listener
Answer: A,C,E

73. You have been recently hired as a database administrator. Your senior manager asks you to study
the production database server and submit a report on the settings done by the previous DBA.
While observing the server settings, you find that the following parameter has been set in the
parameter file of the database:  
REMOTE_OS_AUTHENT = TRUE
What could have been the reason to set this parameter as TRUE?
A. to disable the administration of the operating system from a remote client
B. to allow the start up and shut down of the database from a remote client
C. to enable operating system authentication for a remote client
D. to restrict the scope of administration to identical operating systems
E. to enable the administration of the operating system from a remote client
Answer: C

74. After being hired as a database administrator, you find that there is only one database that is
functional and that is being accessed by the applications. You want to create a replica of the
database, to be used for testing purposes. What is the best method to create the replica?
A. use DBCA to create a template from the existing database to contain the database structure
with data files and then use the same template to create the database in the new location
B. use Database Configuration Assistant (DBCA) to create a template from the existing database
to contain the database structure
C. use DBCA to create a template from the existing database to contain the database structure
and then manually copy the data using Oracle Data Pump
D. create a database by using CREATE DATABASE .. command and manually copy the data
Answer: A

75. View the Exhibit.
Which statement causes more undo generation?

A. DELETE FROM emp WHERE empno=7934;
B. UPDATE emp SET comm=400 WHERE empno=7844;
C. INSERT INTO emp VALUES(7999,'JHON','CLERK',7782,'10-MAY-83',1500,NULL,10);
D. SELECT * FROM emp;
Answer: A

76. Which step do you need to perform to enable a user with the SYSDBA privilege to log in as
SYSDBA in iSQL*Plus?
A. No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
B. Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and
grant the webDba role to the user.
C. The user must be listed in the password file for the authentication.
D. The user must be granted the database administrator (DBA) privilege.
Answer: B

77. Data files of which three tablespaces can be recovered by performing an open recovery? (Choose
three.)  
A. INDEX
B. TEMP
C. SYSTEM
D. SYSAUX
E. UNDO
Answer: A,B,D

78. Which two operations can be flashed back using the Flashback technology? (Choose two.)
A. ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;
B. DROP USER SMITH;
C. DROP TABLE EMPLOYEES;
D. ALTER TABLE SALES_REP DROP PARTITION P1;
E. DROP TABLESPACE USERS;
Answer: B,C

79. Users of HR schema complain about slower-than-normal performance. On investigation the DBA
found that maintenance was recently performed on some of the tables. The DBA traced the query
that takes longer than normal to execute.
View the Exhibit exhibit_before.
After the DBA resolves the problem, the query performs normally.
View the Exhibit exhibit_after.
What action would the DBA have taken to resolve the performance problem?

A. analyzed the EMPLOYEES table to collect the current statistics
B. moved the EMPLOYEES table in to a locally managed tablespace
C. reorganized the associated indexes for the EMPLOYEES table that were in an unusable state
D. moved the EMPLOYEES table to another location in the same tablespace
E. moved the indexes associated with the EMPLOYEES table to the same tablespace where the
EMPLOYEES table exists
Answer: C

80. Your database is running in the ARCHIVELOG mode. You placed a tablespace, tbs_1, offline with
the immediate option. Which statement is correct in this scenario?
A. The operation would fail if tbs_1 were a read/write tablespace.
B. The operation would fail if tbs_1 were the default tablespace for the database.
C. The operation would fail if tbs_1 were a read-only tablespace.
D. If the above operation were successful, media recovery would be required to bring the
tablespace online.  
E. If the above operation were successful, instance recovery would be required to bring the
tablespace online.
Answer: D

81. In the PROD database you have granted the RESUMABLE system privilege to the CONNECT
role. Resumable space operation has been enabled for all user sessions. You want users NOT to
be aware of any kind of space-related problems while performing transactions. Instead, you want
the problem to be resolved by a database trigger automatically. Which combination of triggering
time and event would you use to achieve this objective?
A. AFTER TRUNCATE
B. BEFORE INSERT OR DELETE OR UPDATE
C. BEFORE CREATE
D. BEFORE SUSPEND
E. AFTER CREATE
F. AFTER INSERT OR DELETE OR UPDATE
G. AFTER SUSPEND
Answer: G

82. Your database is started by using the server parameter file (SPFILE). You issued this command to
change the value of the LOG_BUFFER initialization parameter:
ALTER SYSTEM SET LOG_BUFFER=24M SCOPE=BOTH;
What would be the outcome of this command?
A. The command would return an error because LOG_BUFFER is a static parameter.
B. You need to restart the database so that parameter changes can come into effect.
C. The command would succeed only if initialization parameter LOG_ARCHIVE_MAX_PROCESS
is set to value 2.
D. The parameter value would be changed and it would come into effect immediately.
Answer: A

83. You are using the backup scheduler in Enterprise Manager (EM) to schedule a backup of your
database. Which type of script does the backup scheduler generate?
A. Operating System (OS) script
B. Recovery Manager (RMAN) script
C. Enterprise Manager (EM) script
D. SQL script
E. PL/SQL script
Answer: B

84. You want the user APP_DBA to administer the Oracle database from a remote machine.
APP_DBA is granted the SYSDBA privilege to perform administrative tasks on the database.
Which file is used by the Oracle database server to authenticate APP_DBA?
A. listener controller file
B. password file
C. control file
D. control file and password file
Answer: B

85. You work in a data warehouse environment that involves the execution of complex queries. The
current content of the SQL cache holds the ideal workload for analysis. You want to analyze only
few most resource-intensive statements. What would be your suggestion to receive
recommendations on the efficient use of indexes and materialized views to improve query
performance?
A. Run the Automatic Database Diagnostic Monitor (ADDM).
B. Run the SQL Access Advisor.
C. Run the SQL Tuning Advisor (STA).
D. Run the Automatic Workload Repository (AWR) report.
Answer: B

86. Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)?
(Choose two.)
A. The ADDM analysis provides only the diagnostic information but does not provide
recommendations.
B. The ADDM requires at least four AWR snapshots for analysis.
C. The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR).
D. The ADDM calls other advisors if required, but does not provide recommendations about the
advisors.
E. The ADDM runs after each AWR snapshot is collected.
Answer: C,E

87. Which three pieces of information are considered while deciding the size of the undo tablespace in
your database? (Choose three.)
A. undo blocks generated per second
B. the size of an undo block
C. the size of the database buffer cache
D. the size of the redo log files
E. the value of the UNDO_RETENTION parameter
Answer: A,B,E

88. You are in the middle of a transaction and very crucial data has been modified. Because of a
hardware failure, the instance has shut down before synchronizing all the database files. Which
two statements are true? (Choose two.)
A. On startup, perform media recovery and then instance recovery.
B. On startup, CKPT coordinates instance recovery.
C. Uncommitted changes will be rolled back after the database is opened.
D. On startup, use RMAN to perform instance recovery.
E. On startup, SMON coordinates instance recovery.
F. On startup, all the files will be synchronized and you get both committed and uncommitted data.
Answer: C,E

89. User A executes the following command to update the TRANS table:
SQL> UPDATE B.trans SET tr_amt=tr_amt+500 WHERE c_code='C005';
Before user A issues a COMMIT or ROLLBACK command, user B executes the following
command on the TRANS table:
SQl> ALTER TABLE trans MODIFY (tr_type VARCHAR2(3));
What would happen in this scenario?
A. The ALTER TABLE command fails due to the resource being busy.
B. The transaction for user A is rolled back.
C. The ALTER TABLE command modifies the column successfully.
D. The ALTER TABLE command waits until user A ends the transaction.
Answer: A

90. Your database is not configured for session failover. Your tnsnames.ora file contains the following
details:
test.us.oracle.com=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=off)
(FAILOVER=ON)
(ADDRESS=(PROTOCOL=tcp)(HOST=test1-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=test2-server)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=test.us.oracle.com)))
Which feature is enabled in this case?
A. Connect-time failover
B. Instance failover
C. Database failover
D. Load balancing  
E. Transparent Application Failover (TAF)
Answer: A

91. You backed up the control file to trace. Which statement is true about the trace file generated?
A. The trace file contains the instructions to manually re-create the control file.
B. The trace file is a backup set created during the backup of the control file.
C. The trace file is an image copy of the control file created during the backup of the control file.
D. The trace file has a SQL script to re-create the control file.
E. The trace file is in binary format.
Answer: D

92. You are using flat files as the data source for one of your data warehousing applications. You plan
to move the data from the flat file structures to an Oracle database to optimize the application
performance. In your database you have clustered tables. While migrating the data, you want to
have minimal impact on the database performance and optimize the data load operation. Which
method would you use to load data into Oracle database?
A. use the external table population
B. use the conventional path data load of SQL*Loader utility
C. use the Oracle Data Pump export and import utility
D. use the direct path data load of Oracle export and import utility
Answer: B

93. You execute the following set of commands to create a database user and to grant the system
privileges in your production environment.
SQL> CREATE USER user01
IDENTIFIED BY oracle
DEFAULT TABLESPACE tbs1
TEMPORARY TABLESPACE temp
PROFILE default
/  
SQL> GRANT create session, create table TO user01;
While executing the command to create a table, the user gets the following error message and the
CREATE TABLE.. command fails.
ERROR at line 1:
ORA-01950: no privileges on tablespace
What could be the possible reason for this error message?
A. The tablespace TBS1 is full.
B. The user does not have sufficient system privileges to create table in the TBS1 tablespace.
C. The user does not have sufficient privileges to create table on the default permanent
tablespace.
D. The user does not have quota on the TBS1 tablespace.
E. The user is not the owner of the SYSTEM tablespace.
Answer: D

94. You are using flat files as the data source for one of your data warehousing applications. You plan
to move the data from the flat file structures to an Oracle database to optimize the application
performance. In your database you have clustered tables. While migrating the data, you want to
have minimal impact on the database performance and optimize the data load operation. Which
method would you use to load data into Oracle database?
A. use the external table population
B. use the direct path data load of Oracle export and import utility
C. use the Oracle Data Pump export and import utility
D. use the conventional path data load of SQL*Loader utility
Answer: D

95. User SCOTT wants to export his objects using Oracle Data Pump and executes the following
command:
$ expdp scott/tiger
directory = EXPORT_DIR
dumpfile = scott.dmp  
include = table
include = view:"like '%DEPARTMENTS%'"
content = DATA_ONLY
Which task would the command accomplish?
A. Oracle Data Pump would export all of the table structures along with data and all the views.
B. Oracle Data Pump would export the table data and the view definitions where the view name
contains a string named DEPARTMENTS.
C. Oracle Data Pump would export only the data of all of the tables and views.
D. Oracle Data Pump would export the table data and the view definitions with data where view
name contains a string named DEPARTMENTS.
E. Oracle Data Pump would export all of the table structures and the view definitions with data
where view name contains a string named DEPARTMENTS.
Answer: B

96. Which naming method uses the tnsnames.ora file to store the connect descriptor used by the
client while connecting to the database instance from a remote machine?
A. local naming method
B. host naming method
C. external naming method
D. directory naming method
Answer: A

97. View the Exhibit to observe the message received while trying to drop the SL_REP user in Oracle
Enterprise Manager.
The SL_REP user owns objects and is currently connected to the database instance. What would
happen if you click YES in the Exhibit?
A. The drop operation waits until the session started by the user ends.
B. The user is dropped successfully, and the session started by the user is killed.
C. An error is returned, and the user is not dropped.
D. The user is dropped, and the objects owned by the user are transferred to the recycle bin.
Answer: C

98. You want to move all the objects of the APPS user in the test database to the DB_USER schema
of the production database. Which option of IMPDP would you use to accomplish this task?
A. REMAP_TABLESPACE
B. FULL
C. SCHEMAS
D. REMAP_SCHEMA
E. REMAP_DATAFILES
Answer: D

99. Which two statements regarding the LOGGING clause of the CREATE TABLESPACE... statement
are correct? (Choose two.)
A. This clause is not valid for a temporary or undo tablespace.
B. The tablespace-level logging attribute can be overridden by logging specifications at the table,
index, materialized view, materialized view log, and partition levels.
C. If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate
redo.
D. The tablespace will be in the NOLOGGING mode by default, if not specified while creating a
tablespace.
Answer: A,B

100. You work in a data warehouse environment that involves the execution of complex queries. The
current content of the SQL cache holds the ideal workload for analysis. You want to analyze only
few most resource-intensive statements. What would be your suggestion to receive
recommendations on the efficient use of indexes and materialized views to improve query
performance?
A. Run the Automatic Database Diagnostic Monitor (ADDM).
B. Run the SQL Tuning Advisor (STA).
C. Run the Automatic Workload Repository (AWR) report.
D. Run the SQL Access Advisor.
Answer: D

101. Which two statements are true about a bitmap index? (Choose two.)
A. It is recommended for the columns that have unique values.
B. It can be converted to a B-tree index by using the ALTER INDEX command.
C. It has a bitmap segment for each distinct value in the key column, containing a string of bits in
which each bit represents the presence or absence of a key column value.
D. Updating the key column locks the whole bitmap segment that contains the bit for the key value
to be updated.
Answer: C,D

102. You scheduled a backup by using the Schedule Backup option in Oracle Enterprise Manager.
Which statement about the script created by Oracle Enterprise Manager is true?
A. It is a Recovery Manager (RMAN) script.
B. It is a PL/SQL file.
C. It is a SQL script.
D. It is an operating system (OS) file.
Answer: A

103. The application development team has developed PL/SQL procedures and functions for different
purposes and calls them as and when required. The loading of individual procedures or functions
into memory degrades performance with every call. Also, it causes a security problem for
individual subprograms and loss of program units when the whole system is transported into a
new location. Which method would you recommend to the application developers to solve this
problem?
A. using anonymous PL/SQL blocks instead of subprograms
B. referring to views instead of tables inside the subprograms
C. creating PL/SQL packages to include interrelated subprograms
D. avoiding the use of cursors in the subprograms
Answer: C

104. Your database is running under automatic undo management and the UNDO_RETENTION
parameter is set to 900 sec. You executed the following command to enable retention guarantee:
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
What effect would this statement have on the database?
A. The extents containing committed data in the undo tablespace are not overwritten until the
instance is shut down.
B. The extents in undo tablespace retain data until the next full database backup.
C. The extents containing committed data in the undo tablespace are never overwritten.
D. The extents which no longer contain uncommitted data in the undo tablespace are not
overwritten for at least15 minutes.
Answer: D

105. You have a large amount of historical data in an operating system file. Some analysts in your
organization need to query this data. The file is too large to load into your current database. Which
is the most effective method to accomplish the task?  
A. Load the data into your database by using the PARALLEL clause.
B. Upgrade the hardware/memory to accommodate the data.
C. Use an external table so you can have the metadata available in your database, but leave the
data in the operating system files.
D. Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
Answer: C

106. According to your backup strategy, you performed an incremental level 0 backup of your
database. Which statement regarding this backup is true?
A. The backup is similar to image copy.
B. The backup contains only unused data blocks.
C. The backup contains all used data blocks.
D. The backup contains all data blocks changed since the last incremental level 1 backup.
Answer: C

107. Which two statements about bigfile tablespaces are true? (Choose two.)
A. The bigfile tablespace have only one data file.
B. The bigfile tablespace can be converted to a smallfile tablespace.
C. The database can have only one bigfile tablespace.
D. The extent management is dictionary managed.
E. The segment space management is automatic.
Answer: A,E

108. View the Exhibit and examine the output.
Which statement describes the conclusion?

A. The users should use bind variables instead of literals in the query.
B. The shared pool size should be increased to accommodate the SQL statements.
C. Preparing indexes on the tables used in the SQL statements would improve the library cache
performance.
D. The dictionary cache is consuming more space than the library cache.
Answer: A

109. View the Exhibit and analyze the CREATE TABLE statements used to create the ITEMS and
ORDERS tables.
The ITEMS table has 50 rows and ORDERS table has 500 rows. Because you decide to not deal
with the item code 188 in future, you execute the following command to remove the item:
SQL> DELETE FROM items WHERE item_code=188;
What is the effect of this command?

A. The command fails with integrity violation error.
B. The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the
ITEMS table, and the dependent rows in the ORDERS table are populated with NULL values in
their ITEM_CODE columns.
C. The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the
ITEMS table, and the foreign key constraint is disabled in the ORDERS table.
D. The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the
ITEMS table, and the dependent rows in the ORDERS table are also deleted automatically.  
Answer: D

110. Which statement is true about the loss of system-critical data files in ARCHIVELOG mode?
A. The data files can be recovered till the last committed transaction.
B. The data files can be recovered to the time of the most recent backup.
C. The data files can be recovered without shutting down the database.
D. The data file is taken offline automatically; the database remains open.
Answer: A

111. In your database, the STATISTICS_LEVEL initialization parameter is set to BASIC. What is the
impact of this setting?
A. Only the timed operating system (OS) statistics and plan execution statistics are collected.
B. The Oracle server dynamically generates the necessary statistics on tables as part of query
optimization.
C. The optimizer statistics are collected automatically.
D. The snapshots for the Automatic Workload Repository (AWR) are not generated automatically.
E. Snapshots cannot be collected manually by using DBMS_WORKLOAD_REPOSITORY
PACKAGE.
Answer: D

112. You define an alert to be raised when the USERS tablespace usage has reached 80% of the total
space. Which area would you refer to, in order to confirm that the alert has been raised due the
event?
A. the Database Control Home page
B. the DBA_ALERTS view
C. the Database Control performance page
D. the alert.log file
E. the Database Control Maintenance page
Answer: A

113. View the Exhibit.
What would happen if you change the value of Desired Mean Time To Recover to 30?

A. The Redo Log Advisor would be disabled.
B. Automatic checkpoint tuning would be disabled.
C. The MTTR Advisor would be enabled.
D. The redo log from log buffers would be written to redo log files at a slower rate.
Answer: C

114. Your database is configured in NOARCHIVELOG mode. All the control files have been lost due to
a hard disk failure but the data files are not lost. You have the closed whole database backup
available to you. Which two statements are true in this scenario? (Choose two.)
A. The instance aborts.
B. The database can be recovered by restoring the control files from the backup.
C. The database cannot be recovered.
D. The database can be restored till the point of the last closed whole database backup.
E. The database remains opened and you have to shut it down with the ABORT option.
Answer: A,D

115. On which three can you use Recovery Manager (RMAN) to perform incremental backup? (Choose
three.)  
A. flashback log file
B. control files
C. whole database
D. parameter file
E. data files
F. archived log files
G. change tracking file
H. password file
I. tablespaces
Answer: C,E,I

116. You notice this warning in the alert log file:
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and
has 0 remaining bytes available.
What would you do to reclaim the used space in the Flash Recovery Area?
A. Decrease the retention time for the files.
B. Manually delete all the archived log files from the Flash Recovery Area by using operating
system (OS) commands.
C. Back up the Flash Recovery Area.
D. Increase the retention time for the files.
Answer: A,C

117. View the Exhibit.
Your Oracle 10g database has 6 tablespaces in which:
-TEMP is the default temporary tablespace
-UNDOTBS1 is the default undo tablespace
-USERS is the default permanent tablespace
In this database, which two tablespaces can be made read only? (Choose two.)

A. SYSAUX
B. TEMP
C. UNDOTBS1
D. USERS
E. PROD
F. SYSTEM
Answer: D,E

118. While setting up an Oracle database for one of your critical applications, you want to ensure that
the database is backed up at regular intervals without your intervention. What should you do to
achieve the objective?
A. schedule the database backup using DBMS_JOB package after creating the database
B. schedule the database backup using Recovery Manager (RMAN) commands after creating the
database
C. configure the Flash Recovery Area to enable automatic database backup
D. schedule the database backup using Database Configuration Assistant (DBCA) while creating
the database
E. configure the database to run in ARCHIVELOG mode
Answer: D

119. Which two statements regarding archive log destinations are true? (Choose two.)
A. The archive log files can be written only to local destinations.
B. The archiving information can be traced to the alert log file whenever the archiving to a
destination is successful.  
C. The archive logs must be written to all the destinations.
D. The number of archiving destinations must be equal to the number of archive processes
(ARCn).
E. A maximum of 10 destinations can be specified.
Answer: B,E

120. Which two statements are true regarding the SGA_TARGET initialization parameter? (Choose
two.)
A. It can be increased up to the value of the SGA_MAX_SIZE parameter.
B. Increasing the value of SGA_TARGET up to the value of SGA_MAX_SIZE disables the
automatic shared memory management feature.
C. Reducing the value of the SGA_TARGET parameter takes away memory from both autotuned
and manually sized components.
D. Increasing the value of the SGA_TARGET parameter distributes the increased memory among
all the autotuned components.
Answer: A,D

121. A user complains about getting this error after issuing a certain SQL statement:
ORA-02393: exceeded call limit on CPU usage
Because of the error, the SQL statement gets aborted. What action would you take to increase the
CPU usage limit in the subsequent sessions of the user?
A. Set the RESOURCE_LIMIT initialization parameter to FALSE.
B. Modify the resource limit in the profile used by the user.
C. Increase the value of the SESSION_CACHED_CURSORS initialization parameter.
D. Increase the value of the SESSION_MAX_OPEN_FILES initialization parameter.
Answer: B

122. You are working on an online transaction processing (OLTP) system. You notice that a PL/SQL
procedure got executed twice at 2:00 p.m. This has incorrectly updated the EMP_SAL table. How  
would you revert the table to its state at 2:00 p.m.?
A. Perform point-in-time recovery to 2:00 p.m.
B. Restore the entire database from the recent backup and open it.
C. Issue the rollback statement with system change number (SCN).
D. Use Flashback Table feature to revert the changes.
Answer: D

123. Your database is started with SPFILE. You want the database instance to be dynamically
registered with a listener L2 with the following details:
Protocol: TCP
Host: indl151e
Port: 1525
Which is the correct order of the steps that you would follow to achieve this?
1. Set the LOCAL_LISTENER parameter to L2 dynamically.
2. Make an entry for L2 in tnsnames.ora on the database server.
3. Restart L2.
4. Modify the listener.ora file to add the instance name in SID_LIST of L2.
A. 1, 2, 4, 3
B. 1, 2; 3 and 4 are not required.
C. 1, 2, 3; 4 is not required.
D. 2, 1; 3 and 4 are not required.
Answer: D

124. View the Exhibit to observe the privileges granted to the MGR_ROLE role. The SCOTT user has
been granted the CONNECT and RESOURCE roles only.
The database administrator (DBA) grants MGR_ROLE to the SCOTT user by using this command:
SQL> GRANT MGR_ROLE TO SCOTT WITH ADMIN OPTION;
Which statement is true about the SCOTT user after he is granted this role?

A. The SCOTT user can grant the MGR_ROLE role and the privileges in it to other users.
B. The SCOTT user can grant only the MGR_ROLE role to other users but not the privileges in it.
C. The SCOTT user can grant the privileges in the MGR_ROLE role to other users but cannot
revoke privileges from them.
D. The SCOTT user can grant the privileges in the MGR_ROLE role to other users but not with
ADMIN_OPTION.
Answer: B

125. Your tnsnames.ora file has the following entry for the service alias ORCL:
ORCL =
( DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.24.216)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
The TNSPING command executes successfully when tested with ORCL but you are not able to
connect to the database instance with the following command:
SQL> CONNECT scott/tiger@orcl
What could be the reason for this?
A. The listener is not running at the server.
B. The orcl.oracle.com database service is not registered with the listener.
C. The DEFAULT_DOMAIN parameter is set to a wrong value in the SQLNET.ORA file.
D. The TNS_ADMIN environmental variable is set to a wrong value.
Answer: B

126. A user wants to connect to the database instance from an application that is running on a remote
machine. Which tools should the DBA use to establish the required configuration to ensure that
the user is able to connect to the database instance? (Choose two.)
A. Oracle Net Manager
B. Oracle Universal Installer (OUI)
C. Oracle Enterprise Manager
D. Data Pump
E. Database Configuration Assistant (DBCA)
Answer: A,C

127. Your database is open and users are connected using the LISTENER listener. The new DBA of
the system stops the listener by using the following command:
LSNRCTL> STOP
What would happen to the sessions that are presently connected to the database instance?
A. The sessions are not allowed to perform any operations till the listener is started.
B. The sessions are able to perform only queries.
C. The active transactions are rolled back and the sessions get terminated.
D. The sessions are not affected and continue to function normally.
Answer: D

128. View the Exhibit.
Which statement regarding the dept and emp tables is true?

A. When you delete a row from the emp table, automatically the corresponding rows are updated
with null values in the dept table.
B. When you delete a row from the emp table, you would receive a constraint violation error.
C. When you delete a row from the dept table, you would receive a constraint violation error.
D. When you delete a row from the dept table, automatically the corresponding rows are deleted
from the emp table.
E. When you delete a row from the emp table, automatically the corresponding rows are deleted
from the dept table.
F. When you delete a row from the dept table, automatically the corresponding rows are updated
with null values in the emp table.
Answer: D

129. You have been recently hired as a database administrator. Your senior manager asks you to study
the production database server and submit a report on the settings done by the previous DBA.
While observing the server settings, you find that the following parameter has been set in the
parameter file of the database:
REMOTE_OS_AUTHENT = TRUE  
What could have been the reason to set this parameter as TRUE?
A. to enable the administration of the operating system from a remote client
B. to enable operating system authentication for a remote client
C. to allow the start up and shut down of the database from a remote client
D. to restrict the scope of administration to identical operating systems
E. to disable the administration of the operating system from a remote client
Answer: B

130. Which two operations can be flashed back using the Flashback technology? (Choose two.)
A. DROP TABLE EMPLOYEES;
B. DROP USER SMITH;
C. ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;
D. DROP TABLESPACE USERS;
E. ALTER TABLE SALES_REP DROP PARTITION P1;
Answer: A,B

131. Which two statements about Recovery Manager (RMAN) backups are true? (Choose two.)
A. Online redo log files can be backed up.
B. RMAN backup can be taken only if the database is configured in ARCHIVELOG mode.
C. Only consistent database backups can be performed.
D. Archived redo log files are backed up.
E. Only used data blocks can be backed up as backup sets.
Answer: D,E

132. Which two statements are true regarding a PL/SQL package body? (Choose two.)
A. It can be changed and recompiled without making the package specification invalid.
B. It cannot invoke subprograms defined in other packages.
C. It cannot be created without a package specification.
D. It can contain only the subprograms defined in the package specification.
Answer: A,C

133. You want to create a new optimized database for your transactional production environment to be
used by a financial application. While creating the database, you want the Oracle software to take
care of all basic settings to optimize the database performance. Which method would you use to
achieve this objective?
A. Use Enterprise Manager to create a new database with the Online Transaction Processing
(OLTP) option.
B. Use the CREATE DATABASE .. command to create the database with Automatic Storage
Management (ASM) file system.
C. Use the Database Configuration Assistant (DBCA) to create the database with Oracle-managed
files.
D. Use the CREATE DATABASE .. command to create the database with Oracle-managed files.
E. Use Database Configuration Assistant (DBCA) to create the database with Transaction
Processing template.
Answer: E

134. Which statement is true when you run the SQL Tuning Advisor with limited scope?
A. SQL Profile recommendations are not generated for the SQL statements.
B. SQL structure analysis is not performed for the SQL statements.
C. The staleness and absence of the statistics are not checked for the objects in the query
supplied to the SQL Tuning Advisor.
D. Access path analysis is not performed for the SQL statements.
Answer: A

135. You have to shut down the database instance with the ABORT option because of a hardware
failure. Which statement is true about the subsequent opening of the database?
A. The database would open normally.  
B. The database would open after automatically performing instance recovery.
C. The database would not open, and it would stop at mount stage.
D. The database would not open, and you have to perform database recovery to open it.
Answer: B

136. While planning to manage more than one database in your system, you perform the following
activities:
1. Organize different categories of files into independent subdirectories.
2. Use consistent naming convention for database files.
3. Separate administrative information pertaining to each database.
Which option corresponds to the type of activities performed by you?
A. Oracle Grid Architecture
B. Optimal Flexible Architecture
C. Oracle database architecture
D. Oracle Managed Files
Answer: B

137. You want to set up the Oracle Database 10g installation to follow the French language, with
France as the territory, and the date to be displayed in French for all users' sessions. Which
environmental variable would you set to achieve this objective?
A. NLS_DATE_LANGUAGE
B. NLS_LANG
C. NLS_NCHAR_CHARACTERSET
D. NLS_LANGUAGE
E. NLS_CHARACTERSET
F. NLS_TERRITORY
G. NLS_LENGTH_SEMANTICS
Answer: B

138. Your test database is running in NOARCHIVELOG mode. What are the implications of this?
A. You can perform closed database backups.
B. You can perform the backup of only the SYSTEM tablespace when the database is open.
C. You can perform open database backups.
D. You cannot perform schema-level logical backups.
Answer: A

139. Which two database operations can be performed at the MOUNT stage of database startup?
(Choose two.)
A. configuring the database in ARCHIVELOG mode
B. multiplexing of control files
C. dropping the database user
D. renaming of data files
E. renaming of control files
Answer: A,D

140. After being hired as a database administrator, you find that there is only one database that is
functional and that is being accessed by the applications. You want to create a replica of the
database, to be used for testing purposes. What is the best method to create the replica?
A. create a database by using CREATE DATABASE .. command and manually copy the data
B. use Database Configuration Assistant (DBCA) to create a template from the existing database
to contain the database structure
C. use DBCA to create a template from the existing database to contain the database structure
with data files and then use the same template to create the database in the new location
D. use DBCA to create a template from the existing database to contain the database structure
and then manually copy the data using Oracle Data Pump
Answer: C

141. View the Exhibit.
Your Oracle 10g database has 6 tablespaces in which:
-TEMP is the default temporary tablespace
- UNDOTBS1 is the default undo tablespace
- USERS is the default permanent tablespace
In this database, which three tablespaces can be made offline? (Choose three.)

A. PROD
B. SYSAUX
C. USERS
D. SYSTEM
E. TEMP
F. UNDOTBS1
Answer: A,B,C

142. While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g
software, you are prompted to run orainstRoot.sh script. What does this script accomplish?
A. It creates the pointer file.
B. It creates the Inventory pointer file.
C. It creates the Oracle user for installation.
D. It modifies the Unix kernel parameters to match Oracle's requirement.
E. It creates the base directory.
Answer: B

143. Because of a power outage, instance failure has occurred. From what point in the redo log does
recovery begin and where does it end?
A. all redo logs before the point of last commit
B. beginning of redo log to end of redo log
C. beginning of redo log to checkpoint position
D. checkpoint position to end of redo log
E. current redo log and inactive redo log
Answer: D

144. Which is the correct description of the significance of the ORACLE_HOME environmental
variable?
A. It specifies the base directory of Optimal Flexible Architecture (OFA).
B. It specifies the directory containing the Oracle-Managed Files.
C. It specifies the directory for database files, if not specified explicitly.
D. It specifies the directory containing the Oracle software.
Answer: D

145. You specified segment space management as automatic for a tablespace. What effect would this
have on space management?
A. The segment would be managed by the freelists.
B. Extents would be managed by the freelists.
C. Free space would be managed by the data dictionary.
D. The segment would be managed by bitmaps.
Answer: D

146. The junior DBA in your organization has accidentally deleted the alert log file. What will you do to
create new alert log file?  
A. Change the value for the BACKGROUND_DUMP_DEST parameter.
B. You have to recover the alert log file from the valid backup.
C. No action required.The file would be created automatically by the instance.
D. Create the new text file file as ALERT.LOG.
Answer: C

147. What is the implication of setting the initialization parameter FAST_START_MTTR_TARGET to 0
in your database?
A. Checkpoint information would not be written to the alert log file.
B. Automatic tuning of checkpoint would be disabled.
C. Redo Log Advisor would be disabled.
D. MTTR Advisor would be disabled.
Answer: B

148. You suspect unauthorized data manipulation language (DML) operations on a particular table. You
want to track users who are performing the transactions and the values used in the transactions.
Also, you plan to transfer these values to another table for analysis. How would you achieve this?
A. by using anonymous PL/SQL blocks
B. by using triggers
C. by auditing all DML operations on the table
D. by using external tables
Answer: B

149. You executed the following command to start the database:
SQL> STARTUP
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 789000 bytes
Variable Size 229635576 bytes
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
Which view would you query at this stage to investigate this missing control file?
A. DBA_CONTROL_FILES
B. V$INSTANCE
C. V$CONTROLFILE
D. V$CONTROLFILE_RECORD_SECTION
E. V$DATABASE_PROPERTIES
Answer: C

150. The application workload on your database is same between 10 a.m. and 11 a.m. on weekdays.
Suddenly you observe poor performance between 10 a.m. and 11 a.m. in the middle of the week.
How would you identify the changes in configuration settings, workload profile, and statistics to
diagnose the possible causes of the performance degradation?
A. by using the Automatic Workload Repository report
B. by using the SQL access advisor
C. by running the Automatic Database Diagnostic Monitor (ADDM)
D. by analyzing the output of the V$ACTIVE_SESSION_HISTORY view
E. by using the Automatic Workload Repository Compare Period report
Answer: E

151. In your database, the Log Writer (LGWR) process is unable to write to a member of a current redo
log group due to read/write failure. Which two effects would you see in your database? (Choose
two.)
A. The database instance aborts.
B. The database operation temporarily halts till the member becomes available.
C. The member would be marked as STALE and an error message would be written to the alert
log file and LGWR trace file.
D. The status of the group changes to INACTIVE and an error message would be written to the
alert log file and LGWR trace file.  
E. Writing proceeds as normal. LGWR writes to the available members of a group and ignores the
unavailable members.
Answer: C,E

152. Your database is in NOARCHIVELOG mode. After which two operations you should take the
backup of control file ? (Choose two.)
A. dropping a table from the database
B. adding a new user to the database
C. dropping a data file from a tablespace
D. dropping a user from the database
E. adding a new tablespace to the database
Answer: C,E

153. View this parameter setting in your database:
DB_CREATE_FILE_DEST='D:\oracle\product\10.2.0\oradata\oracle'
You created a tablespace by using this command:
CREATE TABLESPACE USERS;
Which two statements are true about the USERS tablespace? (Choose two.)
A. The tablespace has two data files.
B. Data files belonging to the USERS tablespace cannot be renamed.
C. The tablespace can be extended without specifying the data file.
D. An error is reported and tablespace creation fails.
E. Data files are created with names generated by the instance.
Answer: C,E

154. The HR user creates a view with this command:  
SQL> CREATE VIEW emp_v AS SELECT * FROM scott.emp;
Now HR wants to grant the SELECT privilege on the EMP_V view to the JIM user. Which
statement is true in this scenario?
A. SCOTT has to grant the SELECT privilege on the EMP table to JIM before this operation.
B. HR can grant the privilege to JIM but without GRANT OPTION.
C. HR can grant the privilege to JIM because HR is the owner of the view.
D. HR needs the SELECT privilege on the EMP table with GRANT OPTION from SCOTT for this
operation.
Answer: D

155. You executed the following command to export the EMPLOYEES table from a remote machine:
$ EXPDP hr/hr@data.us.oracle.com
DUMPFILE=my_dir:exp_hr.log
LOGFILE=data_pump_dir:log_hr.log
TABLES=employees
What would be the outcome of this command?
A. The command would execute successfully. But log file would not be created as
DATA_PUMP_DIR directory is only accessible to user with SYSDBA privilege.
B. The command fails with an error because no absolute path is specified for log file and dumpfile.
C. The command would execute successfully and the export dump file would be created in the
destination of the directory object MY_DIR.
D. The command fails with an error because DATA_PUMP_DIR directory have higher precedence
over the per-file directory.
Answer: C

156. Which three statements are correct about temporary tables? (Choose three.)
A. The data inserted into a temporary table in a session is available to other sessions.
B. Temporary tables are always created in a user's temporary tablespace.
C. Indexes and views can be created on temporary tables.  
D. Data manipulation language (DML) locks are never acquired on the data of temporary tables.
E. Both the data and the structure of temporary tables can be exported.
Answer: B,C,D

157. User SCOTT executes the following command on the EMP table but has not issued COMMIT,
ROLLBACK, or any data definition language (DDL) command:
SQL> SELECT job FROM emp
2 WHERE job='CLERK' FOR UPDATE OF empno;
SCOTT has opened another session to work with the database. Which three operations would
wait when issued in SCOTT's second session? (Choose three.)
A. SELECT job FROM emp WHERE job='CLERK' FOR UPDATE OF empno;
B. DELETE FROM emp WHERE job='MANAGER';
C. LOCK TABLE emp IN EXCLUSIVE MODE;
D. LOCK TABLE emp IN SHARE MODE;
E. INSERT INTO emp(empno,ename) VALUES (1289,'Dick');
Answer: A,C,D

158. In the middle of a transaction, a user session was abnormally terminated but the instance is still up
and the database is open. Which two statements are true in this scenario? (Choose two.)
A. The alert log file gives detailed information about the failure.
B. Data modified by the transaction up to the last commit before the abnormal termination is
retained in the database.
C. The transaction is rolled back by the next session that refers to any of the blocks updated by
the failed transaction.
D. PMON rolls back the transaction and releases the locks.
E. SMON rolls back the transaction and releases the locks.
F. Event Viewer gives more details on the failure.
Answer: B,D

159. You created a response file and want to check it before starting installations in silent mode. You
started installation of Oracle software in interactive mode by providing the response file. In the
middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not
consistent with the response file. What action would you take to detect the cause of this behavior?
A. Refer to the contents of the oraInst.loc file to verify the steps performed by OUI.
B. Compare the contents of the installActions.log file with that of the response file.
C. Refer to the alert log file for information regarding the actions performed by OUI during
installation.
D. Compare the contents of the install.log file with the response file.
Answer: B

160. In your Oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00
pm every Friday. The job has successfully completed. Which three pieces of information would
you check to confirm that the statistics have been collected? (Choose three.)
A. last analyzed date
B. average row size
C. number of extents present in the table
D. size of table in bytes
E. size of table in database blocks
F. number of free blocks in the free list
Answer: A,B,E

161. View the Exhibit to examine the output of the DBA_OUTSTANDING_ALERT view.
After 30 minutes, you executed the following command:
SQL> SELECT reason,metric_value FROM dba_outstanding_alerts;
REASON METRIC_VALUE
------------------------------ ----------------------
Tablespace [TEST] is [28 perce 28.125
nt] full
What could be the two reasons for the elimination of the other rows in the output? (Choose two.)

A. The threshold alerts are transferred to DBA_ALERT_HISTORY.
B. The non-threshold-based alerts are transferred to DBA_ALERT_HISTORY.
C. The threshold alerts related to database metrics are permanently stored in
DBA_ALERT_HISTORY but not the threshold alerts related to instance metrics.
D. The threshold alert conditions are cleared.
Answer: A,D

162. View the Exhibit, and examine the alert messages.
You added space to the TEST tablespace to bring the space usage below the threshold value.
Which statement is true about the Tablespace Full alert?

A. The alert is automatically cleared and sent to the alert history.
B. The alert is cleared and transferred to the alert log file.
C. The alert appears in Oracle Enterprise Manager Database Control until it is manually cleared.
D. The alert is purged because it is a threshold alert.
Answer: A

163. While planning to manage more than one database in your system, you perform the following
activities:
1. Organize different categories of files into independent subdirectories.
2. Use consistent naming convention for database files.
3. Separate administrative information pertaining to each database.
Which option corresponds to the type of activities performed by you?
A. Oracle Managed Files
B. Oracle database architecture
C. Optimal Flexible Architecture
D. Oracle Grid Architecture
Answer: C

164. The SAVE_AMT column in the ACCOUNTS table contains the balance details of customers in a
bank. As part of the year-end tax and interest calculation process, all the rows in the table need to
be accessed. The bank authorities want to track access to the rows containing balance amounts
exceeding $200,000, and then send an alert message to the administrator. Which method would
you suggest to the bank for achieving this task?
A. performing standard database auditing to audit SQL statements with granularity level set to
ACCESS
B. implementing value-based auditing by using triggers
C. performing standard database auditing to audit object privileges by setting the AUDIT_TRAIL
parameter to EXTENDED
D. implementing fine-grained auditing with audit condition and event handler
Answer: D

165. You received complaints about the degradation of SQL query performance. You identified top SQL
queries that consume time. What would be your next step to find out recommendations about
statistics collection and restructuring of the SQL statement to improve query performance?
A. run Segment Advisor
B. run the Automatic Workload Repository (AWR) report
C. run the Automatic Database Diagnostic Monitor (ADDM) on top SQL statements
D. run SQL Tuning Advisor on top SQL statements
Answer: D

166. You are working on a database created with the Oracle Database 10g software in which the
initialization parameter COMPATIBLE is set to 10.0.0. The hr_tbs tablespace in the database is
created as follows:
CREATE TABLESPACE hr_tbs DATAFILE '/oracle/oradata/hr_tbs.dbf' SIZE 50M;
View the Exhibit to see the properties of the database.
Which statement is correct in this scenario?

A. All the tablespaces in the database must be of the smallfile tablespace type.
B. hr_tbs is a bigfile tablespace.
C. The tablespace type bigfile or smallfile must be mentioned in the command.
D. hr_tbs is a dictionary-managed tablespace.
E. All the tablespaces in the database will be locally managed by default.
Answer: E

167. A user receives the following error while executing a query:
ORA-01555: snapshot too old
Which two options can be the solutions to avoid such errors in future? (Choose two.)
A. increase the size of the Database Buffer Cache
B. increase the size of the default temporary tablespace
C. increase the size of redo log files
D. increase the size of the undo tablespace
E. enable the retention guarantee for the undo tablespace
Answer: D,E

168. What are the consequences of executing the SHUTDOWN ABORT command? (Choose two.)
A. The database files are synchronized.
B. The database is closed, but the instance is still started.
C. The database undergoes automatic media recovery during the next startup.
D. Database buffers and redo buffers are not written to the disk.
E. Uncommitted changes are not rolled back.
Answer: D,E