oracle 认证 042_which two statements are true about time zones, da

1.Which three pieces of information are considered while deciding the size of the undo
tablespace in your database? (Choose three.)
A:the size of an undo block
B:the size of the redo log files
C:undo blocks generated per second
D:the size of the database buffer cache
E:the value of the UNDO_RETENTION parameter
Correct Answers: A, C, E
2.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: renaming the control files
B: dropping a user in your database
C: enabling or disabling redo log archiving
D: dropping a tablespace in your database
E: re-creating the control files, after you lost all the control files in your database
Correct Answers: C
3.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 the CREATE DATABASE .. command to create the database with Oracle-managed files.
B: Use the Database Configuration Assistant (DBCA) to create the database with
Oracle-managed files.
C: Use Enterprise Manager to create a new database with the Online Transaction Processing
(OLTP) option.
D: Use Database Configuration Assistant (DBCA) to create the database with Transaction
Processing template.
E: Use the CREATE DATABASE .. command to create the database with Automatic Storage
Management (ASM) file system.
Correct Answers: D
4.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 read at the OPEN stage for the location of data files.
C:Control files are required to bring the database to the NOMOUNT stage.
D:Data files and redo log files are made available to users at the OPEN stage.
E:Data files and online redo log files are checked for consistency while opening the
database.
Correct Answers: A, D, E
5.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 fails if any existing row violates the constraint.
B:It does not validate the existing data in the table.
C:It enables the constraint to be enforced at the end of each transaction.
D:It prevents insert, update, and delete operations on the table while the constraint
is in the process of being enabled.
Correct Answers: A, D
6.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 SQL Tuning Advisor on top SQL statements
C: run the Automatic Workload Repository (AWR) report
D: run the Automatic Database Diagnostic Monitor (ADDM) on top SQL statements
Correct Answers: B
7.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 becomes obsolete after 1,000 seconds.
B: Undo data gets refreshed after every 1,000 seconds.
C: Undo data will be stored permanently after 1,000 seconds.
D: Committed undo data would be retained for 1,000 seconds if free undo space is
available.
E: 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.
Correct Answers: D
8.View the Exhibit.
Which statement regarding the dept and emp tables is true?
A: When you delete a row from the emp table, you would receive a constraint violation
error.
B: When you delete a row from the dept table, you would receive a constraint violation
error.
C: When you delete a row from the emp table, automatically the corresponding rows are
deleted from the dept table.
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 dept table, automatically the corresponding rows are
updated with null values in the emp table.
F: When you delete a row from the emp table, automatically the corresponding rows are
updated with null values in the dept table.
Correct Answers: D
9.Which three statements are correct about temporary tables? (Choose three.)
A:Indexes and views can be created on temporary tables.
B:Both the data and the structure of temporary tables can be exported.
C:Temporary tables are always created in a user's temporary tablespace.
D:The data inserted into a temporary table in a session is available to other sessions.
E:Data manipulation language (DML) locks are never acquired on the data of temporary
tables.
Correct Answers: A, C, E
10.Which statement regarding the contents of the V$PARAMETER view is true?
A: displays only the list of default values
B: displays only the list of all basic parameters
C: displays the currently in effect parameter values
D: displays only the list of all advanced parameters
E: displays the list of all the parameter files of a database
F: displays the current contents of the server parameter file
Correct Answers: C
11.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 all used data blocks.
C: The backup contains only unused data blocks.
D: The backup contains all data blocks changed since the last incremental level 1 backup.
Correct Answers: B
12.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: The user must be listed in the password file for the authentication.
C: No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
D: Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager,
and grant the webDba role to the user.
Correct Answers: D
13.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: current redo log and inactive redo log
B: checkpoint position to end of redo log
C: beginning of redo log to end of redo log
D: all redo logs before the point of last commit
E: beginning of redo log to checkpoint position
Correct Answers: B
14.Which two statements are true regarding the database in ARCHIVELOG mode? (Choose two.)
A:You have to shut down the database to perform the backups.
B:Archiving information is written to the data files and redo log files.
C:You can perform complete database backups without closing the database.
D:Online redo log files have to be multiplexed before putting the database in ARCHIVELOG
mode.
E:All the previous database backups become invalid after you configure the database to
ARCHIVELOG mode.
Correct Answers: C, E
15.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:null lock on the row being updated
B:null lock on the table containing the row
C:ROW SHARE lock for the row being updated
D:ROW EXCLUSIVE lock for the row being updated
E:shared row-exclusive lock for the row being updated
F:a shareable table lock for the table containing the row
G:exclusive table-level lock for the table containing the row
Correct Answers: D, F
16.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:Back up the Flash Recovery Area.
B:Increase the retention time for the files.
C:Decrease the retention time for the files.
D:Manually delete all the archived log files from the Flash Recovery Area by using
operating system (OS) commands.
Correct Answers: A, C
17.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 dictionary cache is consuming more space than the library cache.
C: The shared pool size should be increased to accommodate the SQL statements.
D: Preparing indexes on the tables used in the SQL statements would improve the library
cache performance.
Correct Answers: A
18.Which two operations require undo data? (Choose two.)
A:committing a transaction
B:rolling back a transaction
C:recovering from failed transactions
D:recording a transaction to redo log files
E:rolling forward during instance recovery
Correct Answers: B, C
19.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 cannot be recovered.
C:The database can be recovered by restoring the control files from the backup.
D:The database remains opened and you have to shut it down with the ABORT option.
E:The database can be restored till the point of the last closed whole database backup.
Correct Answers: A, E
20.Which is the correct description of the significance of the ORACLE_HOME environmental
variable?
A: It specifies the directory containing the Oracle software.
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 base directory of Optimal Flexible Architecture (OFA).
Correct Answers: A
21.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 base directory.
C: It creates the Inventory pointer file.
D: It creates the Oracle user for installation.
E: It modifies the Unix kernel parameters to match Oracle's requirement.
Correct Answers: C
22.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: Configure Java Pool to cache the java objects.
B: Configure Streams Pool to enable parallel processing.
C: Increase Shared Pool size to cache more PL/SQL objects.
D: Increase Database Buffer Cache size to increase cache hits.
E: Configure Large Pool to be used by RMAN and shared server.
F: Increase the total System Global Area (SGA) size to increase memory hits.
Correct Answers: E
23.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; 4 is not required.
C: 2, 1; 3 and 4 are not required.
D: 1, 2; 3 and 4 are not required.
Correct Answers: C
24.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: Change the value for the BACKGROUND_DUMP_DEST parameter.
D: No action required.The file would be created automatically by the instance.
Correct Answers: D
25.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 profile assigned to the user.
B: Modify the roles assigned to the users.
C: Modify the object privileges assigned to the user.
D: Modify the system privileges assigned to the users.
E: Modify the value for the RESOURCE_LIMIT parameter in the parameter file.
Correct Answers: A
26.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: configure the database to run in ARCHIVELOG mode
B: configure the Flash Recovery Area to enable automatic database backup
C: schedule the database backup using DBMS_JOB package after creating the database
D: schedule the database backup using Recovery Manager (RMAN) commands after creating
the database
E: schedule the database backup using Database Configuration Assistant (DBCA) while
creating the database
Correct Answers: E
27.Which two statements about Flashback Query are true? (Choose two.)
A:It is generated by using the redo log files.
B:It helps in row-level recovery from user errors.
C:It can be performed to recover ALTER TABLE statements
D:It fails when undo data pertaining to the transaction is overwritten.
E:The database has to be opened with the resetlogs option after performing Flashback
Query.
Correct Answers: B, D
28.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: A leaf will be marked as invalid.
B: An update in a leaf row takes place.
C: The index will be updated automatically at commit.
D: A leaf row in the index will be deleted and inserted.
E: The index becomes invalid when you make any updates.
Correct Answers: D
29.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:TEMP
B:PROD
C:USERS
D:SYSAUX
E:SYSTEM
F:UNDOTBS1
Correct Answers: B, C
30.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 triggers
B: by using external tables
C: by using anonymous PL/SQL blocks
D: by auditing all DML operations on the table
Correct Answers: A
31.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: SQL script
B: PL/SQL script
C: Operating System (OS) script
D: Enterprise Manager (EM) script
E: Recovery Manager (RMAN) script
Correct Answers: E
32.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: An error is returned, and the user is not dropped.
B: The drop operation waits until the session started by the user ends.
C: The user is dropped successfully, and the session started by the user is killed.
D: The user is dropped, and the objects owned by the user are transferred to the recycle
bin.
Correct Answers: A
33.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:LOCK TABLE emp IN SHARE MODE;
B:LOCK TABLE emp IN EXCLUSIVE MODE;
C:DELETE FROM emp WHERE job='MANAGER';
D:INSERT INTO emp(empno,ename) VALUES (1289,'Dick');
E:SELECT job FROM emp WHERE job='CLERK' FOR UPDATE OF empno;
Correct Answers: A, B, E
34.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:Oracle Text
B:Data Pump
C:SQL*Loader
D:Oracle Import
E:External table
Correct Answers: C, E
35.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: implementing value-based auditing by using triggers
B: implementing fine-grained auditing with audit condition and event handler
C: performing standard database auditing to audit object privileges by setting the
AUDIT_TRAIL parameter to EXTENDED
D: performing standard database auditing to audit SQL statements with granularity level
set to ACCESS
Correct Answers: B
36.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:specify extent allocation as Uniform
C:specify extent allocation as Automatic
D:create the tablespace as bigfile tablespace
E:create the tablespace as smallfile tablespace
F:set segment space management to Automatic
G:use the RESIZE clause while creating the tablespace
Correct Answers: B, E, F
37.Your test database is running in NOARCHIVELOG mode. What are the implications of this?
A: You can perform open database backups.
B: You can perform closed database backups.
C: You cannot perform schema-level logical backups.
D: You can perform the backup of only the SYSTEM tablespace when the database is open.
Correct Answers: B
38.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, SMON coordinates instance recovery.
B:On startup, CKPT coordinates instance recovery.
C:On startup, use RMAN to perform instance recovery.
D:Uncommitted changes will be rolled back after the database is opened.
E:On startup, perform media recovery and then instance recovery.
F:On startup, all the files will be synchronized and you get both committed and
uncommitted data.
Correct Answers: A, D
39.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 and then manually copy the data using Oracle Data Pump
D: 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
Correct Answers: D
40.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: Use Flashback Table feature to revert the changes.
C: Restore the entire database from the recent backup and open it.
D: Issue the rollback statement with system change number (SCN).
Correct Answers: B
41.View the Exhibit.
What would happen if you change the value of Desired Mean Time To Recover to 30?
A: The MTTR Advisor would be enabled.
B: The Redo Log Advisor would be disabled.
C: Automatic checkpoint tuning would be disabled.
D: The redo log from log buffers would be written to redo log files at a slower rate.
Correct Answers: A
42.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 remains open.
B: The database gets dismounted.
C: The database becomes read only.
D: The database instance gets aborted.
E: The database gets shut down automatically.
F: The status of the database depends on the status of the USERS tablespace.
Correct Answers: A
43.Which two statements are true about the roles in the Oracle database? (Choose two.)
A:A role can be granted to itself.
B:Roles are owned by the SYS user.
C:Roles can be granted to other roles.
D:A role cannot be assigned external authentication.
E:A role can contain both system and object privileges.
Correct Answers: C, E
44.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 the privileges in the MGR_ROLE role to other users but not
with ADMIN_OPTION.
C: The SCOTT user can grant only the MGR_ROLE role to other users but not the privileges
in it.
D: The SCOTT user can grant the privileges in the MGR_ROLE role to other users but cannot
revoke privileges from them.
Correct Answers: C
45.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: control file
B: password file
C: listener controller file
D: control file and password file
Correct Answers: B
46.Which two statements are true about simple views? (Choose two.)
A:Views can be created as read only.
B:Views are data segments like tables.
C:Views can be created on the basis of more than one table.
D:Data manipulation language (DML) operations cannot be performed on views.
Correct Answers: A, C
47.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 Grid Architecture
C: Optimal Flexible Architecture
D: Oracle database architecture
Correct Answers: C
48.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: host naming method
B: local naming method
C: external naming method
D: directory naming method
Correct Answers: B
49.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-only tablespace.
B: The operation would fail if tbs_1 were a read/write tablespace.
C: The operation would fail if tbs_1 were the default tablespace for the database.
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.
Correct Answers: D
50.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 SQL Access Advisor.
B: Run the SQL Tuning Advisor (STA).
C: Run the Automatic Workload Repository (AWR) report.
D: Run the Automatic Database Diagnostic Monitor (ADDM).
Correct Answers: A
51.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: Upgrade the hardware/memory to accommodate the data.
B: Load the data into your database by using the PARALLEL clause.
C: Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
D: Use an external table so you can have the metadata available in your database, but
leave the data in the operating system files.
Correct Answers: D
52.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 redo log files
B:increase the size of the undo tablespace
C:increase the size of the Database Buffer Cache
D:increase the size of the default temporary tablespace
E:enable the retention guarantee for the undo tablespace
Correct Answers: B, E
53.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 and the export dump file would be created in
the destination of the directory object MY_DIR.
B: 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.
C: The command fails with an error because DATA_PUMP_DIR directory have higher precedence
over the per-file directory.
D: The command fails with an error because no absolute path is specified for log file
and dumpfile.
Correct Answers: A
54.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 created for every session when any user successfully drops a table
owned by SCOTT.
C: One audit record is created for each successful DROP TABLE command executed by any
user to drop tables owned by SCOTT.
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 each successful DROP TABLE command executed in the
session of SCOTT.
Correct Answers: A
55.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 SYSAUX tablespace.
B: The table is moved to the SYSTEM tablespace.
C: The table is removed from the database permanently.
D: The table is renamed and remains in the TBSFD tablespace.
Correct Answers: D
56.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.
Correct Answers: C, D
57.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:The SGA_TARGET value cannot be sized smaller than 100 MB.
B:The shared pool and the default buffer pool will not be sized bigger than 32 MB and
100 MB, respectively.
C:The shared pool and the default buffer pool will not be sized smaller than 32 MB and
100 MB, respectively.
D:124 MB (256 minus 132) of memory is available for use by all the manually sized
components.
E:124 MB (256 minus 132) of memory is available for use by all the manually and
automatically sized components.
Correct Answers: C, E
58.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 SQL access advisor
B: by using the Automatic Workload Repository report
C: by running the Automatic Database Diagnostic Monitor (ADDM)
D: by using the Automatic Workload Repository Compare Period report
E: by analyzing the output of the V$ACTIVE_SESSION_HISTORY view
Correct Answers: D
59.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: avoiding the use of cursors in the subprograms
B: using anonymous PL/SQL blocks instead of subprograms
C: referring to views instead of tables inside the subprograms
D: creating PL/SQL packages to include interrelated subprograms
Correct Answers: D
60.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 protocol used by the listener
C:the server name where the listener runs
D:the log file and trace file destination for the listener
E:the database services to be registered with the listener
Correct Answers: A, B, C
61.What are the consequences of executing the SHUTDOWN ABORT command? (Choose two.)
A:The database files are synchronized.
B:Uncommitted changes are not rolled back.
C:The database is closed, but the instance is still started.
D:Database buffers and redo buffers are not written to the disk.
E:The database undergoes automatic media recovery during the next startup.
Correct Answers: B, D
62.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 only the data of all of the tables and views.
B: Oracle Data Pump would export all of the table structures along with data and all
the views.
C: Oracle Data Pump would export the table data and the view definitions where the view
name contains a string named DEPARTMENTS.
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.
Correct Answers: C
63.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 purged because it is a threshold alert.
B: The alert is cleared and transferred to the alert log file.
C: The alert is automatically cleared and sent to the alert history.
D: The alert appears in Oracle Enterprise Manager Database Control until it is manually
cleared.
Correct Answers: C
64.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:Writing proceeds as normal. LGWR writes to the available members of a group and ignores
the unavailable members.
D:The member would be marked as STALE and an error message would be written to the alert
log file and LGWR trace file.
E:The status of the group changes to INACTIVE and an error message would be written to
the alert log file and LGWR trace file.
Correct Answers: C, D
65.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: V$INSTANCE
B: V$CONTROLFILE
C: DBA_CONTROL_FILES
D: V$DATABASE_PROPERTIES
E: V$CONTROLFILE_RECORD_SECTION
Correct Answers: B
66.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 without shutting down the database.
C: The data file is taken offline automatically; the database remains open.
D: The data files can be recovered to the time of the most recent backup.
Correct Answers: A
67.Which two statements about bigfile tablespaces are true? (Choose two.)
A:The bigfile tablespace have only one data file.
B:The segment space management is automatic.
C:The extent management is dictionary managed.
D:The database can have only one bigfile tablespace.
E:The bigfile tablespace can be converted to a smallfile tablespace.
Correct Answers: A, B
68.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 transaction for user A is rolled back.
B: The ALTER TABLE command modifies the column successfully.
C: The ALTER TABLE command fails due to the resource being busy.
D: The ALTER TABLE command waits until user A ends the transaction.
Correct Answers: C
69.You backed up the control file to trace. Which statement is true about the trace file
generated?
A: The trace file is in binary format.
B: The trace file has a SQL script to re-create the control file.
C: The trace file is a backup set created during the backup of the control file.
D: The trace file contains the instructions to manually re-create the control file.
E: The trace file is an image copy of the control file created during the backup of the
control file.
Correct Answers: B
70.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: Compare the contents of the install.log file with the response file.
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: Refer to the alert log file for information regarding the actions performed by OUI
during installation.
Correct Answers: C
71.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 foreign key constraint is disabled in the ORDERS table.
C: 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.
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 populated with NULL
values in their ITEM_CODE columns.
Correct Answers: C
72.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 able to perform only queries.
B: The sessions are not affected and continue to function normally.
C: The active transactions are rolled back and the sessions get terminated.
D: The sessions are not allowed to perform any operations till the listener is started.
Correct Answers: B
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 enable operating system authentication for a remote client
B: to restrict the scope of administration to identical operating systems
C: to allow the start up and shut down of the database from a remote client
D: to enable the administration of the operating system from a remote client
E: to disable the administration of the operating system from a remote client
Correct Answers: A
74.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: hr_tbs is a bigfile tablespace.
B: hr_tbs is a dictionary-managed tablespace.
C: All the tablespaces in the database will be locally managed by default.
D: All the tablespaces in the database must be of the smallfile tablespace type.
E: The tablespace type bigfile or smallfile must be mentioned in the command.
Correct Answers: C
75.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 Sundays contains all the blocks that have ever been use in
the database.
B:The backup performed on Sundays contains all the blocks that have changed since the
last level 1 backup.
C:The backup performed on each working day contains all the blocks that have changed
since the last level 0 or level 1 backup.
D:The backup performed on each working day contains all the blocks that have changed
since the last level 0 backup.
Correct Answers: A, C
76.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: moved the EMPLOYEES table to another location in the same tablespace
D: reorganized the associated indexes for the EMPLOYEES table that were in an unusable
state
E: moved the indexes associated with the EMPLOYEES table to the same tablespace where
the EMPLOYEES table exists
Correct Answers: D
77.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: any control file
B: any archive log file
C: one of the redo log members
D: loss of the initialization parameter file
E: any data file belonging to the default permanent tablespace
Correct Answers: A
78.Which two operations can be flashed back using the Flashback technology? (Choose two.)
A:DROP USER SMITH;
B:DROP TABLE EMPLOYEES;
C:DROP TABLESPACE USERS;
D:ALTER TABLE SALES_REP DROP PARTITION P1;
E:ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;
Correct Answers: A, B
79.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:If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate
redo.
C:The tablespace will be in the NOLOGGING mode by default, if not specified while creating
a tablespace.
D:The tablespace-level logging attribute can be overridden by logging specifications
at the table, index, materialized view, materialized view log, and partition levels.
Correct Answers: A, D
80.Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)?
(Choose two.)
A:The ADDM runs after each AWR snapshot is collected.
B:The ADDM requires at least four AWR snapshots for analysis.
C:The ADDM analysis provides only the diagnostic information but does not provide
recommendations.
D:The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR).
E:The ADDM calls other advisors if required, but does not provide recommendations about
the advisors.
Correct Answers: A, D
81.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: BLOB
B: CLOB
C: BFILE
D: LONG RAW
Correct Answers: C
82.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: Use the Database Resource Manager.
B: Set the limits in the profiles used by users.
C: Modify the SESSIONS initialization parameter.
D: Set the warning threshold for the Current Logons Count metric.
Correct Answers: D
83.In your database, the STATISTICS_LEVEL initialization parameter is set to BASIC. What
is the impact of this setting?
A: The optimizer statistics are collected automatically.
B: Only the timed operating system (OS) statistics and plan execution statistics are
collected.
C: The Oracle server dynamically generates the necessary statistics on tables as part
of query optimization.
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.
Correct Answers: D
84.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 Oracle Data Pump export and import utility
C: use the conventional path data load of SQL*Loader utility
D: use the direct path data load of Oracle export and import utility
Correct Answers: C
85.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: Upgrade the hardware/memory to accommodate the data.
B: Load the data into your database by using the PARALLEL clause.
C: Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
D: Use an external table so you can have the metadata available in your database, but
leave the data in the operating system files.
Correct Answers: D
86.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:It is possible to have more than one primary key constraint in a single table.
C:The primary key constraint can be referred by only one foreign key constraint.
D:The primary key constraint can be imposed by combining more than one column.
E:The non-deferrable primary key constraint creates an unique index on the primary key
column if it is not already indexed.
Correct Answers: D, E
87.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 database performance would be enhanced.
B: The redo log files would be get filled more frequently.
C: The overall database performance would be degraded.
D: The mean time to recover (MTTR) would be increased.
Correct Answers: C
88.Data files of which three tablespaces can be recovered by performing an open recovery?
(Choose three.)
A:TEMP
B:UNDO
C:INDEX
D:SYSAUX
E:SYSTEM
Correct Answers: A, C, D
89.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:TEMP
B:PROD
C:USERS
D:SYSAUX
E:SYSTEM
F:UNDOTBS1
Correct Answers: B, C, D
90.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: HR can grant the privilege to JIM but without GRANT OPTION.
B: HR can grant the privilege to JIM because HR is the owner of the view.
C: SCOTT has to grant the SELECT privilege on the EMP table to JIM before this operation.
D: HR needs the SELECT privilege on the EMP table with GRANT OPTION from SCOTT for this
operation.
Correct Answers: D
91.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: USER_DUMP_DEST
B: CORE_DUMP_DEST
C: TRACE_DUMP_DEST
D: BACKUP_DUMP_DEST
E: BACKGROUND_DUMP_DEST
Correct Answers: A
92.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 the CREATE DATABASE .. command to create the database with Oracle-managed files.
B: Use the Database Configuration Assistant (DBCA) to create the database with
Oracle-managed files.
C: Use Enterprise Manager to create a new database with the Online Transaction Processing
(OLTP) option.
D: Use Database Configuration Assistant (DBCA) to create the database with Transaction
Processing template.
E: Use the CREATE DATABASE .. command to create the database with Automatic Storage
Management (ASM) file system.
Correct Answers: D
93.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: Change the value for the BACKGROUND_DUMP_DEST parameter.
D: No action required.The file would be created automatically by the instance.
Correct Answers: D
94.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: The parameter value would be changed and it would come into effect immediately.
C: You need to restart the database so that parameter changes can come into effect.
D: The command would succeed only if initialization parameter LOG_ARCHIVE_MAX_PROCESS
is set to value 2.
Correct Answers: A
95.Which two statements regarding archive log destinations are true? (Choose two.)
A:A maximum of 10 destinations can be specified.
B:The archive logs must be written to all the destinations.
C:The archive log files can be written only to local destinations.
D:The archiving information can be traced to the alert log file whenever the archiving
to a destination is successful.
E:The number of archiving destinations must be equal to the number of archive processes
(ARCn).
Correct Answers: A, D
96.On which three can you use Recovery Manager (RMAN) to perform incremental backup?
(Choose three.)
A:data files
B:control files
C:tablespaces
D:password file
E:parameter file
F:whole database
G:flashback log file
H:archived log files
I:change tracking file
Correct Answers: A, C, F
97.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 is not the owner of the SYSTEM tablespace.
C: The user does not have quota on the TBS1 tablespace.
D: The user does not have sufficient system privileges to create table in the TBS1
tablespace.
E: The user does not have sufficient privileges to create table on the default permanent
tablespace.
Correct Answers: C
98.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 database buffer cache
C: because of data from a temporary tablespace
D: because of undo data from the undo tablespace
Correct Answers: D
99.You specified segment space management as automatic for a tablespace. What effect
would this have on space management?
A: Extents would be managed by the freelists.
B: The segment would be managed by bitmaps.
C: The segment would be managed by the freelists.
D: Free space would be managed by the data dictionary.
Correct Answers: B
100.Which two statements about Flashback Query are true? (Choose two.)
A:It is generated by using the redo log files.
B:It helps in row-level recovery from user errors.
C:It can be performed to recover ALTER TABLE statements
D:It fails when undo data pertaining to the transaction is overwritten.
E:The database has to be opened with the resetlogs option after performing Flashback
Query.
Correct Answers: B, D
101.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 and kill both the sessions responsible for the deadlock
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 remember that deadlocks are resolved
automatically by Oracle database
E: examine the trace file for details and use the Undo Advisor to rollback the transaction
that caused the deadlock
Correct Answers: D
102.Which two are valid locking levels that are used by transactions in an Oracle database?
(Choose two.)
A:row level
B:block level
C:object level
D:schema level
E:database level
Correct Answers: A, C
103.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: RECOVER LOGFILE BLOCK GROUP 2;
B: ALTER DATABASE DROP LOGFILE GROUP 2;
C: ALTER DATABASE CLEAR LOGFILE GROUP 2;
D: ALTER DATABASE RECOVER LOGFILE GROUP 2;
E: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Correct Answers: E
104.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:Event Viewer gives more details on the failure.
B:The alert log file gives detailed information about the failure.
C:PMON rolls back the transaction and releases the locks.
D:SMON rolls back the transaction and releases the locks.
E:The transaction is rolled back by the next session that refers to any of the blocks
updated by the failed transaction.
F:Data modified by the transaction up to the last commit before the abnormal termination
is retained in the database.
Correct Answers: C, F
105.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: B-tree
B: bitmap
C: reverse key
D: function based
Correct Answers: B
106.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 SQL script.
B: It is a PL/SQL file.
C: It is an operating system (OS) file.
D: It is a Recovery Manager (RMAN) script.
Correct Answers: D
107.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: add the user, SYSTEM, to the schemas option in the command
D: add network_link = devdb.us.oracle.com option in the command
E: remove the schemas option and add the network_link = devdb.us.oracle.com option in
the command
F: remove the dumpfile option and add the network_link = devdb.us.oracle.com option in
the command
Correct Answers: F
108.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: 5,2,3,4
B: 1,2,4,3,5
C: 2,4,3,4,5
D: 4,5,6,2,3
Correct Answers: B
109.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:More than one database is to be registered with the listener.
B:The users will connect the database by using the host naming method.
C:The Oracle Enterprise Manager is to be used to monitor an Oracle9i database.
D:The database that is to be registered with the listener is configured in shared server
mode.
E:The listener is not configured on the default port of 1521 and the instance is not
configured to register with a nondefault port.
Correct Answers: C, E
110.Which statement is true when you run the SQL Tuning Advisor with limited scope?
A: Access path analysis is not performed for the SQL statements.
B: SQL structure analysis is not performed for the SQL statements.
C: SQL Profile recommendations are not generated for the SQL statements.
D: The staleness and absence of the statistics are not checked for the objects in the
query supplied to the SQL Tuning Advisor.
Correct Answers: C
111.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 in undo tablespace retain data until the next full database backup.
B: The extents containing committed data in the undo tablespace are never overwritten.
C: The extents which no longer contain uncommitted data in the undo tablespace are not
overwritten for at least15 minutes.
D: The extents containing committed data in the undo tablespace are not overwritten until
the instance is shut down.
Correct Answers: C
112.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: recover using Oracle Flashback Database technology
B: restore the data files from backup and perform a complete recovery
C: restore only the corrupted data file and perform tablespace recovery
D: restore the data files from backup and perform cancel-based recovery
E: restore the entire database from the most recent backup and start the instance and
open the database
Correct Answers: E
113.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 operating system authentication for a remote client
B: to restrict the scope of administration to identical operating systems
C: to allow the start up and shut down of the database from a remote client
D: to enable the administration of the operating system from a remote client
E: to disable the administration of the operating system from a remote client
Correct Answers: A
114.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:An error is reported and tablespace creation fails.
C:Data files are created with names generated by the instance.
D:The tablespace can be extended without specifying the data file.
E:Data files belonging to the USERS tablespace cannot be renamed.
Correct Answers: C, D
115.Which two operations can be flashed back using the Flashback technology? (Choose
two.)
A:DROP USER SMITH;
B:DROP TABLE EMPLOYEES;
C:DROP TABLESPACE USERS;
D:ALTER TABLE SALES_REP DROP PARTITION P1;
E:ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;
Correct Answers: A, B
116.You specified extent management as local for a tablespace. How will it affect space
management in the tablespace?
A: All the extents will be of the same size.
B: Bitmap will be used to record free and allocated extents.
C: Free extents will be managed by the data dictionary tables.
D: The tablespace will be system managed and the users cannot specify the extent size.
Correct Answers: B
117.Which three statements are true regarding the logical structure of the Oracle
database? (Choose three.)
A:Each segment contains one or more extents.
B:Multiple tablespaces can share single data file.
C:A data block is the smallest unit of I/O for data files.
D:It is possible to have tablespaces of different block sizes in a database.
E:Each data block in the database always corresponds to one OS block.
Correct Answers: A, C, D
118.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: moved the EMPLOYEES table to another location in the same tablespace
D: reorganized the associated indexes for the EMPLOYEES table that were in an unusable
state
E: moved the indexes associated with the EMPLOYEES table to the same tablespace where
the EMPLOYEES table exists
Correct Answers: D
119.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 not open, and it would stop at mount stage.
C: The database would open after automatically performing instance recovery.
D: The database would not open, and you have to perform database recovery to open it.
Correct Answers: C
120.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 Grid Architecture
C: Optimal Flexible Architecture
D: Oracle database architecture
Correct Answers: C
121.What is the implication of setting the initialization parameter
FAST_START_MTTR_TARGET to 0 in your database?
A: MTTR Advisor would be disabled.
B: Redo Log Advisor would be disabled.
C: Automatic tuning of checkpoint would be disabled.
D: Checkpoint information would not be written to the alert log file.
Correct Answers: C
122.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: configure the database to run in ARCHIVELOG mode
B: configure the Flash Recovery Area to enable automatic database backup
C: schedule the database backup using DBMS_JOB package after creating the database
D: schedule the database backup using Recovery Manager (RMAN) commands after creating
the database
E: schedule the database backup using Database Configuration Assistant (DBCA) while
creating the database
Correct Answers: E
123.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:Set the UNDO_RETENTION parameter to 900 or more.
B:Create an initial undo segment in the undo tablespace.
C:Enable the retention guarantee for the undo tablespace.
D:Set the UNDO_TABLESPACE parameter to UNDO_TBS1.
E:Set the UNDO_MANAGEMENT initialization parameter to AUTO.
Correct Answers: D, E
124.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_LANG
B: NLS_LANGUAGE
C: NLS_TERRITORY
D: NLS_CHARACTERSET
E: NLS_DATE_LANGUAGE
F: NLS_LENGTH_SEMANTICS
G: NLS_NCHAR_CHARACTERSET
Correct Answers: A
125.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 succeeds and privileges are revoked from JIM.
B: The command fails because SCOTT still has privileges with him.
C: The command fails because HR cannot revoke the privileges from JIM.
D: Only HR can perform SELECT, INSERT, and UPDATE operations on the EMP table.
Correct Answers: C
126.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 alert.log file
B: the DBA_ALERTS view
C: the Database Control Home page
D: the Database Control performance page
E: the Database Control Maintenance page
Correct Answers: C
127.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:Data Pump
B:Oracle Net Manager
C:Oracle Enterprise Manager
D:Oracle Universal Installer (OUI)
E:Database Configuration Assistant (DBCA)
Correct Answers: B, C
128.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 CREATE
B: AFTER SUSPEND
C: BEFORE CREATE
D: AFTER TRUNCATE
E: BEFORE SUSPEND
F: AFTER INSERT OR DELETE OR UPDATE
G: BEFORE INSERT OR DELETE OR UPDATE
Correct Answers: B
129.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 dictionary cache is consuming more space than the library cache.
C: The shared pool size should be increased to accommodate the SQL statements.
D: Preparing indexes on the tables used in the SQL statements would improve the library
cache performance.
Correct Answers: A
130.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 optimizer statistics for few objects in the database were locked before the last
task.
C: The STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to
TYPICAL before the last task.
D: The optimizer statistics was collected manually by using the DBMS_STATS package before
the last task.
Correct Answers: C
131.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.
Correct Answers: A, D
132.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.
Correct Answers: B
133.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 and then manually copy the data using Oracle Data Pump
D: 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
Correct Answers: D
134.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: Compare the contents of the install.log file with the response file.
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: Refer to the alert log file for information regarding the actions performed by OUI
during installation.
Correct Answers: C
135.Your database is in NOARCHIVELOG mode. After which two operations you should take
the backup of control file ? (Choose two.)
A:adding a new user to the database
B:dropping a user from the database
C:dropping a table from the database
D:dropping a data file from a tablespace
E:adding a new tablespace to the database
Correct Answers: D, E
136.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: The user must be listed in the password file for the authentication.
C: No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
D: Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager,
and grant the webDba role to the user.
Correct Answers: D
137.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 AUTOEXTEND enabled
B:tablespace with dictionary-managed extents
C:tablespace with a uniform extent allocation of 1 MB
D:tablespace with segment space management as manual
E:tablespace with segment space management as automatic
Correct Answers: A, C, D
138.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: Use Flashback Table feature to revert the changes.
C: Restore the entire database from the recent backup and open it.
D: Issue the rollback statement with system change number (SCN).
Correct Answers: B
139.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 consistent.
B:The backups would be inconsistent.
C:The backups need to be restored and database has to be recovered in case of a media
failure.
D:The backups need not be restored during recovery in case of a media failure.
E:The backups would be possible only if the database is running in ARCHIVELOG mode.
F:The backups would be possible only if the database is running in NOARCHIVELOG mode.
Correct Answers: B, C, E
140.Which two database operations can be performed at the MOUNT stage of database startup?
(Choose two.)
A:renaming of data files
B:dropping the database user
C:renaming of control files
D:multiplexing of control files
E:configuring the database in ARCHIVELOG mode
Correct Answers: A, E
141.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 SQL Access Advisor.
B: Run the SQL Tuning Advisor (STA).
C: Run the Automatic Workload Repository (AWR) report.
D: Run the Automatic Database Diagnostic Monitor (ADDM).
Correct Answers: A
142.Which two statements are true regarding a PL/SQL package body? (Choose two.)
A:It cannot be created without a package specification.
B:It cannot invoke subprograms defined in other packages.
C:It can contain only the subprograms defined in the package specification.
D:It can be changed and recompiled without making the package specification invalid.
Correct Answers: A, D
143.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 Oracle Data Pump export and import utility
C: use the conventional path data load of SQL*Loader utility
D: use the direct path data load of Oracle export and import utility
Correct Answers: C
144.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: 1,2,3,4
B: 3,2,1,4
C: 3,1,2,4
D: 2,3,1,4
Correct Answers: C
145.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:average row size
B:last analyzed date
C:size of table in bytes
D:size of table in database blocks
E:number of free blocks in the free list
F:number of extents present in the table
Correct Answers: A, B, D
146.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 cannot use the role at all.
B: The user can grant the role to other users.
C: The user needs to enable the role explicitly.
D: The user can start using the role immediately.
Correct Answers: C
147.View the Exhibit.
Which statement causes more undo generation?
A: SELECT * FROM emp;
B: DELETE FROM emp WHERE empno=7934;
C: UPDATE emp SET comm=400 WHERE empno=7844;
D: INSERT INTO emp VALUES(7999,'JHON','CLERK',7782,'10-MAY-83',1500,NULL,10);
Correct Answers: B
148.In which situation would you use the Oracle Shared Server configuration?
A: when performing export and import using Oracle Data Pump
B: when performing backup and recovery operations using Oracle Recovery Manager
C: when performing batch processing and bulk loading operation in a data warehouse
environment
D: in an online transaction processing (OLTP) system where large number of client
sessions are idle most of the time
Correct Answers: D
149.Which two statements about Recovery Manager (RMAN) backups are true? (Choose two.)
A:Online redo log files can be backed up.
B:Archived redo log files are backed up.
C:Only used data blocks can be backed up as backup sets.
D:Only consistent database backups can be performed.
E:RMAN backup can be taken only if the database is configured in ARCHIVELOG mode.
Correct Answers: B, C
150.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:It is terminated after receiving the error and JIM can continue with his transaction.
B:The transaction in it is terminated after receiving the error and JIM can continue
with his transaction.
C:SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
D:SCOTT has to reexecute the last command in the transaction after he commits the
transaction.
Correct Answers: C, D
151.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 alert conditions are cleared.
B:The threshold alerts are transferred to DBA_ALERT_HISTORY.
C:The non-threshold-based alerts are transferred to DBA_ALERT_HISTORY.
D:The threshold alerts related to database metrics are permanently stored in
DBA_ALERT_HISTORY but not the threshold alerts related to instance metrics.
Correct Answers: A, B
152.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 SYSAUX tablespace.
B: The table is moved to the SYSTEM tablespace.
C: The table is removed from the database permanently.
D: The table is renamed and remains in the TBSFD tablespace.
Correct Answers: D
153.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 TNS_ADMIN environmental variable is set to a wrong value.
C: The orcl.oracle.com database service is not registered with the listener.
D: The DEFAULT_DOMAIN parameter is set to a wrong value in the SQLNET.ORA file.
Correct Answers: C
154.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.
Correct Answers: D
155.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:NLS_LANG
B:ORACLE_SID
C:ORACLE_HOME
D:ORACLE_BASE
E:LD_LIBRARY_PATH
Correct Answers: B, C
156.View the Exhibit.
Which statement regarding the dept and emp tables is true?
A: When you delete a row from the emp table, you would receive a constraint violation
error.
B: When you delete a row from the dept table, you would receive a constraint violation
error.
C: When you delete a row from the emp table, automatically the corresponding rows are
deleted from the dept table.
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 dept table, automatically the corresponding rows are
updated with null values in the emp table.
F: When you delete a row from the emp table, automatically the corresponding rows are
updated with null values in the dept table.
Correct Answers: D
157.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:alter the foreign key constraint to include the cascade option
B:alter the foreign key constraint to include the on delete cascade option
C:first, drop the EMPLOYEES table and then delete the rows from the DEPARTMENTS table
D:first, drop the DEPARTMENTS table and then delete the rows from the EMPLOYEES table
E:first, delete all of the rows from EMPLOYEES table and then delete the rows from the
DEPARTMENTS table for department id 30
F:first, delete rows from the EMPLOYEES table for department id 30 and then delete the
rows from the DEPARTMENTS table for department id 30
Correct Answers: B, F
158.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 base directory.
C: It creates the Inventory pointer file.
D: It creates the Oracle user for installation.
E: It modifies the Unix kernel parameters to match Oracle's requirement.
Correct Answers: C
159.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: PROD
C: USERS
D: SYSAUX
E: SYSTEM
Correct Answers: C
160.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: FULL
B: SCHEMAS
C: REMAP_SCHEMA
D: REMAP_DATAFILES
E: REMAP_TABLESPACE
Correct Answers: C
161.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: Load balancing
B: Instance failover
C: Database failover
D: Connect-time failover
E: Transparent Application Failover (TAF)
Correct Answers: D
162.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 created for every session when any user successfully drops a table
owned by SCOTT.
C: One audit record is created for each successful DROP TABLE command executed by any
user to drop tables owned by SCOTT.
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 each successful DROP TABLE command executed in the
session of SCOTT.
Correct Answers: A
163.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: PGA
C: Shared Pool
D: Streams Pool
Correct Answers: B
164.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: Modify the resource limit in the profile used by the user.
B: Set the RESOURCE_LIMIT initialization parameter to FALSE.
C: Increase the value of the SESSION_CACHED_CURSORS initialization parameter.
D: Increase the value of the SESSION_MAX_OPEN_FILES initialization parameter.
Correct Answers: A
165.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 STATISTICS_LEVEL initialization parameter to ALL.
B: Create a baseline on a pair of snapshots that have the statistics of the peak-load
period in the database.
C: Decrease the snapshot interval in the AWR to collect more number of snapshots during
the peak-load period.
D: Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero
to avoid the automatic purging of the snapshots.
Correct Answers: B
166.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 FGA_LOG$ table.
C:The audit trail for FGA is stored in the AUD_LOG$ table.
D:FGA enables a SQL predicate to define when to audit an event.
E:FGA audits DELETE statements only when audit columns are specified.
F:FGA includes the SQL statement used by the user as part of the audit event entry.
Correct Answers: B, D, F
167.Which two statement about Automatic Storage Management (ASM) are true? (Choose two.)
A:ASM provides mirroring on file by file basis.
B:ASM provides automatic load balancing across all ASM disks.
C:ASM supports the Oracle database and operating system files.
D:ASM can be used to store trace files, alert log files, and the server parameter file
(SPFILE).
Correct Answers: A, B
168.Which three descriptions are correct about the effects of the TRUNCATE command on
a table? (Choose three.)
A:The corresponding indexes for the table are also truncated.
B:Delete triggers on the table are fired during the execution of the TRUNCATE command.
C:Very little or no undo data is generated during the execution of the TRUNCATE command.
D:The child table is truncated when the TRUNCATE command is applied on the parent table.
E:The high-water mark (HWM) is set to point to the first useable data block in the table
segment.
Correct Answers: A, C, E