1z0-030

Oracle9i:

New Features for Administrators

QUESTION NO: 1

Examine the code.

CREATE ROLE readonly IDENTIFIED USING app.chk_readwrite ;

CREATE ROLE readwrite IDENTIFIED USING app.chk_readwrite ;

CREATE OR REPLACE PROCEDURE app.chk_readwrite

AUTHID CURRENT_USER IS

ipchk STRING(30);

BEGIN

IF says context (‘USERENV’, ‘ISDBA’=’TRUE’

THEN DBMS SESSION.SET_ROLE (‘READWRITE’) ;

ELSE DMBS_SESSION_SET_ROLE )’READONLY’) ;

END IF ;

END;

/

Which three statements correctly describe the Secure Application role definition?

(Choose three.)

(The SYS_CONTEXT calls returns whether or not the session is a true DBA

session.)

A. No user or application has to remember or hide a password.

B. It prevents everyone except a true DBA session from acquiring the READWRITE

role.

C. app.chk_readwrite is called whenever a user tries to access rows protected by the

READONLY or READWRITE label.

D. app.chk_readwrite is called by users or applications when they want to enable the

READONLY or READWRITE role.

Answer: A, B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 10-14

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-135

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 2

Examine the extract from a SQL*Plus session.

SQL> SELECT partition_name, high_value, tablespace_name

2 FROM user_tab_partitions

1z0-030

 

– 4 –

3 WHERE table_name = ‘LOCATIONS’;

PARTITION_NAME HIGH_VALUE TABLES

———– —————————————————– —–

REGION_EAST ‘MA’, ‘NY’, ‘CT’, ‘NH’, NULL, ‘MD’, ‘VA’, ‘PA’, ‘NJ’ tbs1

REGION_WEST ‘CA’, ‘AZ’, ‘NM’, ‘OR’, ‘WA’, ‘UT’, ‘NV’, ‘CO’, tbs2

REGION_SOUTH ‘TX’, ‘KY’, ‘TN’, ‘LA’, ‘MS’, ‘AR’, ‘AL’, ‘GA’ tbs3

REGION_CENTRAL ’OH’, ‘ND’, ‘SD’, ‘MO’, ‘IL’, ‘MI’, NULL, ‘IA’ tbs4

SQL> ALTER TABLE locations

2 SPLIT PARTITION region_east

3 VALUES(‘CT’,’MA’,’MD’)

4 INTO

5 (PARTITION region_north_east TABLESPACE tbs2

6 ,PARTITION region_south_east STORAGE (NEXT 2M));

Assuming that there is at least one row for each value shown in the HIGH VALUE

column, what is the result of the ALTER TABLE statement?

A. The REGION_SOUTH_EAST partition will be created in the TBS2 tablespace.

B. The statement will fail because no values are supplied for

REGION_SOUTH_EAST partition keys.

C. The REGION_SOUTH_EAST partition will contain only rows with a NULL

value in the current REGION_EAST partition.

D. The REGION_SOUTH_EAST partition will be created with storage

characteristics inherited from the LOCATIONS table.

E. Rows with partitioning keys in the current REGION_EAST partition not included

in the VALUES clause will be stored in the REGION_SOUTH_EAST partition.

Answer: E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 134-136

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-108

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

QUESTION NO: 3

What criterion does Oracle9i use to determine whether a database file is an Oracle

Managed File?

A. The filename format

B. Information stored inside a data dictionary table

C. Information stored in the ALERT.LOG file for the corresponding instance

1z0-030

 

– 5 –

D. Information stored inside the corresponding initialization parameter file for the

instance

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 4

Oracle9i extends the cursor sharing functionality with the new value of SIMILAR

for the CURSOR_SHARING parameter. With CURSOR_SHARING = SIMILAR,

cursors are shared for safe literals only. What is meant by ‘safe literals only’?

A. No literal value is substituted for a shared cursor.

B. Different execution plans are generated for substituted literal values.

C. The substitution of a literal value will produce different execution plans.

D. The substitution of any literal value will produce exactly the same execution plan.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 57-59

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 5

The Dynamic SGA feature allows the SGA to grow and shrink dynamically

according to an ALTER SYSTEM command.

This avoids the previous need of shutting down the instance in order to modify the

components of the SGA, namely the buffer cache and shared pool components.

Which three statements are true for the Dynamic SGA feature? (Choose three.)

A. The maximum granule size is 4 MB.

B. The minimum SGA configuration is three granules.

C. SGA memory is based on granules by SGA components.

D. The size of the SGA components is set by the SGA_MAX_SIZE parameter.

E. The size of the SGA components is limited by the setting of SGA_MAX_SIZE

parameter.

1z0-030

 

– 6 –

Answer: C, D, E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 180-182

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 16-17

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 6

What is true about version-enabled tables?

A. The unit of versioning is the schema.

B. It is possible to version enable a table pertaining to SYS.

C. There are as many segments as versions for the same base table.

D. It is possible to create workspaces if there is no version-enabled table in the

database.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 168-176

Chapter 3: Manageability Enhancements

QUESTION NO: 7

Automatic Consumer Group Switching is an important new feature of the Database

Resource Manager.

Which three plan directive parameters are used to control this feature? (Choose

three.)

A. SWITCH_TIME

B. SWITCH_GROUP

C. MAX_SESS_POOL

D. SWITCH_ESTIMATE

Answer: A, B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129

Chapter 3: Manageability Enhancements

1z0-030

 

– 7 –

QUESTION NO: 8

You can use Oracle Flashback to look at past activity in your database.

What are two other characteristics of Oracle Flashback? (Choose two.)

A. Oracle Flashback uses undo information to construct consistent data.

B. You can use a cursor opened while using an Oracle Flashback image to perform

DML once you deactivate Oracle Flashback in your session.

C. You can only use an Oracle Flashback view of the data if the required records are

still in the online redo log files.

D. Oracle Flashback lists the DML that was executed during the period you identify

when initiating your session

Answer: B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 45-47

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 64-68

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 9

If you create your database using Oracle Managed Files (OMF), what is true?

A. You must use Oracle predefined names to identify raw partitions that will hold

OMF structures.

B. You must define directories for two online redo log groups and three copies of the

control file.

C. If your CREATE DATABASE command fails, any OMF database files that have

already been created will be dropped automatically.

D. You must use only OMF data files when adding new tablespaces or data files to

your database in the future.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features

1z0-030

 

– 8 –

QUESTION NO: 10

What is true regarding a transaction executed in Automatic Undo Management mode?

A. It is possible for the transaction to span different undo segments.

B. It is possible for the transaction to reuse inactive extents from another undo

segment.

C. It is possible for the transaction to use inactive extents from another undo

tablespace.

D. It is possible for the transaction to extend the undo tablespace, even if defined

with AUTOEXTENSIBLE set to FALSE.

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-166

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 53-54

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 11

Which two are true regarding external tables? (Choose two.)

A. External tables can be updated.

B. External tables are read-only tables.

C. Indexes can be created on external tables.

D. Indexes cannot be created on external tables.

Answer: B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 131-134

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 111-116

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

QUESTION NO: 12

When does Oracle9i flag an index as being used in the V$OBJECT_USAGE view?

A. During a query’s parse time only

B. During a query’s execution time only

C. During any kind of DML statement execution

1z0-030

 

– 9 –

D. During the execution of an INSERT statement only

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 56-57

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 13

The list below displays four steps that you need to execute in order to switch from

the primary database role to the standby database role.

Choose the correct order of execution for the steps.

1. Shut down and start up the former primary instance without mounting the

instance.

2. Issue the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL

STANDBY command.

3. Issue the ALTER DATABASE MOUNT STANDBY DATABASE command.

4. End read or update activity on the former primary and standby databases.

A. 1, 2, 3, 4

B. 3, 2, 4, 1

C. 4, 2, 1, 3

D. 4, 3, 2, 1

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 64-72

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 14

Media recovery can sometimes be stopped by the inability to read past a certain

point in the redo stream. This is often referred to as “stuck recovery.”

Before Oracle9i, the Database Administrator had few options to deal with stuck

recovery. If the corrupt redo could not be recovered from some other source, then

1z0-030

 

– 10 –

all transactions that committed after the corrupt point in the redo stream would be

lost.

Oracle9i changes that with the Trial Recovery feature. Trial Recovery is used to test

the application of the redo logs to the database.

What are three other characteristics of Trial Recovery? (Choose three.)

A. Speeds up subsequent media recovery actions

B. Writes any uncorrupted blocks to disk during the test recovery

C. Can be invoked by adding the TEST option to any RECOVER command

D. Allows the Administrator to determine how many blocks are affected by

corruption

E. Marks blocks as corrupt in memory, allowing the test recovery to proceed to

completion

Answer: A, D, E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 186-187

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 73-86

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 15

You can control instance recovery performance by setting parameters to define

targets for _______.

A. The size of the redo buffer in the SGA

B. The instance that will perform recovery in a Real Application Clusters database

C. The number of blocks to be read during instance recovery, or the time needed to

complete instance recovery

D. The number of passes through the redo logs made by the recovery process to

identify blocks needing recovery

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 42-45

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 73-75

Chapter 3: New Oracle9i Availability and Recoverability Features

1z0-030

 

– 11 –

QUESTION NO: 16

Which three statements are true about the privileged connection options available in

Oracle9i? (Choose three.)

A. The CONNECT INTERNAL is disallowed.

B. The Server Manager tool is no longer supplied.

C. The listener must be running to make a connection.

D. The use of a remote password login file (orapw<sid>) is unchanged.

Answer: A, B, C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 5-10

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-135

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 17

You are responsible for a data warehouse application that uses records from an

external table to update one of the dimension tables periodically. The records in the

external table may contain data for new rows in the dimension table, or for updates

to its existing rows.

Which type of SQL command would you use to transfer the data from the external

table to the dimension table as efficiently as possible?

A. MERGE

B. SELECT … CROSS JOIN

C. INSERT ALL … SELECT

D. CREATE VIEW … CONSTRAINT

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 131-134

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 111-116

Chapter 4: New Oracle9i Database DSS and Data-Warehouse Features

QUESTION NO: 18

1z0-030

 

– 12 –

Which three resource plan parameters are used to enable Automatic Consumer

Group Switching to estimate job execution times automatically, and to perform a

switch to a low priority consumer group before they are initiated? (Choose three.)

A. SWITCH_TIME

B. SWITCH_GROUP

C. MAX_SESS_POOL

D. SWITCH_ESTIMATE

Answer: A, B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129

Chapter 3: Manageability Enhancements

QUESTION NO: 19

You are attempting to create an Oracle-Managed Files (OMF) tablespace in a

production database with the following statement and receive the following error

message:

CREATE TABLESPACE tbsl;

ORA-02199: missing DATAFILE/TEMPFILE clause

oerr ora 2199

02199, 00000, “missing DATAFILE/TEMPFILE clause”

*Cause: A CREATE TABLESPACE statement has no DATAFILE/TEMPFTLE clause.

*Action: Specify DATAFILE/TEMPFILE clause.

What is the corrective action to create the OMF based tablespace?

A. Issue the CREATE TABLESPACE tbsl DATAFILE SIZE 10M; command.

B. Issue the CREATE TABLESPACE tbsl EXTENT MANAGEMENT ORACLE;

command.

C. Set the db_create_file dest parameter using the ALTER SESSION command and

re-issue the statement.

D. An OMF tablespace is not allowed; only control files and/or redo log files can be

created by OMF.

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

1z0-030

 

– 13 –

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 20

Why can setting too low a value for the FAST_START_MTTR_TARGET

parameter reduce your overall database performance?

A. Data blocks have to be written more frequently by DBWR.

B. Redo blocks have to be written more frequently by LGWR.

C. You need to substantially increase the size of your database buffer cache to hold

the additional checkpoint records.

D. You need to substantially increase the size of your redo buffer cache to hold the

additional checkpoint records.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 42-45

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 73-75

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 21

The CURSOR_SHARING parameter is set to SIMILAR at the instance level and

you issue these SELECT statements in the order shown below:

SELECT * FROM employees WHERE department _id=50;

SELECT * FROM employees WHERE department_id=70;

There is an index on the DEPARTMENT ID column of the 1,000,000 row

EMPLOYEES table. Ninety percent of the employees are part of

DEPARTMENT_ID 50.

In which two scenarios will Oracle9i share the corresponding cursors for the two

statements? (Choose two.)

A. You are using the rule-based optimizer.

B. You are using the cost-based optimizer with up-to-date statistics but with no

histograms.

C. You are using the cost-based optimizer with up-to-date statistics and histograms

computed for the DEPARTMENT_ID column.

1z0-030

 

– 14 –

D. You are using the cost-based optimizer with up-to-date statistics and histograms

computed for the DEPARTMENT_ID column and system statistics gathered.

Answer: A, B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 57-59

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 22

To increase the availability of an Advanced Replication environment, you can add a

new master to a replication group without quiescing the group. This is achieved

____________.

A. Only when the new master site already has existing replication groups

B. By issuing a single command to create, populate, and activate the new master

C. When the master definition site is not the same for all of the master groups

D. Without impacting end users who are executing data manipulation language

(DML) commands on the replicated tables

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 83-93

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 146-147

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 23

The DBMS_STATS.GATHER_SYSTEM_STATS routine collects system statistics

in a user-defined time frame.

Which four statements are true of the GATHER_SYSTEM_STATS routine?

(Choose four.)

A. You can use the GATHERING_MODE => ‘END’ option to end system statistics

collection.

B. You can use the INTERVAL option to specify a fixed interval to collect statistics.

C. You can use the GATHERING_MODE => ‘START’ option to enable manual

statistics collection.

1z0-030

 

– 15 –

D. The routine allows the optimizer to consider a system’s CPU and I/O utilization

and performance.

E. The routine allows the optimizer to invalidate existing cached plans.

Answer: A, B, C, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 220-222

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 180-185

Chapter 6: Oracle9i SQL, PL/SQL New Features

QUESTION NO: 24

Examine this startup script called startmydb.sql:

CONNECT myid/mypwd AS SYSDBA

STARTUP

EXIT

In Oracle8i, you could have executed this script by invoking Server Manager with

the command:

svrmgrl @startmydb.sql

Which alternative command could you use to run the startmydb.sqp script with

SQL*Plus to start your Oracle9i database?

A. sqlplus @startmydb.sql

B. sqlplus -Sstartmydb.sql

C. sqlplus -S @startmydb.sql

D. sqlplus /NOLOG @startmydb.sql

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 4-10

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-139

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 25

1z0-030

 

– 16 –

What are two key features of Automatic Undo Management? (Choose two.)

A. Oracle manages all undo space automatically.

B. The SYSTEM tablespace is never used to store automatic undo segments.

C. Undo tablespaces contain all undo information not stored in the SYSTEM

rollback segment.

D. Rollback segments are gradually replaced with automatic undo segments as the

instance gathers statistics about rollback requirements.

E. The number of automatic undo segments available to an instance is determined by

the number of undo tablespaces defined in its initialization parameter file.

Answer: A, C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-164

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 26

Examine this fragment from a SQL*Plus session:

SQL> SELECT name, value FROM v$sysstat

2> WHERE name LIKE ‘work area executions%’;

NAME VALUE

——————————– ——-

work area executions – optimal 1544

work area executions – onepass 11

work area executions – multipass 1038

What can you conclude about the setting of the PGA_AGGREGATE_TARGET

initialization parameter?

A. You cannot conclude anything because these statistics are not related to the

PGA_AGGREGATE_TARGET parameter.

B. It may be set too low because there are many more multipass work area

executions than one pass executions.

C. It may be set too high because there are many more multipass work area

executions than one pass executions.

D. It is probably at its optimal setting because the ratio of one pass to multipass work

area executions is below 0.10 (ten percent).

1z0-030

 

– 17 –

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 176-182

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 27

Which two are true regarding the Automatic Undo Management feature? (Choose

two).

A. PMON is responsible for shrinking undo segments as needed.

B. SMON is responsible for shrinking undo segments as needed.

C. To use this feature, you must create undo segments into an undo tablespace.

D. Oracle9i automatically creates a fixed number of undo segments per undo

tablespace.

E. In a Real Application Clusters environment, you must create one undo tablespace

per opened instance.

Answer: B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-164

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 28

For the best performance, what should be done with blocks transferred using the

Cache Fusion algorithm in a Real Application Clusters database?

A. They should be transferred across a high-speed cluster interconnect.

B. They should be written to disk by the sending instance and read from disk by the

receiving instance.

C. They should be granted, by the Global Cache Service, an exclusive mode resource

for each instance involved.

D. They should be transferred through the cache area of the Global Resource

Directory on the instance where the block is mastered.

Answer: A

1z0-030

 

– 18 –

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 223-230

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 193-206

Chapter 7: Oracle9i Real Application Clusters

QUESTION NO: 29

Examine this statement, which creates a Cartesian product of the COUNTRIES and

REGION tables:

SQL> SELECT c.country_name,

2> r.region name

3> FROM countries c, region r;

Oracle9i SQL: 1999 syntax supports the same functionality with which join type?

A. Equijoin

B. Cross join

C. Merge join

D. Natural join

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 242-249

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 158-161

Chapter 6: Oracle9i SQL, PL/SQL New Features

QUESTION NO: 30

Which three table transformations can be done by using online redefinition of

tables? (Choose three.)

A. Delete rows

B. Rename a column

C. Change a heap table to an index-organized table

D. Change the data type of a column from LONG to BLOB

E. Change the data type of a column from DATE to CHAR

Answer: B, C, D

1z0-030

 

– 19 –

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 74-83

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 31

You use the TABLESPACES option of the Export command to export, not

transport, the contents of a set of tablespaces. What are the only contents of the

dump file?

A. 1. Non-partitioned tables in the tablespace set

2. Partitioned tables that have all their partitions in the tablespace set

3. Indexes on all the exported tables

B. 1. Non-partitioned tables in the tablespace set

2. Partitioned tables that have at least one partition in the tablespace set

3. Indexes on all the exported tables

C. 1. Non-partitioned tables in the tablespace set

2. Partitioned tables that have all their partitions in the tablespace set

3. Indexes on the exported tables if the indexes are in the tablespace set or have at

least one partition in the tablespace set

D. 1. Non-partitioned tables in the tablespace set

2. Partitioned tables that have at least one partition in the tablespace set

3. Tables (including all partitions, if they exist) that have an index in the

tablespace set

4. Indexes on all the exported tables

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 49-51

Chapter 2: Availability Enhancements

QUESTION NO: 32

What are three characteristics of the Data Guard Broker? (Choose three.)

A. It runs as a process called DMON.

B. It can be controlled through the Data Guard Manager GUI and command-line

interface,

C. It is started by setting the DRS START parameter to TRUE in your initialization

file

1z0-030

 

– 20 –

D. It uses a configuration file stored on the primary database server and, optionally,

on one of your standby database servers

Answer: A, B, C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 51-74

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 33

What does Block Media Recovery (BMR) do?

A. Does only a recovery of the specified blocks

B. Replaces bad blocks by substituting good blocks

C. Uses the DBMS_REPAIR package to fix block corruptions

D. Does a restore and a recovery of the specified blocks

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 186

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 84-86

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 34

Oracle9i offers security improvements in the middle tier. One of these

improvements is the Secure Application role.

What are three key benefits of this improvement? (Choose three).

A. The role is enabled through a package.

B. The password is hidden within the application itself.

C. It uses the SYS_CONTEXT mechanism to authenticate user access.

D. It removes the requirement to embed a password in the application itself.

Answer: A, B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 10-24

Chapter 1: Security Enhancements

1z0-030

 

– 21 –

Oracle 9i New Features, Robert Freeman, p. 132-135

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 35

User defined events in Oracle Enterprise Manager return status, and possibly

values, to the Oracle Enterprise Manager Event Monitor. What are user defined

events?

A. Database based scripts (PL/SQL, Java) run by the Agent

B. Database Event Triggers, which issue Alerts to the Agent

C. Scripts run by the Agent in any language on the host server

D. User written PL/SQL programs in the database, which the OEM console can

execute and display the results

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 189-201

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 219-220

Chapter 7: Oracle9i Real Application Clusters

QUESTION NO: 36

When enabling Flashback using a wall-clock time, within what period does the

database choose a System Commit Number (SCN)?

A. One minute of the time specified

B. One second of the time specified

C. Five minutes of the time specified

D. Five seconds of the time specified

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 45-47

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 63-68

Chapter 3: New Oracle9i Availability and Recoverability Features

1z0-030

 

– 22 –

QUESTION NO: 37

A switchover to a standby database differs from a failover to the standby in a

number of ways.

What are two of the key differences that characterize a switchover? (Choose two.)

A. The operation is planned and causes no data loss.

B. The standby database must be running in a no data divergence mode.

C. The old primary database can take on the standby role and be available for a

future switchover.

D. The primary and standby database must be using exactly the same release and

patch level of Oracle9i.

E. Either the primary database’s online or archived log files, but not necessarily both,

must be available.

Answer: A, C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 51-74

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 38

Which operation causes an index to be considered “used” while monitoring is

turned on for that particular index?

A. Only when the index is specified in a hint

B. When data is fetched by an execution plan that uses that index

C. When a statement is executed and the execution plan contains a reference to the

index

D. When a statement is parsed and the resulting execution plan contains a reference

to the index

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 56-59

Chapter 2: Oracle9i Architecture Changes

1z0-030

 

– 23 –

QUESTION NO: 39

The multithreaded agent architecture for heterogeneous services is similar to the

Oracle multithreaded server architecture. The principal difference is that it uses

threads instead of processes.

What are three kinds of threads that it uses? (Choose three.)

A. TCP

B. Task

C. Monitor

D. Dispatcher

E. Shared Server

F. Shutdown Address

Answer: A, C, E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 148-150

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 59-60

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 40

Which three table transformations can be done using online redefinition of tables?

(Choose three.)

A. Delete rows

B. Drop a column

C. Change a heap table to an index-organized table

D. Change the data type of a column from LONG to BLOB

E. Change a range-partitioned table to a list-partitioned table

Answer: B, C, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 74-83

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 41

1z0-030

 

– 24 –

Online index rebuild functionality has been extended to include which four index

structures? (Choose four.)

A. Bitmap indexes

B. Reverse key indexes

C. Function-based indexes

D. Key-compressed indexes on regular tables

E. Key-compressed indexes on IOT (including secondary indexes)

Answer: B, C, D, E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 74-83

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 42

There is more than one way to set the server to detect and affect long running

operations automatically. What is the best choice if you want to reduce the impact of

long running operations on other users without aborting the long running

operations?

A. Define user profiles and set the CPU_PER_CALL limit.

B. Define a SWITCH_TIME for a plan in the Resource Manager.

C. Create a batch job that checks V$SESSION_LONGOPS; the batch job alters the

session priority of the long running operations.

D. Create a user defined event in the Oracle Enterprise Manager, which monitors

V$SESSION_LONGOPS.

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129

Chapter 3: Manageability Enhancements

QUESTION NO: 43

Which three attributes of the LOG_ARCHIVE_DEST_n initialization parameter

control the data availability mode of a Data Guard standby database? (Choose

three.)

1z0-030

 

– 25 –

A. SYNC or ASYNC to identify the network transmission mode

B. LGWR or ARCH to identify the primary database process responsible for sending

redo information to the standby

C. AFFIRM or NOAFFIRM to control whether log archiving disk write operations

are to be performed synchronously or asynchronously

D. PROTECTED or UNPROTECTED to control the degree of divergence and data

loss at the standby database

Answer: A, B, C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 51-74

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 44

Which statement correctly describes the function of the Oracle9i Cache Fusion

feature?

A. It provides each session with its own view of the database at a different point in

the past.

B. It enables you to execute scalable applications on a clustered database without

having to partition the users or the database tables.

C. It lets you dynamically reassign memory in your database buffer cache to

different block buffer sizes.

D. It allows you to add new sites to multimaster replication environment without

quiescing the master definition site.

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 223-230

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 193-206

Chapter 7: Oracle9i Real Application Clusters

QUESTION NO: 45

What is true if you want to switch undo tablespaces from the current one, called

UNDO1, to a new one called UNDO2?

1z0-030

 

– 26 –

A. It is NOT possible to switch unless no active transaction exists in UNDO1.

B. It is possible to switch to UNDO2, but current active transactions will abort.

C. It is possible to switch to UNDO2; current active transactions will be

automatically migrated to UNDO2.

D. It is possible to switch to UNDO2; only current active transactions will continue

to execute inside UNDO1

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-166

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 46

Examine the list of variables and their data types:

Name Data Type

TS, TS1 TIMESTAMP

TSZ TIMESTAMP WITH TIME ZONE

TLZ TIMESTAMP WITH LOCAL TIME ZONE

IYM INTERVAL YEAR TO MONTH

IDS, IDS1 INTERVAL YEAR To SECOND

Which three expressions using the new date and time data types are valid? (Choose

three.)

A. IDS* 2

B. TS + IYM

C. TS -TS1

D. IDS – TS

E. IDS + IYM

Answer: A, B, E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 266-271

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-135

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

1z0-030

 

– 27 –

QUESTION NO: 47

Consider the following statement:

SQL> EXECUTE DBMS _STATS.GATHER_SCHEMA_STATS (-

2> ownname => ‘OE’, –

3> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, –

4> method opt => ‘for all columns size AUTO’);

What is the effect of ‘for all columns size AUTO’ of the METHOD_OPT option?

A. The Oracle server creates a new histogram based on existing histogram

definitions for all table, column, and index statistics for the OE schema.

B. The Oracle server creates a histogram based on data distribution regardless of

how the application uses the column/s for all table, column, and index statistics

for the OE schema.

C. The Oracle server creates a histogram based on data distribution and application

usage of the column/s for all table, column, and index statistics for the OE

schema.

D. The Oracle server creates a histogram based on application usage, regardless of

data distribution, for all table, column, and index statistics for the OE schema.

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 220-222

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 180-181

Chapter 6: Oracle9i SQL, PL/SQL New Features

QUESTION NO: 48

Consider this RMAN command:

RMAN> CONFIGURE RETENTION POLICY CLEAR;

What is the effect of this command?

A. Backups will never expire.

B. It removes any retention policy.

C. The DELETE OBSOLETE command will fail with an error.

D. It sets the retention policy to the default of REDUNDANCY 1.

1z0-030

 

– 28 –

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 182-189

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 79-84

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 49

Examine the statement:

DROP TABLESPACE IND2

INCLUDING CONTENTS

AND DATAFILES;

What is the result of the statement?

A. The contents and data files belonging to the IND2 tablespace are dropped.

B. The statement will only succeed if the IND2 tablespace was built using Oracle-

Managed Files (OMF) data files.

C. The statement will only succeed if the data files belonging to the IND2 tablespace

are Oracle-Managed Files (OMF) or if the database was created using OMF.

D. The contents of the tablespace are dropped along with all its data files except

those identified with the Oracle-Managed Files (OMF) naming convention.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-158

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 50

Using a bitmap structure to manage database object space has several benefits over

using freelist structures.

Which three are benefits of managing space using automatic segment-space

managed objects? (Choose three)

A. It readily responds to dynamic changes for concurrent access.

B. It improves performance and space utilization in a multi-instance environment.

1z0-030

 

– 29 –

C. It requires fewer space related options than freelist managed structures.

D. It improves performance for SYSTEM tablespaces where a high degree of

concurrency is required

Answer: A, C, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-140

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 53-54

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 51

The EMPLOYEES table has six indexes and DML operations are slow. Which

command begins monitoring the EMPLOYEE_IDX_FK index to determine whether

it has been used by an execution plan?

A. ALTER TABLE employees monitor index employee_idx_fk;

B. ALTER INDEX employee_idx_fk monitoring on;

C. ALTER TABLE employees monitor all indexes;

D. ALTER INDEX employee_idx_fk monitoring usage;

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 56-59

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 52

Which two are true regarding automatic space management segments? (Choose

two.)

A. You can create an automatic space management segment in a dictionary-managed

tablespace.

B. You can create an automatic space management segment only in a locally

managed tablespace.

C. You can successfully invoke the DBMS_SPACE.FREE_BLOCKS procedure on

an automatic space management segment.

1z0-030

 

– 30 –

D. You CANNOT successfully invoke the DBMS_SPACE.FREE_BLOCKS

procedure on an automatic space management segment.

Answer: C, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-140

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 53-54

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 53

The database was started up using a text parameter file.

What will be the default scope of changes made by using the SET clause of the

ALTER SYSTEM statement?

A. Only MEMORY is updated

B. Only SPFILE values are updated

C. Only init<SID>.ora parameters are updated.

D. Both SPFILE and MEMORY memory values are updated.

E. Both init<SID>.ora parameters and SPFILE values are updated.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 80-83

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 32-37

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 54

Examine the following parameter settings from an initialization (init.ora) file:

DB_CREATE_FILE_DEST = ‘/u01/oradata/’

DB_CREATE_ONLINE_LOG_DEST_1 = ‘/u02/oradata/’

DB_CREATE_ONLINE_LOG_DEST_2 = ‘/u03/oradata/’

If you create an Oracle Managed Files (OMF) database using these settings, what is

the result?

1z0-030

 

– 31 –

A. The data files, temp files, and control file will be on device /u01; one online redo

log group will be on device /u02; another redo log group will be on device /u03.

B. The data files and temp files will be on device /u01; one copy of the control file

and one online redo log group will be on device /u02; another copy of the control

file and a second redo log group will be on device /u03.

C. The data files, temp files, online redo log files, and control file will be on device

/u01; multiplexed copies of the archive log files will be created: one set on device

/u02, and another set on device /u03.

D. The data files and temp files will be on device /u01; one copy of the control file

and the first member in each online redo log group will be on device /u02; another

copy of the control file and a second member of each redo log group will be on

device /u03.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 55

Examine the statement:

SQL> CREATE TABLESPACE user_ data

2> EXTENT MANAGEMENT LOCAL

3> SEGMENT SPACE MANAGEMENT AUTO;

Which two assumptions must be true for this statement to execute successfully?

(Choose two.)

A. Oracle Managed Files are used for this instance.

B. The USER_DATA tablespace is managed using FET$/UET$ tables.

C. The COMPATIBLE initialization parameter must be 9.0.0 or higher.

D. Space within segments in the USER_DATA tablespace is managed with freelists.

Answer: A, C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-140

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 53-54

Chapter 2: Oracle9i Architecture Changes

1z0-030

 

– 32 –

QUESTION NO: 56

The direct handoff connection method to an Oracle9i Shared Server involves fewer

messages than in previous releases of the multithreaded architecture.

Which type of message has been eliminated?

A. The initial request from the client to the listener

B. Messages between the dispatcher and the shared server background processes

C. A redirect message from the listener to the client during client connections

D. Round-trip messages between the listener and the shared server background

processes

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 144-152

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 59-60

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 57

You want to drop the TBS1 tablespace from your database.

You also want to delete the corresponding data files automatically, and not have to

do it manually. What should you do?

A. Use the DROP DATAFILE command

B. Use the DROP TABLESPACE command

C. Ensure that all database files are Oracle Managed Files before using the DROP

TABLESPACE command

D. Ensure that the DB_FILE_CREATE_DEST initialization parameter is set before

using the DROP TABLESPACE command

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 157-158

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features

1z0-030

 

– 33 –

QUESTION NO: 58

Which two statements are true regarding the use of UTF-16 encoding? (Choose

two.)

A. Enables easier loading of multinational data

B. Uses a fixed-width multibyte encoding sequence

C. Asian characters are represented in three characters

D. Uses a variable-width multibyte encoding sequence

E. European characters are represented in one or two bytes

Answer: A, B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 266-278

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 139-146

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 59

Consider this RMAN command:

RMAN> CONFIGURE RETENTION POLICY

2> TO RECOVERY WINDOW OF 7 DAYS;

What does the command accomplish?

A. Establishes the fixed number of backups that must be kept for media recovery

B. Establishes the fixed number of backups required to perform media recovery

within 7 days

C. Sets the fixed number of days that backups are held for media recovery

D. Ensures that sufficient backups and logs will be kept so that a point-in-time

recovery to any time in the last 7 days is possible

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 183-189

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 79-86

Chapter 3: New Oracle9i Availability and Recoverability Features

1z0-030

 

– 34 –

QUESTION NO: 60

The new time and date data types support greater precision for capturing fractional

seconds and time zone information. This gives the Oracle9i database greater

flexibility in supporting locality preferences.

What is the default degree of precision of fractional seconds when specifying the

TIMESTAMP data type?

A. 6 digits

B. 9 digits

C. 12 digits

D. 22 digits

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 266-278

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 139-146

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 61

To assist you in migrating your existing LONG columns to LOB data types, the

ALTER TABLE syntax has been enhanced to support LONG to CLOB migration

and LONG RAW to BLOB migration.

During this migration, if redo logging is enabled for the table or for the LOB being

created, the ALTER TABLE … MODIFY

command temporarily _______ the space requirements.

A. Halves

B. Doubles

C. Triples

D. Quadruples

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 262-263

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 147

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

1z0-030

 

– 35 –

QUESTION NO: 62

Examine the statement:

ALTER TABLE sales

EXCHANGE PARTITION q1_2000

WITH TABLE sales q1_2000

UPDATE GLOBAL INDEXES

PARALLEL (DEGREE 4);

What is the result of the statement?

A. All valid global indexes on the SALES table will retain their USABLE status after

the statement completes.

B. Nonpartitioned indexes on the SALES table will be marked UNUSABLE, while

valid partitioned global and local indexes will retain their USABLE status.

C. Valid indexes on the exchanged table SALES _Q1_2000 will remain in the

USABLE state, while all indexes associated with the new partition Q12000, will

be marked UNUSABLE, including any global indexes on SALES.

D. Global indexes on the SALES table will be maintained concurrently with the

exchange operation if the statement executes with the desired degree of

parallelism (four) otherwise it will execute serially and mark the global indexes

UNUSABLE.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 130-131

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-107

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

QUESTION NO: 63

In a Data Guard switchover operation, which command must you execute on the

database that is being switched from the standby to the primary role?

A. ALTER DATABASE MOUNT STANDBY DATABASE;

B. ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

C. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL

PRIMARY;

D. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL

STANDBY;

1z0-030

 

– 36 –

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 51-74

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 64

Which command can you use to alter the active size of the SGA dynamically, where

the entered value differs from the current one?

A. ALTER SYSTEM SET SGA_MAX_SIZE=500M ;

B. ALTER SYSTEM DROP DB_2K_CHACHE_SIZE ;

C. ALTER SYSTEM SET DB_CACHE_SIZE=AUTO ;

D. ALTER SYSTEM SET SHARED_POOL_SIZE=40M;

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 180-182

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 16-17

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 65

Which two procedures or functions are part of the DBMS_METADATA package?

(Choose two.)

A. GET_DDL

B. GET_XML

C. GET_TYPE

D. GET_VIEW

E. GET_TABLE

Answer: A, B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 136-138

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 44-46

1z0-030

 

– 37 –

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 66

Which statement describes the distinction between Fine-Grained Audit (FGA) and

Fine-Grained Access Control (FGAC)?

A. FGA is an extension of FGAC.

B. FGAC prohibits access by row; FGA enables access by row.

C. Both use Application Context, but only FGAC can use the Secure Application

role.

D. FGA tracks when sensitive rows have been accessed; FGAC prevents access to

sensitive rows.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 21-22

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-139

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 67

You start a multithreaded heterogeneous service agent with this initialization

parameter:

MAX_DISPATCHERS = 10

What is the result?

A. A maximum of ten dispatcher threads can be active in the agent.

B. A maximum of ten sessions can be connected to the agent simultaneously.

C. A maximum of ten concurrent sessions can be connected to each dispatcher.

D. A maximum of ten external dispatcher processes can register with the listener

thread.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 144-152

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 59-60

1z0-030

 

– 38 –

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 68

Which three parameters are ignored when creating an automatic space

management segment? (Choose three.)

A. PCTFREE

B. PCTUSED

C. INITRANS

D. MAXTRANS

E. FREELISTS

F. FREELIST GROUPS

Answer: B, E, F

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-140

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 53-54

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 69

What is the benefit of the Cached Execution Plan feature?

A. SQL statement performance is improved.

B. The SQL statement no longer needs to be cached in the library cache.

C. Improved diagnosability, as the execution plan of a cached SQL statement in

memory is preserved.

D. The execution plan is available for diagnosis after the SQL statement is aged out

of the library cache.

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 217-218

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 185-186

Chapter 6: Oracle9i SQL, PL/SQL New Features

1z0-030

 

– 39 –

QUESTION NO: 70

When querying the new v$spparameter view, the column ISSPECIFIED displays

the value TRUE for a number of parameters. What does this mean?

A. The value for the parameters is TRUE.

B. The parameters are specified in the PFILE parameter file

C. The parameters are specified in the SPFILE parameter file.

D. Changes to the parameter are only in the currently running instance.

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 80-83

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 35-36

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 71

The TYPE clause used in the creation of an external table defines which access

driver is used to convert the data when needed by SQL statements inside the

database. Which type of access driver is supported for external tables?

A. Import

B. ORACLE LOADER

C. Metadata API

D. Direct path export

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 131-134

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 111-116

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

QUESTION NO: 72

You are using automatic space-managed segments, and you wish to change the value

of PCTFREE for a given table.

What can you do to ensure the change is made in the bitmap structure?

A. Use the ALTER command, then execute the dbms_space package.

1z0-030

 

– 40 –

B. Use the ALTER command, then execute the dbms_repair.segment_fix_status

procedure.

C. Use the ALTER command to change the PCTFREE value of automatic spacemanaged

segments.

D. It is not possible to change the PCTFREE value of automatic space-managed

segments.

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-142

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 53-54

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 73

The enterprise data warehouse routinely receives data from various production

OLTP databases. The database block size on the data warehouse is 8192, and in the

OLTP databases it may be 2048, 4096 or 8192. The current SGA is 112 MB.

Oracle9i has the ability to transport tablespaces with different block sizes.

Which two parameters are required in order to “plug in” the OLTP tablespaces

into the warehouse database? (Choose two.)

A. DB_2K_CACHE_SIZE

B. DB_4K_CACHE_SIZE

C. DB_8K_CACHE_SIZE

D. DB_2K_BLOCK_BUFFERS

E. DB_4K_BLOCK_BUFFERS

F. DB_8K_BLOCK_BUFFERS

Answer: A, B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 164-166

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 14-16

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 74

You created a database by using the Database Configuration Assistant

What must you do to access a user account in the sample schemas?

1z0-030

 

– 41 –

A. Create the user account and unlock the account.

B. Create the user account and define a password.

C. Unlock the user account and define a password.

D. Set the 07_DICTIONARY_ACCESSIBILITY parameter to TRUE.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 8-9

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 146-147

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 75

Users complain that SQL statements using a particular index fail. Using

DBVERIFY, you find that two separate blocks in the index have become corrupt.

The database data files for user data and index are very large.

What is the least disruptive recovery strategy available in Oracle9i?

A. Rebuild the index using the online option.

B. Take the individual data file offline (not the whole tablespace), restore and

recover the data file from backup with RMAN, then set the data file online again.

C. Without setting the individual data file offline, use RMAN with Block Media

Recovery to restore and recover only those blocks.

D. Take the individual data file (not the whole tablespace) offline, use RMAN with

Block Media Recovery to restore and recover only those blocks, then set the data

file online again.

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 186

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 84-86

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 76

Which two data types can be converted to LOBs using an ALTER TABLE…

MODIFY command? (Choose two.)

1z0-030

 

– 42 –

A. RAW

B. LONG

C. VARCHAR

D. LONG RAW

Answer: B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 262-263

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 147

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 77

Your company has four DBAs. You need to know which DBA added a column to the

JOB_HISTORY table. The database cannot be offline during work hours. Which

LogMiner command can you use to find out which user made the table change?

A. SELECT username,operation,sql_redo from V$logmnr_contents where

operation=’DDL’;

B. SELECT username,operation,sql_redo from V$logmnr_logs where

operation=’DDL’;

C. You must wait until after hours, shut the database down, and issue the command

EXECUTE DBMS_LOGMNR_D.BUILD to extract the information from the

database.

D. D The task cannot be done. LogMiner does not track DDL commands in Oracle9i.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 100-110

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 68-72

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 78

Which is a restriction on a list partitioned table?

A. You cannot create global range partitioned indexes on the table.

B. The optimizer will not execute partition wise joins on the table’s partitions.

1z0-030

 

– 43 –

C. You must include at least one element in the value list of each partition of the

table, even if that element is the keyword NULL.

D. Partition pruning will not occur during query optimization if a range of values is

included in the query predicate.

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 134-136

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-107

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

QUESTION NO: 79

Consider this syntax:

MERGE INTO tl

USING t2 ON (join predicate)…..

What does the MERGE syntax do?

A. It performs a merge join of the row from T2 only if it doesn’t exist in the T1 table.

B. It creates a natural join of tables T1 and T2 for all columns that have the same

name.

C. It creates a Cartesian product of table T1 and table T2 for all columns that have

the same name.

D. For each row from T2, it updates the row if it exists within table T1, otherwise it

inserts the row into T1.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 253

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 122-123

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

QUESTION NO: 80

How can you make a shared server-side initialization parameter file available to all

instances of a Real Application Clusters database?

1z0-030

 

– 44 –

A. Include an SPFILE parameter in each instance-specific initialization file.

B. Create an SPFILE copy on each client from which the instance will be started.

C. Store the SPFILE in a raw partition with a vendor-specific location and name.

D. Use a single client to start the instances and create the SPFILE on this client.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 227-230

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 188-192

Chapter 7: Oracle9i Real Application Clusters

QUESTION NO: 81

What are three benefits of performing data definition language (DDL) statements

against a partitioned table with the UPDATE GLOBAL INDEXES clause? (Choose

three.)

A. Global indexes are rebuilt automatically at the end of the DDL operation thereby

avoiding problems with the UNUSABLE status.

B. You do not have to search for invalid global indexes after the DDL command

completes and rebuild them individually

C. Global indexes are maintained during the operation of the DDL command and

therefore can be used by any concurrent query

D. Global indexes remain intact and available for use by data manipulation language

(DML) statements even for sessions that have not enabled the skipping of

unusable indexes.

Answer: A, B, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 130-131

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-107

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

QUESTION NO: 82

In the Oracle9i Data Guard architecture, what is the purpose of the Log Transport

Services?

A. To transfer redo log information to one or more destinations

1z0-030

 

– 45 –

B. To apply redo log records sent from the primary database to a standby database at

the receiving location

C. To synchronize changes to the control files on all standby databases with changes

on the primary database when a log switch occurs

D. To batch archived log files on the primary database until a defined number of

checkpoints have been processed and then to distribute the archives to each

standby database

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 51-74

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 83

Examine the command:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY)

COLUMNS;

What does the command accomplish?

A. Creates an additional copy of the database online redo log files

B. Stores the primary key column values of each row involved in a DML operation

in the online redo log files

C. Stores the primary key column values of each row involved in a DML operation

in the supplemental log files

D. Stores the old and new primary key column values of each row involved in a

DML operation only when the primary key is modified in the online redo log files

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 100-110

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 68-72

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 84

1z0-030

 

– 46 –

What does the Metadata Application Programming Interface (API) allow you to do?

A. Repair damaged data dictionary entries

B. Delete data dictionary information about database objects you no longer need

C. Extract data definition commands from the data dictionary in a variety of formats

D. Prepare pseudocode modules for conversion to Java or PL/SQL programs with a

Metadata code generator

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 136-138

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 44-46

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 85

What should you look at first to compute the number of undo blocks written per

second to disk?

A. V$UNDOSTAT

B. V$ROLLSTAT

C. V$TRANSACTION

D. DBA_UNDO_EXTENTS

E. DBA_ROLLBACK_SEGS

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-164

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 24-25

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 86

A bitmap join index is defined as __________.

A. An index used to join two bitmap indexes on a table

B. A bitmap index created for the join of two or more tables

C. A bitmap index created on the join of two or more indexes

D. A bitmap index created on the join of two or more indexed-organized tables

1z0-030

 

– 47 –

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 142-143

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 54-56

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 87

What is the new Oracle Shared Server?

A. An improved version of multithreaded server configuration

B. A connection pooling configuration where several clients are connected to the

same server process.

C. Two or more database servers, which share data by means of database links; the

client software is unaware to which server it is connected

D. A configuration of Real Application Clusters where the client connection is

routed to the least busy instance.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 144-152

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 59-60

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 88

The EMPLOYEES table is stored in the SAMPLE tablespace.

The corresponding IPK_EMP index for the EMPLOYEES table’s primary key is

stored in the INDX tablespace.

Out of 12 partitions, only partition P1 of the SALES table is stored in the SAMPLE

tablespace.

Which objects will be exported by this command?

exp system/manager tablespaces=SAMPLE

A. Only the EMPLOYEES table

B. Only the EMPLOYEES table and its corresponding primary key index

C. The EMPLOYEES table and partition P1

1z0-030

 

– 48 –

D. The EMPLOYEES table, IPK_EMP index, and the SALES table

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 49-51

Chapter 2: Availability Enhancements

QUESTION NO: 89

Oracle9i offers the ability to monitor the dynamic use of the SGA through the

DB_CACHE_ADVICE parameter. When this parameter is set to READY, what is

its effect?

A. The Advisory is on and will capture both CPU and memory overhead.

B. The Advisory is on but the memory for the Advisory is not allocated.

C. The Advisory is off and the memory for the Advisory is not allocated.

D. The Advisory is off but the memory for the Advisory remains allocated.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 180-182

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 16-17

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 90

Which four are true regarding the Workspace Manager in Oracle9i? (Choose four.)

A. Automatically versions all tables

B. Automatically installed with Oracle9i

C. Merges changes with parent rows or discards changes

D. Provides mechanism to identify and resolve conflicts

E. Allows for version enabling tables by use of a packaged procedure

Answer: B, C, D, E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 166-176

Chapter 3: Manageability Enhancements

1z0-030

 

– 49 –

QUESTION NO: 91

Oracle9i provides a database package called dbms_redefinition to perform an online

rebuild of a table.

Which two steps are you recommended to do prior to issuing the

dbms_redefinition_start_redef table procedure call? (Choose two).

A. Grant privileges on the interim table

B. Invoke the dbms_redefinition.can_redef_table procedure

C. Invoke the dbms_redefinition.sync_interim_table procedure

D. Create any triggers, indexes, or constraints on the interim table

E. Create an empty interim table with all the desired characteristics

Answer: B, E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 78-80

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 92

What type of protection is implemented when the DBA issues the ALTER

DATABASE SET STANDBY DATABASE PROTECTED; command?

A. The standby database is protected against write operations

B. The primary database is protected against write operations

C. The primary database is protected against data loss and data divergence

D. The standby database is protected against data loss and data divergence

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 64-72

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 93

An index-organized table (IOT) called SPARSE has had many records deleted.

1z0-030

 

– 50 –

Which command reorganizes the IOT so that the empty space is removed, while

minimizing the impact for users?

A. EXPORT with TABLES and IMPORT with the INDEX and IGNORE options.

B. ALTER TABLE _ COALESCE ;

C. ALTER INDEX _ REBUILD ONLINE ;

D. CREATE TABLE _ AS SELECT _ ONLINE;

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 76-78

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53

Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 94

What does the Character Set Scanner command line utility do?

A. Reports only on any Unicode character conversions required

B. Performs character set conversions to the new specified character set

C. Scans on any character set conversion required, then converts the data

D. Checks for any character definitions that will fail conversion to the new character

set

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 273-275

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 141

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 95

What is true regarding a shared, server-side parameter file for a Real Application

Clusters database?

A. It can contain parameters with distinct values for each instance.

B. It can contain only parameters with identical values for each instance.

C. It must contain an IFILE parameter for each instance’s individual parameter file.

D. It must be located in the default location for the primary instance’s parameter file.

1z0-030

 

– 51 –

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 227-230

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 188-192

Chapter 7: Oracle9i Real Application Clusters

QUESTION NO: 96

What is required for the Character Set Scanner to operate?

A. Oracle Enterprise Manager

B. A special schema installed with csminst.sql

C. A separate tablespace called csscan to store the results

D. A GUI capable workstation, because it uses a Java interface

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 273-275

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 141

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 97

Which two are true regarding a list-partitioned table? (Choose two.)

A. It is possible to partition an index-organized table using the LIST method.

B. It is NOT possible to have multiple columns as the partition key of a listpartitioned

table.

C. The keyword NULL can be specified as a partition literal value for one partition

of a list-partitioned table.

D. The keyword MAXVALUE can be specified as a partition literal value for one

partition of a list-partitioned table.

Answer: B, C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 134-136

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-107

1z0-030

 

– 52 –

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

QUESTION NO: 98

Before adding a nonstandard block size to your database, which preparatory task

must you complete?

A. Define a new buffer pool by setting the DB_nK_CACHE_SIZE parameter where

n is the new block size.

B. Redefine the buffer pool by setting the DB_mK_CACHE_SIZE and

DB_nK_CACHE_SIZE parameters, where m is the standard block size and n is

the new block size.

C. Rebuild the control file using an instance with an initialization file containing two

DB_BLOCK_SIZE parameters, one for the standard and one for the new block

size.

D. Rebuild the control file using an instance with an initialization file containing two

DB_nK_CACHE_SIZE parameters, one for the standard and one for the new

block size.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 164-166

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 14-16

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 99

Oracle Enterprise Manage (OEM) can generate database reports. Which statement

about this feature is true?

A. Generating reports requires a repository.

B. Report generation is scheduled in the job system.

C. The report is placed in a table for suitable selects.

D. The reports are in HTML format for viewing in a browser.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 199-200

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 219-220

1z0-030

 

– 53 –

Chapter 7: Oracle9i Real Application Clusters

QUESTION NO: 100

The Oracle9i LogMiner needs a data dictionary copy to display the object names.

From which three locations can LogMiner retrieve the data dictionary information?

(Choose three.)

A. Separate supplemental log file

B. Information in the control file

C. The current online data dictionary

D. A Data Dictionary copy stored in the redo log file

E. A Data Dictionary copy stored in an operating system flat file

F. Redo information stored within the redo vectors of the DML operation

Answer: C, D, E

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 100-110

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 68-72

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 101

What is true regarding fact tables having bitmap join indexes defined on them?

A. You can create a bitmap join index on a temporary table.

B. You can create a bitmap join index on an index-organized table.

C. Tables participating in the join condition of the bitmap join index definition

cannot be updated concurrently.

D. You cannot have more than one dimension table being part of the join condition

of a bitmap join index definition.

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 142-143

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 54-56

Chapter 2: Oracle9i Architecture Changes

1z0-030

 

– 54 –

QUESTION NO: 102

What are the two methods used to add a second, or subsequent, block size to an

existing database? (Choose two.)

A. Create a new tablespace and include a block size specification.

B. Transport a tablespace with a different block size into the database

C. Re-create the control file to specify a new block size for specific data files

D. Take a tablespace offline, and then put it back online with a new block size

specification.

Answer: C, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 164-166

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 13

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 103

What must you do to enable Automated SQL Execution Memory Management?

A. Execute the DBMS_STATS.GATHER_SYSTEM_STATS procedure with

appropriate values

B. Set the instance parameters PGA_AGGREGATE_TARGET and

WORKAREA_SIZE_POLICY to appropriate values.

C. Set the instance parameters SORT_ AREA_ SIZE,

BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE,

HASH_AREA_SIZE, SORT_AREA_SIZE, and

SORT_AREA_RETAINED_SIZE all to AUTO.

D. Unset the instance parameters SORT_AREA_SIZE,

BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE,

HASH_AREA_SIZE, SORT_AREA_SIZE, and

SORT_AREA_RETAINED_SIZE.

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 176-182

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 17-18

Chapter 1: Oracle9i Database Administration and Management Features

1z0-030

 

– 55 –

QUESTION NO: 104

In order to speed foreign key creation, Oracle9i will cache the first _________

primary key values only where there are multirow DML statements.

A. 32

B. 128

C. 256

D. 512

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 260-261

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 167

Chapter 6: Oracle9i SQL, PL/SQL New Features

QUESTION NO: 105

What statement is true about taking a database default temporary tablespace

offline?

A. You cannot take a default temporary tablespace offline.

B. The database must be quiesced before you perform this action.

C. You must ensure that all users are assigned to an alternative temporary tablespace

first.

D. You must first ensure that the default temporary tablespace has the permanent

tablespace characteristic.

Answer: A

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 158-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 18-19

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 106

You decide to activate the resumable space allocation feature for all your database

users by enabling the feature in a logon trigger.

This causes a statement that requires space beyond a user’s quota ___________.

1z0-030

 

– 56 –

A. To wait indefinitely for the quota to be increased, regardless of the timeout setting

for other space allocation problems.

B. To fail because quota limits do not activate the resumable space allocation feature

C. To wait in a suspended state until the quota is increased or until the timeout value

is reached

D. To invoke the default trigger that resets user quota, if there is free space in the

related tablespace

Answer: C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 47-51

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 25-32

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 107

The Performance Manager, part of Oracle9i Enterprise Manager, can monitor the

performance of which three Oracle Shared Server components? (Choose three.)

A. Listeners

B. Dispatchers

C. Event handler

D. Shared servers

Answer: A, B, C

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 189-201

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 219-220

Chapter 7: Oracle9i Real Application Clusters

QUESTION NO: 108

Which statements are true regarding the new Persistent Configuration feature in

RMAN? (Choose two.)

A. It allows you to set the retention period of backups.

B. It remembers the last backup command you used so that you can reschedule it

easily.

1z0-030

 

– 57 –

C. It enables you to store your Channel attribute settings in the Recovery Catalog

with each script.

D. It enables you to store the settings for Channel attributes so that you do not have

to specify them in each backup or restore command.

Answer: A, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 182-189

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 79-84

Chapter 3: New Oracle9i Availability and Recoverability Features

QUESTION NO: 109

What do you need to do in order for the Database Resource Manager to estimate, in

advance, the execution time of a statement?

A. Set the TIMED_STATISTICS initialization parameter

B. Gather optimizer statistics on related objects.

C. Set the TIMED_OS_STATISTICS initialization parameter.

D. Nothing

Answer: B

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129

Chapter 3: Manageability Enhancements

QUESTION NO: 110

You need to version enable the MORTGAGE table containing customer mortgage

trend information. Which syntax version enables the MORTGAGE table?

A. DBMS_LT.ENABLETABLE(‘MORTGAGE’);

B. DBMS_WM.ENABLE_TABLE(‘MORTGAGE’);

C. DBMS_LT.ENABLEVERSIONING(‘MORTGAGE’);

D. DBMS_WM.ENABLEVERSIONING(‘MORTGAGE’);

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 166-176

Chapter 3: Manageability Enhancements

1z0-030

 

– 58 –

QUESTION NO: 111

During scheduled maintenance for a database that runs 24 hours a day, 7 days a

week, the DBA wants to bring online a larger undo tablespace, smu_tbs2, while

application users are performing DML. The current ONLINE undo tablespace is

smu_tbsl .

The DBA issues the following command:

SQL>ALTER SYSTEM SET undo_tablespace = smu_tbs2;

What happens?

A. Because the database is operating in Automatic Undo Management (AUM) mode,

Oracle will dynamically balance a new rollback usage request across segments in

both smu_tbsl and smu_tbs2.

B. All new transactions will use rollback segments in smu_tbs2, and any existing

sessions with active transactions will get an ORA- 3113 : end-of-file on

communication channel error.

C. The ALTER SYSTEM command will fail if there are sessions with active

transactions, so the DBA must have the database in restricted mode to perform the

operation.

D. All undo segments in the smu_tbsl tablespace will have v$rollstat.status =

‘PENDING OFFLINE’ until all existing transactions have been committed or

rolled back. All new transactions will use smu_tbs2.

E. In order for the application users not to be impacted by the maintenance

operation, the DBA will need to dynamically change undo_managment =

MANUAL in order for users to use rollback segment undo while the undo

tablespace is modified.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-164

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 112

1z0-030

 

– 59 –

When adding a new master site in an Advanced Replication environment without

quiescing replication activity, you can instantiate the database at the new master site

by using one of two methods.

Which two methods can be used to perform this step? (Choose two.)

A. Perform change-based recovery on the new master site.

B. Perform a cancel-based recovery on the new master site

C. Perform a time-based recovery on the new master site.

D. Transport the relevant tablespace set from the master definition site to the new

master site.

E. Perform an export using the FLASHBACK_SCN option on the master definition

site and then import the dump file to the new master database.

Answer: A, D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 83-93

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 147

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 113

What is the meaning of using “character semantics” to create a table?

A. You use only use CHAR data types.

B. All columns are a single character wide.

C. You use single letter names of all columns.

D. You specify the width of a column in characters, not in bytes.

E. You use only character type data types (CHAR, NCHAR, VARCHAR2,

NVARCHAR2, CLOB, NCLOB).

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 275-276

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 139-141

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 114

1z0-030

 

– 60 –

Your database has tablespaces with different block sizes. What must you do to back

these up with RMAN?

A. You need to issue a separate RMAN BACKUP command to group tablespaces

with the same block size.

B. You need to use RMAN BACKUP BLOCKSIZE=nK to select tablespaces that

are not the same size as the standard block size.

C. You can simply issue the RMAN BACKUP command, because a backup set can

contain tablespaces of different block sizes.

D. You must allow for the fact that RMAN will generate several backupsets, one for

each block size, when you issue the RMAN BACKUP command.

Answer: D

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 183-189

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 79-86

Chapter 3: New Oracle9i Availability and Recoverability Features

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