Some good question and answer from 10g OCP Certification Exam Guide part 1

PART1 

Question  1. Which of the following commands will start the iSQL*Plus server process?

(Choose the best answer.)

A. emctl start isqlplus

B. isqlplusctl start

C. isqlplus /nolog

D. lsnrctl start isqlplus

2. You issued the URL http://127.0.0.1:5500/em and received an error. What

could be the problem? (Choose three answers.)

A. You have not started the Database Listener

B. You have not started the dbconsole.

C. The dbconsole is running on a different port.

D. You are not logged onto the database server node.

E. You have not started the Grid Control agent.

F. You have not started the database.

3. Which files must be synchronized for a database to open? (Choose the best

answer.)

A. Datafiles, online redo log files, and the controlfile

B. The parameter file and the password file

C. All the multiplexed controlfile copies

D. None. SMON will synchronize all files by instance recovery after opening

the database.

4. During the transition from NOMOUNT to MOUNT mode, which file or files

are required? (Choose the best answer.)

A. Parameter file

B. Controlfiles

C. Online redo logs

D. Datafiles

E. All of the above

5. You shut down your instance with SHUTDOWN IMMEDIATE. What will

happen on the next startup? (Choose the best answer.)

A. SMON will perform automatic instance recovery.

B. You must perform manual instance recovery.

C. PMON will roll back uncommitted transactions.

D. The database will open without recovery.

6. You have created two databases on your computer and want to use Database

Control to manage them. Which of the following statements are correct?

(Choose two answers.)

A. You cannot use Database Control, because it can manage only one

database per computer.

B. You must use Grid Control, as you have multiple databases on the

computer.

C. You must start one OC4J process and contact it on different ports for each

database.

D. You must start one OC4J instance per database.

E. You must set the ORACLE_SID variable appropriately before starting an

OC4J instance.

7. You issue the command SHUTDOWN, and it seems to hang. What could be

the reason? (Choose the best answer.)

A. You are not connected as SYSDBA or SYSOPER.

B. There are other sessions logged on.

C. You have not connected with operating system or password file

authentication.

D. There are active transactions in the database; when they complete, the

SHUTDOWN will proceed.

8. What action should you take after terminating the instance with SHUTDOWN

ABORT? (Choose the best answer.)

A. Back up the database immediately.

B. Open the database and perform database recovery.

C. Open the database and perform instance recovery.

D. None, but some transactions may be lost.

E. None. Recovery will be automatic.

9. Using Database Control, you stop the Database Listener. Which of the

following statements is true? (Choose the best answer.)

A. Database Control will no longer be able to manage the database.

B. Existing sessions will be terminated.

C. No new sessions can be established.

D. You must restart the listener with the lsnrctl utility.

10. Database Control is a multitier web application. Which tier is responsible for

window management? (Choose the best answer.)

A. The dbconsole middle tier

B. The procedures within the database tier

C. The client browser

D. The OC4J application runtime environment

Answers

1. B. The emctl command is used to start the dbconsole, lsnrctl is used to start

database listeners, and isqlplus from a command line won’t do anything.

2. B, C, and D. Any of these could be a reason. A, E, and F are related to other

processes.

3. A. These are the three file types that make up a database: datafiles, online

redo log files, and the controlfile.

4. B. Remember which files are read at each stage: the parameter file at NOMOUNT,

the online redo log files and the datafiles at OPEN.

5. D. This holds true because IMMEDIATE is a clean shutdown.

6. D and E. D is correct because one OC4J can only support one Database Control

process to manage one database, and E is correct because without this the emctl

utility will not know which OC4J to start.

7. B. This answer is correct because the default shutdown mode is NORMAL,

which will wait for all sessions to log off.

8. E. It is vital to remember this!After a crash, or an abort, recovery is automatic

and unstoppable. No data is ever lost, and you need take no action at all.

9. C. Without a listener there is no way to launch the server process needed for

a session.

10. C. Your local window management is done by your local browser. The other

answers refer to processes that reside on the application server tier (A and D),

or within the database (B).

PART2


Questions
1. Which line of code will cause the following SQL statement to fail? (Choose
the best answer.)
1 CREATE BIGFILE TABLESPACE OCP10gDATA
2 DATAFILE '/oracle/ocp10gdata/ocp10gdata02.dbf'
3 EXTENT MANAGEMENT LOCAL
4 FREELISTS 5
5 NOLOGGING;
A. 1
B. 2
C. 3
D. 4
E. 5
F. The statement will succeed

2. You have mounted the database but did not open it. Which views do you
need to query if you need the locations of all datafiles and the names of
the tablespaces they belong to? (Choose all correct answers.)
A. V$DATAFILE

B. DBA_DATA_FILES
C. V$TABLESPACE
D. DBA_TABLESPACES
E. V$TEMPFILE
F. DBA_TEMP_FILES
G. V$UNDOFILE

3. You attempt to create a tablespace but receive an error that the datafile for the
tablespace cannot be created. The size of the datafile you wanted to create is
3GB, and you specified the SMALLFILE option for the tablespace. You verify
that the operating system directory where the file will reside is owned by the
same user as Oracle and the user has full read/write permissions. You are logged

in to the database as the user SYSTEM, and there is plenty of disk space on the

hard drive. What is the likely cause of the error? (Choose the best answer.)

A. You cannot create a file larger than 2GB in an Oracle database when

specifying SMALLFILE.

B. The operating system cannot create a file larger than 2GB.

C. You must specify the WITH OVERWRITE option for the datafile

specification.

D. You must specify the REUSE option for the datafile specification.

E. You must specify the AUTOEXEND option for the datafile specification.

4. You want to be able to re-create a tablespace quickly in case of failure but do

not have the SQL code to perform the operation. What is the best way to

determine which SQL statement will properly re-create the tablespace with

all options correctly set? (Choose the best answer.)

A. Use the Generate DDL option of iSQL*Plus.

B. Use the Generate DDL option of Enterprise Manager.

C. Use the Create Like option of iSQL*Plus.

D. Use the Create Like option of Enterprise Manager.

E. Query the CODE column of the V$TABLESPACE view.

F. Query the TEXT column of the DBA_TABLESPACES view.

5. Which line of code will cause the following SQL statement to fail? (Choose

the best answer.)

1 CREATE BIGFILE TABLESPACE OCP10gDATA

2 DATAFILE '/oracle/ocp10gdata/ocp10gdata02.dbf'

3 EXTENT MANAGEMENT DICTIONARY

4 FREELISTS 5

5 NOLOGGING;

A. 1

B. 2

C. 3

D. 4

E. 5

F. The statement will succeed

6. You determine that a datafile belonging to your ARCHIVE2002 tablespace

is too large. You want to reduce the size of the datafile so that disk space is

not wasted. This tablespace will not have any data added to it. When you

use Enterprise Manager to reduce the size of the datafile belonging to the

tablespace, you receive an error. What is the most likely cause? (Choose

the best answer.)

A. You cannot reduce the size of datafiles in Oracle.

B. You cannot reduce the size of datafiles using Enterprise Manager.

C. You do not have sufficient permissions to reduce the size of the file.

D. The file does not exist.

E. The file contains data beyond the size you want to reduce the file to.

7. You issue the following command to drop a tablespace and receive an error

indicating that the tablespace cannot be dropped. What is the likely cause?

(Choose the best answer.)

DROP TABLESPACE SYSAUX INCLUDING CONTENTS CASCADE CONSTRAINTS;

A. System tablespaces cannot be dropped.

B. You do not have permissions to drop the SYSAUX tablespace.

C. Objects in other tablespaces depend on objects in the tablespace being

dropped.

D. You cannot drop objects in the tablespace that you did not create.

E. The command should succeed.

8. You want to change extent management on your DATA09 tablespace from local

to dictionary to match the other tablespaces in the DATA01–DATA08 range.

Which method can be used to make this change? (Choose the best answer.)

A. DBMS_SPACE_ADMIN.TABLESPACE_DICTIONARY_MANAGED

B. DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_DICITONARY

C. Enterprise Manager

D. ALTER TABLESPACE DATA09 EXTENT MANAGEMENT DICTIONARY

E. You cannot convert a locally managed tablespace to dictionary

management

9. What permissions are required to create a tablespace? (Choose all correct

answers.)

A. CREATE TABLESPACE

B. MANAGE DATABASE

C. DBA

D. SYSDBA

E. SYSOPER

10. What types of segments can tablespaces in Oracle Database 10g store?

(Choose all correct answers.)

A. Tables

B. Sort segments

C. Redo segments

D. Undo segments

E. DBA segments

F. Clusters

Answers

1. D. When specifying local extent management in the creation of a tablespace,

you cannot specify a dictionary-managed segment storage parameter as well.

FREELISTS can be specified only when extent management is dictionary,

so that part of the CREATE TABLESPACE statement would cause the entire

statement to fail.

2. A, C, and E. Because the database is not yet in an OPEN state, any of the

DBA_ data dictionary views are not yet accessible; they can be queries only

when the database is in an open state. The V$TABLESPACE view will provide

you a list of tablespaces. The V$DATAFILE and V$TEMPFILE views can be

joined to the V$TABLESPACE view using the TS# column to retrieve a list

of all tablespaces and their datafiles. There is no V$UNDOFILE view.

3. D. The most likely reason that you are receiving an error on the creation of

the datafile for the tablespace is that a file with that name already exists. To

correct this, you must specify the REUSE option on the datafile specification

to have Oracle overwrite the existing file (or delete the file manually from the

hard disk). Neither Oracle nor the operating system will prevent a file of 3GB

being created for a SMALLFILE tablespace.

4. B. The Generate DDL option of Enterprise Manager will generate the SQL

code to re-create the selected tablespace. You can then cut and paste this into

a SQL script for later execution. There is no Generate or Create Like option

in iSQL*Plus, and the Create Like option of Enterprise Manager will display

a new page to enter the name of a tablespace with the same parameters as

the one selected. There is no CODE column in the V$TABLESPACE or TEXT

column in DBA_TABLESPACES.

5. C. In order to create a BIGFILE tablespace, you must specify local extent

management and automatic segment space management. You cannot create

a dictionary-managed BIGFILE tablespace.

6. E. Oracle allows you to reduce the size of datafiles, and this can be

accomplished from the command line or using Enterprise Manager. If

you are able to change the size of the file in Enterprise Manager, you have

the necessary permissions to perform the action. The most likely reason

you are unable to perform the action is that more data exists in the datafile

than the size you want to reduce it to. Specify a larger value and try again.

7. A. SYSAUX is a system tablespace and cannot be dropped. The same holds

true for the SYSTEM tablespace.

8. E. Once a tablespace is made locally managed, it is not possible to convert

it to a dictionary-managed tablespace. There is no command or Enterprise

Manager option to perform the change.

9. A and C. You must be granted either the CREATE TABLESPACE system

permission or the DBA role to create a tablespace. There is no MANAGE

DATABASE permission. The SYSOPER and SYSDBA roles provide permissions

for managing the instance and database but do not specifically grant the

holder the permission to create a tablespace.

10. A, B, D, and F. Tablespaces in Oracle can store tables, clusters (both are types

of permanent segments, which also include indexes, partitions, and others),

undo segments, and sort segments (a type of temporary segment). Redo

segments and DBA segments do not exist. Redo is stored in the redo log files.



PART3

Questions


1. You have created a new user called George. You have assigned George the
RESTRICTED_ACCESS user profile. The profile settings have all limits
configured as DEFAULT except for a concurrent sessions limit whose value is
set to 1. George asks you to help with a problem, and when you arrive at his
desk, you notice that he has several SQL*Plus sessions open, all of which are
connected to the instance with his user account. You suspect that your profile
limits are not being enforced. Which of the following should you investigate
to determine the cause of the problem? (Choose two correct answers.)
A. DBA_USER_LIMITS table
B. RESOURCE_LIMIT initialization parameter
C. DBA_PROFILE_LIMITS data dictionary view
D. DBA_PROFILES data dictionary view
E. V$LIMITS dynamic performance view


2. Which line of code will cause the following SQL statement to fail? (Choose
the best answer.)
1 CREATE USER Sam
2 IDENTIFIED EXTERNALLY BY $amP@ssw0rd
3 DEFAULT TABLESPACE Users
4 TEMPORARY TABLESPACE User_Temp
5 QUOTA 2048MB ON APPS_DATA
A. 1
B. 2
C. 3
D. 4
E. 5
F. The statement will succeed

3. You have been asked to provide additional information to your manager on
how system privileges can be assigned and behave. Which of the following
statements about system privileges are true? (Choose all correct answers.)
A. System privileges can be granted to others if you use the WITH ADMIN
OPTION.
B. System privileges can be granted to others if you use the WITH GRANT
OPTION.
C. Only the DBA can grant system privileges, since the DBA owns the database.
D. System privileges can be granted only by the owner of the database.

E. When revoked for a user, system privileges will also be revoked for any
user to whom that revokee granted them.
F. When revoked for a user, system privileges will not be revoked for any user
to whom that revokee granted them.

4. How would a user change his active profile? (Choose the best answer.)
A. ALTER USER SET PROFILE=NewProfile
B. ALTER SYSTEM SET PROFILE=NewProfile
C. ALTER SESSION SET PROFILE=NewProfile
D.
ALTER DATABASE SET PROFILE=NewProfile
E. A user cannot change his active profile

5. If you create a profile and specify limits for only some of the profile settings,
what value will be automatically assigned to any resource limit you do not

include in your CREATE PROFILE statement? (Choose the best answer.)

A. DEFAULT

B. 0

C. UNLIMITED

D. UNKNOWN

E. You must specify a value for all profile limits

6. If you do not specify a TEMPORARY TABLESPACE when creating a new user

account, what will be the value of this parameter when the user is created?

(Choose the best answer.)

A. SYSTEM

B. TEMP

C. NULL

D. Database default temporary tablespace

E. You must specify a value for TEMPORARY TABLESPACE

7. Which of the following commands can a new user called Anthony issue after

successfully connecting to the instance and establishing a user session? (Choose

all correct answers.)

A. ALTER USER Anthony PAS

B. ALTER USER Anthony QUOTA 2M ON SYSTEM;

C. ALTER USER Anthony ACCOUNT LOCK;

D. ALTER USER Anthony TEMPORARY TABLESPACE TEMP;

E. ALTER USER Anthony IDENTIFIED BY NEWPASS;

8. While passing by Benjamin’s desk, you notice that he is using SQL*Plus to

query data in your TempOrders table. You did not grant Benjamin privileges

to issue SELECT statements against your TempOrders table. Why is Benjamin

able to query your table? (Choose all correct answers.)

A. A user to whom you granted the SELECT privilege on the table also granted

it to Benjamin.

B. Benjamin is a DBA and can query any table in the database.

C. You granted Benjamin the UPDATE privilege on the TempOrders table,

which automatically grants the SELECT privilege.

D. Benjamin has been granted the SELECT ANY TABLE privilege by the DBA.

E. Benjamin has been granted the SELECT privilege on your TempOrders

table by a user to whom you granted the SELECT privilege WITH ADMIN

OPTION.

9. Which of the following statements will fail when granting privileges to the

role QueryRole? (Choose the best answer.)

A. GRANT CONNECT TO QueryRole;

B. GRANT CONNECT TO QueryRole WITH ADMIN OPTION;

C. GRANT SELECT ON Orders TO QueryRole;

D. GRANT SELECT ON Orders TO QueryRole WITH GRANT OPTION;

E. GRANT DBA TO QueryRole WITH ADMIN OPTION;

10. When creating a user using Enterprise Manager instead of the CREATE USER

statement, which additional privileges are granted to the user? (Choose the

best answer.)

A. SELECT_CATALOG_ROLE

B. SYSDBA

C. CONNECT

D. RESOURCE

E. DBA

Answers

1. B and D. You can query the DBA_PROFILES data dictionary view to determine

the settings of all profiles in the database, including the one George is supposed

to have assigned to him. The RESOURCE_LIMIT initialization parameter must

be set to TRUE for the concurrent sessions limit to be enforced, so you need

to verify this setting in the running instance and in the SPFILE. DBA_USER_

LIMITS, DBA_PROFILE_LIMITS, and V$LIMITS views do not exist.

2. B. When you create a user, you can specify at most one authentication

method. This SQL statement specified both EXTERNALLY and password

(BY $amP@ssw0rd) authentication at the same time, which would generate

an error when the command was executed.

3. A and F. If you have been granted a system privilege WITH ADMIN OPTION,

you can also grant the same privilege to others and specify the WITH ADMIN

OPTION. The WITH GRANT OPTION applies to object privileges and not

system privileges. If system privileges are revoked for a user to whom they

were granted WITH ADMIN OPTION, they will not be revoked for other

users to whom the revoke granted them; there are no cascading revokes for

system privileges. The DBA is a role and is not the only one that can grant

system privileges, and neither is the user SYS (the database owner), in that

anyone holding the DBA role can grant them, since the DBA role has system

privileges granted to it WITH ADMIN OPTION.

4. E. A user cannot himself change his active profile. Only the DBA can modify

a user’s profile or the profile limits.

5. A. If you do not specify a value for a limit in your profile, it will automatically

be assigned the value of DEFAULT. This means that Oracle will enforce whatever

value exists for the limit in the DEFAULT profile.

6. D. The currently defined database default temporary tablespace will be assigned

to the user if you do not specify a TEMPORARY TABLESPACE for the user. If

no database default temporary tablespace is defined, the user will be assigned

SYSTEM as the temporary tablespace.

7. E. Anthony, like any new user who has been configured with password

authentication, has the ability to change his password at any time. No other

changes to the user account are allowed to be performed by the user himself.

8. B and D. Benjamin can query your TempOrders table only if he is a DBA, who

automatically has the SELECT ANY TABLE privilege, or if Benjamin has been

granted the SELECT ANY TABLE privilege by the DBA. Another user to whom

you only granted the SELECT privilege could not grant it to Benjamin unless

you specified the WITH GRANT OPTION. Granting the UPDATE privilege on

the table does not automatically grant the SELECT privilege.

9. D. It is not possible to grant object privileges to roles WITH GRANT OPTION,

so this statement will fail. All other statements will succeed, assuming you

have the privileges required to perform the operation.

10. C. Using Enterprise Manager to create a user will automatically grant the user

the CONNECT role, which includes the CREATE SESSION privilege, so the

user is able to connect to the instance right away. This role also includes the

privileges to create tables, views, synonyms, sequences, and other database

objects, which may or may not be desired for all users.