OCA Oracle 10g Ch8 - Managing Consistency and Concurrency

Ch8 - Managing Consistency and Concurrency

Review Questions

1. What will be the salary of employee number 189 at the completion of
the following SQL statements?
update emp set salary = 1000 where employee_num = 189;
savepoint save_1;
update emp set salary = salary * 1.1 where employee_num = 189;
savepoint save_2;
update emp set salary = salary * 1.1 where employee_num = 189;
savepoint save_3;
rollback to savepoint save_2;
commit;
update emp set salary = 1500 where employee_num = 189;
savepoint save_4;
rollback to save_4;
commit;

A. 1000
B. 1100
C. 1111
D. 1500
---
Ans: D.
The last ROLLBACK statement rolls back all DML statements since
SAVEPOINT SAVE_4; the
last UPDATE was executed before the SAVEPOINT to SAVE_4, therefore the
change made by
the last UPDATE is unchanged, and the salary remains 1500.

2. Which of the following commands returns an error if the transaction
starts with SET TRANSACTION
READ ONLY?
A. ALTER SYSTEM
B. SELECT
C. ALTER USER
D. SET ROLE
---
Ans: C.
The ALTER USER command changes data, even though it resides in the data
dictionary; no
data in a table can be changed in a READ ONLY transaction.

3. Which of the following commands is most likely to generate an error
message? (Choose two.)
A. ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=MEMORY;
B. ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
C. ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=MEMORY;
D. ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=SPFILE;
E. ALTER SYSTEM SET UNDO_TABLESPACE=RBS1 SCOPE=BOTH;
---
Ans: A, C.
You cannot dynamically change the parameter UNDO_MANAGEMENT after the
instance has
started. You can, however, change the UNDO_TABLESPACE parameter to
switch to another undo
tablespace while the instance is up and running.

4. Guaranteed undo retention can be specified for which of the following
objects?
A. A tablespace
B. A table
C. The database
D. A transaction
E. The instance
---
Ans: A. Guaranteed undo retention can be set at the tablespace level by
using the RETENTION
GUARANTEE clause with either the CREATE TABLESPACE or ALTER TABLESPACE
command. Only
undo tablespaces can have this attribute.

5. Which dynamic performance view can help you adjust the size of an
undo tablespace?
A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$SESSION
D. V$ROLLNAME
---
Ans A.
When database activity is at its peak, the V$UNDOSTAT view, in
conjunction with the value for
UNDO_RETENTION and DB_BLOCK_SIZE, can be used to calculate an optimal
undo tablespace
size. Also, the Undo Advisor in the EM Database Control can provide the
same optimal
tablespace size in a GUI environment.

6. Which of the following lock modes permits concurrent queries on a
table but prohibits updates
to the locked table?
A. ROW SHARE
B. ROW EXCLUSIVE
C. EXCLUSIVE
D. SHARE ROW EXCLUSIVE
E. SHARE
----
Ans: E.
SHARE mode permits concurrent queries but prohibits updates to the
locked table. SHARE
mode is required to create an index on the table.

7. The highest level at which a user can request a lock is the ________
level.
A. Schema
B. Table
C. Row
D. Block
---
Ans: B.
The highest level at which a user can request a lock is the table level;
the only other lock level
available to a user is a row level lock. Users cannot lock at the block
or schema level.

8. In the following scenario, two different transactions are updating
rows in the same table. What
happens at 11:45? (Choose the best answer.)

A. One of the users calls the DBA who immediately kills one of the
sessions holding the lock.
B. The transactions in both Session 1 and Session 2 are both rolled back
after both sessions
receive an ORA-00060: Deadlock detected while waiting for resource
message, and
the statements in both transactions must be re-executed, but no other
work is lost.
C. Both Session 1 and Session 2 are killed by Oracle with an ORA-00028:
Your session has
been killed message and must redo all other statements executed since
the last COMMIT.
D. Session 1 generates an ORA-00060: Deadlock detected while waiting for
resource
message and rolls back the transaction. The user in Session 2 is then
free to roll back or commit
their transaction.

Session 1 Time Session 2
------------------------------------------------------------------
update employees set salary = 11:29 update employees set
manager =
salary * 1.2 where employee_id 100 where employee_id
= 109;
= 102;

update employees set salary = 11:44 update employees set
manager =
salary * 1.2 where employee_id 100 where employee_id
= 102;
= 109;

? 11:45 ?

-------------

Ans: D.
At 11:45, both sessions are waiting for the row locked by the other
session. Within a short
but predetermined amount of time, Oracle rolls back the statement that
detected the deadlock,
which could be either session and is not dependent on when each of the
transactions started or
attempted to update rows locked by other users.

9. To retrieve the rollback segment name assigned to a transaction, you
can join the dynamic performance
view V$TRANSACTION to which other dynamic performance view?
A. V$ROLLSTAT
B. V$ROLLNAME
C. V$UNDOSTAT
D. V$TRANSACTION_ENQUEUE
---
Ans: B.
The column XIDUSN in the view V$TRANSACTION can be joined with the
column USN in
V$ROLLNAME to retrieve the column NAME in V$ROLLNAME containing the
rollback segment name.

10. Select the statement that is not true regarding undo tablespaces.
A. Undo tablespaces will not be created if they are not specified in the
CREATE DATABASE command.
B. Two undo tablespaces can be active if a new undo tablespace was
specified and the old one
contains pending transactions.
C. You can switch from one undo tablespace to another while the database
is online.
D. UNDO_MANAGEMENT cannot be changed dynamically while the instance is
running.
---
Ans: A.
If an undo tablespace is not explicitly created in the CREATE DATABASE
command, Oracle
automatically creates one with the name SYS_UNDOTBS.

11. To resolve a lock conflict, which of the following methods can you
use? (Choose two.)
A. Oracle automatically resolves the lock after a short but predefined
time period by killing the
session that is holding the lock.
B. The DBA can kill the session holding the lock.
C. The user can either roll back or commit the transaction that is
holding the lock.
D. Oracle automatically resolves the lock after a short but predefined
period by killing the session
that is requesting the lock.
---
Ans: B, C.
Locks are resolved at the user level by either committing or rolling
back the transaction
holding the lock. Also, the DBA can kill the session holding the lock as
a last resort.

12. If all extents in an undo segment fill up, which of the following
occurs next? (Choose all that apply.)
A. A new extent is allocated in the undo segment if all existing extents
still contain active transaction
data.
B. Other transactions using the segment are moved to another existing
segment with enough
free space.
C. A new undo segment is created, and the transaction that filled up the
undo segment is moved
in its entirety to another undo segment.
D. The first extent in the segment is reused if the undo data in the
first extent is not needed.
E. The transaction that filled up the undo segment spills over to
another undo segment.
---
Ans: A, D.
If a transaction fills up an undo segment, either a new extent is
allocated for the undo segment
or other extents in the segment are reused if the undo data in those
extents is no longer
needed by other transactions using the same undo segment. Transactions
cannot cross segment
boundaries in an undo tablespace nor can they move to another segment.

13. Which of the following commands returns control to the user
immediately if a table is already
locked by another user?
A. LOCK TABLE HR.EMPLOYEES IN EXCLUSIVE MODE WAIT DEFERRED;
B. LOCK TABLE HR.EMPLOYEES IN SHARE MODE NOWAIT;
C. LOCK TABLE HR.EMPLOYEES IN SHARE MODE WAIT DISABLED;
D. LOCK TABLE HR.EMPLOYEES IN EXCLUSIVE MODE NOWAIT DEFERRED;
---
Ans: B.
Regardless of the type of lock requested, NOWAIT is required if you want
the command with
the lock request to terminate immediately if a lock is already held on
the table.

14. Two transactions occur at the wall clock times in the following
table. What happens at 10:05?

Session 1 Time Session 2
update customer set region = 'H' 9:51
where state='WI' and
county='GRANT';

9:59 update
customer set mgr=201
where
state='IA' and

county='JOHNSON';

update customer set region='H' 10:01
where state='IA' and
county='JOHNSON';

10:05 update
customer set mgr=201
where
state='WI' and

county='GRANT';

A. Session 2 will wait for Session 1 to commit or roll back.
B. Session 1 will wait for Session 2 to commit or roll back.
C. A deadlock will occur, and both sessions will hang unless one of the
users cancels their statement
or the DBA kills one of the sessions.
D. A deadlock will occur, and Oracle will cancel one of the statements.
E. Neither session is updating the same column, so no waiting or
deadlock will occur.
---
Ans: D.
At 10:01, Session 1 waits for Session 2. At 10:05, a deadlock will
occur; Oracle detects the
deadlock and cancels one of the statements.

15. Undo information falls into all the following categories except for
which of the following?
A. Uncommitted undo information
B. Undo information required in case an instance crash requires a roll
forward operation when
the instance is restarted
C. Committed undo information required to satisfy the undo retention
interval
D. Expired undo information that is no longer needed to support a
running transaction
---
Ans: B.
Undo information is required for instance recovery, but only to roll
back uncommitted transactions
after the online redo logs roll forward.

16. Undo segments are owned by which user?
A. SYSTEM
B. The user that initiated the transaction
C. SYS
D. The user that owns the object changed by the transaction
----
Ans: C.
Undo segments are always owned by SYS.

17. Undo data in an undo tablespace is not used for which of the
following purposes?
A. Providing users with read-consistent queries
B. Rolling forward after an instance failure
C. Flashback queries
D. Recovering from a failed transaction
E. Restoring original data when a ROLLBACK is issued
---
Ans: B.
The online redo log files are used to roll forward after an instance
failure; undo data is used
to roll back any uncommitted transactions.

18. Which dynamic performance view shows which transactions are assigned
to which undo segment
in the undo tablespace?
A. V$TRANSACTION
B. V$ROLLSTAT
C. V$SESSION
D. V$UNDOSTAT
---
Ans: A.
The dynamic performance view V$TRANSACTION contains the column XIDUSN,
which is the
undo segment number in the current undo tablespace.

19. The user SCOTT runs a query at 8:25 A.M. that receives an ORA-01555:
Snapshot too old error
after running for 15 minutes. An alert is sent to the DBA that the undo
tablespace is incorrectly
sized. At 10:15 A.M., the DBA checks the initialization parameter
UNDO_RETENTION, and its value
is 3600; the parameter is sized correctly. The DBA doubles the size of
the undo tablespace by adding
a second datafile. At 1:15 P.M., the user SCOTT runs the same query and
once again receives an
ORA-01555: Snapshot too old error. What happens next? (Choose the best
answer.)
A. The DBA receives another alert indicating that the undo tablespace is
still undersized.
B. The user SCOTT calls the DBA to report that the query is still
failing.
C. The second datafile autoextends so that future queries will have
enough undo to complete
when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds
another datafile to the
undo tablespace, and then the query runs to completion.

Ans: B.
Even if the size of the undo tablespace is adjusted after an undo space
problem, only one alert
is sent for each 24-hour period. Therefore, the only way that the
problem will be resolved
promptly is for SCOTT to call the DBA, as the DBA will not receive
another alert until the next
day when another query fails.

20. The EM Database Control Undo Advisor screen uses ___________ to
recommend the new size
of the undo tablespace.
A. The value of the parameter UNDO_RETENTION
B. The number of Snapshot too old errors
C. The current size of the undo tablespace
D. The desired amount of time to retain undo data
E. The most recent undo generation rate
----
Ans: D. The Undo Advisor screen uses the desired time period for undo
data retention and analyzes

OCA Oracle 10g -- ch7. Managing Data with SQL, PL/SQL, and Utilities

7. Managing Data with SQL, PL/SQL, and Utilities
================================================
Review Questions

1. Which of the following conditions prevents you from being able to
insert into a view?
A. A TO_NUMBER function on one of the base table columns
B. A CONNECT BY clause in the view definition
C. A column of type RAW
D. All of the above

----
Ans: B
B. You cannot insert into a view that contains a CONNECT BY, ORDER BY,
or GROUP BY clause.

2. Changes made with an UPDATE statement are made permanent in the
database after what occurs?
A. DBWR flushes the changes to disk.
B. You issue a SAVEPOINT statement.
C. You issue a COMMIT statement.
D. A checkpoint occurs.
---
Ans: C
A commit makes pending DML changes permanent. When a checkpoint occurs,
DBWR
flushes dirty buffers to disk, which is independent of transaction
boundaries.

3. Which of the following is not a PL/SQL program?
A. Library
B. Trigger
C. Function
D. Procedure
---
Ans: A.
A library can be a database object, but is associated with an operating
system shared library.
PL/SQL programs include procedures, functions, packages, package bodies,
and triggers.

4. Why would you execute a CREATE OR REPLACE PROCEDURE statement instead
of a DROP
PROCEDURE and a CREATE PROCEDURE?
A. It is less typing.
B. There is no difference between the two.
C. CREATE OR REPLACE PROCEDURE does not invalidate dependent objects.
D. DROP PROCEDURE and CREATE PROCEDURE require regranting of privileges.
----
Ans: D.
Using CREATE OR REPLACE PROCEDURE is less typing, but, more importantly,
when you drop
an object, all privileges granted on that object are dropped as well.
When you perform a CREATE
OR REPLACE, you do not lose privileges granted on that object.

5. Which of the following is not a trigger event?
A. UPDATE
B. SELECT
C. NOAUDIT
D. SERVERERROR
---
Ans: B.
You can create a trigger for just about any database event that involves
a change to data, but
you cannot create a SELECT trigger in Oracle10g.

6. Which of the following statements can be used to enable triggers?
A. ALTER SYSTEM
B. ALTER TABLE
C. ALTER USER
D. ALTER PACKAGE
---
Ans: B.
You can enable triggers with either an ALTER TRIGGER ENABLE statement or
with an ALTER
TABLE ENABLE ALL TRIGGERS statement.

7. Which of the following is true?
A. Directories are like roles or profiles; they are not owned by any
individual user.
B. Directories are like synonyms or database links; they can be public
or private.
C. Directories are like triggers and packages; they are owned by a
schema.
D. You can grant SELECT and UPDATE object privileges on directories.
----
Ans A.
The READ and WRITE object privileges are applicable to directory
objects; SELECT and UPDATE
are not. Directory objects are not owned by any individual schema, so
they are like roles and
profiles in this regard, not triggers and packages.

8. Which of the following keywords is used with a Data Pump import to
copy data from one
schema to another?
A. fromuser and touser
B. source_schema and target_schema
C. rename_schema
D. remap_schema
--
Ans D.
The keywords fromuser and touser are from the older imp import program.
Data Pump
uses remap_schema (not rename_schema) to map a source to a target
schema.

9. The application development manager asks you to make sure the new
Oracle10g database that
her team will use has PL/SQL warning enabled and the optimizing compiler
set to maximum
optimization. What do you have to do to satisfy these requirements?
A. Nothing. They are the default settings.
B. The optimizing compiler is set to maximum by default, but you have to
set the initialization
parameter plsql_warnings to ENABLE:ALL.
C. plsql_warnings is set to maximum by default, but you need to set
plsql_optimize_mode =2
to increase it to maximum.
D. You need to set both plsql_warnings and plsql_optimize_mode to
nondefault settings.
---
Ans: B.
The default settings are to disable plsql_warnings and enable maximum
optimization (level 2).

10. Which of the following is not a mode for Data Pump export?
A. Database
B. Tablespace
C. Table
D. Metadata
---
Ans D.
Data Pump export modes include database, tablespace, table, and schema.
The keyword
metadata is used in conjunction with the content parameter. To export
only metadata, you
specify content=metadata.

11. To perform a Data Pump import from a live database, which parameter
needs to be set?
A. db_link
B. network_link
C. dumpfile
D. directory
---
Ans: B
The network_link parameter specifies a database link to the source
database.

12. You perform nightly metadata-only Data Pump exports of your
development database. One
morning, you get a frantic call from a developer who accidentally
dropped a package he had
been working on for the past week. Which Data Pump import parameter will
you need to extract
the package DDL from the dump file?
A. show
B. sqlfile
C. reuse_datafiles
D. tablespaces
----
Ans: B. To extract the package DDL, you need to specify a sqlfile= and
an include parameter.

13. What will the following Data Pump import command do?
impdp system/password network_link=sys_test
schemas="GL" remap_schema="GL:GLI" content=both
nologfile=y include=TABLE:"LIKE 'PROD%'"
A. Copy the GL tables whose names start with PROD from the sys_test
database, placing them
into the GLI schema.
B. Copy the GLI tables whose names start with PROD from the sys_test
database, placing
them into the GL schema.
C. Load the data in file sys_test.dmp into the table GLI.products.
D. Copy the metadata from user GL in sys_test into the local database.
---
Ans: A
A. The remap_schema parameter defines the source and target schemas, and
the include
parameter identifies which objects to include in the import job.

14. Your requirements call for loading a lot of data from a flat file
dumped from the mainframe into
the orders table at night while a few users in Singapore enter orders
interactively. You are using
SQL*Loader to load the data, but it takes longer than desired. What
might you do to speed up
the process?
A. Switch to direct path mode.
B. Increase the bind size.
C. Write a Java program to parse and load the data.
D. Write a C program to parse and load the data.
----
Ans: B.
Writing your own Java or C program to do the same thing as SQL*Loader
would be a costly
decision in terms of development effort. Getting your own program to
outperform SQL*Loader
would be difficult at best. Direct path loading would certainly speed up
the load, but would lock
the table exclusively, prohibiting your Singapore users from entering
data interactively. Increasing
the bind size would allow SQL*Loader to make fewer database calls,
resulting in fewer network
round-trip communications and potentially improving the performance. The
key concept
to grasp with this question is that direct path loads lock the table
exclusively; therefore they
should not be used on active tables.

15. Which type of PL/SQL program can be called in the select list of a
SQL statement?
A. Trigger
B. Procedure
C. Function
D. None of the above
--
Ans: C.
Functions can be called in several places, including in the SELECT,
WHERE, ORDER BY, and
GROUP BY clauses of SQL statements. Triggers are invoked when their
trigger event fires. Procedures
are invoked as stand-alone statements.

16. You need to let an application role execute the SLEEP procedure in
the DBMS_LOCK package,
but do not want to let an application role have access to the other more
powerful capabilities of
the DBMS_LOCK package. How can you satisfy these requirements best?
A. Grant EXECUTE on dbms_lock to user system. Then create a procedure in
the system schema
that calls DBMS_LOCK.SLEEP. Finally, grant EXECUTE on this procedure to
the application role.
B. Grant EXECUTE on DBMS_LOCK to the application role.
C. Grant EXECUTE on DMBS_LOCK.SLEEP to the application role.
D. Write your own procedure to mimic the functionality of the
DBMS_LOCK.SLEEP procedure.
---
Ans: A.
You cannot grant privileges on only one packaged procedure. You can only
grant EXECUTE
on the whole package. To be more restrictive in granting privileges, you
need to create an intermediate
procedure that calls the single procedure you want and grant EXECUTE on
that intermediate
procedure to the grantee.

17. Which of the following triggering events fire when statistics are
gathered on a table?
A. ANALYZE and DDL
B. ASSOCIATE STATISTICS and ANALYZE
C. GATHER STATISTICS and DDL
D. ASSOCIATE STATISTICS and DISASSOCIATE STATISTICS
---
Ans: A.
The ASSOCIATE STATISTICS and DISASSOCIATE STATISTICS events fire when an
ASSOCIATE
STATISTICS or DISASSOCIATE STATISTICS statement is executed, not when
statistics are gathered.
There is no GATHER STATISTICS triggering event. The triggering events
that will fire when
statistics are gathered on a table are the ANALYZE and DDL events.

18. Which Data Pump export mode captures user definitions?
A. Table mode
B. Tablespace mode
C. DBA mode
D. Schema mode
--
Ans: D.
Table and tablespace modes do not capture user account definitions.
Schema mode can, and
database mode always captures user definitions. There is no DBA mode.

19. You need to copy the GL schema from production to qa_test, changing
the tablespace for
indexes from gl_index to fin_indx. What is the best way to satisfy these
requirements?
A. First, use Data Pump to copy the schema without indexes. Then, change
the default tablespace
for user GL in qa_test to fin_indx. Next, use Data Pump to copy the
indexes. Finally, change
the default tablespace for user GL back to gl_data.
B. Use the dbms_metadata package to extract table and index DDL. Then,
use Notepad (or
sed) to edit this DDL, changing the tablespace for the indexes. Finally,
run the DDL in the
qa_test database.
C. Use Data Pump import, specifying a remap_datafile parameter to change
the datafile location
for indexes.
D. Use Data Pump import specifying a remap_tablespace parameter to
change the tablespace
location for indexes.
----
Ans: D.
Options A and B are a lot of work. The remap_datafile parameter applies
only to CREATE
TABLESPACE and CREATE DIRECTORY statements, not indexes. The
remap_tablespace parameter
tells Data Pump Import to change the tablespace that objects are stored
in between the source and
the target database.

20. Which of the following INSERT statements raises an exception?
A. INSERT INTO ORDERS SELECT * FROM STANDING_ORDERS
B. INSERT FIRST WHEN ORDER_TYPE IN (2,5,12) THEN INSERT INTO ORDERS
SELECT *
FROM STANDING_ORDERS
C. INSERT FIRST WHEN ORDER_TYPE IN (2,5,12) THEN INTO ORDERS SELECT *
FROM
STANDING_ORDERS
D. INSERT INTO ALL WHEN ORDER_TYPE IN (2,5,12) THEN INTO ORDERS SELECT *
FROM
STANDING_ORDERS
-----
Ans: D.
Single table inserts must begin with the keywords INSERT INTO and cannot
contain the keywords
THEN INTO. Multitable INSERT statements cannot begin with the keywords
INSERT INTO
and may contain the keywords THEN INTO. Option D contains an invalid
combination of keywords
and will thus raise an exception.

OCA Oracle 10g Ch6 User Administration and Security

OCA Oracle 10g Ch6 User Administration and Security

Review Questions

1. Which of the following statements creates an Oracle account, but lets the operating system

authenticate logons?

A. create user ops$admin identified by os;

B. create user ops$admin identified externally;

C. create user ops$admin nopassword;

D. create user ops$admin authenticated by os;

----

Authentication by the operating system is called external authentication, and the Oracle

account name must match the operating system account name prefixed with the OS_AUTHENT_

PREFIX string.

Ans: B

2. Which of the following types of statements can use a temporary tablespace?

A. An index creation

B. SQL statements with a GROUP BY clause

C. A hash join operation

D. All of the above

---

Any operation that requires a large sort or other creation of temporary segments will create,

alter, and drop those temporary segments in the TEMPORARY tablespace.

ANs: D.

3. Which of the following statements gives user desmond the ability to alter table gl.accounts?

A. grant alter on gl.accounts to desmond;

B. grant alter to desmond on gl.accounts;

C. grant alter table to desmond;

D. allow desmond to alter table gl.accounts;

---

Altering a table in another user’s schema requires either the object privilege ALTER on that

object or the system privilege ALTER ANY TABLE. Option A has the correct syntax for granting

the object privilege on ALTER gl.accounts to user desmond. Although option C would allow

user desmond to alter his own tables, he would need the ALTER ANY TABLE privilege to alter

another user’s table.

Ans: A.

4. Which of the following statements gives user desmond the ability to alter table gl.accounts as

well as give this ability to other accounts?

A. grant alter any table with grant option to desmond;

B. grant alter on gl.accounts to desmond with admin option;

C. grant alter any table to desmond with grant option;

D. grant alter any table to desmond with admin option;

--

Either the ALTER ANY TABLE system privilege or the ALTER object privilege is required. To

confer the ability to further grant the privilege requires the keywords WITH ADMIN OPTION for

system or role privileges or the keywords WITH GRANT OPTION for object privileges. Only option

D has both the correct syntax and the correct keywords.

Ans: D.

5. The following SQL statement will allow user regina to perform which operations on sequence

oe.orders_seq?

GRANT ALL ON oe.orders_seq TO regina;

A. Select the next value from oe.orders_seq.

B. Alter sequence oe.orders_seq to change the next value.

C. Change the number of sequence numbers that will be cached in memory.

D. Both A and C.

E. All of the above.

---

The ALL option for a sequence includes the SELECT and ALTER privileges. The SELECT privilege

lets Regina select the next value from the sequence. The ALTER privilege lets Regina change

the cache but not the next value.

Ans: D.

6. User system granted SELECT on sh.products to user ian using WITH GRANT OPTION. Ian then

granted SELECT on sh.products to user stuart. Ian has left the company, and his account is

dropped. What happens to Stuart’s privileges on sh.products?

A. Stuart loses his SELECT privilege on sh.products.

B. Stuart retains his SELECT privilege on sh.products.

C. Stuart loses his SELECT privilege if Ian was dropped with the CASCADE REVOKE option.

D. Stuart retains his SELECT privilege if Ian was dropped with the NOCASCADE REVOKE option.

--

When object privileges are granted through an intermediary, they are implicitly dropped when

the intermediary is dropped. There are no CASCADE REVOKE or NOCASCADE REVOKE options.

Ans: A.

7. User system granted SELECT ANY TABLE to user ian using WITH ADMIN OPTION. Ian then

granted SELECT ANY TABLE to user stuart. Ian has left the company, and his account is

dropped. What happens to Stuart’s privileges?

A. Stuart loses his privileges.

B. Stuart retains his privileges.

C. Stuart loses his privileges if Ian was dropped with the CASCADE REVOKE option.

D. Stuart retains his privileges if Ian was dropped with the NOCASCADE REVOKE option.

--

When system privileges are granted through an intermediary, they are not affected when the

intermediary is dropped. There are no CASCADE REVOKE or NOCASCADE REVOKE options.

Ans: B.

8. Which of the following system privileges can allow the grantee to masquerade as another user

and therefore should be granted judiciously?

A. CREATE ANY JOB

B. ALTER USER

C. CREATE ANY PROCEDURE

D. All of the above

----

The CREATE ANY JOB and CREATE ANY PROCEDURE system privileges allow the grantee to create

and run programs with the privileges of another user. The ALTER USER PRIVILEGE allows

the grantee to change a user’s password, connect as that user, and then change the password

back. These are all powerful system privileges and should be restricted to as few administrative

users as practical.

Ans: D

9. Which of the following statements enables the role user_admin in the current session?

A. alter session enable role user_admin;

B. alter session set role user_admin;

C. alter role user_admin enable;

D. set role user_admin;

---

Ans: D. The SET ROLE statement enables or disables roles in the current session.

10. Which of the following SQL statements allows user augustin to use the privileges associated

with the password-protected role info_czar, which has been granted to him?

A. set role all;

B. alter user augustin default role all;

C. alter session enable role info_czar;

D. alter session enable info_czar identified by brozo

----

To enable a password-protected role, you need to either execute a SET ROLE statement specifying

the password or alter the user to make the role a default role. Default roles do not require

a set role statement or a password to become enabled.

Ans: B.

11. By default, how much tablespace can any account use for a new table?

A. None

B. Up to the current free space in the tablespace

C. Unlimited space, including autoextends

D. Up to the default quota established at tablespace creation time

---

By default, user accounts have no quota in any tablespace. Before a user can create a table or

an index, you need to either give the user a quota in one or more specific tablespaces, or grant

the UNLIMITED TABLESPACE system privilege to give unlimited quota (including autoextends) in

all tablespaces.

Ans: A

12. Which of the following SQL statements results in a disconnection after a session is idle for

30 minutes?

A. alter session set idle_timeout=30;

B. alter session set idle_timeout=1800;

C. alter profile limit idle_time 30;

D. alter profile set idle_timout 30;

---

Profiles limit the amount of idle time, CPU time, logical reads, or other resource-oriented

session limits. Option C uses the correct syntax to limit idle time for a session to 30 minutes.

Ans: C

13. Which of the following prevents a user from reusing a password when they change their password?

A. Setting the initialization parameter NO_PASSWORD_REUSE to TRUE

B. Altering that user’s profile to UNLIMITED for PASSWORD_REUSE_TIME and 1 for PASSWORD_REUSE_MAX

C. Altering that user’s profile to UNLIMITED for both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX

D. Using a password verify function to record the new password and compare the new passwords

to those recorded previously

---

Although option D could also work, it involves storing the passwords in a table in the database,

which could be a security concern. It also takes a lot more effort to configure and maintain.

The better technique is to use the standard database profile features PASSWORD_RESUSE_TIME

and PASSWORD_REUSE_MAX. Setting one of these profile parameters to UNLIMITED and the other

to a specific value prevents passwords from being reused. If both of these profile parameters are

set to UNLIMITED, these parameters are essentially disabled. There is no initialization parameter

called NO_PASSWORD_REUSE.

Ans B

14. How can you prevent someone from using an all-numeric password?

A. Set the initialization parameter PASSWORD_COMPLEXITY to ALPHANUM.

B. Alter that user’s profile setting PASSWORD_COMPLEXITY to ALPHNANUM.

C. Alter the user’s profile to use a password verify function that performs REGEX comparisons

to validate the password.

D. There is no mechanism that lets you prevent an all-numeric password.

---

There are no standard password complexity settings in either the initialization parameters or

profiles. A password verify function can validate new passwords against any rules that you can

code in PL/SQL, including regular expression comparisons.

Ans: C.

15. Which of the following is not an object privilege on a table?

A. SELECT

B. DEBUG

C. REFERENCES

D. READ

---

The object privileges on a table include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX,

REFERENCES, and DEBUG, but not READ. READ is a valid object privilege, but only on a directory—

a database object that is outside the scope of the OCA exam.

Ans: D

16. Which of the following statements about user administration and security is the most true?

Select the best answer.

A. Password-protected roles require a password before they can become enabled.

B. You can disable any role that you find in your session_roles view.

C. If you execute alter profile student limit idle_time 10; and then execute alter

user scott profile student;, then user scott will be disconnected from future sessions

after 10 minutes of idle time.

D. You can limit a table to a maximum size on disk.

---

This question is tricky. All the options look correct and in fact are mostly true. But option D

is the most correct option. Password-protected roles that are included in a user’s default role list

are enabled by default and do not need a password. Your session_roles view contains both

roles granted directly to you and those you inherit through another role. You cannot disable roles

that you inherit by way of another role without disabling the role granted directly to you. For

example, you cannot disable SCHEDULER_ADMIN without disabling DBA. Limiting a profile to 10

minutes of idle time will cause future sessions to timeout after 10 idle minutes, but only if the initialization

parameter RESOURCE_LIMIT is set to TRUE (the default is FALSE). Because each schema

owner can be assigned tablespace quotas, you can effectively limit all of a user’s segments to a maximum

size, thus setting an upper limit on the size of any single table.

Ans: D

17. Which of the following SQL statements limit attempts to guess passwords by locking an account

after three failed logon attempts?

A. alter profile default limit failed_login_attempts 3;

B. alter system set max_logon_failures = 3 scope=both;

C. alter user set failed_login_attempts = 3;

D. alter system set failed_login_attempts = 3 scope=both;

---

Ans: A. You limit the number of failed logon attempts with a profile.

18. Where can the database write audit_trail records?

A. In a database table

B. In a file outside the database

C. Both in the database and in an operating system file

D. Either in the database or in an operating system file

---

The destination of audit_trail records is controlled by the initialization parameter audit_

trail. Setting this parameter to DB or DB_EXTENDED causes the audit trail to be written to a database

table. Setting the parameter to OS causes the audit trail to be written to an operating system file.

Ans: D.

19. Which of the following activities can be audited?

A. Unsuccessful deletions from the audit_trail table

B. Unsuccessful selects from the employee_salary table

C. All GRANT and REVOKE statements on procedures executed by user system

D. All of the above

---

Audit unsuccessful deletions from the audit table with the following SQL:

AUDIT DELETE ON sys.aud$ WHENEVER NOT SUCCESSFUL;

Audit unsuccessful selects from all tables with the following:

AUDIT NOT EXISTS;

Audit all grant and revoke statements on procedures executed by user SYSTEM with the following:

AUDIT grant procedure BY system;

Ans: D

20. How do you manage fine-grained auditing?

A. With the AUDIT and NOAUDIT statements

B. With the DBMS_FGA package

C. With the GRANT and REVOKE statements

D. With the CREATE, ALTER, and DROP statements

----

ANs: B

Fine-grained auditing is managed using the DBMS_FGA package. The AUDIT and NOAUDT statements

are used to manage statement, privilege, or object auditing. The GRANT and REVOKE

statements are used to manage system, object, and role privileges. The CREATE, ALTER, and

DROP statements are used to manage several types of database objects and settings.

OCA 10g - Ch5 Oracle Shared Server

Chapter 5 - Oracle Shared Server

Review Questions and Answers

1. All the following are reasons to configure the server using Shared Server except:
a. Overall memory utilization is reduced.
b. The system is predominantly used for decision support with large result sets returned.
c. The system is predominantly used for small transactions with many users.
d. The number of idle connections on the server is reduced.
---
Ans: B.
Shared Server is a scalability option of Oracle. It provides a way to increase the number of supported user processes while reducing the overall memory usage. This configuration is well suited to high-volume, small-transaction-oriented systems with many users connected. Because users share processes, the number of overall idle processes is also reduced. It is not well suited for large data retrieval type applications such as decision support.

2. Which of the following is true about Shared Server?
a. Dedicated connections cannot be made when Shared Server is configured.
b. It is recommended that index rebuilds be performed when connected via Shared Server.
c. The database can be started when connected via Shared Server.
d. The database cannot be stopped when connected via Shared Server.
---
Ans: D.
Users can request dedicated connections in a Shared Server configuration.
Index rebuilds and data-intensive activities should be performed using dedicated server.
The database cannot be started when connecting via Shared Server. So the correct answer is D.

3. The administrator wants to allow a user to connect via a dedicated connection into a
database configured in Shared Server mode. Which of the following lines accomplishes this?
a. (SERVER=DEDICATED)
b. (CONNECT=DEDICATED)
c. (INSTANCE=DEDICATED)
d. (MULTITRHEADED=FALSE)
e. None of the above
----
Ans: A.
A user must explicitly request a dedicated connection when a server is configured in Shared Server mode. Otherwise, the user gets a Shared Server connection. The correct parameter is (SERVER=DEDICATED).

4. In which of the following files would you find the Shared Server configuration parameters?
a. listener.ora
b. mts.ora
c. init.ora
d. tnsnames.ora
e. sqlnet.ora
----
Ans: C.
The Shared Server configuration parameters exist in the init.ora file on the Oracle Server machine.


5. Which of the following is a component of Shared Server?
a. Shared user processes
b. Checkpoint processes
c. Dispatcher processes
d. Dedicated server processes
----
Ans: C.
In Shared Server, users connect to a pool of shared resources called dispatchers.
A client connects to the listener, and the listener redirects the request to a dispatcher.
The dispatchers handle all the user requests for the session. Many users can share dispatchers.

6. You want to put the database in Shared Server mode. Which of the following files will you modify?
a. tnsnames.ora
b. cman.ora
c. names.ora
d. init.ora
---
Ans D.
Because the database has to be configured in Shared Server mode, you must change the init.ora file. The other choices are also configuration files, but none of them are used to configure Shared Server.


7. What choice in the Oracle Net Manager allows for the configuration of Shared Server?
a. Local
b. Service Naming
c. Listeners
d. Profile
e. None of the above
---
Ans E.
This is one of the tricky questions again! You can use Oracle Net Manager to configure many options and files, including tnsnames.ora and sqlnet.ora. But because Shared Server is a characteristic of the database server and not of the network, you cannot use Oracle Net Manager to configure it.


8. You want two TCP/IP dispatchers and one IPC dispatcher to start when the instance is started. Which of the following lines will accomplish this?
a. dispatchers=(protocol=tcp)(dispatchers=2)(protocol=IPC) (dispatchers=1)
b. dispatchers="(protocol=tcp)(dispatchers=2)(protocol=IPC)(dispatchers=1)"
c. dispatchers_start=(protocol=tcp)(dispatchers=2)(protocol=IPC) (dispatchers=1)
d. dispatchers_start=(pro=tcp)(dis=2)(pro=IPC)(dis=1)
---
Ans: B.
The DISPATCHERS parameter of the init.ora file is used to configure dispatchers, so the correct answer is option B. All the other choices are invalid parameters.


9. What is the name of the piece of shared memory that client connections are bound to during communications via Shared Server?
a. Program Global Area
b. System Global Area
c. Virtual Circuit
d. Database Buffer Cache
e. None of the above
----
Ans C.
The System Global Area is the shared memory segment that Oracle obtains on instance startup. The Program Global Area is an area of memory used primarily during dedicated connections. The Database Buffer Cache is actually a component of the Program Global Area. Virtual Circuits are the shared memory areas to which clients bind.

10. What is the first step that the dispatcher performs after it receives a request from the user?
a. Pass the request to a shared server.
b. Place the request in a request queue in the PGA.
c. Place the request in a request queue in the SGA.
d. Process the request.
---
Ans: C.
Once a dispatcher receives a request from the user process, it places the request on the request queue. Remember that in a Shared Server environment, a request can be handled by a shared server process. This is made possible by placing the request and user information in the SGA.


11. Dispatchers have all the following characteristics except:
A. Dispatchers can be shared by many connections.
B. More dispatchers can be added dynamically with the ALTER SYSTEM command.
C. A dispatcher can listen for multiple protocols.
D. Each dispatcher has its own response queue.
---
Ans. C.
Many users can connect to dispatchers, and dispatchers can be added dynamically. Also, each dispatcher does have its own response queue. The only one of these options that is false is option C because dispatchers can listen for only one protocol. You can configure multiple dispatchers so that each is responsible for different protocols.


12. When configured in Shared Server mode, which of the following is contained in the PGA?
A. Cursor state
B. Sort information
C. User session data
D. Stack space
E. None of the above
---
Ans: D.
A small PGA is maintained even though most of the user-specific information is moved to the SGA (specifically called the UGA in the Shared Pool or the Large Pool). The only information left in the reduced PGA is stack space.

13. Which of the following is false about shared servers?
A. Shared servers can process requests from many users.
B. Shared servers receive their requests directly from dispatchers.
C. Shared servers place completed requests on a dispatcher response queue.
D. The SHARED_SERVERS parameter configures the number of shared servers to start at instance startup.
--
Ans B.
Shared Servers can process requests from many users.
The completed requests are placed into the dispatchers' response queues.
The servers are configured with the SERVERS parameter.
However, shared servers do not receive requests directly from dispatchers.
The requests are taken from the request queue.

14. Which of the following is not a step in the processing of a Shared Server request?
A. Shared servers pass information back to the client process.
B. Dispatchers place information in a request queue.
C. Users pass requests to a dispatcher.
D. The dispatcher picks up completed requests from its response queue.
E. None of the above.
---
Ans: A.
Study the steps of what happens during a request via Shared Server. Dispatchers receive requests from users and place the requests on request queues. Only dispatchers interact with client processes. Shared servers merely execute the requests and place the results back on the dispatcher's response queue.

15. When you are configuring Shared Server, which initialization parameter would you likely need to modify?
A. DB_CACHE_SIZE
B. DB_BLOCK_BUFFERS
C. LARGE_POOL_SIZE
D. BUFFER_SIZE
E. None of the above
---
Ans: C.
Shared Server requires a shift of memory away from individual session processes to the SGA. More information has to be kept in the SGA (in the UGA) within the Shared Pool. A Large Pool is configured and is responsible for most of the SGA space allocation. Option C is the correct answer. The cache size and block buffers settings do not affect Shared Server.
C.

16. Which of the following is false about request queues?
A. They reside in the SGA.
B. They are shared by all the dispatchers.
C. Each dispatcher has its own request queue.
D. The shared server processes remove requests from the request queue.
---
Ans: C.
Request queues reside in the SGA, and there is one request queue per instance. This is where shared server processes pick up requests that are made by users. Dispatchers have their own response queues, but they share a single request queue.

17. You want to gather information about users connected via Shared Server connections. Which of the following is the view that contains this information?
A. V$USERS
B. V$QUEUE
C. V$SESS_STATS
D. V$CIRCUIT
E. None of the above
--
Ans D.
You can use several V$ views to manage the Shared Server.
V$QUEUE gives information regarding the request and response queues. V$USERS and V$SESS_STATS are not valid views. V$CIRCUIT gives information about the users who are connected via Shared Server connections, and it provides the necessary information.


18. What is the process that notifies the listener after a database connection is established?
A. SMON
B. DBWR
C. PMON
D. LGWR
---
Ans: C.
The PMON process notifies the listener after a client connection is established. This is so that the listener
can keep track of the number of connections being serviced by each dispatcher.


19. You want to gather performance and tuning-related information for the shared server processes. You should start by querying which of the following views?
A. V$USERS
B. V$CIRCUIT
C. V$SHARED_SERVER_MONITOR
D. V$SESS_STATS
----
Ans: C.
You can query the V$SHARED_SERVER_MONITOR view to display information about the maximum number of connections and sessions, the number of servers started and terminated, and the server high-water mark. These numbers can help determine whether you should start more shared servers.


20. Which command can you execute to get details about the number of sessions connected via Shared Server?
A. lsnrctl sessions
B. lsnrctl conn
C. lsnrctl status
D. lsnrctl services
E. None of the above
---
Ans: D.
Dispatchers register with listeners so that when a listener redirects a connection to a dispatcher, the listener knows how many active connections the dispatcher is serving. The lsnrctl services command summarizes the number of connections established, connections currently active, and other valuable information regarding Shared Server.
The lsnrctl status command summarizes only dispatchers and does not display any details about connections.

OCA Oracle 10g Ch4 - Oracle Net Service

 

Ch4 - Oracle Net Service

Review Questions

 

1. All of the following are examples of networking architectures except:

A. Client/server

B. N-tier

C. Single-tier

D. Two-tier

E. All the above are examples of network architectures.

---

Ans: E.

All these are examples of network connectivity configurations. Networking can be as simple as

a dumb terminal connected directly to a server via a serial connection. It can also be as complex

as an n-tier architecture that involves clients, middleware, the Internet, and database servers.

 

 

2. You manage one non-Oracle database and several Oracle databases. An application needs to

access the non-Oracle database as if it were part of the Oracle databases. What tool allows you

to do this? (Choose the best answer.)

A. Oracle Advanced Security

B. Oracle Connection Manager

C. Heterogeneous Services

D. Oracle Net

E. None of the above

---

Ans: C.

Heterogeneous Services is the correct answer because these services provide cross-platform

connectivity to non-Oracle databases. Oracle Advanced Security would not solve this application

problem because it addresses security and not accessibility to non-Oracle databases. Oracle

Net would be part of the solution, but another Oracle Network component is necessary. Connection

Manager would also not be able to accommodate this requirement on its own.

 

 

3. Which of the following is true about Oracle Net?

A. It is not an option included in the Oracle Enterprise installation.

B. It works only on TCP/IP platforms.

C. It has the ability to communicate with non-Oracle data sources.

D. It is never installed directly on a client workstation.

----

Ans: C.

When you are using the Heterogeneous Services capabilities of Oracle Net, you do have the

ability to communicate with non-Oracle data sources.

 

4. You want to centrally administer all the Oracle network services in a large Oracle 10g installation

that runs many network services. Which of the following facilities would best provide this

functionality at minimal cost?

A. Advanced Security

B. Heterogeneous Services

C. Oracle Shared Server

D. Oracle Internet Directory

---

Ans: D.

The best solution to the problem is the Oracle Internet Directory because it facilitates centralized

naming. Advanced Security, Heterogeneous Services, and Oracle Shared Server would

not provide a solution to this business need because none of these address the issue of centrally

managing network services.

 

 

5. Which of the following files must be present on the Oracle server to start a non-default

Oracle listener?

A. listener.ora

B. lsnrctl.ora

C. sqlnet.ora

D. tnsnames.ora

---

Ans: A.

The listener is the process that manages incoming connection requests. The listener.ora

file is used to configure the listener and must be configured to start a non-default listener. The

sqlnet.ora file is an optional client- and server-side file. The tnsnames.ora file is used for

doing local naming resolution. There is no such file as lsnrctl.ora.

 

6. Which of the following is the correct way to start the default listener?

A. lsnrctl startup listener

B. lsnrctl start

C. listener start

D. listener start listener

---

Ans: B.

Because the default listener name is LISTENER, simply enter lsnrctl start. The name

LISTENER is assumed to be the listener to start in this case.

 

7. Which of the following parameters sets the number of seconds a server process waits to get a

valid client request?

A. connect_waittime_listener_name

B. connect_wait_listener_name

C. timeout_listener_name

D. inbound_connect_timeout_listener_name

---

Ans: D. When a user makes a connection request, the listener passes control to some server process

or dispatcher. Once the user is attached to this process, all negotiations and interaction with the

database pass through this process. If the user supplies an invalid user ID or password, the process

waits for a period of time for a valid response. If the user does not contact the server process

with a valid response in the allotted time, the server process terminates, and the user must contact

the listener so that the listener can again spawn a process or redirect the client to an existing

dispatcher. This period of time that the process waits is specified by the connect_timeout_

listener_name parameter. This parameter is specified in seconds.

 

 

8. When dynamic service registration is used, you will not see the service listed in which of the

following files where it would normally be located?

A. sqlnet.ora

B. tnsnames.ora

C. listener.ora

D. None of the above

--

Ans: C.

When services are dynamically registered with the listener, their information is not present in

the listener.ora file.

 

9. You have just made changes to the listener.ora file for the listener called listener1 using

Oracle Net Manager. Which of the following commands or combinations of commands would

you use to put the changes into effect with the least amount of client disruption?

A. lsnrctl stop listener1 followed by lsnrctl start listener1

B. lsrnctl restart listener1

C. lsnrctl reload listener1

D. lsnrctl reload

---

Ans: C. Although you can use choice A to stop and start the listener, doing so temporarily disrupts clients

attempting to connect to the database. Choice D is fine if you are starting and stopping the

default listener called LISTENER, but we are using a non-default listener. Choice B is not valid

because RESTART is not a valid command-line argument for lsnrctl. Therefore, the best method is

C, to use the lsnrctl reload listener1 command to load the new set of values in for the listener

without disrupting connection service to the databases that the listener is servicing.

 

 

10. What is the default name of the process that makes external calls via Oracle Net?

A. lsnrctl

B. external

C. extproc

D. procext

----

Ans: C.

The default name of the external procedure process is extproc. lsnrctl is a utility used to

manage the listener service. External and procext are not valid responses.

 

11. What are the ways in which a client can resolve a net service name? (Choose all that apply.)

A. Localnaming

B. Hostnaming

C. Easy Connect Naming

D. Oracle Global Naming

E. All the above

---

Ans: A, B, C.

Oracle uses service names in networks in much the same way it uses synonyms in the

database. Service names provide location transparency and hide the complexity of connect

string information. You can configure Oracle Net to connect in several ways, including hostnaming,

localnaming, OID, and Oracle Easy Connect Naming. Oracle Global Naming is not

a valid Oracle option.

 

 

12. Connection Manager provides which of the following?

A. Multiplexing

B. Cross-protocol connectivity

C. Network access control

D. All the above

--

Ans: D.

Connection Manager is a middleware solution that provides for multiplexing of connections,

cross-protocol connectivity, and network access control. All the answers describe Connection

Manager.

 

13. Which is a requirement for using hostnaming?

A. You must use tnsnames.ora on the client.

B. You must be using TCP/IP.

C. You must have an OID present.

D. You must have a sqlnet.ora file present on the client.

E. None of the above.

----

Ans: B.

Hostnaming is typically used in small installations that have few Oracle databases. This is an

attractive option when you want to minimize client-side configuration. TCP/IP is a requirement

when you use hostnaming.

 

14. To which of the choices below does the following statement apply? 'Prevents direct communication

between a client outside the corporate network and applications inside the corporate

network.'

A. Proxy-based firewalls

B. Filter-based firewalls

C. Both types of firewalls

D. Neither type of firewall

---

Ans: A.

Proxy-based firewalls prevent any direct contact between a client outside the corporate firewall

and applications inside a corporate firewall. Filter-based firewalls inspect the packet headers but

pass the packet on without modification to the destination application. Proxy-based firewalls act

more as a relay between external clients and internal applications.

 

15. Which of the following statements about tnsnames.ora is false?

A. It is used to resolve an Oracle service name.

B. It can exist on the client.

C. It is used for localnaming.

D. It does not support the TCP/IP protocol

----

Ans: D.

A tnsnames.ora file is configured when you want to use localnaming, and it typically exists

on the client workstation. It is also used to resolve a service name. The tnsnames.ora file used in

localnaming does indeed support the TCP/IP protocol.

 

16. You manage a large network of servers with many Oracle10g instances configured on each

server. The company would like to centralize the Oracle services into a common location for

ease of maintenance. Which of the following would be the best choice for centralized naming

services?

A. Oracle Easy Connect Naming

B. Localnaming

C. Oracle Names Server

D. Oracle Internet Directory

--

Ans: D.

OID is Oracle’s preferred centralized naming service. Oracle Names Server is no longer supported

in Oracle 10g.

 

17. A client receives the following error message:

'ORA-12154 TNS:could not resolve the connect identifier

specified'

Which of the following could be possible causes of the error? (Choose all that apply.)

A. The listener is not running on the Oracle server.

B. The user entered an invalid net service name.

C. The user supplied the correct net service name, but the net service name is misspelled in the

tnsnames.ora file.

D. The listener is not configured to listen for this service.

--

Ans: B, C.

Supplying a net service name that is not contained in the tnsnames.ora file can cause this

error. Problems with the tnsnames.ora file can cause this error too. Listener problems would

not cause this error.

 

18. What portion of the tnsnames.ora file specifies the name or IP address of the server where the

listener process is listening?

A. CONNECT_DATA

B. PORT

C. SERVICE_NAME

D. HOST

---

Ans: D.

The HOST portion specifies the name of the server to contact. CONNECT_DATA specifies the

database service to connect to. The PORT portion specifies the location where the listener is

listening on the HOST. SERVICE_NAME is the name of the actual database service.

 

19. Which of the following is the utility that you can use to test the network connections across TCP/IP?

A. trcasst

B. lsnrctl

C. namesctl

D. ping

E. None of the above

---

Ans: D. Protocols come with tools that allow you to test network connectivity. One such utility for

TCP/IP is ping. The user supplies either an IP address or a hostname to the ping utility. It then

searches the network for this address. If it finds one, it displays information on data that is sent and

received and how quickly it found this address. The other choices are Oracle-supplied utilities.

 

20. A client wants to connect to the database dbprod.com located on the dbprod.com server to a

non-default port using Oracle Easy Connect. Which of the following connect strings is the best

choice for the client use?

A. CONNECT scott/tiger@dbprod.com:1522

B. CONNECT scott/tiger@1521:dbprod.com/dbprod.com

C. CONNECT scott/tiger@dbprod.com/1522:dbprod.com

D. CONNECT scott/tiger@dbprod.com:1521/dbprod.com

E. CONNECT scott/tiger@dbprod.com:1522/dbprod.com

---

Ans: A.

The correct syntax to use with the Oracle Easy Connect Naming method when you are

connecting to a non-URL location is as follows: connect username/password@host:port/

service_name. If the service name and the host are identical, you do not have to include the service

name. If the port is any port other than the default port of 1521, it must be specified. Because

we want to connect to a non-default port where the database name and the hostname are the same,

the best answer is A.