DBMS Questions and Answers for Interview (Part-IV)

46. How do you analyse TKPROF ?

TKPROF filename.tra O/P file EXPLAIN = USR/PWD0

47. what parameter variables to be set to use TKPROF ?

SQL PROF

48. How many types of lockings are there ?

5 types of locks.

To lock is to temporarily restrict other user’s access to data. The restriction is placed on such data is called “a lock”. The modes are SHARE, SHARE UPDATE,EXCLUSIVE,ROW SHARE AND ROW EXCLUSIVE. Not all locks can be acquired in all modes.

49. What is a SHARE LOCK ?

A SHARE lock is one that permits other users to query data, but not to change it.

50. What is a SHARE UPDATE LOCK ?

A SHARE UPDATE lock is one that permits other users to both query and lock data.

51. What is an EXCLUSIVE LOCK ?

An EXCLUSIVE LOCK is one that permits other users to query data, but not to change it. It differs from the SHARE lock because it does not permit another user to place any type of lock on the same data; several users may place SHARE locks on the same data at the same time.

52 What is ROWSHARE, SHAREUPDATE and ROW EXCLUSIVE locks ?

With a ROW SHARE or SHARE UPDATE lock, no users can lock the whole table for exclusive access, allowing concurrent access for all users to the table. The two types of locks are synonymous, and SHARE UPDATE exists for compatibility with previous versions of ORACLE.

ROW EXCLUSIVE locks are similar to ROW SHARE but they prohibit shared locking, so only one user user may access the table at the same time.

53. What is a DEAD LOCK ?

A DEAD lock is a rare situation in which two or more user processes of a database cannot complete their tansactions.This occurs because each process is holding a resource that the other process requires (such as a row in a table) in order to complete.Although these situations occur rarely, ORACLE detects and resolves deadlocks by rolling back the work of one of the processes.

54. How do you analyse which resources has locked for what ?

Use MONITOR SESSION.

55. How to kill a SESSION ?

ALTER SESSION KILL ID, NUMBER FROM SQLDBA;

56. What are USER_EXITS ?

It is an utility in SQL*FORMS for making use of HOST 3 GL languages for the purpose like ONLINE PRINTING etc.

57. When will you use the trigger WHEN-NEW-FORM-INSTANCE ?

At FORMS STARTUP Oracle navigates to the first navigable item in the first navigable block. This trigger fires after successful completion of any Navigational trigger (i.e) It will not fire if the control retuns to the CALLING FORM from the CALLED FORM.

Usage: For initialization at FORMS STARTUP.

58. What is an INDEX ? Why are indexes used in a table ?

INDEX is a general term for an ORACLE / SQL feature used primarily to speed execution an impose UNIQUENESS upon certain data. INDEX provides a faster access method to one table’s data than doing a full table scan. There are several types of Indexes :

UNIQUE INDEX, COMPRESSED INDEX, CONCATENATED INDEX. An Index has an entry for each value found in the table’s Indexed field(s) ( except those with a NULL value ) and pointer(s) to the rows having that value.

59. What is an UNIQUE INDEX ?

An UNIQUE INDEX ia an index that imposes uniqueness on each value in indexes. The index may be one column or concatenated columns.

60 What is an COMPRESSED INDEX ?

A COMPRESSED INDEX is an index for which only enough index information is stored to identify unique enrties; information that an index stores with the previous or following key is “compressed” (truncated) and not stored to reduce the storage overhead required by an index.

61. What is an CONCATENATED INDEX ?

A CONCATENATED INDEX is one that is created on more than one column of a table. It can be used to guarentee that those columns are unique for every row in the table and to speed access to rows via those columns

62. What is a UNION, UNION ALL,INTERSECTION and MINUS operator ?

The UNION operator returns ALL DISTINCT ROWS selected by either query.

The UNION ALL operator returns ALL ROWS selected by either query including duplicates.

The INTERSECTION operator returns ONLY ROWS that are COMMON to both the queries.

The MINUS operator returns ALL DISTINCT ROWS selected only by the first query and not by the second.

63. What does ‘GROUP BY’ statement do ?

GROUP BY statement causes a SELECT statement to produce ONE SUMMARY ROW for all selected rows that have identical values in one or more specified column or expressions. Each expe\ressionin the SELECT clause must be one of the following :

1] A CONSANT

2] A Function without parameters

3] A GROUP function like SUM , AVG.

4] Matched IDENTICALLY to a expression in the ‘GROUP BY’ clause.

64. In 2 SELECT statements SELECT A FROM DUAL; and SELECT B FROM DUAL; What will be

the difference in using ‘UNION’ and ‘UNION ALL’ ?

UNION returns all distinct rows selected by either of the query, whereas UNION ALL returns ALL ROWS selected by either query including duplicates.

64. Give one example where you will use DATABASE TRIGGERS ?

For AUDITING purposes we use database triggers.

65. Do you have any idea about ROW-CHAINING ? How will you resolve the issue if there is row-

chaining in a table ?

When a row NO LONGER FITS WITHIN THE DATABLOCK, it is stored in more than one database block, and that therefore have several row pieces.

Resolving: Use ANALYZE to identify chained rows and also provides statistics on the chained rows. Eg: ANALYZE ledger LIST CHAINED ROWS INTO CHAINED_ROWS:

(CHAINED_ROWS is a user defined table)

For creating chained_rows run the UTLCHAIN.SQL script.