Questions1-10

QUESTION 1:
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.
D. Information stored inside the corresponding initialization parameter file for the
instance.
Answer: A
Explanation:In OMF Oracle uses Operating System(file system of OS) to create and delete database files.Only Location is required for creating database files.Oracle will use naming conventions when it creates the OMF files. In this naming convention,
%t represents the tablespace name, %u is a unique 8-character string, and %g stands for the
redo log group number.hence answer A.
EXTRA
Now there are three types of file and there locations are as follows
1>Datafile=

DB_CREATE_FILE_DEST

2>Controlfile and Online Redolog=

DB_CREATE_ONLINE_LOG_DEST_n, where n is any integer between 1 and 5

if 2nd parameter is not specified than controlfile and redo is generated at location specified by 1st location.

QUESTION 2:
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
Explanation:Cursor sharing enables us to use same execution plan for all Queries having same structure but some are using literals while some are using bind variable.
example:
1> SQL> select * from emp where deptno =10;/literal
2> SQL> select * from emp where deptno =20;/literal
SQL> variable deptno number/declaring variable
SQL> exec :deptno := 10/defining value
3> SQL> select * from emp where deptno = :deptno;/using
SQL> exec :deptno := 20/defining value
4>> SQL> select * from emp where deptno = :deptno;/using
Query 1,2 and 3 will be treated different and each one will have its own parsing,Execution plan but Query 3 and 4 will have same parse and exe plan and hence it saves resource and CPU execution time.
but to have same exe plan for all the four Query we use cursor_sharing parameter
It takes values like EXACT,SIMILAR and FORCE.
When you specify SIMILAR,
Oracle only uses the execution plan if it is certain that the execution plan does not have any
association with the specific literal value.
Generally exetion plan doesn't depend on literal values.such literals are said to be as safe literals and hence the word 'safe literals only'


QUESTION 3:
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.
Answer: B, C, E
Explanation:
The minimum SGA configuration is three granules. One is for fixed SGA, one for database buffer, one for shared pool. Oracle9i has enhanced the nature of SGA parameters; they are now dynamic. You can change the values of the shared pool and the buffer cache without restarting the database instance. The Oracle9i dynamic SGA concept enables you to take memory from one area of the SGA and allocate it to another area as needed while the database instance is up and running. Additionally, the unit of memory allocation for SGA is a granule in Oracle9i. Oracle9i also introduces SGA_MAX_SIZE, a new static parameter that enables the DBA to start with a smaller SGA and dynamically increase it to the maximum value specified by SGA_MAX_SIZE. If you do not set SGA_MAX_SIZE or if you set it to a value less than
initial SGA size, you cannot increase the SGA size later.
Incorrect Answers
A: The size of a granule is 4 MB if the SGA at startup is less than 128 MB; it will be 16 MB
otherwise.
D: The size of the SGA components is not set by the SGA_MAX_SIZE parameter. This


QUESTION 4:
There is more than one way to set the server to detect and affect long running operations automatically. What is the best choice of 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
Explanation:

CPU_PER_CALL:-It limits the time given by CPU for every Query.
example if a user A fires 10 Queries and B fires 2 Queries.and CPU_PER_CALL is 0.5sec then after 6 second have passed, 5 seconds are spend for executing user A's Query and 1 second is utilized for user B.so there is no impact,but...if we use
SWITCH_TIME: and allocate switch_time=0.5 sec then after 6 second have passed,3 sec is allocated to user A and equally to user B.thus it avoids an impact of long running Queries.hence answer A.



QUESTION 5:
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
Answer: A, B, D
Explanation:
Oracle supports two types of partitioned indexes: local indexes and global indexes. A local index is "equi-partitioned" with the base table, so that every table partition has exactly one corresponding index partition. Index maintenance is very easy when modifying a base table with local indexes. When a table partition is dropped, the corresponding index partitions are dropped. When a table partition is added, corresponding index partitions are added.
Unlike local indexes, a global index can be partitioned independently of the base table. Hence there may not be a 1-to-1 correspondence between global index partitions and the base table partitions. Index maintenance is more complicated when using global indexes, since dropping or adding a partition to the table may affect all partitions of the index. In Oracle8i, all global indexes of a partitioned table are marked UNUSABLE after a partition-maintenance operation. These global indexes then have to be rebuilt, which can be an expensive operation.
Oracle's partitioning capabilities have been enhanced in Oracle9i with the addition of a feature that enables global indexes to be maintained during partition maintenance operations. A new clause, UPDATE GLOBAL INDEXES, can optionally be appended to the maintenance commands. When this clause is present, Oracle will automatically and efficiently maintain all global indexes instead of making them UNUSABLE. For example, when dropping a partition, the command to take advantage of this new feature is: ALTER TABLE T1 DROP PARTITION P1 UPDATE GLOBAL INDEXES;


QUESTION 6:
Which statement correctly describes the function of 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
Explanation:

Oracle cache fusion.
referring to the diagram above,A real application cluster consists of a cluster of several servers (nodes) with a set of shared disks.In diagram there are 3 servers sharing database on a single disk.
If server 1 require some data from server 2's instance then
IN 7i
data from server 1 was written to disk database and then server 2 retrieves from disk(called pinging),
IN 8i
data could be directly transferred to server 2 using INTERCONNECT if that data was not modified by server 1(called consistent read cache fusion) and if modified then we need to use above method i.e.pinging
IN 9i
data from server 1 is transferred to server 2 irrespective of data is modified one or not modified.(called cache fusion)
Thus this technology enables you to execute scalable_applications on a clustered database without having
to partition the users or the database tables


QUESTION 7:
What is true if you want to switch undo tablespaces from the current one, called
UNDO1, to a new one called UNDO2?
A. It is NOT possible to switch unless no active transaction exist 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
Explanation:
You can switch undo tablespaces from the current one, called UNDO1, to a new one called
UNDO2. Only current active transactions will continue to execute inside UNDO1, all new
transactions will be assigned to the new undo tablespace.


QUESTION 8:
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, IDSI INTERVAL YEAR To SECOND
Which three expressions using the new data and time data types are valid? (Choose
three)
A. IDS* 2
B. TS + IYM
C. TS -TSI
D. IDS - TS
E. IDS + IYM
Answer: A, B, E
Explanation:
IDS*2, TS+IYM and IDS+IYM are valid new date and time data types.
You cannot subtract timestamps.

QUESTION 9:
Consider the following statement:
SQL> EXECUTE DBMS_STATS.GATHER_SHEMA_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 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
Explanation:
Oracle server creates histogram based on the data(not the distribution of data) and application usage(how many inserts,delete,updates).this data is generated for the columns of all tables and indexes owned by user OE.


QUESTION 10:
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
Explanation:
External tables are read-only tables whose data resides in an external OS flat file, and whose
definition is stored inside the database. Indexes cannot be created on external tables.They are used as a container to transfer data from one database to another database at different location.Data from database is written to an external file(OS flat file) and later on copied from external file to new database.
but 10g allows read and write operations on external table.