|
Oracle Interview Questions and Answers
What third party tools can be used with Oracle EBU/
RMAN? (for DBA)
The following Media Management Software Vendors have
integrated their media management software packages with
Oracle Recovery Manager and Oracle7 Enterprise Backup
Utility. The Media Management Vendors will provide first
line technical support for the integrated backup/recover
solutions.
Veritas NetBackup
EMC Data Manager (EDM)
HP OMNIBack II
IBM's Tivoli Storage Manager - formerly ADSM
Legato Networker
ManageIT Backup and Recovery
Sterling Software's SAMS:Alexandria - formerly from
Spectralogic
Sun Solstice Backup
Why and when should one tune? (for DBA)
One of the biggest responsibilities of a DBA is to
ensure that the Oracle database is tuned properly. The
Oracle RDBMS is highly tunable and allows the database
to be monitored and adjusted to increase its
performance. One should do performance tuning for the
following reasons:
The speed of computing might be wasting valuable human
time (users waiting for response); Enable your system to
keep-up with the speed business is conducted; and
Optimize hardware usage to save money (companies are
spending millions on hardware). Although this FAQ is not
overly concerned with hardware issues, one needs to
remember than you cannot tune a Buick into a Ferrari.
How can a break order be created on a column in an
existing group? What are the various sub events a mouse
double click event involves?
By dragging the column outside the group.
What is the use of place holder column? What are the
various sub events a mouse double click event involves?
A placeholder column is used to hold calculated values
at a specified place rather than allowing is to appear
in the actual row where it has to appear.
What is the use of hidden column? What are the various
sub events a mouse double click event involves?
A hidden column is used to when a column has to embed
into boilerplate text.
What database aspects should be monitored? (for DBA)
One should implement a monitoring system to constantly
monitor the following aspects of a database. Writing
custom scripts, implementing Oracle's Enterprise
Manager, or buying a third-party monitoring product can
achieve this. If an alarm is triggered, the system
should automatically notify the DBA (e-mail, page, etc.)
to take appropriate action.
Infrastructure availability:
. Is the database up and responding to requests
. Are the listeners up and responding to requests
. Are the Oracle Names and LDAP Servers up and
responding to requests
. Are the Web Listeners up and responding to requests
Things that can cause service outages:
. Is the archive log destination filling up?
. Objects getting close to their max extents
. User and process limits reached
Things that can cause bad performance:
See question "What tuning indicators can one use?".
Where should the tuning effort be directed? (for DBA)
Consider the following areas for tuning. The order in
which steps are listed needs to be maintained to prevent
tuning side effects. For example, it is no good
increasing the buffer cache if you can reduce I/O by
rewriting a SQL statement. Database Design (if it's not
too late):
Poor system performance usually results from a poor
database design. One should generally normalize to the
3NF. Selective denormalization can provide valuable
performance improvements. When designing, always keep
the "data access path" in mind. Also look at proper data
partitioning, data replication, aggregation tables for
decision support systems, etc.
Application Tuning:
Experience showed that approximately 80% of all Oracle
system performance problems are resolved by coding
optimal SQL. Also consider proper scheduling of batch
tasks after peak working hours.
Memory Tuning:
Properly size your database buffers (shared pool, buffer
cache, log buffer, etc) by looking at your buffer hit
ratios. Pin large objects into memory to prevent
frequent reloads.
Disk I/O Tuning:
Database files needs to be properly sized and placed to
provide maximum disk subsystem throughput. Also look for
frequent disk sorts, full table scans, missing indexes,
row chaining, data fragmentation, etc
Eliminate Database Contention:
Study database locks, latches and wait events carefully
and eliminate where possible. Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory
utilization. For more information, read the related
Oracle FAQ dealing with your specific operating system.
What are the various sub events a mouse double click
event involves? What are the various sub events a mouse
double click event involves?
Double clicking the mouse consists of the mouse down,
mouse up, mouse click, mouse down & mouse up events.
What are the default parameter that appear at run time
in the parameter screen? What are the various sub events
a mouse double click event involves?
Destype and Desname.
What are the built-ins used for Creating and deleting
groups?
CREATE-GROUP (function)
CREATE_GROUP_FROM_QUERY(function)
DELETE_GROUP(procedure)
What are different types of canvas views?
Content canvas views
Stacked canvas views
Horizontal toolbar
vertical toolbar.
What are the different types of Delete details we can
establish in Master-Details?
Cascade
Isolate
Non-isolate
What is relation between the window and canvas views?
Canvas views are the back ground objects on which you
place the interface items (Text items), check boxes,
radio groups etc.,) and boilerplate objects (boxes,
lines, images etc.,) that operators interact with us
they run your form . Each canvas views displayed in a
window.
What is a User_exit?
Calls the user exit named in the user_exit_string.
Invokes a 3Gl program by name which has been properly
linked into your current oracle forms executable.
How is it possible to select generate a select set for
the query in the query property sheet?
By using the tables/columns button and then specifying
the table and the column names.
How can values be passed bet. precompiler exits & Oracle
call interface?
By using the statement EXECIAFGET & EXECIAFPUT.
How can a square be drawn in the layout editor of the
report writer?
By using the rectangle tool while pressing the
(Constraint) key.
How can a text file be attached to a report while
creating in the report writer?
By using the link file property in the layout boiler
plate property sheet.
How can I message to passed to the user from reports?
By using SRW.MESSAGE function.
Does one need to drop/ truncate objects before
importing? (for DBA)
Before one import rows into already populated tables,
one needs to truncate or drop these tables to get rid of
the old data. If not, the new data will be appended to
the existing tables. One must always DROP existing
Sequences before re-importing. If the sequences are not
dropped, they will generate numbers inconsistent with
the rest of the database. Note: It is also advisable to
drop indexes before importing to speed up the import
process. Indexes can easily be recreated after the data
was successfully imported.
How can a button be used in a report to give a drill
down facility?
By setting the action associated with button to Execute
pl/sql option and using the SRW.Run_report function.
Can one import/export between different versions of
Oracle? (for DBA)
Different versions of the import utility is upwards
compatible. This means that one can take an export file
created from an old export version, and import it using
a later version of the import utility. This is quite an
effective way of upgrading a database from one release
of Oracle to the next.
Oracle also ships some previous catexpX.sql scripts that
can be executed as user SYS enabling older imp/exp
versions to work (for backwards compatibility). For
example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql
on an Oracle 8 database to allow the Oracle 7.3 exp/imp
utilities to run against an Oracle 8 database.
What are different types of images?
Boiler plate imagesImage Items
Can one export to multiple files?/ Can one beat the Unix
2 Gig limit? (for DBA)
From Oracle8i, the export utility supports multiple
output files. This feature enables large exports to be
divided into files whose sizes will not exceed any
operating system limits (FILESIZE= parameter). When
importing from multi-file export you must provide the
same filenames in the same sequence in the FILE=
parameter. Look at this example:
exp SCOTT/TIGER FILE=D:\F1.dmp,E:\F2.dmp FILESIZE=10m
LOG=scott.log
Use the following technique if you use an Oracle version
prior to 8i:
Create a compressed export on the fly. Depending on the
type of data, you probably can export up to 10 gigabytes
to a single file. This example uses gzip. It offers the
best compression I know of, but you can also substitute
it with zip, compress or whatever.
# create a named pipe
mknod exp.pipe p
# read the pipe - output to zip file in the background
gzip < exp.pipe > scott.exp.gz &
# feed the pipe
exp userid=scott/tiger file=exp.pipe ...
What is bind reference and how can it be created?
Bind reference are used to replace the single value in
sql, pl/sql statements a bind reference can be created
using a (:) before a column or a parameter name.
How can one improve Import/ Export performance? (for
DBA)
EXPORT:
. Set the BUFFER parameter to a high value (e.g. 2M)
. Set the RECORDLENGTH parameter to a high value (e.g.
64K)
. Stop unnecessary applications to free-up resources for
your job.
. If you run multiple export sessions, ensure they write
to different physical disks.
. DO NOT export to an NFS mounted filesystem. It will
take forever.
IMPORT:
. Create an indexfile so that you can create indexes
AFTER you have imported data. Do this by setting
INDEXFILE to a filename and then import. No data will be
imported but a file containing index definitions will be
created. You must edit this file afterwards and supply
the passwords for the schemas on all CONNECT statements.
. Place the file to be imported on a separate physical
disk from the oracle data files
. Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i)
considerably in the init$SID.ora file
. Set the LOG_BUFFER to a big value and restart oracle.
. Stop redo log archiving if it is running (ALTER
DATABASE NOARCHIVELOG;)
. Create a BIG tablespace with a BIG rollback segment
inside. Set all other rollback segments offline (except
the SYSTEM rollback segment of course). The rollback
segment must be as big as your biggest table (I think?)
. Use COMMIT=N in the import parameter file if you can
afford it
. Use ANALYZE=N in the import parameter file to avoid
time consuming ANALYZE statements
. Remember to run the indexfile previously created
Give the sequence of execution of the various report
triggers?
Before form , After form , Before report, Between page,
After report.
What are the common Import/ Export problems? (for DBA )
ORA-00001: Unique constraint (...) violated - You are
importing duplicate rows. Use IGNORE=NO to skip tables
that already exist (imp will give an error if the object
is re-created).
ORA-01555: Snapshot too old - Ask your users to STOP
working while you are exporting or use parameter
CONSISTENT=NO
ORA-01562: Failed to extend rollback segment - Create
bigger rollback segments or set parameter COMMIT=Y while
importing
IMP-00015: Statement failed ... object already exists...
- Use the IGNORE=Y import parameter to ignore these
errors, but be careful as you might end up with
duplicate rows.
Why is it preferable to create a fewer no. of queries in
the data model?
Because for each query, report has to open a separate
cursor and has to rebind, execute and fetch data.
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
|
