|
Oracle Interview Questions and Answers
What is the use of break group? What are the various
sub events a mouse double click event involves?
A break group is used to display one record for one
group ones. While multiple related records in other
group can be displayed.
What tuning indicators can one use? (for DBA)
The following high-level tuning indicators can be used
to establish if a database is performing optimally or
not:
. Buffer Cache Hit Ratio
Formula: Hit Ratio = (Logical Reads - Physical Reads) /
Logical Reads
Action: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior
to 9i) to increase hit ratio
. Library Cache Hit Ratio
Action: Increase the SHARED_POOL_SIZE to increase hit
ratio
What tools/utilities does Oracle provide to assist with
performance tuning? (for DBA)
Oracle provide the following tools/ utilities to assist
with performance monitoring and tuning:
. TKProf
. UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats
monitoring
. Statspack
. Oracle Enterprise Manager - Tuning Pack
What is STATSPACK and how does one use it? (for DBA)
Statspack is a set of performance monitoring and
reporting utilities provided by Oracle from Oracle8i and
above. Statspack provides improved BSTAT/ESTAT
functionality, though the old BSTAT/ESTAT scripts are
still available. For more information about STATSPACK,
read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.
Install Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- Install Statspack -
sqlplus "/ as sysdba" @spcreate.sql-- Enter tablespace
names when prompted
Use Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- Take a performance snapshots
exec statspack.snap;
o Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- Enter two snapshot id's for difference
report
Other Statspack Scripts:
. sppurge.sql - Purge a range of Snapshot Id's between
the specified begin and end Snap Id's
. spauto.sql - Schedule a dbms_job to automate the
collection of STATPACK statistics
. spcreate.sql - Installs the STATSPACK user, tables and
package on a database (Run as SYS).
. spdrop.sql - Deinstall STATSPACK from database (Run as
SYS)
. sppurge.sql - Delete a range of Snapshot Id's from the
database
. spreport.sql - Report on differences between values
recorded in two snapshots
. sptrunc.sql - Truncates all data in Statspack tables
What are the common RMAN errors (with solutions)? (for
DBA)
Some of the common RMAN errors are:
RMAN-20242: Specification does not match any archivelog
in the recovery catalog.
Add to RMAN script: sql 'alter system archive log
current';
RMAN-06089: archived log xyz not found or out of sync
with catalog
Execute from RMAN: change archivelog all validate;
How can you execute the user defined triggers in forms
3.0 ?
Execute Trigger (trigger-name)
What ERASE package procedure does ?
Erase removes an indicated global variable.
What is the difference between NAME_IN and COPY ?
Copy is package procedure and writes values into a
field.
Name in is a package function and returns the contents
of the variable to which you apply.
What package procedure is used for calling another form
?
Call (E.g. Call(formname)
When the form is running in DEBUG mode, If you want to
examine the values of global variables and other form
variables, What package procedure command you would use
in your trigger text ?
Break.
SYSTEM VARIABLES
The value recorded in system.last_record variable is
of type
a. Number
b. Boolean
c. Character. ?
b. Boolean.
What is mean by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle
User Process.
What is hit ratio ?
It is a measure of well the data cache buffer is
handling requests for data. Hit Ratio = (Logical Reads -
Physical Reads - Hits Misses)/ Logical Reads.
How do u implement the If statement in the Select
Statement
We can implement the if statement in the select
statement by using the Decode statement. e.g. select
DECODE (EMP_CAT,'1','First','2','Second'Null); Here the
Null is the else statement where null is done .
How many types of Exceptions are there
There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
What are the inline and the precompiler directives
The inline and precompiler directives detect the values
directly
How do you use the same lov for 2 columns
We can use the same lov for 2 columns by passing the
return values in global values and using the global
values in the code
How many minimum groups are required for a matrix report
The minimum number of groups in matrix report are 4
What is the difference between static and dynamic lov
The static lov contains the predetermined values while
the dynamic lov contains values that come at run time
How does one manage Oracle database users? (for DBA)
Oracle user accounts can be locked, unlocked, forced to
choose new passwords, etc. For example, all accounts
except SYS and SYSTEM will be locked after creating an
Oracle9iDB database using the DB Configuration Assistant
(dbca). DBA's must unlock these accounts to make them
available to users.
Look at these examples:
ALTER USER scott ACCOUNT LOCK -- lock a user account
ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked
users account
ALTER USER scott PASSWORD EXPIRE; -- Force user to
choose a new password
What is the difference between DBFile Sequential and
Scattered Reads?(for DBA)
Both "db file sequential read" and "db file scattered
read" events signify time waited for I/O read requests
to complete. Time is reported in 100's of a second for
Oracle 8i releases and below, and 1000's of a second for
Oracle 9i and above. Most people confuse these events
with each other as they think of how data is read from
disk. Instead they should think of how data is read into
the SGA buffer cache.
db file sequential read:
A sequential read operation reads data into contiguous
memory (usually a single-block read with p3=1, but can
be multiple blocks). Single block I/Os are usually the
result of using indexes. This event is also used for
rebuilding the control file and reading data file headers
(P2=1). In general, this event is indicative of disk
contention on index reads.
db file scattered read:
Similar to db file sequential reads, except that the
session is reading multiple data blocks and scatters
them into different discontinuous buffers in the SGA.
This statistic is NORMALLY indicating disk contention on
full table scans. Rarely, data from full table scans
could be fitted into a contiguous buffer area, these
waits would then show up as sequential reads instead of
scattered reads.
The following query shows average wait time for
sequential versus scattered reads:
prompt "AVERAGE WAIT TIME FOR READ REQUESTS"
select a.average_wait "SEQ READ", b.average_wait "SCAT
READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';
What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
What is the use of TABLES option in EXP command ?
List of tables should be exported.ze)
What is the OPTIMAL parameter?
It is used to set the optimal length of a rollback
segment.
How does one use ORADEBUG from Server Manager/ SQL*Plus?
(for DBA)
Execute the "ORADEBUG HELP" command from svrmgrl or
sqlplus to obtain a list of valid ORADEBUG commands.
Look at these examples:
SQLPLUS> REM Trace SQL statements with bind variables
SQLPLUS> oradebug setospid 10121
Oracle pid: 91, Unix process pid: 10121, image:
oracleorcl
SQLPLUS> oradebug EVENT 10046 trace name context
forever, level 12
Statement processed.
SQLPLUS> ! vi /app/oracle/admin/orcl/bdump/ora_10121.trc
SQLPLUS> REM Trace Process Statistics
SQLPLUS> oradebug setorapid 2
Unix process pid: 1436, image: ora_pmon_orcl
SQLPLUS> oradebug procstat
Statement processed.
SQLPLUS>> oradebug TRACEFILE_NAME
/app/oracle/admin/orcl/bdump/pmon_1436.trc
SQLPLUS> REM List semaphores and shared memory segments
in use
SQLPLUS> oradebug ipc
SQLPLUS> REM Dump Error Stack
SQLPLUS> oradebug setospid <pid>
SQLPLUS> oradebug event immediate trace name errorstack
level 3
SQLPLUS> REM Dump Parallel Server DLM locks
SQLPLUS> oradebug lkdebug -a convlock
SQLPLUS> oradebug lkdebug -a convres
SQLPLUS> oradebug lkdebug -r <resource handle> (i.e
0x8066d338 from convres dump)
Are there any undocumented commands in Oracle? (for DBA)
Sure there are, but it is hard to find them. Look at
these examples:
From Server Manager (Oracle7.3 and above): ORADEBUG HELP
It looks like one can change memory locations with the
ORADEBUG POKE command. Anyone brave enough to test this
one for us? Previously this functionality was available
with ORADBX (ls -l $ORACLE_HOME/rdbms/lib/oradbx.o; make
-f oracle.mk oradbx) SQL*Plus: ALTER SESSION SET
CURRENT_SCHEMA = SYS
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 35
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
