Oracle Interview Questions

Oracle Interview Questions Download Oracle Interview Questions PDF

Oracle Interview Questions

The various rollback segment states are:

  • Invalid,
  • Needs recovery,
  • Partly available,
  • Offline and
  • Online.

Triggers giving Update/Insert to the table possessing referential integrity constraint on the triggering table are termed as constraining triggers.

Oracle is a database server that is used to handle data in a structured manner. It lets its users to retrieve and to store data in a way that multiple users can access similar data at the same time. Oracle achieves this with high efficiency. Many security checks are followed to limit access to authorized users only and to retrieve data in case of accidental data loss.

The constructs for a package, function or a procedure are:

  • Exceptions,
  • Cursors
  • Variables and constants

In order to prevent Grants from getting dropped we create and replace procedures rather than drop and recreate.

It’s a cursor formed by Oracle internally for individual SQL.

Every user is given particular attributes identified by a username termed as a user account. The below can be incorporated into the user attributes:

  • Passwords to access database,
  • Roles and privileges,
  • Default tablespace containing database objects and
  • Default temporary tablespace facilitating query handling workspace.

It is a file which has a binary nature and contains initialization parameter’s list. It is located machines where database server executes. Initialization parameters of server parameter file are persistent.

Each operating Oracle database is associated with an Oracle instance. As soon as a database server starts the database, it is assigned a memory area by Oracle called System Global Area (SGA) and starts one or more than one processes. The SGA and Oracle processes together are referred to as an Oracle instance. The process and memory of an instance are used to efficiently handle data used by multiple users.

Parts of a database trigger are

  • Trigger statement or event,
  • Trigger restriction and
  • Trigger action

Parameter file is a file having a track of initialization parameters and the matching values. The two types of Oracle supported parameter files are:

  • Initialization parameter file – Text version and
  • Server parameter file – Binary version

Based on individual installations users can specify their individual initialization parameters.

It is the memory area which contains shared data like SQL statements shared pool and buffer cache, between all users. As soon as an Oracle database instance starts the SGA is allocated. Value changes become effective during subsequent startup.

The five query types in Oracle are as follows:

  • Compound queries,
  • Nested queries,
  • Correlated queries,
  • Subqueries
  • Normal queries.