Questions(51-60)

QUESTION 51:
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
Explanation:
When a client machine initiates a shared server connection to the database in Oracle9i, the
listener uses the direct handoff whenever possible. This connection process requires fewer
network calls and, accordingly, less overhead.
When the listener receives the request from the client for a shared server connection, it locates
the address of the least loaded dispatcher process. In previous versions, the next step would
have been to pass this address back to the client for the client to contact the dispatcher again
over the network. Instead, in Oracle9i's direct handoff method, the listener hands the
connection request to the dispatcher. The dispatcher communicates directly with the client to
establish a connection without the need for the additional network calls that were required in
previous versions.

QUESTION 52:
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 the 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.
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
Explanation:

Oracle9i will share the corresponding cursors for the two statements if you are using the rulebased
optimizer or the cost-based optimizer with up-to-date statistics but with no histograms.

QUESTION 53:
An index-organized table (IOT) called SPARSE has had many records deleted.
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
Explanation:
COALESCE means De fragmentation.It separates used memory from free memory.
The ALTER TABLE table_name COALESCE statement will reorganize the IOT. The rebuild
operation creates a new tree to defragment the index, as opposed to the coalesce operation that
coalesces the leaf blocks within the same branch of the tree.Data is places at the leaf node of a tree index or IOT.hence COALESCE data means COALESCE used leaf from unused leaf.e.g if lead1 has 40% data and leaf2 has 30% data then after COALESCE leaf1 will have 70%data and leaf2 will be 100% free.

QUESTION 54:
Data file DF1 belongs to tablespace TS1.,and the database is in ARCHIVELOG mode.
You did an incomplete recover in the following steps:
1. Take data file DF1 offline.
2. Copy a backup copy of DF1 into the current DF1 location.
3. Execute RECOVER DATAFILE UNTIL TIME sometime in the past.
4. Bring DF1 online.
5. Execute ALTER DATABASE OPEN RESETLOGS.
To guarantee that the database is recoverable in the future, what is the least amount of
work needed?
A. Back up the data file.
B. Bring tablespace TS1 online.
C. Copy redo logs to the backup directory.
D. Make a complete backup right after opening the database.
Answer: D
Explanation:
Its an thumb rule that we need to take backup after and before(corrupt data backup manually) incomplete recovery.

After any type of incomplete recovery a complete backup is required immediately after
opening the database. It needs to be done because of resetlogs option: after resetting logs any
previous backup is unusable any more.


QUESTION 55:
QUESTION 56:
Which three statements about direct path exports are true? (Choose three)
A. You cannot export rows containing collection data types.
B. Direct path exports can be performed interactively using exp.
C. The export client, exp, writes data to the dump file without any unnecessary data
conversion.
D. The format and column specifications are identical to those found in conventional-path
exports.
E. The direct path export type is noted in the output to the screen, in the export log file,
and the export dump file.
F. Direct path export is essentially a SELECT * FROM TABLE statement with a
complex (but highly optimized) query predicate.
Answer: A, C, E
Explanation:
It is not possible to export rows with collection data types(varray and nested tables)"Restrictions for Direct Path Exports
Keep the following restrictions in mind when you are using direct path mode:
To invoke a direct path Export, you must use either the command-line method or a parameter file. You cannot invoke a direct path Export using the interactive method.
The Export parameter BUFFER applies only to conventional path Exports. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.
You cannot use direct path when exporting in tablespace mode (TRANSPORT_TABLESPACES=Y).
The QUERY parameter cannot be specified in a direct path Export.
A direct path Export can only export data when the NLS_LANG environment variable of the session invoking the export is equal to the database character set. If NLS_LANG is not set or if it is different than the database character set, a warning is displayed and the export is discontinued. The default value for the NLS_LANG environment variable is AMERICAN_AMERICA.US7ASCII." .


QUESTION 57:
In RMAN, when the RESYNC CATALOG command is issued, in which two situations
does the command file actually perform synchronization? (Choose two)
A. After the COPY command.
B. After adding a data file in the target database.
C. After adding a tablespace in the target database.
D. After adding a tablespace in the catalog database.
E. After dropping a data file in the catalog database.
F. After running SQL Loader to load a large amount of data.
Answer: B, C
Explanation:
If you issue RESYNC CATALOG after COPY and BACKUP command then there will be no sync done.Bcoz COPY and BACKUP command are auto sync command.but after adding a datafile or tablespace RESYNC CATALOG will actually performe sync between catalog and controlfile.

 QUESTION 58:
Archive file format LOG_ARCHIVE_FORMAT is defined as arch_%s.arc. A week ago,
you just archived arch_4.arch to tape, then removed all archived files to save disk space.
Yesterday you saw arch_10.arc and arch_11.arc on the disk. Today, you see arch_3.arc
in the archive directory. Which two could you happened? (Choose two)
A. Redo logs were restored from tape for recovery.
B. The file names are only used when they are copied to tape.C. The database was opened with the RESETLOGS option and log numbers restarted.
D. Log names run in circular fashion; arch_3.arc is re-created by the ARCn process.
E. A file name is randomly chosen by the archive process; as long as it is unique, it does
not have to be sequential.
Answer: A, C
Explanation:
If you see arch_3.arc in the archive directory again, it means that redo logs restore from tape
happened and or the database was opened with the RESETLOGS option and the log files
sequence have been restarted.

QUESTION 59:
What is true about this statement used during import?
imp hr/hr tables= (employee, departments) file=hr.dmp ignore=y
A. It ignores duplicates.
B. It prevents character set translation on import.
C. It allows rows to be inserted into a table that already exists.
D. It ignores storage parameters in the DML executed during import.
Answer: C
Explanation:
If a table already exists at the target database where we are importing data and ignore=y, then the rows are imported to the existing tables, otherwise errors will be reported and no rows are loaded into the table. The IGNORE=Y option allows rows to be inserted into a table that already exists.

QUESTION 60:

Click the Exhibit button and examine the diagram, which illustrates the components
performing tasks within an Oracle Shared Sever environment.
Which task is being performed by component 4?
A. The PMON process registers the request in the response queue.
B. A shared sever places the process request in the response queue.
C. A request within the request queue is processed by an idle shared server process.
D. A dispatcher assigns a request from the request queue to and idle shared server
process.
Answer: C
Explanation:
An idle shared server process will process a request within the request queue.