1z0-040

Oracle Database 10g: New Features for Administrators

 

QUESTION NO: 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:

QUESTION NO: 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:

1z0- 040

 

– 4 –

QUESTION NO: 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:

QUESTION NO: 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?

1z0- 040

 

– 5 –

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:

QUESTION NO: 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

1z0- 040

 

– 6 –

Answer:

QUESTION NO: 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:

QUESTION NO: 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:

1z0- 040

 

– 7 –

QUESTION NO: 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:

QUESTION NO: 9

The Automatic Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes on your database.

Your database if facing a series of interrelated problems over a period of two hours. You need to ensure

that the ADDM analysis is run over a time span of two hours in future.

What would you do?

A. Create two custom ADDM tasks.

B. Modify the AWR snapshot time interval to two hours.

C. Create a new scheduler window for a time period of two hours.

D. Modify the time interval by using the DBMS_JOB.INTERVAL procedure.

E. Modify the Automatic Workload Repository (AWR) snapshot retention period to two hours.

1z0- 040

 

– 8 –

Answer:

QUESTION NO: 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:

QUESTION NO: 11

Consider the following command to add a new disk group called “tdgroupA” with two failover groups:

CREATE DISKGROUP tdgrouopA NORMAD REDUNDANCY

FAILOVERGROUP control01 DISK

‘/devices/A1’,

‘/devices/A2’,

‘/devices/A3’

FAILOVERGROUP control02 DISK

‘/devices/B1’,

‘/devices/B2’,

‘/devices/B3’;

The disk “/devices/A1” is currently a member disk of a disk group by name “tdgroup1”

1z0- 040

 

– 9 –

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:

QUESTION NO: 12

Exhibit:

Exhibit

1z0- 040

 

– 10 –

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 => BDMS_FGA.ALL_COLUMNS,

audit-trail => BDMS_FGA.DB.EXTENDED,

statement_types => ‘INSERT, UPDATE’);

END,

Which statement is true about the fine-grained auditing implemented by this policy?

1z0- 040

 

– 11 –

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

QUESTION NO: 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:

QUESTION NO: 14

Which statement regarding the COMPATIBLE parameter is correct?

1z0- 040

 

– 12 –

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:

QUESTION NO: 15

You are using Recovery Manager (RMAN) to perform backups. In which three situations would you

perform a compressed backup? (Choose three.)

A. Your database includes a large number of BFILEs.

B. You are making image copies, and you have not enabled tablespace compression.

C. You are backing up to tape and your tape device performs its won compression.

D. You are using disk-based backups and disk space in your Flash Recovery Area, or other 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:

QUESTION NO: 16

You plant o create a database ‘PROD’ using the Database Configuration Assistant (DBCA).

1z0- 040

 

– 13 –

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:

QUESTION NO: 17

Which two dynamic performance views would you query to determine the endian format of your

platform? (Choose two.)

A. V$DATABASE

B. V$INSTANCE

C. V$PLATFORM

D. V$TABLESPACE

E. V$CONTROLFILE

F. V$TRANSPORTABLE_PLATFORM

Answer:

QUESTION NO: 18

1z0- 040

 

– 14 –

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:

QUESTION NO: 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 BDMS_SCHEDULER.PURGE_LOG ( log_history => 5, job_name => ‘JOB_LOG’);

D. EXECUTE BDMS_SCHEDULER.PURGE_LOG ( log_history => 5,which_log =>

‘WINDOW_LOG’);

Answer:

QUESTION NO: 20

Which three actions are required to configure the Flashback Database? (Choose three.)

1z0- 040

 

– 15 –

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:

QUESTION NO: 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:

QUESTION NO: 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.

1z0- 040

 

– 16 –

A. REFRESH

B. TUNE_MVIW

C. REGISTER_MVIEW

D. EXPLAIN_REWRIE

E. REFRESH_DEPENDENT

Answer:

QUESTION NO: 23

The ______ identifies an helps to resolve performance problems relating to the execution of SQL

statements by recommending which indexes, materialized views, or materialized view logs to create, drip,

or retain.

A. Undo Advisor

B. MTTR Advisor

C. Memory Advisor

D. Segment Advisor

E. SQL Tuning Advisor

F. SQL Access Advisor

Answer:

QUESTION NO: 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?

1z0- 040

 

– 17 –

A. FREQ = MONTHLY; BYDAY = 2FRI;

B. FREQ = MONTHLY, BYDAY = FRI2;

C. FREQ = MONTHLY; BYDAY = -2FRI;

D. FREQ = MONTHLY; BYDAY = FRI (2);

Answer:

QUESTION NO: 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:

QUESTION NO: 26

You want an ASM instance to manage the files of your database. To achieve this objective, you specify

the following parameters in the parameter file of the database.

INSTANCE_TYPE = RDBMS

LARGE_POOL_SIZE = 8MB

BD_BLOCK_SIZE = 4K

LOG_ARCHIEVE_DEST = +dgroupA

1z0- 040

 

– 18 –

LOG_ARCHIEVE_FORMAT = “$ORACLE_SID_&s_&S_&t_&t”

DB_CREAT_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_ARCHIEVE_DEST

E. LOG_ARCHIEVE_FORMAT

F. DB_CREATE_FILE_DEST

Answer:

QUESTION NO: 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:

QUESTION NO: 28

1z0- 040

 

– 19 –

Tom is the DBA of DNX Bank. The users of the banking system are complaining that all debit

transactions are taking too long to execute. The details of the application used for this purpose are listed

below:

Application – Bank_DNX to access banking accounts

Module – CUSTLEDGER

Action DEBIT_ENTRY

Which command should Tom execute in order to gather static for the problem?

A. EXECUTE BDMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (‘BANK_DNX’,

‘CUSTLEDGER’);

B. EXECUTE BDMS_MONITOR.SERV_MOD_ACT_STAT_ENALBLE (‘BANK_DNX’);

C. EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (‘DEBIT_ENTRY’);

D. EXECUTE BDMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE ‘BANK_DNX’,

‘CLUSTLEDGER ‘,’ DEBIT_ENTRY’);

E. EXECUTE BDMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (‘CLUSTLEDGER’,

‘DEBIT_ENTRY’);

Answer:

QUESTION NO: 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

1z0- 040

 

– 20 –

Answer:

QUESTION NO: 30

Examine the following command to create an external table from EMPLOYEES and DEPARTMENTS

database tables.

1. CREATE TABLE employee_ext

2. (employee_id first_name, department_name)

3. ORGANIZATION EXTERNAL

4. (

5. TYPE ORACLE_LOADER

6. DEFAULT DIRECTORY ext_dir

7. LOCATION (‘emp1.dmp’)

8. )

9. PARALLEL

10. AS

11. SELECT e.emplyee_id, e.first_name, e.last_name,

d.department_name

12. FROM employees e, departments d;

Which line of the command would cause an error?

A. line 2, because the column names have been specified

B. line 7, because file name must have a .dat extension

C. line 3, because ORGANIZATION EXTERNAL has been specified

D. line 9, because the PARALLEL option cannot be specified with one file name

E. line 12, because there is no join defined between EMPLOYEES and DEPARTMENTS table

F. line 5, because ORACLE_LOADER is not a valid structure for external table creation with the

SELECT statement

1z0- 040

 

– 21 –

Answer:

QUESTION NO: 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> shut down imeediate;

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:

QUESTION NO: 32

Exhibit

1z0- 040

 

– 22 –

Examine the SQL statement used to create the EMP table.

You need to shrink the EMP table segment. Which SQL statement would you execute as a prerequisite

before you execute the ALTER TABLE emp SHRINK SPACE; command?

A. ALTER TABLESPACE emp OFFLINE;

B. ALTER TABLESPACE emp READ ONLY;

C. ALTER TABLE emp DEALLOCATE UNUSED;

D. ALTER TABLE emp ENABLE ROW MOVEMENT;

E. ALTER TABLE emp DISABLE ALL TRIGGERS;

F. ALTER TABLE emp ENABLE NOVALIDATE CONSTRINT emp_last_name_nn;

Answer:

QUESTION NO: 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

1z0- 040

 

– 23 –

E. DBA_DATAPUMP_SESSIONS

Answer:

QUESTION NO: 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 table space 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:

QUESTION NO: 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.

1z0- 040

 

– 24 –

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:

QUESTION NO: 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:

QUESTION NO: 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

1z0- 040

 

– 25 –

D. use the FLASHBACK TABLE command

E. use the FLASHBACK DATABASE command

Answer:

QUESTION NO: 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:

QUESTION NO: 39

You want to execute the following statements in resumable mode:

CREATE TABLE … AS SELECT

CREATE IDNEX

CREATE MATERIALIZED VIEW

Which two methods can be used to enable a session for resumeable space allocation? (Choose two.)

A. Set the RESUMABLE_TIMEOUT initialization parameter to true.

B. Set the RESUMABLE_TIMEOUT initialization parameter to none.

1z0- 040

 

– 26 –

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:

QUESTION NO: 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 DATEFILE;

C. ALTER TABLESPACE adtbs AUTOEXTEND ON;

D. ALTER TABLESPACE adtbs ADD DATAFILE ‘/proddb/data/adtbs1.dbf’ SIZE 10G;

E. ALTER TABLE SPACE adtbs MODIFY DATAFILE ‘/proddb/data/adtbs.dbf’ AUTOEXTEND

ON;

Answer:

QUESTION NO: 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’,

1z0- 040

 

– 27 –

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:

QUESTION NO: 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:

QUESTION NO: 43

You executed the following command:

1z0- 040

 

– 28 –

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:

QUESTION NO: 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:

1z0- 040

 

– 29 –

QUESTION NO: 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:

QUESTION NO: 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:

QUESTION NO: 47

You used the following command to perform backup:

1z0- 040

 

– 30 –

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:

QUESTION NO: 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:

QUESTION NO: 49

You modified the optimizer statistics of a table by using the DBMS_STATS.GATHER_TABLE_STATS

procedure.

1z0- 040

 

– 31 –

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:

QUESTION NO: 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:

QUESTION NO: 51

1z0- 040

 

– 32 –

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:

QUESTION NO: 52

Which parameter avoids the overwriting of archived redo log files when you recover the database with

the RESETLOGS option?

A. LOG_ARCHIEVE_DEST

B. LOG_ARCHIVE_TRACE

C. LOG_ARCHIEVE_FORMAT

D. LOG_ARCHIEVE_CONFIG

E. LOG_ARCHIEVE_DEST_10

F. LOG_ARCHIVE_LOCAL_FIRST

Answer:

1z0- 040

 

– 33 –

QUESTION NO: 53

View the Exhibit and examine the highlighted alert.

Exhibit #1

Exhibit #2

You have received an alert with the Category specified as Snapshot Too Old. Which page would you open

from the Advisor Central page to determine the system recommendations to avoid such alerts in future?

A. ADDM

B. Memory Advisor

C. Segment Advisor

D. Undo Management

E. SQL Tuning Advisor

F. SQL Access Advisor

Answer:

1z0- 040

 

– 34 –

QUESTION NO: 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:

QUESTION NO: 55

You executed the following command on a partitioned table sales_test:

ALTER TABLE sales_test

MOVE PARTITION s_q3 TABLESPACE users2

UPDATE INDEXEXS

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

1z0- 040

 

– 35 –

QUESTION NO: 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:

QUESTION NO: 57

While creating an Oracle 10g Release 1 (10.1) database, you set the COMPATIBLE parameter to 10.0.0

in the initialization parameter file.

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:

QUESTION NO: 58

Which three statements regarding collection of database usage metrics are correct? (Choose three.)

1z0- 040

 

– 36 –

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:

QUESTION NO: 59

Exhibit

As shown in the diagram, in-memory statistics are transferred to the disk at regular intervals. Which

background process performs this activity?

1z0- 040

 

– 37 –

A. CJQ

B. CKPT

C. MMAN

D. SMON

E. PMON

F. MMON

G. DBWR

Answer:

QUESTION NO: 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:

QUESTION NO: 61

Identify three key features of ASM. (Choose three.)

A. file striping

B. file mirroring

C. Segment Advisor

1z0- 040

 

– 38 –

D. automatic file creation

E. automatic disk rebalancing

F. automatic file size increment

G. automatic undo management

Answer:

QUESTION NO: 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:

QUESTION NO: 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.

1z0- 040

 

– 39 –

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:

QUESTION NO: 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:

QUESTION NO: 65

An online tablespace, TEST_TBS, is full and you realize that no server-managed tablespace threshold

alerts were generated for the TEST_TBS tablespace. What could be the reason, if the TEST_TBS

tablespace does not include autoextensible data files?

1z0- 040

 

– 40 –

A. TEST_TBS is a small file tablespace.

B. TEST_TBS is a bigfile tablespace (BFT).

C. TEST_TBS is the default temporary tablespace.

D. TEST_TBS is the dictionary-managed tablespace.

E. Threshold values for the TEST_TBS tablespace are not explicitly specified.

Answer:

QUESTION NO: 66

You performed an incomplete recovery and opened the database with the RESETLOGS option. The

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

Note: Answers to the unanswered questions will be provided shortly. First customer, if any, faster than us in

providing answers will receive credit for each answer provided.

Send your suggestions to  .

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em OCP e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s