DEVFYI - Developer Resource - FYI

ORACLE Interview Questions and Answers

Part:   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  36  37  38  39  40  41  42  43  44  45  46  47  48  49  50  51  52  53  54  55  56  57  58  59  60  61  62  63  64  65  66  67  68  69  70  71  72  73  74  75  76  77  78  79  80  81  82  83  84  85  86  87  88  89  90  91  92  93  94  95  96  97  98  99  100  101  102  103  104  105  106 

(Continued from previous part...)

41. What is a Tablespace?

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together


42. What is Rollback Segment ?

A Database contains one or more Rollback Segments to temporarily store "undo" information.


43. What are the Characteristics of Data Files ?

A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.


44. How to define Data Block size ?

A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.


45. What does a Control file Contain ?

A Control file records the physical structure of the database. It contains the following information.
Database Name
Names and locations of a database's files and redolog files.
Time stamp of database creation.


46.What is difference between UNIQUE constraint and PRIMARY KEY constraint ?

A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls.


47.What is Index Cluster ?

A Cluster with an index on the Cluster Key


48.When does a Transaction end ?

When it is committed or Rollbacked.


49. What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ? What are the factors that affect OPTIMIZER in choosing an Optimization approach ?

Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.


50. What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?

The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

(Continued on next part...)

Part:   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  36  37  38  39  40  41  42  43  44  45  46  47  48  49  50  51  52  53  54  55  56  57  58  59  60  61  62  63  64  65  66  67  68  69  70  71  72  73  74  75  76  77  78  79  80  81  82  83  84  85  86  87  88  89  90  91  92  93  94  95  96  97  98  99  100  101  102  103  104  105  106 

ORACLE Interview Questions and Answers