Blog Archive

Theme images by Storman. Powered by Blogger.

Wednesday, August 17, 2016

Oracle 11g DBA Interview Questions And Answers.


Oracle 11g DBA Interview Questions And Answers. Oracle DBA Latest Interview Questions and Answers. Most Frequently Asked Oracle DBA Interview Questiions and Answers.Oracle DBA Interview Questions For Freshers And Experienced.

What is a database?

Database offer a single point of mechanism for storing and retrieving information with the help of tables.
Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute. 
It is a structure that stores information about the attributes of the entities and relationships among them.
It also stores data types for attributes and indexes.
Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.
What are the different types of storage systems available and which one is used by Oracle?

Two types of storage systems are available
Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)

Most databases use RDBMS model, Oracle also uses RDBMS model.
Hierarchical Storage Management System (HSM)
Information Management System (IMS) from IBM.
Integrated Database Management System (IDMS) from CA.
Explain some examples of join methods.

Join methods are of mainly 3 types.

Merge Join -
Sorting both the tables using join key and then merge the rows which are sorted.

Nested loop join 
It gets a result set after applying filter conditions based on the outer table.
Then it joins the inner table with the respective result set.

Hash join 
It uses hash algorithm first on smaller table and then on the other table to produce joined columns. After that matching rows are returned.
What are the components of logical data model and list some differences between logical and physical data model?

Components of logical data model are 

Entity Entity refers to an object that we use to store information. It has its own table.

Attribute It represents the information of the entity that we are interested in. It is stored as a column of the table and has specific datatype associated with it.

Record It refers to a collection of all the properties associated with an entity for one specific condition, represented as row in a table.

Domain It is the set of all the possible values for a particular attribute.

Relation Represents a relation between two entities.

Difference between Logical and Physical data model.

Logical data model represents database in terms of logical objects, such as entities and relationships.

Physical data model represents database in terms of physical objects, such as tables and constraints.
What is normalization? What are the different forms of normalization?

Normalization is a process of organizing the fields and tables of a relational database to minimize redundancy and dependency.
It saves storage space and ensures consistency of our data.

There are six different normal forms

First Normal Form If all underlying domains contain atomic values only.

Second Normal Form If it is in first normal form and every non key attribute is fully functionally dependent on primary key.

Third Normal Form If it is in 2nd normal form and every non key attribute is non transitively dependent on the primary key.

Boyce Codd Normal Form A relation R is in BCNF if and only every determinant is a candidate key.

Fourth Normal Form 

Fifth Normal Form
Differentiate between a database and Instance and explain relation between them?

Database is a collection of three important files which include data files, control files and redo log files which physically exist on a disk

Whereas instance is a combination of oracle background process (SMON, PMON, DBWR, LGWR) and memory structure (SGA, PGA).

Oracle background processes running on a computer share same memory area. 

An instance can mount and open only a single database, ever.

A database may be mounted and opened by one or more instances (using RAC).
What are the components of SGA?

SGA is used to store shared information across all database users.
It mainly includes Library cache, Data Dictionary cache, Database Buffer Cache, Redo log Buffer cache, Shared Pool.

Library cache It is used to store Oracle statements.

Data Dictionary Cache It contains the definition of Database objects and privileges granted to users.

Data Base buffer cache It holds copies of data blocks which are frequently accessed, so that they can be retrieved faster for any future requests.

Redo log buffer cache It records all changes made to the data files.
Difference between SGA and PGA.

SGA (System Global Area) is a memory area allocated during an instance start up.
SGA is allocated as 40% of RAM size by default.
SGA size is controlled by DB_CACHE_SIZE parameter defined in initialization parameter file (init.ora file or SPFILE).

PGA (Program or Process Global Area) is a memory area that stores a user session specific information.
PGA is allocated as 10% of RAM size by default.
What are the disk components in Oracle?

These are the physical components which gets stored in the disk.

Data files
Redo Log files
Control files 
Password files
Parameter files
What is System Change Number (SCN)?

SCN is a unique ID that Oracle generates for every committed transaction. 
It is recorded for every change in the redo entry.
SCN is also generated for every checkpoint (CKPT) occurred.
It is an ever increasing number which is updated for every 3 seconds
You can get the SCN number by querying select SCN from v$database from SQLPLUS.

What is large object in oracle? Explain its purposes

Large objects (LOB’s) are exclusively used to hold large amounts of data. It can hold data in tetra bytes.......
Types of large objects in oracle
BLOBs, CLOBs, and NCLOBs are Internal LOBs stored inside database tablespaces to optimize space and efficient access......
Define read-only replication and its uses
Read only replication creates local copy of table data originating from one or more remote master tables......

0 on: "Oracle 11g DBA Interview Questions And Answers."