I read The Blue heaven 1z0-040 exam questions and found some errors in answers on them.
May be my
redaction will be more usefull for OCP upgraters...

---

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:

While the simplest way to run an ADDM analysis over a specific time period is with the Oracle Enterprise Manager GUI, ADDM can
also be run manually using the $ORACLE_HOME/rdbms/admin/addmrpt.sql script and DBMS_ADVISOR package APIs.

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
Explanation:

Oracle recovery components interact with the flash recovery area ensuring that the
database is completely recoverable using files in flash recovery area. All files necessary to recover the database following a media failure are
part of flash recovery area. Following is a list of recovery-related files in flash recovery area:
В• Current control file
В• Online
logs
В• Archived logs
В• Flashback logs
В• Control file autobackups
В• Control file copies
В• Datafile
copies
В• Backup pieces

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
Explanation:

Recover database from last full backup, which has been taken at April 15. And
apply all of the archived log until time April 22, 7:59 a.m.

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
Explanation:

DBMS_RLS.ADD_POLICY Policy Types
Policy Type Description
STATIC Predicate is assumed to be the same regardless of the runtime environment.
Static policy functions are executed once and then cached in SGA. Statements accessing the same object do not re-execute the policy function.
However, each execution of the same cursor could produce a different row set even for the same predicate because the predicate may filter the data
differently based on attributes such as SYS_CONTEXT or SYSDATE. Applies to only one object.
SHARED_STATIC Same as STATIC except that the
server first looks for a cached predicate generated by the same policy function of the same policy type. Shared across multiple
objects.
CONTEXT_SENSITIVE Server re-evaluates the policy function at statement execution time if it detects context changes since the last use
of the cursor. For session pooling where multiple clients share a database session, the middle tier must reset context during client switches. Note
that the server does not cache the value returned by the function for this policy type; it always executes the policy function on statement parsing.
Applies to only one object.
SHARED_CONTEXT_SENSITIVE Same as CONTEXT_SENSITIVE except that the server first looks for a cached predicate
generated by the same policy function of the same policy type within the same database session. If the predicate is found in the session memory, the
policy function is not re-executed and the cached value is valid until session private application context changes occur. Shared across multiple
objects.
DYNAMIC The default policy type. Server assumes the predicate may be affected by any system or session environment at any time, and
so always re-executes the policy function on each statement parsing or execution. Applies to only one object.


Question 9
The Automatic
Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes on your database. Your database is 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
Explanation:
MODIFY_SNAPSHOT_SETTINGS - This AWR procedure controls two aspects of snapshot generation. The INTERVAL
setting affects how often snapshots are automatically captured while the RETENTION setting affects how long snapshots are retained in the Workload
Repository.


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: B (answer A in original)
Explanation:
The
requirements for enabling Flashback Database are:
1) Your database must be running in ARCHIVELOG mode, because archived logs are used in the
Flashback Database operation.
2) You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery
area.

Question 11
Consider the following command to add a new disk group called В“tdgroupAВ” with two failover groups:
CREATE DISKGROUP
tdgrouopA NORMAL 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
Explanation:


REDUNDANCY Clause - The REDUNDANCY clause lets you specify the redundancy level of the disk group.
FAILGROUP Clause -
Use this clause to specify a name for one or more failure groups.
You cannot use this clause to change the failure group of a disk. Instead you
must drop the disk from the disk group and then add the disk back into the disk group as part of the new failure group.
If qualified_disk_clause
points to one or more disks that have already been assigned to a different disk group, then Oracle Database returns an error unless you also specify
FORCE.



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: C
Explanation:
Because the
audit_column_opts => DBMS_FGA.ALL_COLUMNS,
audit record is created for all INSERT and UPDATE statements on both the ACCOUNT_ID and AMOUNT columns.

Parameter Description
object_schema The schema of the object to be audited.
Default value: NULL. (If NULL, the current effective user schema is assumed.)
object_name The name of the object to be
audited.
policy_name The unique name of the policy.
audit_condition A condition in a row that indicates a monitoring condition. NULL is allowed
and acts as TRUE. Default value: NULL
audit_column The columns to be checked for access. These can include hidden columns. The default, NULL,
causes audit if any column is accessed or affected. Default value: NULL
handler_schema The schema that
contains the event handler. The default, NULL, causes the current schema to be used.
Default value: NULL
handler_module The function name of the event handler; includes the package name if necessary. This function is invoked
only after the first row that matches the audit condition is processed in the query. If the procedure fails with exception, the user SQL statement
will fail as well. Default value: NULL
enable Enables the policy if TRUE, which is the default.
Default value: TRUE
statement_types The SQL statement types to which this policy is applicable: insert, update, delete, or select only.
Default value: SELECT
audit_trail Whether to populate LSQLTEXT and LSQLBIND in fga_log$. Default value:
DB_EXTENDED
audit_column_opts Establishes whether a statement is audited when the query references any column specified in the audit_column
parameter or only when all such columns are referenced. Default value: ANY_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
Explanation:

-SQL_TRACE, FAST_START_MTTR_TARGET, LOG_CHECKPOINT_INTERVAL В– no commentВ…-:))
-
DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may be flashed back.
- The database must
have been put in FLASHBACK mode with an ALTER DATABASE FLASHBACK ON statement. This statement provides a fast alternative to performing incomplete
database recovery.
But users should be able to use FLASHBACK QUERY FEATURES, not to use FLASHBACK DATABASE.
Then weВ’ll use:
-UNDO_RETENTION -
Retention is specified in units of seconds. This parameter determines the low threshold value of undo retention. The effect of the UNDO_RETENTION
parameter is immediate, but it can only be honored if the current undo tablespace has enough space. You enable the guarantee option by specifying
the RETENTION GUARANTEE clause for the undo tablespace


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
Explanation:
Before
upgrading to the new Oracle Database 10g release, the COMPATIBLE initialization parameter must be set to 9.2.0, which is the lowest possible setting
for Oracle Database 10g release 10.1. Only a subset of Oracle Database 10g features are available while the COMPATIBLE initialization parameter is
set to this value. After upgrading to the new Oracle Database 10g release, you can set the COMPATIBLE initialization parameter to match the release
number of the new release. Doing so enables you to use all of the features of the new release, but prevents you from downgrading back to your
previous release.
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 own compression.
D. You are using disk-based backups and disk space
in your Flash Recovery Area, or other disk-based 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
Explanation:

BFILE is externally located data, then compression donВ’t
need them.
With the COMPRESSED option, binary compression is used. The data written into the backup set is compressed to reduce the overall size of
the backup set. There is some CPU overhead associated with compressing backup sets. If the database being backed up is running at or near its
maximum load, you may find the overhead from using AS COMPRESSED BACKUPSET unacceptable. In most other circumstances, compressing backup-sets saves
enough disk space to be worth the CPU overhead. Oracle Corporation recommends that you use RMAN's integrated binary compression instead of external
compression utilities when you need to make compressed backups. When backing up to a locally attached tape device, compression provided by the media
management vendor is usually preferable to the binary compression integraed into the database, so use uncompressed backup sets and turn on the
compression provided by the media management vendor. In any case, you should not use both forms of compression together.

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 ending 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
Explanation:
V$DATABASE displays information about the database from the control file. Some of columns
are interesting for us:
Column Datatype Description
DBID NUMBER Database identifier calculated when the database is created and
stored in all file headers
NAME VARCHAR2(9) Name of the database
PLATFORM_ID NUMBER Platform identification number of the
database
PLATFORM_NAME VARCHAR2(101) Platform name of the database
V$TRANSPORTABLE_PLATFORM displays all platform names and their
identifiers:
Column Datatype Description
PLATFORM_ID NUMBER Platform identification
number
PLATFORM_NAME VARCHAR2(101) Platform name
ENDIAN_FORMAT VARCHAR2(14) Platform endian format:
-Big
-Little
-UNKNOWN
FORMAT

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: A (original is C)
Explanation:
PURGE_LOG Procedure Parameters

Parameter Description
log_history This specifies how much history (in days) to keep. The valid range is 0 - 999. If set to 0, no
history is kept.
which_log This specifies which type of log. Valid values for which_log are job_log, window_log, and
job_and_window_log.
job_name This specifies which job-specific entries must be purged from the jog log. This can be a comma-delimited list of job
names and job classes. Whenever job_name has a value other than NULL, the which_log argument implicitly includes the job log.

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
Explanation:

The requirements for enabling Flashback Database are:
1) Your database must be running in
ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
2) You must have a flash recovery area enabled, because
flashback logs can only be stored in the flash recovery area.

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,D,E,F
(Original is A, C, D, F)
Explanation:

You can also use Enterprise Manager to clone Oracle database instances. For example, after you have
configured an instance, tuned it, and tested it, you may want to clone that database instance. Enterprise Manager provides a Clone Database tool
that steps you through the Oracle database clone operation.
The Clone Database tool clones a database instance to an existing Oracle home. If you
want to create a new Oracle home to clone the instance to, use the Clone Oracle Home tool to create a new Oracle home, then use the Clone Database
tool to clone the instance to that home.
Because the Clone Database tool use the RMAN for database cloning, there are no different in state of
database В– NOMOUNT, MOUNT or OPEN.


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
Explanation:

DBMS_ADVISOR.TUNE_MVIEW (task_name IN OUT VARCHAR2, mv_create_stmt IN [CLOB | VARCHAR2])
The
TUNE_MVIEW procedure can generate the output statements that correct the defining query by adding extra columns such as required aggregate columns
or fix the materialized view logs to achieve the FAST REFRESH goal.
When the ENABLE QUERY REWRITE clause is specified, TUNE_MVIEW will also fix the
statement using a process similar to REFRESH FAST, that will redefine the materialized view so that as many of the advanced forms of query rewrite
are possible.

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
Explanation:

The SQL Access Advisor helps define
appropriate access structures such as indexes and materialized views to optimize SQL queries. It takes a SQL workload as an input and recommends
which indexes, materialized views, or logs to create, drop, or retain for faster performance.

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
Explanation:


repeat_interval = frequency_clause [В…..] [";" byday_clause] [В…В…]

frequency_clause = "FREQ" "=" frequency
frequency = "YEARLY"
| "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY"
byday_clause = "BYDAY" "=" byday_list
byday_list = byday
( "," byday)*
byday = [weekdaynum] day
weekdaynum = [minus] daynum
daynum = 1 through 53 /* if frequency is yearly */
daynum = 1
through 5 /* if frequency is monthly */
day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN"

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,D,F (original is B, E, F)
Explanation:

Data Pump Export is a utility for unloading data and
metadata into a set of operating system files called a dump file set. Because Data Pump is server-based, rather than client-based, dump files, log
files, and SQL files are accessed relative to server-based directory paths. Data Pump requires you to specify directory paths as directory objects.
A directory object maps a name to a directory path on the file system.

Question 26
You want an ASM (Automatic Storage Management) 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
DB_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
Explanation:

The Automatic Storage Management instance is a special Oracle instance that manages the disks in
disk groups. There is no need to specify and manage filenames. Wherever a file is created, a disk group can be specified instead. Every new file
automatically gets a new unique name. This prevents using the same filename in two different databases. Disk group naming avoids using two different
names for the same file. LOG_ARCHIVE_FORMAT is ignored if a disk group is specified for LOG_ARCHIVE_DEST (for example, LOG_ARCHIVE_DEST =
+dgroup1).

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 statistic 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
Explanation:

SERV_MOD_ACT_STAT_ENABLE Procedure
Parameters
Parameter Description
service_name Name of the service for which statistic aggregation is enabled.
module_name Name of the
MODULE. An additional qualifier for the service. It is a required parameter.
action_name Name of the ACTION. An additional qualifier for the
Service and MODULE name. Omitting the parameter (or supplying ALL_ACTIONS constant) means enabling aggregation for all Actions for a given
Server/Module combination. In this case, statistics are aggregated on the module level.

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.
CREATE TABLE employee_ext
(employee_id, first_name,
department_name)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_dir
LOCATION (В‘emp1.dmpВ’))
PARALLEL
AS
SELECT
e.emplyee_id, e.first_name, e.last_name, d.department_name
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
Explanation:

TYPE access_driver_type indicates the access driver of the external table. The access driver is the
API that interprets the external data for the database. Oracle Database provides two access drivers: ORACLE_LOADER and ORACLE_DATAPUMP. If you do
not specify TYPE, then the database uses ORACLE_LOADER as the default access driver. You must specify the ORACLE_DATAPUMP access driver if you
specify the AS subquery clause to unload data from one Oracle Database and reload it into the same or a different Oracle Database.

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
Explanation:

The table that
follows lists the SHUTDOWN modes and describes the behavior of the ASM instance in each mode.
SHUTDOWN Mode Action Taken By Automatic Storage
Management
NORMAL ASM waits for the connected ASM instances (and other ASM SQL sessions) to exit before shutting down the
instance.
IMMEDIATE ASM waits for any in-progress SQL to complete before shutting down the ASM instance, but does not wait for database
instances to disconnect.
TRANSACTIONAL Same as IMMEDIATE.
ABORT Automatic Storage Management immediately shuts down.


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 CONSTRГЂINT emp_last_name_nn;
Answer: D
Explanation:

The shrink clause lets you manually
shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. This clause is valid
only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark,
and releases the recuperated space immediately.
Compacting the segment requires row movement. Therefore, you must enable row movement for the
object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before
issuing this clause.
The default is DISABLE ROW MOVEMENT.

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
Explanation:

DBA_DATAPUMP_JOBS displays all Data Pump jobs in the
database.
Column Datatype NULL Description
OWNER_NAME VARCHAR2(30) User that initiated the
job
JOB_NAME VARCHAR2(30) Name of the job
OPERATION VARCHAR2(30) Type of operation being
performed
JOB_MODE VARCHAR2(30) Mode of operation being performed
STATE VARCHAR2(30) Current job
state
DEGREE NUMBER Number of worker processes performing the operation
ATTACHED_SESSIONS NUMBER Number of sessions attached
to the job

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
Explanation:

Using the RENAME TO clause of the ALTER TABLESPACE, you can
rename a permanent or temporary tablespace. When you rename a tablespace the database updates all references to the tablespace name in the data
dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if this tablespace were, for example, the
default tablespace for a user, then the renamed tablespace would show as the default tablespace for the user in the DBA_USERS view.
В• If any
datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.
В• If the
tablespace is read only, then datafile headers are not updated. This should not be regarded as corruption; instead, it causes a message to be
written to the alert log indicating that datafile headers have not been renamed. The data dictionary and control file are updated.
В• If the
tablespace is an undo tablespace and if the following conditions are met, then the tablespace name is changed to the new tablespace name in the
server parameter file (SPFILE).
-The server parameter file was used to start up the database.
-The tablespace name is specified as the
UNDO_TABLESPACE for any instance.

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, F, G
Explanation:

Schedule Job Execution (B)
Database administrators can schedule and monitor recurring
database maintenance jobs, such as backups or nightly data warehousing loads and extracts, and they can schedule job execution based on time or
events.
With Enterprise Manager, you can enable and disable Scheduler jobs, alter the settings for existing jobs, start or stop current jobs, and
view Scheduler information.
A job can also be created by pointing to both a named program and schedule. (F)
External Tasks (G)
An external job
is a job that runs outside the database. All external jobs run as a low-privileged guest user, as has been determined by the database administrator
while configuring external job support. Because the executable will be run as a low-privileged guest account, you should verify that it has access
to necessary files and resources.
Clustered Environment (C)
In both cluster and non-cluster environments, you can track performance metrics by
service using the Automatic Workload Repository (AWR). Other Oracle tools and facilities such as Oracle Scheduler, Parallel Query, and Oracle
Streams Advanced Queuing can also use services to manage their workloads.


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
Explanation:

V$DATABASE displays information about the database from the control
file.
Column Datatype Description
DBID NUMBER Database identifier calculated when the database is created and stored in all file
headers
NAME VARCHAR2(9) Name of the database
CREATED DATE Creation date of the database
FLASHBACK_ON VARCHAR2(3) Indicates
whether the database is in FLASHBACK mode (YES) or not (NO)


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
Explanation:

Use the FLASHBACK TABLE statement to restore an
earlier state of a table in the event of human or application error. You cannot roll back a FLASHBACK TABLE statement. However, you can issue
another FLASHBACK TABLE statement and specify a time just prior to the current time.

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
Explanation:

The recycle bin is actually a data dictionary
table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the
likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or
the unlikely situation where they must be purged by the database because of tablespace space constraints.
Each user can be thought of as having his
own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the
user owns.
When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are
purged.

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
Explanation:

RESUMABLE Clauses
These clauses let you enable and disable resumable space allocation. This feature allows
an operation to be suspended in the event of an out-of-space error condition and to resume automatically from the point of interruption when the
error condition is fixed.
ENABLE RESUMABLE
This clause enables resumable space allocation for the session.
TIMEOUT
TIMEOUT lets you specify (in
seconds) the time during which an operation can remain suspended while waiting for the error condition to be fixed. If the error condition is not
fixed within the TIMEOUT period, then Oracle Database aborts the suspended operation.

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
Explanation:

Two clauses of the ALTER TABLESPACE
statement support datafile transparency when you are using bigfile tablespaces:
1)RESIZE: The RESIZE clause lets you resize the single datafile in
a bigfile tablespace to an absolute size, without referring to the datafile.
2) AUTOEXTEND (used outside of the ADD DATAFILE clause):
With a
bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD DATAFILE clause. For example: ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT
20G;
An error is raised if you specify an ADD DATAFILE clause for a bigfile tablespace.

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, C, D, F (original is A, D, E, F)
Explanation:

For the D answer see the point
nВ°1.
For the C answer see the point nВ°2.
For the A answer see the point nВ°3.
For the F answer see the point nВ°4.

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.

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
Explanation:

Incrementally Updated Backups: Rolling Forward Datafile Image Copies
By
using the BACKUP INCREMENTAL... FOR RECOVER OF COPY WITH TAG... syntax you can create level 1 incremental backups suitable for rolling forward a
level 0 incremetal image copy backup of your database. RECOVER COPY OF... WITH TAG... is used to perform incremental update of a backup.

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
Explanation:

Temporary Tablespace Groups
Temporary tablespace groups lets users consume temporary space from multiple tablespaces. Using a
tablespace group, rather than a single temporary tablespace, can alleviate problems caused when one tablespace is inadequate to hold the results of
a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to
use multiple temporary tablespaces.
A tablespace group name can appear where a tablespace name, would appear when assigning a default temporary
tablespace for the database or assigning a temporary tablespace for a user.
Specify an empty string (' ') to indicate that tablespace is not a
member of any 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
Explanation:

While the
data and metadata are being transferred, a master table is used to track the progress within a job. The master table is implemented as a user table
within the database. The specific function of the master table for export and import jobs is as follows:
-For export jobs, the master table records
the location of database objects within a dump file set. Export builds and maintains the master table for the duration of the job. At the end of an
export job, the content of the master table is written to a file in the dump file set.
-For import jobs, the master table is loaded from the dump
file set and is used to control the sequence of operations for locating objects that need to be imported into the target database.
The master table
is created in the schema of the current user performing the export or import operation. Therefore, that user must have sufficient tablespace quota
for its creation. The name of the master table is the same as the name of the job that created it. Therefore, you cannot explicitly give a Data Pump
job the same name as a preexisting table or view.
For all operations, the information in the master table is used to restart a job.

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
Explanation:

Starting with Oracle Database 10g, you can transport tablespaces across platforms. Many, but not all,
platforms are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are
supported, and to determine their platform IDs and their endian format (byte ordering).
If the source platform and the target platform are of
different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to
the target format. RMAN's CONVERT command is only required in cases where you are transporting between platforms with different byte
ordering.

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
Explanation:

When using DURATION you can run the backup with the maximum possible performance, or run as slowly as possible
while still finishing within the allotted time, to minimize the performance impact of backup tasks. To maximize performance, use the MINIMIZE TIME
option with DURATION.
To extend the backup to use the full time available, use the MINIMIZE LOAD option.
Note:
these issues when using DURATION
and MINIMIZE LOAD with a tape backup:
-Efficient backup to tape requires tape streaming. If you use MINIMIZE LOAD, RMAN may reduce the rate of
backup to the point where tape streaming is not optimal.
-RMAN will hold the tape resource for the entire duration of the backup window. This
prevents the use of the tape resource for any other purpose during the backup window.
Because of these concerns, it is not recommended that
MINIMIZE LOAD be used with tape.

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
Explanation:

Server-Generated Alerts
For problems that cannot be resolved automatically and
require administrators to be notified, such as running out of space, the Oracle Database provides server-generated alerts. The Oracle Database can
monitor itself and send out alerts to notify you of any problem in an efficient and timely manner.
Monitoring activities take place as the database
performs its regular operation. This ensures that the database is aware of problems the moment they arise. The alerts produced by the Oracle
Database not only notify the problem, they also provide recommendations on how the reported problem can be resolved. This ensures quick problem
resolution and helps prevent potential failures.
Tablespace Space Used (%) and Tablespace Space Used (%) (dictionary managed)
The metrics in this
metric category check for the amount of space used by the tablespaces. If the percentage of used space is greater than the values specified in the
threshold setting, then a critical or warning alert is generated.
Target Version Server Evaluation Frequency Collection Frequency
Upload Frequency Default Warning Threshold Default Critical Threshold Consecutive Number of Occurrences Preceding
Notification Alert Text
8.1.7.4; 9.0.1.x; 9.2.0.x Every 30 Minutes Every 30 Minutes After Every
Sample 85 97 1 Tablespace [%name%] is [%value% percent] full
10.1.0.x Every 10 Minutes Every 30 Minutes
After Every Sample 85 97 1 Generated By Database Server
Suspended Session
This metric category contains the metrics
that represent the number of resumable sessions that are suspended due to some correctable error.
Evaluation and Collection Frequency
Upload Frequency Default Warning Threshold Default Critical Threshold Consecutive Number of Occurrences Preceding
Notification Alert Text
Every 5 Minutes Not Uploaded 0 Not Defined 1 %value% session(s) are suspended.
Recovery Area
Free Space (%)
This metric represents the recovery area free space as a percentage.
Target Version Collection Frequency
10.1.0.x
Every 15 Minutes


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
Explanation:

RESTORE_TABLE_STATS procedure restores statistics of a table as of a specified timestamp
(as_of_timestamp). The procedure will restore statistics of associated indexes and columns as well. If the table statistics were locked at the
specified timestamp the procedure will lock the statistics. The procedure will not restore user defined statistics.
DBMS_STATS.RESTORE_TABLE_STATS
(
ownname VARCHAR2,
tabname VARCHAR2,
as_of_timestamp TIMESTAMP WITH TIME ZONE,

restore_cluster_index BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE);
RESTORE_TABLE_STATS Procedure
Parameters
Parameter Description
ownname The schema of the table for which the statistics are to be restored.
tabname The table
name.
as_of_timestamp The timestamp to which to restore statistics.
restore_cluster_index If the table is part of a cluster, restore
statistics of the cluster index if set to TRUE.
force Restores statistics even if the table statistics are locked. If the table statistics were
not locked at the specified timestamp, it unlocks the statistics.

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
Explanation:

Restrictions on Flashback Queries
-You cannot use the VERSIONS clause in flashback queries
to temporary or external tables, or tables that are part of a cluster.
-You cannot use the VERSIONS clause in flashback queries to views. However,
you can use the VERSIONS syntax in the defining query of a view.

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
Explanation:

1)The alert file, or alert log, is a special trace file. The alert
file of a database is a chronological log of messages and errors.

2)The following dictionary views provide information about server
alerts:
DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
DBA_ALERT_HISTORY lists a history of alerts that have been
cleared.
V$ALERT_TYPES provides information such as group and type for each alert.

3)The Enterprise Manager Database Home page allows you to
view important performance and status information about your database instance from a single source, including:
-Instance name, database version,
Oracle home location, media recovery options, and other pertinent instance data
-Current instance availability
-Host CPU metrics and aggregate
session activity data
-High availability statistics
-Outstanding alerts
-Session and SQL-related performance information
-Key space usage
metrics

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
Explanation:

LOG_ARCHIVE_FORMAT is applicable only if you are using the redo log in ARCHIVELOG mode. Use a
text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to
the string specified in the LOG_ARCHIVE_DEST parameter.
The following variables can be used in the format:
%s log sequence number
%S log sequence
number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names
are constructed for the archived log files across multiple incarnations of the database.

Question 53
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
Explanation:

Oracle Database provides a Segment Advisor that helps you determine whether an object has space available for
reclamation based on the level of space fragmentation within the object.

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
Explanation:

Optional
clauses for local indexes let you specify physical and storage characteristics for updated local indexes and their partitions.
1)You can specify
physical attributes, tablespace storage, and logging for each partition of each local index. Alternatively, you can specify only the PARTITION
keyword and let the database update the as follows
-For operations on a single table partition (such as MOVE PARTITION and SPLIT PARTITION), the
corresponding index partition inherits the attributes of the affected index partition. The database does not generate names for new index
partitions, so any new index partitions resulting from this operation inherit their names from the corresponding new table partition.
-For MERGE
PARTITION operations, the resulting local index partition inherits its name from the resulting table partition and inherits its attributes from the
local index.
2)For a composite-partitioned index, you can specify tablespace storage for each subpartition.
The following operations support the
UPDATE INDEXES clause:
ADD PARTITION | SUBPARTITION
COALESCE PARTITION | SUBPARTITION
DROP PARTITION | SUBPARTITION
EXCHANGE PARTITION |
SUBPARTITION
MERGE PARTITION | SUBPARTITION
MOVE PARTITION | SUBPARTITION
SPLIT PARTITION | SUBPARTITION
TRUNCATE PARTITION |
SUBPARTITION

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 in initialization parameter file. Which two
statements are truein 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
Explanation:

B) MMON performs various manageability-
related background tasks, for example:
-Issuing alerts whenever a given metrics violates its threshold value
-Taking snapshots by spawning
additional process (MMON slaves)
-Capturing statistics value for SQL objects which have been recently modified
D) A metric is a data point
sampled by a Management Agent and sent to the Oracle Management Repository.
F) Components that use SYSAUX as their default tablespace during
installation include Automatic Workload Repository, Oracle Streams, Oracle Text, and Enterprise Manager Repository.

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
Explanation:

SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of
tables with unusable indexes or index partitions. Enables does not allow inserts, deletes, and updates on tables with unusable indexes or index
partitions.

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
Explanation:

В”policy_type => dbms_rls.staticВ” explanation:
Predicate is assumed to be the same regardless of the runtime environment.
Static policy functions are executed once and then cached in SGA. Statements accessing the same object do not reexecute the policy function.
However, each execution of the same cursor could produce a different row set even for the same predicate because the predicate may filter the data
differently based on attributes such as SYS_CONTEXT or SYSDATE. Applies to only one object.

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, E
Explanation:

The value of the SGA_TARGET parameter should be set to the amount of
memory that you want to dedicate for the SGA. In response to the workload on the system, the automatic SGA management distributes the memory
appropriately for the following memory pools:
-Database buffer cache (default pool)
-Shared pool
-Large pool
-Java pool
If these automatically
tuned memory pools had been set to nonzero values, those values are used as a minimum levels by Automatic Shared Memory Management.
The following
pools are manually sized components and are not affected by Automatic Shared Memory Management:
-Log buffer
-Other buffer caches, such as KEEP,
RECYCLE, and other block sizes
-Streams pool
-Fixed SGA and other internal allocations
To manually size these memory pools, you need to set the
DB_KEEP_CACHE_SIZE, STREAMS_POOL_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, and LOG_BUFFER initialization parameters. The memory allocated to
these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically
tuned memory pools.
SGA_TARGET is a dynamic parameter and can be changed through Oracle Enterprise Manager or with the ALTER SYSTEM command.


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 auto extensible data files?

A. TEST_TBS is a small
file tablespace.
B. TEST_TBS is a big file 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
Explanation:

ORA-13906: The
tablespace is not of the right type.
Cause: An attempt was made to set a threshold on dictionary-managed tablespaces.
Action: Check the
tablespace type and reissue the command.

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
Explanation:

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
Explanation:

V$SYSAUX_OCCUPANTS
displays SYSAUX tablespace occupant information.
Column Datatype Description
OCCUPANT_NAME VARCHAR2(64) Occupant
name
OCCUPANT_DESC VARCHAR2(64) Occupant description
SCHEMA_NAME VARCHAR2(64) Schema name for the
occupant
MOVE_PROCEDURE VARCHAR2(64) Name of the move procedure; null if not applicable
MOVE_PROCEDURE_DESC VARCHAR2(64) Description
of the move procedure
SPACE_USAGE_KBYTES NUMBER Current space usage of the occupant (in KB)


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
Explanation:

SET_THRESHOLD Procedure
This procedure will set warning and critical thresholds for given
metrics.
Syntax
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id IN NUMBER,
warning_operator IN NUMBER,

warning_value IN VARCHAR2,
critical_operator IN NUMBER,
critical_value IN VARCHAR2,
observation_period
IN NUMBER,
consecutive_occurrences IN NUMBER,
instance_name IN VARCHAR2,
object_type IN NUMBER,

object_name IN VARCHAR2);
Parameters
Parameter Description
metrics_id The internal name of the metrics.
warning_operator The
operator for the comparing the actual value with the warning threshold (such as OPERATOR_GE).
warning_value The warning threshold value. This is
NULL if no warning threshold is set. A list of values may be specified for OPERATOR_CONTAINS.
critical_operator The operator for the
comparing the actual value with the critical threshold.
critical_value The critical threshold value. This is NULL if not set. A list of values may
be specified for OPERATOR_CONTAINS.
observation_period The period at which the metrics values are computed and verified against the threshold
setting. The valid range is 1 to 60 minutes.
consecutive_occurrences The number of observation periods the metrics value should violate the
threshold value before the alert is issued.
instance_name The name of the instance for which the threshold is set. This is NULL for database-
wide alerts.
object_type See Object Types Defined as Constants.

object_name The name of the object. This is NULL for SYSTEM.

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 non problem 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
Explanation:

Oracle Database provides a Segment Advisor
that helps you determine whether an object has space available for reclamation based on the level of space fragmentation within the
object.

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 if 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
Explanation:

When PARTIAL is used, no error is
reported when a backup command is interrupted due to the end of the backup window. Instead, a message showing which files could not be backed will
be displayed.

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
Explanation:

Oracle Database Release 10g New Features in Backup and Recovery
For any use of the BACKUP command that creates backupsets, you
can take advantage of RMAN's support for binary compression of backupsets, by using the AS COMPRESSED BACKUPSET option to the BACKUP command. The
resulting backupsets are compressed using an algorithm optimized for efficient compression of Oracle database files. No extra uncompression steps
are required during recovery if you use RMAN's integrated compression. If you are backing up to tape and your tape device performs its own
compression, you should not use both RMAN backupset compression and the media manager vendor's compression.



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
Explanation:

Automatic Shared Memory Management simplifies the configuration of the SGA and is
the recommended memory configuration. To use Automatic Shared Memory Management, set the SGA_TARGET initialization parameter to a nonzero value and
set the STATISTICS_LEVEL initialization parameter to TYPICAL or ALL.

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
Explanation:

A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks)
datafile. 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.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space
management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace can be bigfile tablespaces
even if their segments are manually managed.

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
Explanation:

DBA_FEATURE_USAGE_STATISTICS displays information about database feature usage
statistics.
Column Datatype NULL Description
DBID NUMBER NOT NULL Database identifier of the database being
tracked
NAME VARCHAR2(64) NOT NULL Name of the feature
VERSION VARCHAR2(17) NOT NULL Database version in which the
feature was tracked
DETECTED_USAGES NUMBER NOT NULL Number of times the system has detected usage for the
feature
TOTAL_SAMPLES NUMBER NOT NULL Number of times the system has woken up and checked for feature
usage
CURRENTLY_USED VARCHAR2(5) Indicates whether usage was detected the last time the system checked (TRUE) or not
(FALSE)
FIRST_USAGE_DATE DATE First sample time the system detected usage of the feature
LAST_USAGE_DATE DATE Last
sample time the system detected usage of the feature
AUX_COUNT NUMBER This column stores feature-specific usage data in number
format.
FEATURE_INFO CLOB This column stores feature-specific usage data in character format.
LAST_SAMPLE_DATE DATE
Amount of time (in seconds) between the last two usage sample times
LAST_SAMPLE_PERIOD NUMBER Amount of time (in hours) between
the last two usage sample times
SAMPLE_INTERVAL NUMBER Sample interval
DESCRIPTION VARCHAR2(128) Description of the
feature and usage detection logic

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
Explanation:

When setting up a flash recovery area, you must choose
a location (a directory or Automatic Storage Management disk group) to hold the files. The flash recovery area cannot be stored on a raw file
system.
You must also determine a disk quota for the flash recovery area, the maximum space to be used for all files stored there. You must choose
a location large enough to accomodate the required disk quota. When the disk space limit is approached, Oracle can delete nonessential files to make
room for new files, subject to the limitations of the retention policy.

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: D
(original is B)
Explanation:

INCLUDE - Enables you to filter the metadata that is exported by specifying objects and object types for the
current export mode. The specified objects and all their dependent objects are exported. Grants on these objects are also exported.
REMAP_SCHEMA -
Loads all objects from the source schema into a target schema. If the schema you are remapping to does not already exist, the import operation
creates it, provided the dump file set contains the necessary CREATE USER metadata and you are importing with enough privileges.

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: F (original is
C)
Explanation:

The Oracle database uses the database character set for data stored in SQL CHAR datatypes (CHAR, VARCHAR2, CLOB, and LONG), for
identifiers such as table names, and for SQL statements and PL/SQL source code. Only single-byte character sets and varying-width character sets
that include either ASCII or EBCDIC characters are supported as database character sets. Multibyte fixed-width character sets (for example,
AL16UTF16) are not supported as the database character set.

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
Explanation:

RBAL coordinates rebalance activity for disk groups in an Automatic Storage
Management instance. It performs a global open on Automatic Storage Management disks. ORBn performs the actual rebalance data extent movements in an
Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

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 if you use the UPDATE INDEX clause in the above
command.
Answer: C, F
Explanation:

Use the MOVE PARTITION clause to move partition to another segment. Oracle Database moves local index
partitions corresponding to the specified partition. If the moved partitions are not empty, then the database marks them UNUSABLE. The database
invalidates global indexes on heap-organized tables. You can update these indexes during this operation using the UPDATE INDEX clauses.

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
Explanation:

When you use the ORACLE_DATAPUMP access driver to unload data, the data is unloaded in parallel when the PARALLEL clause or
parallel hint has been specified and when multiple locations have been specified for the external table. Each parallel process writes to its own
file. Therefore, the LOCATION clause should specify as many files as there are degrees of parallelism. If there are fewer files than the degree of
parallelism specified, then the degree of parallelism will be limited to the number of files specified. If there are more files than the degree of
parallelism specified, then the extra files will not be used.


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
Explanation:

RENAME - Use this
clause to rename tablespace. This clause is valid only if tablespace and all its datafiles are online and the COMPATIBLE parameter is set to 10.0.0
or greater.
If you re-create the control file, and if the datafiles that Oracle Database uses for this purpose are restored backups whose headers
reflect the old tablespace name, then the re-created control file will also reflect the old tablespace name.

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
Explanation:

The three possible options are:
DBMS_SCHEDULER.LOGGING_OFF - No logging will be
performed for any jobs in this class.
DBMS_SCHEDULER.LOGGING_RUNS - The Scheduler will write detailed information to the job log for all runs of
each job in this class.
DBMS_SCHEDULER.LOGGING_FULL - In addition to recording every run of a job, the Scheduler will record all operations
performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the
log.

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
Explanation:

REMAP_SCHEMA - Loads all objects from the source schema into a target schema. If the schema you are
remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata and you
are importing with enough privileges.

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
Explanation:

MODIFY_SNAPSHOT_SETTINGS - This procedure controls two aspects of
snapshot generation. The INTERVAL setting affects how often snapshots are automatically captured while the RETENTION setting affects how long
snapshots are retained in the Workload Repository.

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
Explanation:

disk_clauses:

add_disk_clause:


add_disk_clause - Use this clause to add one or more disks to the disk group and specify attributes for the newly added disk. Automatic Storage
Management automatically rebalances the disk group as part of this operation.
You cannot use this clause to change the failure group of a disk.
Instead you must drop the disk from the disk group and then add the disk back into the disk group as part of the new failure group.

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
Explanation:

Restrictions and Usage Notes to convert endian:
-Both source and destination databases must be running with
initialization parameter COMPATIBLE set to 10.0 or higher.
-The CONVERT command can be run either on the source host or the destination host. When
converting on the source host, CONVERT... TO is used to identify the destination platform (and the source platform is, implicitly, the platform of
the source host). When converting on the destination host, use CONVERT... FROM to identify the source platform (and the destination platform is,
implicitly, the platform of the destination host).
-CONVERT does not do in-place conversion of datafiles. It creates an output file that is
readable on the specified platform.
-Prior to Release 10g, CLOBs were created in a variable width character set and stored in an endian-dependent
format. The CONVERT command does not perform conversions on these CLOBs. Instead, RMAN captures the endian format of each LOB column and propagates
it to the target database. Subsequent reads of this data by the SQL layer will interpret the data correctly based on either endian format and write
it out in an endian- independent way if the tablespace is writeable.
-CLOBs created in Oracle Database Release 10g are stored in character set
AL16UTF16, which is platform independent.

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 flashback 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
Explanation:

You must be using automatic undo management to use the Flashback Table feature. It is based on undo information stored in an undo
tablespace.
The Undo Advisor improves manageability of transaction management, especially for automatic undo management. The Undo Advisor presents
the best retention possible for the given undo tablespace. It also advises a size for the undo tablespace when you want to set undo retention to a
particular value. The Undo Advisor is based on system activity statistics, including the longest running query and undo generation rate. Advisor
information includes the following:
-Current undo retention
-Current undo tablespace size
-Longest query duration
-Best undo retention
possible
-Undo tablespace size necessary for current undo retention

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 change