Oracle DB interview question for experienced/Oracle DB Interview Questions and Answers for Freshers & Experienced

When Oracle Allocates An Sga?

When Oracle starts, it reads the initialization parameter file to determine the values of initialization parameters. After this, it allocates an SGA and creates background processes.

What Is Server Virtualization?

Oracle Real Application Clusters 10g (RAC) enables a single database to run across multiple clustered nodes in a grid, pooling the processing resources of several standard machines.

What Is A Segment?

A segment is a group of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table-space

What Are Archiver Processes?

It copies redo log files to a designated storage device after a log switch has occurred. Archiver processes are there only when the database is in ARCHIVELOG mode, and automatic archiving is enabled. An Oracle instance can have up to 10 Archi ver processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of Archiver processes is insufficient to handle the workload.

What Are Job Queue Processes?

Job queue processes are used for processing batch. They run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs like PL/SQL statements or procedures on an Oracle instance.

What Is Recoverer Process?

The re-coverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures. The RECO process of a node automatically connects to other databases involved in an in- doubt distributed transaction.

What Are The Computing Components Of Oracle Grid?

* The computing componenets of oracle grid are
* Oracle Enterprise Manager and Grid Control
* Oracle 10g Database and Real Application Clusters.
* ASM Storage Grid

What Is The Difference Between Large Dedicated Server And Oracle Grid?

Large dedicated server
It has expensive costly components.
High incremental costs.
It has single point of failure.
Enterprise service at higher cost.
Oracle Grid
It has low cost modular components.
Low incremental costs.
It has no single point of failure.
Enterprise service at low cost.

Explain Oracle Grid Architecture?

Grid computing is a information technology architecture that provides lower cost enterprise information systems. Using grid computing, independent hardware, and software components can be connected and rejoined on demand to meet the changing needs of businesses. It also enables the use of smaller individual hardware components

How can you improve the performance of Sql*loader?

You can use direct path load to improve the performance. Indexes and constraints make inserts slow. Removing indexes and constraints improve performance of inserts; and therefore, of SQL*Loader.

What are the main aspects of oracle database security management?

* Controlling access to data(authorization).
* Restricting access to legitimate users (authentication).
* Ensuring accountability on part of the users(auditing).
* Safeguarding key data in the database(encryption).
* Managing the security of the entire organizational information structure (enterprise security).

What is Mutating Trigger?

Into a row-level trigger based on a table trigger body cannot read data from the same table and also we cannot perform DML operation on the same table.

<> If we are trying this oracle server returns mutating error oracle-4091: table is mutating.
<> This error is called mutating error, and this trigger is called a mutating trigger, and the table is called a mutating table.
<> Mutating errors are not occurred in statement-level trigger because through this statement-level trigger when we are performing DML operations automatically data committed into the database, whereas in the row-level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors occur.

What is a difference between ON DELETE CASCADE and ON DELETE SET NULL?

ON DELETE CASCADE Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL Coverts foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.

What is a SET UNUSED option?

SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column. A select query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column. The SET UNUSED information is stored in the USER_UNUSED_COL_TABS dictionary view.

Which tools can you use to start up an Oracle database?

You can start up a database with three tools.

SQL*Plus: This is the most widely used option. You first connect to an idle instance with SQL*Plus and then startup the instance with the “startup” command.
Oracle Enterprise Manager: This is another way of starting up a database. You can logon to Oracle Enterprise Manager even if the database is stopped. OEM will detect the status of the down database and will present you with a “Startup” button. You can startup the database by clicking this button.
RMAN: This is rather a less used tool for starting up a database but it is possible to startup a database from the Recovery Manager command line.

Which components of your database environment can be protected by an “Oracle Restart” configuration?

* Database Instances and Automatic Storage Management (ASM): Database instances and ASM instances will be restarted if they crash somehow.
* Oracle NET Listener: Oracle NET Listener will be started if it crashes and stops listening for an incoming connection.
* ASM Disk Groups: Oracle Restart will mount ASM Disk groups if they are dismounted.
* Database Services: Non-default database services will be started by Oracle Restart feature.
* Oracle Notification Services (ONS): This is another Oracle component that can be protected by Oracle Restart.

What do you mean by recovery catalog?

USER TABLES: Is a collection of tables created and maintained by the user. Contain USER information. DATA DICTIONARY: Is a collection of tables created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user.

What is a nested table and how is it different from a normal table?

A nested table is a database collection object, which can be stored as a column in a table. While creating a normal table, an entire nested table can be referenced in a single column. Nested tables have only one column with no restriction of rows.

For Example:

Here, we are creating a normal table as EMP and referring a nested table TYPE_NAME as a column.

What is locking in SQL?

Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are:

* Implicit Locking: Occurs for all SQL statements except SELECT.
* Explicit Locking: Can be done by the user manually.

Further, there are two locking methods:

Exclusive: Locks out other users
Share: Allows other users to access

What is a trace file and how is it created in oracle?

Trace files are files used to store details of exceptions thrown by Oracle background processes i.e. bar, low, pmon, smon, etc. They are usually created for diagnostic dumps as well and help in debugging and solving exceptions in Oracle.

To create a Trace file in oracle:

Set sql_trace=true with alter session command. This will generate a trace file for all sql commands issued by your user session. This is known as a level-1 trace file. One can also create super detailed level-4 trace files with additional details if the need be. These files are stored in the form $ORACLE_SID_ora_xxx.trc in the trace directory, where xxx is a sequential number. To create a level-4 detailed trace file, we need to know the SID and SERIAL# for the session to trace. Eg: to trace for a session for SID 5: Connect system as sysdba; oradebug SETOSPID 5; oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL4

Explain Oracle Grid Architecture?

The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes the demand for resources and adjusts supply accordingly.

What are the packages in PL SQL?

A Package is a group of related database objects like stored procs, functions, types, triggers, cursors, etc. that are stored in the Oracle database. It is a kind of library of related objects which can be accessed by multiple applications if permitted.

In what scenario you can modify a column in a table?

<> You can increase the width or precision of a numeric column.
<> You can increase the width of numeric or character columns.
<> You can decrease the width of a column only if the column contains null values or if the table has no rows.
<> You can change the data type only if the column contains null values

You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size.

Explain the procedure of restoring RMAN for the recovery if all the instances are down.

Following is the procedure of restoring RMAN for the recovery:

1. Bring all the nodes down.
2. Start a node.
3. Restore all the data files and archive logs.
4. Recover the node.
5. Open the database.
6. Bring other nodes up.
7. Confirm that all nodes are operational.

What is a DEFAULT option in a table?

A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.

What is an ASM Disk Group?

It is a group of disks that ASM manages as a unit. Within the disk groups, a file system interface maintains the Oracle database files. ASM simplifies database storage by consolidating disks into disk groups. This reduces I/O overhead.

What is an ASM instance?

It is an instance that manages ASM disk groups. It comprises the System Global Area(SGA) and background processes. ASM instance mounts a disk group that is made available to the database instance. An ASM instance manages the metadata of a disk group and provides file layout information to the database instances.

What are the dynamic performance views? Who has the access to these views?

* Dynamic performance views are also called V$ views. These views provide information about the sessions.

* Any Oracle user can get information from dynamic performance views if the user has to select any table privilege. This privilege is generally granted through the SELECT_CATALOG_ROLE role.

Why is performance tuning a menacing area for DBA's?

The performance tuning field can't be automated like other features of exp/imp, backup recovery.This is an area that requires more detective work to be carried on part of application programmers and DBA's to check how some process is running slower than expected, why can't we scale applications to a larger number of users without problems like performance degradation etc. This is an area where the technical knowledge must be used along with constant experimentation and observation.

Explain all Joins used in Oracle 9i and later release?

Cross Join: Cross Join clause produces the cross-product of two tables. This is same as a Cartesian product between the two tables.

Natural Joins: Is used to join two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. It is equal to the Equi-Join. If the columns have the same names but different data types, then the Natural Join syntax causes an error.

Join with the USING clause: If several columns have the same names but the data types do not match, then the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equi Join. Use the USING clause to match only one column when more than one column matches. Do not use a table name or alias in the referenced columns. The NATURAL JOIN clause and USING clause are mutually exclusive.

Explain all Joins used in Oracle 8i?

Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where” clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions. Forex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.

Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.

Non-Equi Joins A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=). The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.

Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information (Top 50 Oracle Interview Questions and Answers pdf). The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator

How do you find the total database size in the database?

You can use the following database views to get the information on database size:

* Dba_segments: It provides information about the used space. You can take a total of all the bytes in the dba_segments view to get the used space.
* Dba_data__files: It provides information on space allocated to datafiles for permanent tablespaces.
* v$log: It provides information on redo log files.

What are the benefits of ORDBMS?

The objects as such can be stored in the database. The language of the DBMS can be integrated with an object-oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.

How do you merge two tables?

Data from different tables can be combined using MERGE command. It works by selecting and updating data in the source table and the second table respectively based on the query provided.

Can we save images in a database and if yes, how?

BLOB stands for Binary Large Object, which is a data type that is generally used to hold images, audio & video files or some binary executables.

What are Constraints and how can we use them?

Constraints are rules that you set up for your data.
They can be used either during table creation or later, to alter the table.

In what ways can you write comments in Oracle?

For single statements, you can use two dashes (--).
For a block statement you can use (*--*).

What do you mean by a database transaction & what all TCL statements are available in Oracle?

Transaction occurs when a set of SQL statements are executed in one go. To control the execution of these statements, Oracle has introduced TCL i.e. Transaction Control Statements that use a set of statements

Explain primary key and unique key in Oracle?

A primary key is used to identify each table row uniquely. A primary key can be only one on the table and it cannot hold the null value in the table and we cannot insert any duplicate or null value in a table for that specific column on which the primary key constraint has applied. It is referred to as cluster index. Unique key mainly used to refrain the duplication of values in the table. In the table, unique keys can be multiple and it can hold one null value per column. It referred to as a non-cluster index.

What is the logical structure of the disk resources?

Following is the logical structure of the disk resources:

* Data block: The data block refers to the smallest logical storage unit. Size of a data block is a multiple of the operating system block size.
* Extent: It refers to the contiguous set of data blocks, which is allocated as a unit to a segment.
* Tablespace: The tablespace refers to the final logical storage unit. It is mapped to the physical datafile.
* Segment: The segment allocates a logical structure, such as a table. It is a set of extents, which are stored in the same tablespace.

Why do we need integrity constraints in a database?

Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables .

What are the differences between EBU and RMAN?

* Enterprise Backup Utility (EBU) is a functionally rich, high-performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility.

* The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7's EBU utility. However, there is no direct upgrade path from EBU to RMAN.

Name the various Oracle database objects?

The various Oracle database objects are:

* Sequences.
* Views.
* Tables.
* Tablespaces.
* Indexes.

Is there a difference between Alias and Rename?

Yes, there is.
Rename is an invariable permanent name assigned to a record, while Alias is a temporary name.

What are Privileges and what is their purpose?

Privileges are granted access to another user's data or the ability to execute a command.
They are used in creating roles and can be enabled and disabled for any user.

What are Errors and how do you deal with them?

They are exceptions that occur during programming and can be usually handled by using EXCEPTION block command.

Find the error in the below code snippet if any?

SELECT student_id s_id, student_name name, birthdate date, student_number s_no FROM students;

Here, a reserved keyword ‘date’ has been used as an alias for the column birthdate. This action is illegal in Oracle SQL. In order to set a reserved keyword as an alias, we can use quotation marks. SELECT student_id s_id, student_name name, birthdate “date”, student_number s_no FROM students;

What are SQL functions? Describe in brief different types of SQL functions?

SQL Functions are a very powerful feature of SQL. SQL functions can take arguments but always return some value. There are two distinct types of SQL functions:

1) Single-Row functions: These functions operate on a single row to give one result per row.

Types of Single-Row functions:

* Character
* Number
* Date
* Conversion
* General

2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:


What do you mean by Redo Log file mirroring?

The process of having a copy of redo log files is called mirroring. It is done by creating a group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group. In case a group fails, the database automatically switches over to the next group. It diminishes the performance. Hot backup vs. cold backup A database backup, while it is still up and running, is a Hot backup and it must be in archive log mode. A cold backup is a backup while it is shut down. The database does not require being in archive log mode in this mode. The benefit of a hot backup is that the database is still available for use while the backup is occurring. A cold backup is easier to administer the backup and recovery process. Cold backups do not require being in archive log mode and thus slight performance gain as the database is not writing archive logs to disk.

What is meant by an index?

An index is a schema object, which is created to search the data efficiently within the table. Indexes are usually created on certain columns of the table, which are accessed the most. Indexes can be clustered or non-clustered.

R4R Team
R4R provides Oracle DB Freshers questions and answers (Oracle DB Interview Questions and Answers) .The questions on website is done by expert team! Mock Tests and Practice Papers for prepare yourself.. Mock Tests, Practice Papers,Oracle DB interview question for experienced,Oracle DB Freshers & Experienced Interview Questions and Answers,Oracle DB Objetive choice questions and answers,Oracle DB Multiple choice questions and answers,Oracle DB objective, Oracle DB questions , Oracle DB answers,Oracle DB MCQs questions and answers Java, C ,C++, ASP, C# ,Struts ,Questions & Answer, Struts2, Ajax, Hibernate, Swing ,JSP , Servlet, J2EE ,Core Java ,Stping, VC++, HTML, DHTML, JAVASCRIPT, VB ,CSS, interview ,questions, and answers, for,experienced, and fresher R4r provides Python,General knowledge(GK),Computer,PHP,SQL,Java,JSP,Android,CSS,Hibernate,Servlets,Spring etc Interview tips for Freshers and Experienced for Oracle DB fresher interview questions ,Oracle DB Experienced interview questions,Oracle DB fresher interview questions and answers ,Oracle DB Experienced interview questions and answers,tricky Oracle DB queries for interview pdf,complex Oracle DB for practice with answers,Oracle DB for practice with answers You can search job and get offer latters by studing .learn in easy ways .