Good Luck

---

BlueHeaven В….. Enjoy

Question 1
Which three methods can you use
to run an Automatic Database Diagnostic Monitor
(ADDM) analysis over a specific time period? (Choose three.)
A. Enterprise Manager GUI
B.
DBMS_TRACE package APIs
C. DBMS_ADVISOR package APIs
D. DBMS_MONITOR package APIs
E. $ORACLE_HOME/rdbms/admin/addmrpt.sql script
Answer: A, C,
E
Explanation:
-----------------
To make use of ADDM, a PL/SQL interface called DBMS_ADVISOR has been
implemented. This PL/SQL interface may be
called through the supplied
$ORACLE_HOME/rdbms/admin/addmrpt.sql script, called directly, or used in
combination with the Oracle Enterprise
Manager application. Besides this PL/SQL
package a number of views (with names starting with the DBA_ADVISOR_ prefix)
allow retrieval of the
results of any actions performed with the DBMS_ADVISOR API.
The preferred way of accessing ADDM is through the Enterprise Manager interface,
as it
shows a complete performance overview including recommendations on how to
solve bottlenecks on a single screen. When accessing ADDM manually, you
should
consider using the ADDMRPT.SQL script provided with your Oracle release, as it
hides the complexities involved in accessing the
DBMS_ADVISOR package.

Question 2
Which background process does Automatic Shared Memory Management use to
coordinate the sizing of memory
components?
A. PMON
B. SMON
C. MMNL
D. MMAN
E. MMON
Answer: D
.

Question 3
Which six files are maintained in the Flash Recovery Area?
(Choose six.)
A. control file
B. RMAN files
C. password file
D. parameter file
E. flashback logs
F. data file copies
G. core dump files
H.
archived log files
I. RMAN recovery scripts
J. control file autobackpus
Answer: A, B, E, F, H, J
The Flash Recovery Area is a unified storage
location for all recovery-related files and
activities in an Oracle Database. It includes Control File, Archived Log Files,
Flashback Logs,
Control File Autobackups, Data Files, and RMAN files.

Question 4
Consider the following scenario: You have a directory, data, under the disk
group
tdgroupA. You want to create an alias for one of the data files and you execute the
following command:
ALTER DISKGROUP tdgroupA
ADD ALIAS
В‘+tdgroupA/data/datafile.dbfВ’
FOR В‘+tdgroupA.231.45678В’;
Which task would be accomplished by the command?
A. The command drops the file
+tdgroupA.231.45678
B. The command physically relocates the file to +tdgroupA/data and renames the file to datafile.dbf.
C. The command creates a
copy of the +tdgroupA.231.45678 file and places it in +tdgroupA/data after remaining the file to datafile.dbf.
D. The command creates a synonym,
datafile.dbf, and places it in +tdgroupA/data and does not remove the +tdgroupA.231.45678 file.
E. The command creates a file, datafile.dbf, in
+tdgroupA/ data and removes the references for +tdgroupA.231.45678 from the data dictionary views.
Answer: D
.

Question 5
Exhibit

One the
evening of April 22, you are working on a database created using Oracle
Database 10g. This database operates in the ARCHIVELOG mode. You discover
that
you need crucial data that was dropped from the database at 8:00 a.m. No full
backup has been taken after April 15. What would you do?
A.
recover the database until April 10
B. recover the database until April 15
C. recover the database until 22 7:59 a.m.
D. recovery is not
possible; manually re-create the object
Answer: C

Question 6
You are unable to move the Unified Job Scheduler occupant from the
SYSAUX
tablespace to the USERS tablespace. What could be the reason?
A. None of the SYSAUX occupants can be relocated.
B. The USERS tablespace is
a bigfile tablespace (BFT).
C. The united Job Scheduler occupant cannot be relocated.
D. The SYSAUX occupants can be relocated to the SYSTEM
tablespace only.
Answer: C

Question 7
You enabled block change tracking for faster incremental backups in your database.
Which background
process writes to the change tracking file?
A. RBAL
B. CKPT
C. SMON
D. PMON
E. MMON
F. CTWR
G. DBWR
Answer: F
.
Question 8
You want to
enforce a companyВ’s business policy on several objects by using a single
policy function. Which two types of policies can be assigned to the
policy_type
argument in the dbms_rls.add_policy procedure to achieve the above objective?
(Choose two.)
A. DBMS_RLS.STATIC
B.
DBMS_RLS.DYNAMIC
C. DBMS_RLS.SHARED_STATIC
D. DBMS_RLS.CONTEXT_SENSITIVE
E. DBMS_RLS.SHARED_CONTEXT_SENSITIVE
Answer: C, E

Question 9
The
Automatic Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes
on your database. Your database if facing a series of interrelated
problems over a
period of two hours. You need to ensure that the ADDM analysis is run over a time
span of two hours in future. What would you
do?
A. Create two custom ADDM tasks.
B. Modify the AWR snapshot time interval to two hours.
C. Create a new scheduler window for a time period of
two hours.
D. Modify the time interval by using the DBMS_JOB.INTERVAL procedure.
E. Modify the Automatic Workload Repository (AWR) snapshot
retention period to
two hours.
Answer: B

Question 10
The database is currently running in the NOARCHIVELOG mode. What would be the
first step
to configure Flashback Database?
A. Enable flashback logging.
B. Start the database in the ARCHIVELOG mode.
C. Issue the ALTER DATABASE FLASHBACK
ON; command.
D. Set the FAST_START_MTTR_TARGET initialization parameter.
Answer: A
.

Question 11
Consider the following command to add a new
disk group called В“tdgroupAВ” with two
failover groups:
CREATE DISKGROUP tdgrouopA NORMAD REDUNDANCY
FAILOVERGROUP control01
DISK
В‘/devices/A1В’,
В‘/devices/A2В’,
В‘/devices/A3В’
FAILOVERGROUP control02 DISK
В‘/devices/B1В’,
В‘/devices/B2В’,
В‘/devices/B3В’;
The
disk В“/devices/A1В” is currently a member disk of a disk group by name
В“tdgroup1В”
Which task would be accomplished by the command?
A. The
command would result in an error because a disk group can have only one failover group.
B. This command would result in an error because
/devices/A1 disk is a member of another disk group tdgroup1.
C. A new disk group called tdgroupA will be added with two fialover groups and the
/devices/A1 disk will get reattached to the new disk group without being detached from the existing one.
D. A new disk group called tdgroupA will
be added with two failover groups and the /devices/A1 disk will be ignored for the new disk group because it is a member of an existing disk group
tdgroup1.
E. A new disk group called tdgroupA will be added with two failover groups and the /devices/A1 disk gets detached from the existing disk
group tdgroup1 and attached to the new disk group tdgroupA disk group.
Answer: B
.

Question 12
Exhibit

You have created the following
auditing policy:
BEGIN
dmbs_fga.add_policy (
object_schema => В‘DNXВ’,
object_name => В‘CUSTOMERSВ’
policy_name =>
В‘policy_acc_principalВ’,
audit_condition =>NULL,
audit_column => В‘ACCOUNT_ID, AMOUNTВ’,
audit_column_opts => DBMS_FGA.ALL_COLUMNS,
audit_trail
=> DBMS_FGA.DB_EXTENDED,
statement_types => В‘INSERT, UPDATEВ’);
END;
Which statement is true about the fine-grained auditing implemented by this
policy?
A. An audit record is created for all INSERT and UPDATE statements on either the
ACCOUNT_ID or AMOUNT columns.
B. An audit record is
created for all INSERT and UPDATE statements on both the
ACCOUNT_ID or AMOUNT columns.
C. An audit record is created for all INSERT and UPDATE
statements on both the
ACCOUNT_ID and AMOUNT columns.
D. An audit record is created for all INSERT and UPDATE statements when the
ACCOUNT_ID or
the AMOUNT column contains a NULL value.
Answer: B?
Note:
Argument for not B (disputed): B is not correct, because the audit record is
created
for all INSERT and UPDATE statements if both ACCOUNT_ID and AMOUNT are
accessed in the SQL statement (all the columns from
audit_column).
An audit record is created for all INSERT and UPDATE statements AND both the
ACCOUNT_ID or AMOUNT columns. Instead of An audit
record is created for all INSERT and UPDATE statements on both the ACCOUNT_ID or AMOUNT columns.

Question 13
You need to ensure that the
database users should be able to use the various
flashback query features in order to go back in time by four hours. What should you
do? (Choose
two.)
A. set SQL_TRACE=true
B. set UNDO_RETENTION=14400
C. set FAST_START_MTTR_TARGET=240
D. set LOG_CHECKPOINT_INTERVAL=240
E. set
DB_FLASHBACK_RETENTION_TARGET=14400
F. issue ALTER DATABASE FLASHBACK ON; command
G. set the RETENTION GUARANTEE clause for the undo
tablespace
Answer: B, G

Question 14
Which statement regarding the COMPATIBLE parameter is correct?
A. It is a dynamic parameter.
B. It is an
advanced parameter.
C. It is a new parameter in Oracle 10g.
D. It can have any value between8.0.0 and 10.1.0 in Oracle 10g database.
E. After it
is set to 10.0.0 or greater and the database is opened, it cannot be set
back.
Answer: E
.
Question 15
You are using Recovery Manager (RMAN) to
perform backups. In which three
situations would you perform a compressed backup? (Choose three.)
A. Your database includes a large number of
BFILEs.
B. You are making image copies, and you have not enabled tablespace compression.
C. You are backing up to tape and your tape device
performs its won compression.
D. You are using disk-based backups and disk space in your Flash Recovery Area, or other diskbased
backup
destinations are limited.
E. You are performing your backups to some device over a network where reduced network
bandwidth is more important than
CUP usage.
F. You are using some archival backup media, such as CD or DVD, where reducing backup sizes
saves media costs and archival
storage.
Answer: D, E, F

Question 16
You plant to create a database В‘PRODВ’ using the Database Configuration Assistant
(DBCA).
Which two
tasks can you automate while created the database using the DBCA?
(Choose two.)
A. database backups
B. user quota increment
C. tablespace
defragmentation
D. gathering optimizer statistics
E. data export using Data Pump
F. data export using conventional export
Answer: A,
D

Question 17
Which two dynamic performance views would you query to determine the endian
format of your platform? (Choose two.)
A.
V$DATABASE
B. V$INSTANCE
C. V$PLATFORM
D. V$TABLESPACE
E. V$CONTROLFILE
F. V$TRANSPORTABLE_PLATFORM
Answer: A, F
.
Question 18
Which three
statements regarding the Policy Framework in Enterprise Manager (EM)
are correct?
(Choose three.)
A. Policies are evaluated automatically by
Enterprise Manager.
B. Policy violations are prioritized as High, Medium, or Informational by category.
C. Policies can be disabled completely, or
flagged as ignored, for the specified target.
D. Policies are evaluated based on configuration and metrics collected in Policy Repository.
E.
Policies can be flagged as ignored, but never completely disabled, for the specific target.
F. Policy violations must be corrected, otherwise the
database will shut down until corrective action occurs.
Answer: A, B, C

Question 19
You want to retain the job entries for the last five days
and purge all job entries from
the job log that are older than five days. Select the command that will enable you to
do the job.
A. EXECUTE
DBMS_SCHEDULER.PURGE_LOG ( log_history => 5, which_log =>
В‘JOB_LOGВ’);
B. EXECUTE DBMS_SCHEDULER.PURGE_LOG ( );
C. EXECUTE
DBMS_SCHEDULER.PURGE_LOG ( log_history => 5, job_name =>
В‘JOB_LOGВ’);
D. EXECUTE DBMS_SCHEDULER.PURGE_LOG ( log_history => 5,which_log
=>
В‘WINDOW_LOGВ’);
Answer: C

Question 20
Which three actions are required to configure the Flashback Database? (Choose
three.)
A. set Flash
Recovery Area
B. enable Flashback logging
C. create FLASHBACK tablespace
D. create a user called flashoper
E. start the database in the
ARCHIVELOG mode
F. start the database in the NOARCHIVELOG mode
Answer: A, B, E
.

Question 21
Which four statements regarding the Clone
Database tool are correct? (Choose four.)
A. It clones Oracle databases from release 8.1.7 or later.
B. It clones a source database while the
database is in NOMOUNT state.
C. It clones a source database while the database is in MOUNT state.
D. It clones an Oracle database by using
Recovery Manager (RMAN).
E. It clones a source database at the specified Oracle home and starts the new database instance in the open mode.
F. It
clones a source database and makes the new database instance consistent with the source database up to the backup time of archived log.
Answer: A,
C, D, F
Note: The question is wrong. There isn't a "CLONE DATABASE tool". There are
only DBCA and RMAN tools to clone a
database.
Explanation:
Observation: If we want to clone a database using the DBCA, first we create a database template; after that doesn't matter
if the database source is in NOMOUNT or MOUNT state.
Cloning Databases Using DBCA
The "Template Management" section of the Database
Configuration Assistant (DBCA) can be used to clone databases. The following method creates a clone of and existing database including both the
structure and the data:
В• Start the Database Configuration Assistant (DBCA).
В• On the "Welcome" screen click the "Next" button.
В• On the
"Operations" screen select the "Manage Templates" option and click the "Next" button.
В• On the "Template Management" screen select the
"Create a database template" option and select the "From and existing database (structure as well as data)" sub-option then click the "Next"
button.
В• On the "Source database" screen select the relevant database instance and click the "Next" button.
В• On the "Template
properties" screen enter a suitable name and description for the template, confirm the location for the template files and click the "Next"
button.В• On the "Location of database related files" screen choose either to maintain the file locations or to convert to OFA structure
(recommended) and click the "Finish" button.
В• On the "Confirmation" screen click the "OK" button.
В• Wait while the Database Configuration
Assistant progress screen gathers information
about the source database, backs up the database and creates the template.
By default the template
files are located in the "$ORACLE_HOME/assistants/dbca/templates"
directory.
.
Question 22
The ______ procedure is used to determine which
statements are needed to make
your materialized view eligible for fast refresh and usable for general rewrite.
A. REFRESH
B. TUNE_MVIEW
C.
REGISTER_MVIEW
D. EXPLAIN_REWRITE
E. REFRESH_DEPENDENT
Answer: B

Question 23
The ______ identifies and helps to resolve performance problems
relating to the
execution of SQL statements by recommending which indexes, materialized views, or
materialized view logs to create, drop, or
retain.
A. Undo Advisor
B. MTTR Advisor
C. Memory Advisor
D. Segment Advisor
E. SQL Tuning Advisor
F. SQL Access Advisor
Answer:
F

Question 24
You need to create a schedule that will run a job on the second Friday of each
month. What should the repeat interval of the
schedule be set to?
A. FREQ = MONTHLY; BYDAY = 2FRI;
B. FREQ = MONTHLY, BYDAY = FRI2;
C. FREQ = MONTHLY; BYDAY = -2FRI;
D. FREQ = MONTHLY; BYDAY
= FRI (2);
Answer: A

Question 25
Which three file types are managed by Data Pump jobs? (Choose three.)
A. out files
B. log files
C. alert
files
D. SQL files
E. error files
F. dump files
Answer: B, E, F
.
Question 26
You want an ASM instance to manage the files of your database.
To achieve this
objective, you specify the following parameters in the parameter file of the database.
INSTANCE_TYPE = RDBMS
LARGE_POOL_SIZE =
8MB
BD_BLOCK_SIZE = 4K
LOG_ARCHIVE_DEST = +dgroupA
LOG_ARCHIVE_FORMAT = В“$ORACLE_SID_%s_%t.%tВ”
DB_CREATE_FILE_DEST = +dgroupA
COMPATIBLE =
10.1.0.2.0
CONTROL_FILES = +dgroupA
Which parameter would be ignored while starting up the instance?
A. DB_BLOCK_SIZE
B. CONTROL_FILES
C.
LARGE_POOL_SIZE
D. LOG_ARCHIVE_DEST
E. LOG_ARCHIVE_FORMAT
F. DB_CREATE_FILE_DEST
Answer: E

Question 27
Which statement regarding the
implementation of shared policy functions is correct?
A. The policies need to have the same name.
B. The policy type must be DBMS_RLS.DYNAMIC.
C.
The shared policy function can be enforced on any number of objects.
D. The shared policy function cannot be enforced on more than two
objects.
Answer: C
.
Question 28
Tom is the DBA of DNX Bank. The users of the banking system are complaining that
all debit transactions are
taking too long to execute. The details of the application
used for this purpose are listed below:
Application В– Bank_DNX to access banking
accounts
Module В– CUSTLEDGER
Action DEBIT_ENTRY
Which command should Tom execute in order to gather static for the states
problem?
A. EXECUTE
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (В‘BANK_DNXВ’,
В‘CUSTLEDGERВ’);
B. EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENALBLE (В‘BANK_DNXВ’);
C.
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (В‘DEBIT_ENTRYВ’);
D. EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (В‘BANK_DNXВ’,
В‘CLUSTLEDGER
В‘,В’ DEBIT_ENTRYВ’);
E. EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (В‘CLUSTLEDGERВ’,
В‘DEBIT_ENTRYВ’);
Answer: D

Question 29
You created
a database (using Database Configuration Assistant [DBCA]) by using
one of the default templates. The default permanent tablespace for the non-
system
users, excluding DBSNMP and OUTLN users, will be set to ________.
A. USERS
B. SYSTEM
C. SYSAUX
D. EXAMPLE
Answer: A
.

Question
30
Examine the following command to create an external table from EMPLOYEES and
DEPARTMENTS database tables.
1. CREATE TABLE employee_ext
2.
(employee_id, first_name, department_name)
3. ORGANIZATION EXTERNAL
4. (
5. TYPE ORACLE_LOADER
6. DEFAULT DIRECTORY ext_dir
7. LOCATION
(В‘emp1.dmpВ’)
8. )
9. PARALLEL
10. AS
11. SELECT e.emplyee_id, e.first_name, e.last_name,
d.department_name
12. FROM employees e, departments
d;
Which line of the command would cause an error?
A. line 2, because the column names have been specified
B. line 7, because file name must have
a .dat extension
C. line 3, because ORGANIZATION EXTERNAL has been specified
D. line 9, because the PARALLEL option cannot be specified with one
file name
E. line 12, because there is no join defined between EMPLOYEES and DEPARTMENTS
table
F. line 5, because ORACLE_LOADER is not a valid
structure for external table creation
with the SELECT statement
Answer: F

Question 31
You have three production database, HRDB, FINDB, and
ORGDB, that use the ASM instance. At the end of the day, you execute the following command on the ASM instance to shut down:
SQL> shutdown
immediate; What is the result of executing this command?
A. All the instances, including the ASM instance, are shut down in the ABORT mode.
B. The
ASM instance is shut down, but the other instances are still running.
C. The ASM instance is still functional, but the other instances are shut
down.
D. All the instances, including the ASM instance, are shut down in the IMMEDIATE
mode.
E. HRBD, FINDB, and ORGDB instances are shut down in
the ABORT mode and the
ASM instance is shut down in the IMMEDIATE mode.
F. HRDB, FINDB, and ORGDB instances are shut down in the NORMAL mode and
the
ASM instance is shut down.
Answer: D

Question 32
Exhibit

Examine the SQL statement used to create the EMP table.
You need to shrink
the EMP table segment. Which SQL statement would you execute
as a prerequisite before you execute the ALTER TABLE emp SHRINK SPACE;
command?
A.
ALTER TABLESPACE emp OFFLINE;
B. ALTER TABLESPACE emp READ ONLY;
C. ALTER TABLE emp DEALLOCATE UNUSED;
D. ALTER TABLE emp ENABLE ROW
MOVEMENT;
E. ALTER TABLE emp DISABLE ALL TRIGGERS;
F. ALTER TABLE emp ENABLE NOVALIDATE CONSTRINT emp_last_name_nn;
Answer: D

Question
33
While exporting data by using Data Pump, you find that the export runs for long
period. Because this action is being performed during peak
hours, you decide to stop
the job and restart it during off-peak hours. Which view would you query to
determine the name and status of the stopped
job?
A. DBA_JOBS
B. V$SESSION
C. V$SESSION_LONGOPS
D. DBA_DATAPUMP_JOBS
E. DBA_DATAPUMP_SESSIONS
Answer: D
.
Question 34
In the server
parameter file (SPFILE), the UNDO_TABLESPACE initialization parameter is set to UNDOTBS. You executed the following SQL statement to rename the
UNDOTBS undo tablespace: ALTER TABLESPACE undotbs RENAME TO undotbs_old;
Which statement is correct in this scenario?
A. The tablespace will be
renamed but the data file headers will not be updated.
B. The above SQL statement will fail because you cannot rename an undo tablespace.
C. The
tablespace will be renamed and all the changes will be logged in the alert log file.
D. The tablespace will be renamed but a message will be added
to the alert log file indicating that you should change the corresponding initialization parameter files.
E. To be able to rename the UNDOTBS undo
tablespace, you would need to set the
UNDO_TABLESPACE initialization parameter to some other tablespace name and then
execute the above SQL
command.
Answer: C

Question 35
Identify four uses of the Oracle Scheduler. (Choose four.)
A. Enables you to set idle time limits for a
resource plan.
B. Enables you to schedule job execution based on time.
C. Enables you to execute jobs in a clustered environment.
D. Enables you
to assign priorities to the consumer groups.
E. Enables you to map a consumer group to an Oracle user.
F. Enables you to create a job that makes
use of saved programs and schedules.
G. Enables you to periodically execute operating system script files located on the
same server as the
database.
Answer: B, C, G, F
.
Example of G:
Perhaps the concept can be best introduced through examples. Suppose you have
created a shell
script to move archived log files to a different filesystem as follows:
/home/arup/dbtools/move_arcs.sh
We can specify the OS executable directly
without creating it as a program first.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_2',
schedule_name =>
'EVERY_30_MINS',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
enabled => true,
comments => 'Move Archived
Logs to a Different Directory'
);
end;

Question 36
You enabled Flashback Database with the following command:
ALTER DATABASE FLASHBACK
ON;
Which view would you query to determine whether the Flashback Database has been
enabled?
A. V$SGA
B. V$DATABASE
C. V$INSTANCE
D.
V$FLASHBACK_DATABASE_LOG
E. V$FLASHBACK_DATABASE_STAT
F. V$FLASHBACK_DATABASE_LOGFILE
Answer: B

Question 37
You flashed back the jobs table
at 11:00 a.m. to its state at 9:30 a.m.
At 11:15 a.m., you decided to retrieve the jobs table as it was at 10:12 a.m.
What would you do to
retrieve the jobs table fast and with minimum impact to other
objects?
A. drop and re-create the table
B. perform point-in-time recovery
C. use
the ROLLBACK command with SCN
D. use the FLASHBACK TABLE command
E. use the FLASHBACK DATABASE command
Answer: D
.
Question 38
You executed
the following command to drop a user:
DROP USER scott CASCADE;
Which two statement regarding the above command are correct? (Choose two.)
A. All
the objects of scott are moved to the Recycle Bin.
B. Any objects in the Recycle Bin belonging to scott are purged.
C. All the objects owned by
scott are permanently dropped from the database.
D. All the objects of scott in the Recycle Bin must be purged before executing the
DROP
command.
E. Any objects in the Recycle Bin belonging to scott will not be affected by the above
DROP command.
Answer: B, C

Question 39
You
want to execute the following statements in resumable mode:
CREATE TABLE В… AS SELECT
CREATE INDEX
CREATE MATERIALIZED VIEW
Which two methods
can be used to enable a session for resumable space allocation?
(Choose two.)
A. Set the RESUMABLE_TIMEOUT initialization parameter to true.
B.
Set the RESUMABLE_TIMEOUT initialization parameter to none.
C. Execute the ALTER SESSION ENABLE RESUMABLE statement.
D. Execute the ALTER DATABASE
ENABLE RESUMABLE statement.
E. Set the RESUMABLE_TIMEOUT initialization parameter to a nonzero value.
Answer: C, E

Question 40
You created a
tablespace with the following statement:
CREATE BIGFILE TABLESPACE adtbs
DATAFILE В‘/proddb/data/adtbs.dbfВ’ SIZE 10G;
There is now a requirement
to increase the size of the tablespace.
Which two ALTER statements are correct in this scenario? (Choose two.)
A. ALTER TABLESPACE adtbs RESIZE
20G;
B. ALTER TABLESPACE adtbs ADD DATAFILE;
C. ALTER TABLESPACE adtbs AUTOEXTEND ON;
D. ALTER TABLESPACE adtbs ADD DATAFILE
В‘/proddb/data/adtbs1.dbfВ’ SIZE 10G;
E. ALTER TABLESPACE adtbs MODIFY DATAFILE В‘/proddb/data/adtbs.dbfВ’
AUTOEXTEND ON;
Answer: A,
C
.

Question 41
You imposed a column-level Virtual Private Database (VPD) policy as follows:
BEGIN
DBMS_RLS.ADD_POLICY (object_schema=>
В‘scottВ’,
object_name => В‘employeesВ’,
policy_name => В‘hr_policyВ’,
function_schema => В‘pol_hrВ’,
policy_function =>
В‘hrpolВ’,
sec_relevant_clos => В‘salary,commission_pctВ’);
END;
Which statement regarding hr_policy is true?
A. The policy applies only to the
INDEX statement and note to any other statements
B. The policy applies only to the SELECT statement and not to any other statements.
C. The policy
applies to SELECT, INSERT, UPDATE, DELETE, and INDEX statements.
D. The policy applies to SELECT, INSERT, UPDATE and DELETE statements; it
does
not apply to INDEX statements.
Answer: D
Explanation:
DBMS_RLS.ADD_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN
VARCHAR2,
policy_name IN VARCHAR2,
function_schema IN VARCHAR2 NULL,
policy_function IN VARCHAR2,
statement_types IN VARCHAR2
NULL,
update_check IN BOOLEAN FALSE,
enable IN BOOLEAN TRUE,
static_policy IN BOOLEAN FALSE);
statement_types is the statement types to which
the policy applies. It can be any
combination of SELECT,
INSERT, UPDATE, and DELETE. The default is to apply to all of these types.
The policy
not applies on the INDEX statements.
.

Question 42
You are using SQL Tuning Advisor (STA) to tune SQL workload in your database.
Which four
types of recommendations do you get from Automatic Tuning Optimizer
(ATO) via the STA? (Choose four.)
A. recommendation to create indexes
B.
recommendation to use stored outlines
C. recommendation to create a SQL profile
D. recommendation to gather relevant statistics
E. recommendation
to denormalize the schema
F. recommendation to restructure SQL statements
Answer: A, D, E, F
For the A answer see the point nВ°3.
For the C
answer see the point nВ°2.
Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make
recommendations about how specific statements can be tuned further. This process may take several minutes for a single statement so it is intended
to be used on high-load resource-intensive statements.
In tuning mode the optimizer performs the following analysis:
1. Statistics Analysis - The
optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in
an SQL profile.
2. SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and altering session
specific parameters such as the OPTIMIZER_MODE. If such improvements are possible the information is stored in an SQL profile. If accepted this
information can then used by the optimizer when running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may
still be of benefit when the contents of the table alter drastically. Even so, it's sensible to update profiles periodically. The SQL profiling is
not performed when the tuning optimizer is run in limited mode. The SQL profile doesn't recommend to create a SQL profile.
3. Access Path Analysis -
The optimizer investigates the effect of new or modified indexes on the access path. It's index recommendations relate to a specific statement so
where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
4.
SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance. The
implementation of these suggestions requires human intervention to check their validity. Access Path Analysis - The optimizer investigates the
effect of new or modified indexes on the access path. It's index recommendations relate to a specific statement so where necessary it will also
suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
.

Question 43
You executed the
following command:
RMAN> RECOVER COPY OF DATAFILE В‘/u01/app/oracle/oradata/orcl/users01.dbfВ’;
Which statement regarding the above command is
correct?
A. The В‘/u01/app/oracle/ordata/orcl/users01.dbfВ’ data file is recovered from the image copy.
B. The
В‘/u01/app/oracle/ordata/orcl/users01.dbfВ’ data file is recovered from the last incremental backup.
C. Image copies of the
В‘/u01/app/oracle/ordata/orcl/users01.dbfВ’ data file are updated with all changes up to incremental backup SCN.
D. Image copies of the
В‘/u01/app/oracle/ordata/orcl/users01.dbfВ’ data file are recovered using the above command if data file recovery fails.
Answer: C

Question
44
You have three temporary tablespace groups named G1, G2, and G3 in your
database.
You are creating a new temporary tablespace as
follows:
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE В‘/u1/data/temp1.dbfВ’ SIZE 10M
TABLESPACE GROUP В‘ В‘ ;
Which statement regarding the above
command is correct?
A. It will create the tablespace TEMP1 in group G1.
B. It will create the tablespace TEMP1 in group G3.
C. It will not add
the tablespace TEMP1 to any group.
D. It will create the tablespace TEMP1 in the default group.
E. It will throw an error with message В‘specified
group is not availableВ’.
F. It will create a new group with a system-generated name and add the tablespace
TEMP1 to it.
Answer: C
Note:
The
answer C is not exact, because the command not only "not add the tablespace TEMP1 to any group", but it will delete the TEMPORARY tablespace from
the his group, if it exists. Specify an empty string (' ') will remove tablespace from the tablespace_group_name tablespace group.

Question
45
Which entity enables Oracle Data Pump to restart a stopped job?
A. the SQL file
B. dynamic views
C. the master table
D. the export log
file
E. the repository table
F. the master control process
Answer: C
.
Question 46
You need to transport tablespaces between platforms with
different endians. Which
two steps are
required in addition to the regular steps needed for transporting tablespaces?
(Choose two.)
A. The
DB_FILE_NAME_CONVERT parameter must be set in the init.ora file.
B. The CONVERT command of SQL*Plus must be used to change byte ordering.
C. No
other explicit action is required.
D. The CONVERT command of the Recovery Manager utility must be used to change
byte ordering.
E. The COMPATIBLE
parameter must be set to 10.0.0 or higher in both the source
and the target databases.
Answer: D, E

Question 47
You used the following command
to perform backup:
RMAN> BACKUP AS COPY DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE;
Which recommendation regarding the above command is
true?
A. It is recommended that you do not use the above command in the RUN block.
B. It is recommended that you do not use the MINIMIZE LOAD
option with tape.
C. It is recommended that you use the MINIMIZE LOAD option only when you
perform tablespace backup.
D. It is recommended that
you use the DURATION and MINIMIZE LOAD option when
you perform backup to backup sets.
Answer: B
Note: The answer B is not exact.
MINIMIZE LOAD
option it's not possible with tape instead of "It is recommended that
you do not use the MINIMIZE LOAD option with tape. "
Your answer permit
to make the backup, if you want. The correct answer doesn't
permit to make the backup, because the MINIMIZE LOAD option is not available
when
writing to tape. (is compatible only with the hard disk)
.

Question 48
Identify the three predefined server-generated alerts. (Choose
three.)
A. Drop User
B. Privilege Grants
C. Tablespace Space Usage
D. Tablespace Reoganization
E. Resumable Session Suspended
F. Recovery Area
Low On Free Space
G. SYSTEM Tablespace Size Increment
Answer: C, E, F

Question 49
You modified the optimizer statistics of a table by using
the
DBMS_STATS.GATHER_TABLE_STATS
procedure. You realized that the newly generated statistics have created suboptimal
execution plans for that
particular table. What would you do to revert to the previous
set of statistics as soon as possible?
A. run Automatic Database Diagnostic Monitor
(ADDM) Advisor to recommend a solution
B. wait for the default automatic optimizer statistics generation
C. execute the
DBMS_STATS.RESTORE_TABLE_STATS procedure
D. execute the DBMS_STATS.RESTORE_DATABASE_STATS procedure
E. purge the existing table statistics and
execute the DBMS_STATS.GATHER_TABLE procedure
with a different set of parameters
Answer: C

Question 50
On which two database objects can the
VERSIONS clause of the Flashback Versions
Query be used? (Choose two.)
A. views
B. fixed tables
C. heap tables
D. external tables
E. temporary
tables
F. Index-Organized Tables (IOTs)
Answer: C, F
.
Question 51
You define the Warning threshold for the tablespace usage metric for the
USERS
tablespace to be 60% and the Critical threshold to be 80%. When space usage of the
USERS tablespace exceeds 60%, an alert is raised. Which
two are sources for
detailed alert information? (Choose two.)
A. the alert.log file
B. the V$ALERT_TYPES view
C. the DBA_ALERT_HISTORY view
D.
the Database Control Home page
E. the DBA_ALERT_ARGUMENTS view
F. the DBA_OUTSTANDING_ALERTS view
G. the Database Control Performance page
H.
the Database Control Administration page
Answer: D, F

Question 52
Which parameter avoids the overwriting of archived redo log files when you
recover
the database with the RESETLOGS option?
A. LOG_ARCHIVE_DEST
B. LOG_ARCHIVE_TRACE
C. LOG_ARCHIVE_FORMAT
D. LOG_ARCHIVE_CONFIG
E.
LOG_ARCHIVE_DEST_10
F. LOG_ARCHIVE_LOCAL_FIRST
Answer: C
.
Question 53
View the Exhibit and examine the highlighted alert.
Exhibit
#1

Exhibit #2

You have received an alert with the Category specified as Snapshot Too Old. Which
page would you open from the Advisor Central
page to determine the system
recommendations to avoid such alerts in future?
A. ADDM
B. Memory Advisor
C. Segment Advisor
D. Undo
Management
E. SQL Tuning Advisor
F. SQL Access Advisor
Answer: D
.

Question 54
You have 100 segments in the USERS tablespace. You realize
that the USERS
tablespace is running low on space. You can use Segment Advisor to _______.
A. add data files to the tablespace
B. identify the
segments that you should shrink
C. modify the storage parameters for the tablespace
D. automatically shrink the segments with unused space
E.
check the alerts generated for the table space that is running low on space
Answer: B

Question 55
You executed the following command on a
partitioned table sales_test:
ALTER TABLE sales_test
MOVE PARTITION s_q3 TABLESPACE users2
UPDATE INDEXES
(sales_test_ix
(PARTITION s_q3
TABLESPACE example) );
Which two statements regarding the above command care correct? (Choose two.)
A. The sales_test_ix index will be rebuilt
automatically.
B. The UPDATE INDEXES clause is optional in the above command.
C. The UPDATE INDEXES clause is used only with the MOVE operation on
a partition.
D. The UPDATE INDEXES clause without storage attributes will rebuild the index in
the SYSTEM tablespace.
Answer: A, B

Question
56
Which file gets created automatically while migrating from non-ASM files to ASM disk
group by using RMAN?
A. the control file
B. the alert
log file
C. the parameter file
D. the server parameter file
E. the archived redo log file
Answer: A
.
Question 57
While creating an Oracle
10g Release 1 (10.1) database, you set the COMPATIBLE
parameter to 10.0.0 Questionialization parameter file. Which two statements are true
in this
situation? (Choose two)
A. You can use all the new features in 10g.
B. You can change the setting of the COMPATIBLE parameter to 9.2.0.2.
C. You
can advance the compatibility of your database with the COMPATIBLE initialization
parameter.
D. You can assign any value to the COMPATIBLE
parameter, but the parameter would still take
the default values as 10.0.0.
Answer: A, C

Question 58
Which three statements regarding
collection of database usage metrics are correct?
(Choose three.)
A. The metrics survive database reboots and crashes.
B. The MMON process tracks
and records the database usage metrics.
C. The MMAN process tracks and records the database usage statistics.
D. The Oracle Enterprise Manager
(OEM) repository is used to store metrics.
E. The SMON process tracks and records the database usage metrics once a month.
F. The SYSAUX
tablespace contains table, where the database usage metrics are
physically stored.
G. The SYSTEM tablespace by default contains tables where the
database usage
metrics and physically stored.
Answer: A, B, F
.
Question 59
Exhibit

As shown in the diagram, in-memory statistics are
transferred to the disk at regular
intervals. Which background process performs this activity?
A. CJQ
B. CKPT
C. MMAN
D. SMON
E. PMON
F.
MMON
G. DBWR
Answer: F

Question 60
If the database is shown degraded performance, which three charts on the Database
Control Console
Performance page will help you resolve the problem? (Choose
three.)
A. overall availability chart
B. instance throughput chart
C. waiting and
working sessions chart
D. host paging and run queue information chart
E. undo generation rate and tablespace usage chart
Answer: B, C,
D
.
Question 61
Identify three key features of ASM. (Choose three.)
A. file striping
B. file mirroring
C. Segment Advisor
D. automatic file
creation
E. automatic disk rebalancing
F. automatic file size increment
G. automatic undo management
Answer: A, B, E

Question 62
Which two
statements regarding unusable indexes are correct? (Choose two.)
A. You can set SKIP_UNUSABLE_INDEXES to true to skip unusable indexes.
B. You can
check the alert log file to get a message recorded about unusable
indexes.
C. You do not need to set any parameter because unusable indexes are
skipped
automatically.
D. Your Data Definition Language (DDL) statement shows a warning message when
an index is made unusable.
Answer: A,
B

Question 63
You added the following policy:
EXEC DBMS_RLS.ADD_POLICY (object_schema => В‘scottВ’,
object_name => В‘empВ’,
policy_name =>
В‘hr_policyВ’,
fuction_schema => В‘scottВ’,
policy_function => В‘hrpolВ’,
policy_type => dbms_rls.static,
sec_relevant_cols =>
В‘sal,commВ’);
Which two statements regarding the above policy are correct? (Choose two.)
A. The policy predicate changes for each query.
B. The
policy function does not reexecute for each query.
C. Policy predicates are cached in the System Global Area (SGA).
D. The policy is useful where
every query requires a different predicate and fast
performance is not essential
Answer: B, C
.
Question 64
Consider an Oracle instance that is
using Automatic Shared Memory Management.
You set some of the System Global Area (SGA) parameters as shown below:
SGA_TARGET = 200
MB
SHARED_POOL_SIZE= 20 MB
DB_KEEP_CACHE_SIZE = 30 MB
Which two statements are correct in this scenario? (Choose two.)
A. Shared pool size will
never shrink below 20 MB.
B. A maximum of 20 MB will be allocated to the shared pool.
C. 230 MB will be allocated among the auto-tuned SGA
components.
D. 250 MB will be allocated among the auto-tuned SGA components.
E. If SGA_TARGET is reduced to 150 MB, then it will not affect the
value of the
DB_KEEP_CACHE_SIZE parameter.
Answer: A, ?
Note (uncertainty): . If you want to have two correct answers, you must add or
modify
one: The new correct answer must be: 170 MB (=200-30) will be
allocated among the auto-tuned SGA
components. This means that the total size of the
four auto-tuned components
(shared pool, java pool, default buffer cache, and large pool) is limited to 170MB.

Question 65
An online
tablespace, TEST_TBS, is full and you realize that no server-managed
tablespace threshold alerts were generated for the TEST_TBS tablespace. What
could
be the reason, if the TEST_TBS tablespace does not include autoextensible data
files?
A. TEST_TBS is a small file tablespace.
B. TEST_TBS
is a bigfile tablespace (BFT).
C. TEST_TBS is the default temporary tablespace.
D. TEST_TBS is the dictionary-managed tablespace.
E. Threshold
values for the TEST_TBS tablespace are not explicitly specified.
Answer: D
.
Question 66
You performed an incomplete recovery and opened the
database with the
RESETLOGS option. The LOG_ARCHIVE_FORMAT parameter is set to
В‘ora_%t_%s_%r.logВ’. Which statement regarding the archived redo
log files, created
in an earlier incarnation of the database, is true?
A. The archived redo log files cannot be used.
B. The archived redo log
files will be overwritten.
C. The archived redo log files are deleted automatically.
D. The achieved redo log files should be moved to some other
location.
E. The archived redo log files are still maintained because the file names are unique.
Answer: E
The parameter %r represents the
RESETLOGS identifier and changes every time a
RESETLOGS operation occurs, ensuring that no filename collisions exists in the flash
recovery area
or an archived log file destination.
.
Question 67
Exhibit

View the exhibit and examine the query output. You are logged in as user
SYSTEM.
You plan to relocate the Ultra Search occupant back to the SYSAUX tablespace from
the OCCU_TBS tablespace by using a SQL statement. Which
command would you
use?
A. EXEC WKSYS.MOVE_WK(В‘SYSAUXВ’);
B. EXEC SYS.MOVE_WK(В‘OCCU_TBSВ’);
C. EXEC WKSYS.MOVE_WK(В‘OCCU_TBSВ’);
D. EXEC
WKSYS.MOVE_WK(В‘ULTRASEARCHВ’,В’SYSAUXВ’);
E. EXEC WKSYS.MOVE_WK(В‘ULTRASEARCHВ’,В’OCCU_TBSВ’);
Answer: A
.
Question 68
You explicitly
specified the tablespace usage threshold values while creating a
tablespace. You need to revert the database-wide default tablespace usage
threshold
values for the tablespace.
Which PL/SQL package would you use?
A. DBMS_STATS
B. DBMS_ALERT
C. DBMS_SPACE
D. DBMS_MONITOR
E.
DMBS_SERVER_ALERT
F. DBMS_SPACE_ADMIN
Answer: E

Question 69
Which three statements regarding Automatic Database Diagnostic Monitor (ADDM)
are
correct?
(Choose three)
A. ADDM targets the tuning of individual user response times.
B. ADDM also documents the nonproblem areas of the
system.
C. ADDM analyzes online transaction processing (OLTP) systems only.
D. An ADDM analysis is performed each time a statspack snapshot is
taken.
E. Each ADDM finding has one or more recommendations associated with it.
F. ADDM analyzes the snapshots stored in Automatic Workload
Repository (AWR) on
a regular basis.
Answer: B, E, F

Question 70
You have 100 segments in the USERS tablespace. You get an alert that the
USERS
tablespace is running low on space. You decide to shrink some segments.
Which option would you use to determine which segment to shrink?
A.
Segment Advisor
B. SQL Tuning Advisor
C. SQL Access Advisor
D. Segment Resource Estimation
E. Automatic Database Diagnostic Monitor
(ADDM)
Answer: A
.
Question 71
You want to perform the database backup when user activity on your system is low,
such as between 12:00 a.m. and
2:00 a.m. Which command terminates with an
error of the backup is not complete at the end of the specified duration?
A. RMAN> BACKUP DURATION 2:00
MINIMIZE LOAD DATABASE;
B. RMAN> BACKUP DURATION 2:00 PARTIAL FILESPERSET 1 DATABASE;
C. RMAN> BACKUP DURATION 2:00 PARTIAL MINIMIZE TIME
DATABASE;
D. RMAN> BACKUP AS COPY DURATION 2:00 PARTIAL MINIMIZE LOAD DATABASE;
Answer: A

Question 72
Which three statements regarding
compressed backups are correct? (Choose three)
A. The compression applies to only image copies.
B. The compressed backup is applicable to only
data files.
C. The COMPATIBLE parameter must be set to 10.0.0 or higher.
D. The compressed backup is applicable to only the entire database
backup.
E. Restoration from a compressed backup does not require any special action.
F. The compression provided by media manager and the RMAN
backup compression
should not be used together.
Answer: C, E, F

Question 73
Which two initialization parameters would you set to enable
Automatic Shared
Memory Management? (Choose two)
A. set LOG_BUFFER to zero
B. set SHARED_POOL_SIZE to zero
C. set DB_CACHE_SIZE to a non-zero
value
D. set STATISTICS_LEVEL to BASIC
E. set SGA_TARGET to a non-zero value
F. set STATISTICS_LEVEL to TYPICAL or ALL
Answer: E,
F
.

Question 74
Which three statements regarding the bigfile tablespace (BFT) are correct? (Choose
three)
A. BFT can be dictionary
managed.
B. A BFT always contains a single file.
C. BFT is supported for locally managed tablespaces.
D. In BFT, the maximum file size ranges
from 8 TB to 128 TB.
E. BFT, when uses with Oracle Managed Files (OMF), provides complete data file
transparency.
Answer: B, C, D

D: When the
tablespace block size is 2k, then the maximum tablespace size will be 8
TB. When the tablespace block size is 32k, then the maximum tablespace size
will be
128 TB.

Question 75
You need to determine how often a particular database feature is used in the
database.
Which two methods would
you use to do this? (Choose two)
A. User alert log file
B. Use Database Option Advisor
C. Use the DBMS_STATS package
D. Use the
DBMS_RESOURCE_MANAGER package
E. Run a query on DBA_FEATURE_USAGE_STATISTICS
F. Open the Database Usage Statistics page of Enterprise Manager
(EM)
Answer: E, F

Question 76
You decide to define the Flash Recovery Area manually.
What are the two mandatory initialization parameters you
need to set? (Choose two)
A. LOG_ARCHIVE_DEST_10
B. DB_CREATE_FILE_DEST
C. RECOVERY_PARALLELISM
D. DB_RECOVERY_FILE_DEST
E.
DB_RECOVERY_FILE_DEST_SIZE
F. DB_FLASHBACK_RETENTION_TARGET
Answer: D, E
.
Question 77
Examine the following commands of Data Pump to import
objects to new nonexisting
users hr1 and oe1.
$ expdp system/manager
schemas = hr,oe
directory = EXP_DIR
dumpfile = export.dat
include =
table
$ impdp system/manager
schemas = hr1,oe1
directory = EXP_DIR
dumpfile = export.dat
remap_schema = hr:hr1, oe:oe1
Which objective would
be achieved by the above command set?
A. expdp will fail because no path has been defined for the dumpfile.
B. Expdp will success but Impdp will
fail because users do no exist.
C. impdp would create two schemas called hr1 and oe1 and copy the schema objects
to the new users.
D. Impdp would
create two schemas called hr1 and oe1 and import tables owned by
hr and oe schemas to hr1 and oe1 schemas, respectively.
Answer: B

Question
78
Character large objects (CLOB) data is represented in Oracle database 10g as the
___- character set.
A. UCS2
B. US7ASCII
C. AL16UTF16
D.
WE9IS08859P1
E. D7SIEMENS9780X
F. Same characters set as the database character set.
Answer: C
.
Question 79
Which process performs the
rebalance data extent movements across ASM disk
groups in ASM?
A. Checkpoint (CKPT)
B. System Monitor (SMON)
C. ASM Rebalance (ARBn)
D. Process
Monitor (PMON)
E. ASM Background (ASMB)
F. ASM Rebalance Master (RBAL)
Answer: F
There are at least two new background processes added for an
ASM instance:
- RBAL - coordinates rebalance activity for disk groups
- ORB0, ORB1В… - These perform the actual rebalance data extent movements.
There
can be many of these at
a time.
Any database instance that is using an ASM disk group will contain a background
process called OSMB. The
OSMB process is responsible for communicating with the
ASM instance. A second additional background process, called RBAL (just like in the
ASM
Instance) performs a global open on ASM disks. A global open means that more
than one database instance can be accessing the ASM disks at a
time.
Not C: The ARBn process does not exist in oracle 10g

Question 80
You have a partitioned table called employee in USERS1 tablespace. You
executed
the following
statement on the partition emp_d3 of the table:
ALTER TABLE employee MOVE PARTITION emp_d3 TABLESPACE USERS2;
Which two
statements regarding the local index partition on the emp_d3 partition are
correct? (Choose two)
A. The local index partition cannot be
rebuilt.
B. The local index partition remains USABLE.
C. The local index partition becomes UNUSABLE.
D. The local index partition must be dropped
and re-created manually.
E. The local index partition gets dropped automatically when you move a partition of
the table.
F. The local index
partition will be rebuilt automatically of you use the UPDATE
INDEX clause in the above command.
Answer: C, F
.
Question 81
You want to unload
data from the ORDERS, ORDER_ITEMS, and PRODUCTS database
tables to four os flat files by using the External Table Population methods. To
achieve
this objective, you execute the following command:
Create TABLE orders_ext
(order_id, order_date, product_id,
product_name,quantity)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_dir
LOCATION
(В‘orders1.dmpВ’,В’orders2.dmpВ’,В’orders3.dmpВ’,В’
orders4.dmpВ’)
]
PARALLEL
AS
SELECT
o.order_id,o.order_date,p.product_id,p.product_name,i.quanity
FROM orders o,products p,order_items i
WHERE o.order_id = i.order_id and
i.product_id = p.product_id;
After successful execution of the command, you find that only two files have been
created and not four. Which option
do you need to change to achieve your objective?
A. TYPE
B. LOCATION
C. PARALLEL
D. DEFAULT DIRECTORY
E. ORGANIZATION EXTERNAL
Answer:
C

Question 82
Which two statements are correct regarding renaming a tablespace? (Choose two)
A. You cannot rename a dictionary-managed
tablespace.
B. When you rename a tablespace, all the data files are also renamed accordingly.
C. You cannot rename a tablespace that happens to be
the default permanent
tablespace of a user.
D. You can rename a tablespace provided that COMPATIBILITY initialization
parameter is set to at
least 10.0.0.0.0.
E. When you rename a tablespace, the Oracle 10g database updates all references to
the tablespace name in the data dictionary as
well as the control file.
Answer: D, E
.
Question 83
You want to have a compatible audit trail of everything that has happened to the
jobs in
the job class named JOB_CLASSA. What should the logging level be for
JOB_CLASSA?
A. DBMS_SCHEDULER.ENABLE
B. DBMS_SCHEDULER.LOGGING_OFF
C.
DBMS_SCHEDULER.LOGGING_RUNS
D. DBMS_SCHEDULER.LOGGING_FULL
E. DBMS_SCHEDULER.LOGGING_COMPLETE
Answer: D

Question 84
The application tables
owned by the user TEST in a test database need to be exported to the APPS schema in the production database by using Data Pump. Which option of Data
Pump Import would you use to accomplish this?
A. owner
B. touser
C. attach
D. fromuser
E. remap_schema
Answer: E

Question 85
While
observing database statistics, you found that the Automatic Workload Repository (AWR) snapshots are being purged after each day. You want the
snapshots to be purged on a weekly basis. Which two options would you use to increase the purge interval?
(Choose two)
A. Enterprise Manager
B.
DBMS_JOB.INTERVAL
C. DBMS_SCHEDULER.CHANGE
D. DBMS_SCHEDULER.INTERVAL
E. DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
Answer: A,
E
.
Question 86
Consider the following configuration:
/devices/D1 is a member of disk group dgroupA.
/devices/D2 is a member of disk group
dgroupA.
/devices/D3 is a member of disk group dgroupA.
You plan to add a new disk, /devices/D4, to the disk group dgroupA and execute
the
following
command:
SQL> ALTER DISKGROUP dgroupA ADD DISK В‘/devices/D4В’;
Which task would be accomplished by the command?
A. The command
adds the new disk, D4 to the disk group.
B. The command would result in an error because there is no disk by the name
В“/devices/D*В”.
C. The
command would result in an error because no wildcard characters can be
used in the disk name.
D. The command will be ignored because disks
starting with В“DВ” are already
members of the disk group.
E. The command first detaches all the member disks starting with В“DВ” and
then
reattached all of them including the new disk.
Answer: A

Question 87
Which task would you perform to convert little-endian CLOB data to
the big endian
format while transporting from Oracle9i database to Oracle 10g database?
A. Use the CHANGE command of the RMAN utility.
B. Use the
CONVERT command of the RMAN utility.
C. Transport the data after changing the character set of the target database to big
endian.
D. Transport
the data after changing the character set to be an endian-independent
format in the target database.
E. No action is required because Oracle RDBMS
implicitly does the conversion in
Oracle 10g database while accessing the data.
Answer: E
.
Question 88
The system was running a normal
workload during the last 24 hours. You need to
ensure that with the same workload you should be able to flashbash tables by three
hours. What
would you do to find the system recommendations for the undo
retention period and the undo tablespace size?
A. Set
DB_FLASHBACK_RETENTION_TARGET=10800
B. Create a new scheduler window for the time period of 24 hours and use the
optimizer statistics.
C. Specify
Flashback Retention Time as three hours in the MTTR Advisor page and
use its recommendations.
D. Modify the Automatic Workload Repository (AWR)
snapshot time interval to three
hours and use ADDM findings.
E. Specify New Undo Retention as three hours and Analysis Time period as Last
One
Day in the Undo Advisor page and use its recommendations.
Answer: E

Question 89
To which three elements is the size of the block change
tracking file proportional?
(Choose three)
A. Number of redo log groups.
B. Number of DBWR process.
C. Size of the database in bytes.
D. Number
of archiving destinations.
E. Number of old backups maintained by the block change tracking file.
F. Number of enabled threads in Real Application
Cluster (RAC) environment.
Answer: C, E, F

Question 90
You executed the following SQL statement to shrink the EMPLOYEES table segment
stored
in the EXAMPLE tablespace: ALTER TABLE employees SHRINK SPACE CASCADE;
Which statement is correct in this scenario?
A. The data in the segment
will be compacted but the high water mark will not be adjusted.
B. The EMPLOYEES tablespace will be changed to read-only mode during the shrink
operation.
C. The indexes created on the EMPLOYEES table will need to be rebuilt after the shrinkoperation is over.
D. The shrink behavior will be
cascaded to all dependent segments of the table that support a shrink operation.
E. Data manipulation language (DML) operations will not be
possible on the EMPLOYEE table during the COMPACTION phase of the shrink operation.
Answer: D
.
Question 91
Exhibit:

View the Exhibit and
examine the Data Pump architecture.
Identify the numbered components.
A. 1 В– Oracle Loader, 2 В– Oracle Data Pump, 3 В– Direct Path API
B. 1 В–
Oracle Data Pump, 2 В– Direct Path API, 3 В– Oracle Loader
C. 1 В– Direct Path API, 2 В– Oracle Loader, 3 В– Oracle Data Pump
D. 1 В– Oracle
Loader, 2 В– Direct Path API, 3 В– Oracle Data Pump
E. 1 В– Oracle Data Pump, 2 В– Oracle Loader, 3 В– Direct Path API
Answer: A

Question
92
How frequently does the Automatic Database Diagnostic Monitor (ADDM) analysis
run by default?
A. Every 30 minutes
B. Every 45 minutes
C.
Every 15 minutes
D. Every 60 minutes
E. Each time a statspack snapshot is taken
Answer: D
.
Question 93
Which three statements regarding
resumable statements are correct? (Choose three)
A. Resumable statements cannot contain a distributed transaction.
B. Resumable statements can
only be enabled at the system level.
C. Resumable statements can be enabled at the system level as well as the session level.
D.
RESUMABLE_TIMEOUT=0 disabled resumable space allocation for all sessions.
E. The ALTER SESSION ENABLE RESUMABLE command is obsolete in Oracle
Database 10g.
F. Only the local instance is affected when changing the resumable timeout parameter in a
distribution transaction.
Answer: C, D,
F
C : At the system level:
ALTER SYSTEM SET RESUMABLE_TIMEOUT = 72000;
At the session level:
ALTER SESSION SET RESUMABLE_TIMEOUT = 72000;
E.
ALTER SESSION ENABLE RESUMABLE; is still supported in Oracle 10g

Question 94
Which two sources may be used for a running session by SQL Tuning
Advisor (STA)?
(Choose two)
A. SQL Profiles
B. Recovery Manager (RMAN) Repository
C. Automatic Workload Repository (AWR)
D. Automatic Database
Diagnostic Monitor (ADDM)
Answer: A, C

Question 95
Immediately after adding a new disk to or removing an existing disk from an ASM
instance,
you find that the performance of the database goes down initially until the
time the addition or removal process is completed, and then gradually
becomes
normal. Which two activities would you perform to maintain a consistent
performance of the database while adding or removing disks?
(Choose two)
A. Increase the number of checkpoint processes.
B. Define the POWER option while adding or removing the disks.
C. Increase the
number of ARB processes by setting up a higher value for
ASM_POWER_LIMIT.
D. Increase the number of DBWR processes by setting up a higher value
for
DB_WRITER_PROCSSES.
E. Increase the number of slave database writer processes by setting up a higher
value for DBWR_IO_SLAVES.
Answer: B,
C
.
Question 96
Which four statements regarding the block change track file are correct? (Choose four)
A. The minimum size of this file is 10
MB.
B. The maintenance of this file is fully automatic.
C. The changed blocks are tracked in this file as redo is generated.
D. The location of
this file must be different from that of the database files.
E. The Oracle database records block change information in this file by default.
F.
The V$BLOCK_CHANGE_TRACKING view contains the name and location of this
file.
Answer: A, B, C, F

Question 97
You define the Warning threshold
for the tablespace usage metric for the USERS
tablespace to be 35%. Because of this, several alerts are being raised. After
observing this, you
decide to increase the Warning threshold and clear all old alerts
related to tablespace usage metric. Where would you fund the information
about
cleared alerts?
A. The alert.log file
B. The DBA_ALERT_HISTORY data dictionary table
C. The DBA_ALERT_ARGUMENTS data dictionary table
D.
The DBA_TAB_STATS_HISTORY data dictionary table
E. The DBA_OUTSTANDING_ALERTS data dictionary table
F. The V$ACTIVE_SESSION_HISTORY data
dictionary table
Answer: B

Question 98
Which data dictionary view would you query to find the name of the default
permanent tablespace of the
database?
A. DICTIONARY
B. DBA_TABLESPACES
C. DBA_DIRECTORIES
D. DBA_STORED_SETTINGS
E. DATABASE_PROPERTIES
F. DBA_TABLESPACE_GROUPS
Answer:
E
.
Question 99
Which three statements regarding the properties of a temporary tablespace group
are correct? (Choose three)
A. It is created
explicitly.
B. It has the same namespace as tablespaces.
C. It contains at least one temporary tablespace.
D. It can contain a maximum of 1024
tablespaces in it.
E. It has to be removed manually after the last temporary tablespace is removed.
F. It is created automatically when the first
temporary tablespace is assigned to it.
Answer: B, C, F

Question 100
The SQL Access Advisor may recommend additional structures to improve
the
performance of SQL statements. You can select the type of structure recommended
by the SQL Access Advisor. Which two structures can you
select? (Choose two)
A. Indexes
B. Partitions
C. Synonyms
D. Sequences
E. Database links
F. Materialized views
G. Hash or index clusters
H.
Index-Organized Table
Answer: A, F

Question 101
Sam is a DBA of an OLTP system that has a large number of users. He receives a
complaint from
two users with client ids Richard and Hardy that the Application
response time is poor. To understand the problem faced by Richard and
Hardy
better, Sam wants to gather the statistics for all active sessions of these clients for a
particular instance. Which two of these steps
should Sam perform and in which order
to view the relevant information?
1. Execute DBMS_MONITOR.CLIENT_ID_STAT_ENABLE procedure for the two
clients.
2. Execute DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE procedure for the two
clients.
3. View the data in the V$CLIENT_STATS view.
4. View the
data in the V$SERVICE_STATS view.
A. 1, 3
B. 1, 4
C. 2, 3
D. 2, 4
Answer: C
.
We can turn on tracing for the client using
dbms_monitor.client_id_trace_enable:
SQL> exec dbms_monitor.client_id_trace_enable(client_id=>'richard');

Question 102
You are using the
Automatic Shared Memory Management configuration.
Which four initialization parameters will be tuned automatically? (Choose four)
A.
LOG_BUFFER
B. DB_CACHE_SIZE
C. JAVA_POOL_SIZE
D. LARGE_POOL_SIZE
E. STREAMS_POOL_SIZE
F. DB_Nk_CACHE_SIZE
G. SHARED_POOL_SIZE
H.
DB_KEEP_CACHE_SIZE
I. DB_RECYCLE_CACHE_SIZE
Answer: B, C, D, G
ASMM will tune automatically Db_cache_size, Shared_pool_size, large_pool_size
and
Java_pool_size

Question 103
In which three cases should you collect statistics manually? (Choose three)
A. For large objects (LOBs)
B.
For fixed objects
C. For external tables
D. For undo tablespaces
E. For schema statistics
F. For tables that are bulk loaded
Answer: B, C,
F
You must gather statistics manually for the following situations:
1. When a table is loaded using bulk operation
2. When using external
tables
3. To collect system statistics
To collect statistics on fixed objects.
.
Question 104
In an online transaction processing (OLTP)
environment, you find that the
transaction tables get heavily fragmented during the week. You decide to defragment
the transaction tables on every
Friday at 9:30 p.m. to gain performance. Which two
new features of the Oracle 10g database could you use to automate this task?
(Choose two)
A.
The DBMS_AQ package
B. The DBMS_JOB package
C. The OS level job automation tool
D. The DBMS_SCHEDULER package
E. Enterprise Manager job
scheduling
Answer: B, D
Oracle 10g's new DBMS_SCHEDULER package offers significant improvements over
DBMS_JOB for scheduling jobs and
tasks.
Here is an example of scheduling with DBMS_SCHEDULER. Note that the syntax is
now much clearer, and the parameters even now make sense when
compared to
DBMS_JOB. And gone at last is that wacky
INTERVAL parameter!
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name =>
'HR_STATS_REFRESH'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(''HR'');END;'
,start_date =>
'09/01/2004 09:30 PM'
,repeat_interval => 'WEEKLY BYDAY=5'
,enabled => TRUE
,comments => 'Refreshes the HR Schema every Friday at 9.30
PM'
);
END;
.

Question 105
You have the following requirements with respect to optimizer statistics:
The statistics should be generated
automatically during weekdays (Monday to Friday)
between 10:00 p.m. and 7:00 a.m. The objects that need updated statistics the
most should be
processed first. What should you do?
A. Modify the AUTO_TASKS_JOB_CLASS Scheduler class.
B. Use the DBMS_STATS.GATHER_DATABASE_STATS procedure
with the GATHER AUTO
option.
C. Modify the duration of the default WEEKNIGHT_WINDOW scheduler window to 10:00 pm.7:00
a.m.
D. Not make any
medications, because by default the WEEKNIGHT_WINDOW scheduler window
automatically generates statistics during weekdays between 10:00 p.m. and
7:00 a.m.
Answer: C

Question 106
You are working on a database that was created using Oracle Database 10g, the
default tablespace type
property was not changed. You execute the following
statement to create a tablespace, mytbs: CREATE TABLESAPCE mythbs
DATAFILE
В‘/u1/data/mytbs.dbfВ’ SIZE 100M;
Which two statements are correct regarding the mytbs tablespace? (Choose two)
A. It is a smallfile
tablesapce.
B. It is a bigfile tablespace (BFT).
C. It is a default bigfile tablespace.
D. It is a locally managed tablespace.
E. It is a
dictionary-managed tablespace.
Answer: C, D

Question 107
You executed the following command to back up the control file:
ALTER DATABASE BACKUP
CONTROLFILE TO TRACE;
What do you find in the trace file?
A. Image of the control file
B. Location of the control file
C. Contents of the
control file in text format
D. SQL command to re-create the database
E. SQL command to re-create the control file
F. Contents of the control file
in binary format
Answer: E
.

Question 108
Your boss at braindumps.com wants you to clarify Oracle 10g. Which two steps are performed the first
time any UPDATE statement is issued after the instance is started? Choose two
A. Creating the parse tree of the statement
B. Writing the modified
data blocks to the data files
C. Writing the modified data to the archived redo log files
D. Updating the control file to indicate the most recent
checkpoint
E. Updating the data file header to indicate the most recent checkpoint
F. Reading the blocks to database buffer cache if they are not
already there
Answer: C, D

Question 109
You work as a database administrator for braindumps.com. You have been asked to use a centralized
administrative tool to administer your database servers and application servers. In order to achieve the objective, which component would you
configure on each database server?
A. Database Control
B. Management Server
C. Management Repository
D. Application Server Control
E. Oracle
Management Agent
Answer: E

Question 110
The data file belonging to the SYSTEM tablespace is corrupted and no backup of the file is
available.
How do you recover the data file?
A. The data file cannot be recovered.
B. The date file can be restored from the SYSTEM auto
backup.
C. Take the tablespace offline, drop,k and re-create it.
D. Recovery Manager (RMAN) is used to recover the data file.
E. The ALTER
DATABSE CREATE DATAFILE.. command is used to recover the data file.
Answer: A
.
Question 111
You work as a database administrator for
braindumps.com. You started the instance using the init.ora file. You have two control files and three redo log groups in your database. You decided
to protect the database against failures by adding one more control file.
Which file is the appropriate way to perform this task?
A. Shut down the
instance, copy the control file to the third location and open the database.
B. Abort the instance, copy the control file to the third location,
modify the CONTROL_FILES parameter in the init.ora file and open the database.
C. Copy the control file to the third location, modify the
CONTROL_FILES parameter in the init.ora file, restart the instance and open the database.
D. Shut down the instance, copy the control file to the
third location, modify the
CONTROL_FILES parameter in the init.ora file and open the database.
E. Shut down the instance, startup in the mount
state, copy the control file to the third location, modify the CONTROL_FILES parameter in init.ora and open the database.
Answer: D

Question
112
You work as a database administrator for braindumps.com. You find that the database performance degrades while you backup the REALEXAMS
database using Recovery Manager (RMAN). The REALEXAMS 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.
Answer: E

Question 113
You work as a
database administrator for braindumps.com. You decided to manage client and server connections using Local Naming method in braindumps.com. When you
try to connect to the database you get the following error:
ERROR:
ORA-12154: TNS:could not resolve service name
Which network configuration
files would you look into to resolve this error? Choose two.
A. snmp.ora
B. names.ora
C. spfile.ora
D. sqlnet.ora
E. listener.ora
F.
tnsnames.ora
.
Answer: D, F

Question 114
You work as a database administrator for braindumps.com. In your production database, the size of
Database Buffer Cache needs to be increased immediately for the current as well as future instances of the database. The Oracle instance has been
configured to accommodate any changes in the size of the memory structures.
At this is production database, you want to accomplish this task with
no impact on the userВ’s connections.
Which activity must you have completed before accomplishing this task?
A. You must have started the database
instance in restricted mode.
B. You must have started the database instance in NORMAL mode.
C. You must have started the database instance with
server parameter file.
D. You must have started the database instance but must not have mounted.
E. You must have started the database instance
but must not have opened.
Answer: C