Oracle Interview Questions – Part 23
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.
.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
Q.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.
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.
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.
Q.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.
Q.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.
Q.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 …
Q.Give the sequence of execution of the various report triggers?
Before form , After form , Before report, Between page, After report.
Q.What is an LOV?
An LOV is a scrollable popup window that provides the operator with either a single or multi column selection list.
Q.What is the User-Named Editor?
A user named editor has the same text editing functionality as the default editor, but, because it is a named object, you can specify editor attributes such as windows display size, position, and title.
Q.What is the frame & repeating frame?
A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. of records that are to displayed is not known before.
Q.What is a combo box?
A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.
Q.List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?
SYSTEM – Data dictionary tables.
DATA – Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES – Indexes for Standard operational tables.
INDEXES1 – Indexes of static tables used for standard operations.
TOOLS – Tools table.
TOOLS1 – Indexes for tools table.
RBS – Standard Operations Rollback Segments,
RBS1,RBS2 – Additional/Special Rollback segments.
TEMP – Temporary purpose tablespace
TEMP_USER – Temporary tablespace for users.
USERS – User tablespace.
Q.How to implement the multiple control files for an existing database ?
Shutdown the database Copy one of the existing control file to new location Edit Config ora file by adding new control file. name Restart the database.
Q.What is a logical backup?
Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.
Q.Where can one get a list of all hidden Oracle parameters? (for DBA)
Oracle initialization or INIT.ORA parameters with an underscore in front are hidden or unsupported parameters. One can get a list of all hidden parameters by executing this query:
where substr(KSPPINM,1,1) = ‘_’;
The following query displays parameter names with their current value:
select a.ksppinm “Parameter”, b.ksppstvl “Session Value”, c.ksppstvl “Instance Value”
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
order by a.ksppinm;
Remember: Thou shall not play with undocumented parameters!
Q.What is meant by free extent ?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.
Q.What is the use of IGNORE option in IMP command ?
A flag to indicate whether the import should ignore errors encounter when issuing CREATE commands.
Q.What is the use of FULL option in EXP command ?
A flag to indicate whether full databse export should be performed.
Q.What is the use of SHOW option in IMP command ?
A flag to indicate whether file content should be displayed or not.
Q.What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS ?
These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively. For Eg, instead of inserting a row in a table an existing row can be updated in the same table.
Q.What are the types of Pop-up window ?
the pop-up field editor
pop-up list of values
Q.What do you mean by a page ?
Pages are collection of display information, such as constant text and graphics
Q.What are the type of User Exits ?
ORACLE Precompliers user exits
OCI (ORACLE Call Interface)
Non-ORACEL user exits.
Q.Name the two files that are created when you generate the form give the filex extension ?
INP (Source File)
FRM (Executable File)
Q.What package procedure used for invoke sql *plus from sql *forms ?
Host (E.g. Host (sqlplus))
Q.How can one optimize XYZ queries? (for DBA)
It is possible to improve XYZ queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints. If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.
Q.What Enter package procedure does ?
Enter Validate-data in the current validation unit.
Q.When should one rebuild an index? (for DBA)
You can run the ‘ANALYZE INDEX VALIDATE STRUCTURE’ command on the affected indexes – each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The ‘badness’ of the index can then be judged by the ratio of ‘DEL_LF_ROWS’ to ‘LF_ROWS’.
Q.What are the unrestricted procedures used to change the popup screen position during run time ?
Q.What is the maximum size of a form ?
255 character width and 255 characters Length.
Q.What is the difference between system.current_field and system.cursor_field ?
1.System.current_field gives name of the field.
2.System.cursor_field gives name of the field with block name.