Interview Questions

AJAX Interview Questions Android Interview Questions Angular 2 Interview Questions AngularJs Interview Questions Apache Presto Interview Questions Apache Tapestry Interview Questions Arduino Interview Questions ASP.NET MVC Interview Questions Aurelia Interview Questions AWS Interview Questions Blockchain Interview Questions Bootstrap Interview Questions C Interview Questions C Programming Coding Interview Questions C# Interview Questions Cakephp Interview Questions Cassandra Interview Questions CherryPy Interview Questions Clojure Interview Questions Cobol Interview Questions CodeIgniter interview Questions CoffeeScript Interview Questions Cordova Interview Questions CouchDB interview questions CSS Buttons Interview Questions CSS Interview Questions D Programming Language Interview Questions Dart Programming Language Interview Questions Data structure & Algorithm Interview Questions DB2 Interview Questions DBMS Interview Questions Django Interview Questions Docker Interview Questions DOJO Interview Questions Drupal Interview Questions Electron Interview Questions Elixir Interview Questions Erlang Interview Questions ES6 Interview Questions and Answers Euphoria Interview Questions ExpressJS Interview Questions Ext Js Interview Questions Firebase Interview Questions Flask Interview Questions Flex Interview Questions Fortran Interview Questions Foundation Interview Questions Framework7 Interview Questions FuelPHP Framework Interview Questions Go Programming Language Interview Questions Google Maps Interview Questions Groovy interview Questions GWT Interview Questions Hadoop Interview Questions Haskell Interview Questions Highcharts Interview Questions HTML Interview Questions HTTP Interview Questions Ionic Interview Questions iOS Interview Questions IoT Interview Questions Java BeanUtils Interview Questions Java Collections Interview Questions Java Interview Questions Java JDBC Interview Questions Java Multithreading Interview Questions Java OOPS Interview Questions Java Programming Coding Interview Questions Java Swing Interview Questions JavaFX Interview Questions JavaScript Interview Questions JCL (Job Control Language) Interview Questions Joomla Interview Questions jQuery Interview Questions js Interview Questions JSF Interview Questions JSP Interview Questions KnockoutJS Interview Questions Koa Interview Questions Laravel Interview Questions Less Interview Questions LISP Interview Questions Magento Interview Questions MariaDB Interview Questions Material Design Lite Interview Questions Materialize CSS Framework Interview Questions MathML Interview Questions MATLAB Interview Questions Meteor Interview Questions MongoDB interview Questions Moo Tools Interview Questions MySQL Interview Questions NodeJS Interview Questions OpenStack Interview Questions Oracle DBA Interview Questions Pascal Interview Questions Perl interview questions Phalcon Framework Interview Questions PhantomJS Interview Questions PhoneGap Interview Questions Php Interview Questions PL/SQL Interview Questions PostgreSQL Interview Questions PouchDB Interview Questions Prototype Interview Questions Pure CSS Interview Questions Python Interview Questions R programming Language Interview Questions React Native Interview Questions ReactJS Interview Questions RequireJs Interview Questions RESTful Web Services Interview Questions RPA Interview Questions Ruby on Rails Interview Questions SAS Interview Questions SASS Interview Questions Scala Interview Questions Sencha Touch Interview Questions SEO Interview Questions Servlet Interview Questions SQL Interview Questions SQL Server Interview Questions SQLite Interview Questions Struts Interview Questions SVG Interview Questions Swift Interview Questions Symfony PHP Framework Interview Questions T-SQL(Transact-SQL) Interview Questions TurboGears Framework Interview Questions TypeScript Interview Questions UiPath Interview Questions VB Script Interview Questions VBA Interview Questions WCF Interview Questions Web icon Interview Questions Web Service Interview Questions Web2py Framework Interview Questions WebGL Interview Questions Website Development Interview Questions WordPress Interview Questions Xamarin Interview Questions XHTML Interview Questions XML Interview Questions XSL Interview Questions Yii PHP Framework Interview Questions Zend Framework Interview Questions Network Architect Interview Questions

Top 25 Oracle DBA Interview Question for 2020

1) What is an Oracle Instance?

Database instance is a set of memory structures that manages database file. When an instance is started Oracle database allocate memory area called SGA (System Global Area).

2) What is Control File ?

It is a small binary file which stores the Physical Structure of database. Every Database consists of a Control File.

3) Which file is accessed first when Oracle Database starts?

Order is:
  • spfile.ora
  • pfile.ora (if spfile.ora not accessed).

4) What does PMON and SMON process do?

PMON (Process Monitor): It is responsible for recovering of process when user process fails. It also, helps in process cleanup. SMON (System Monitor): It is responsible for the recovery of system after the failure. It also, helps in performing critical tasks as instance recovery and dead transaction recovery etc.

5) Describe Instance recovery?

Oracle database performs instance recovery when due to instance failure database is restarted and performs following actions:
  • ABORT shutdown
  • FORCE start-up

6) What is Tablespaces?

It allows database admin to define location in the file system where the file respective database objects can be stored.

7) What is Redo Log?

Redo Log is a recovery action which consists of two or more pre-existed files that store all the changes done to the database.

8) Define Checkpoint?

Checkpoint: It is defined as a point where if any modification takes place on database then the modification of that block is made to a memory copy of a block. Eventually when we commit block is not written but Redo Log is therefore, the system will checkpoint your modified blocks to disk.

9) Enlist the types of checkpoint in Oracle.

  • Full checkpoint
  • Thread checkpoint
  • File checkpoint
  • Parallel Query checkpoint
  • Object checkpoint
  • Log switch checkpoint
  • Incremental checkpoint

10) Give the name of process that reads and write data in data files.

Oracle Server Process reads the block. DBWR Process writes into the data files.

11) What is Shared Pool?

Shared pool is a memory area generated at the time of startup time in the RAM heap which is a component of SGA (System global area).

12) Differentiate between PFile and SPFile.

  • PFile is a "TEXT FILE" whereas SPFile is "BINARY FILE".
  • In PFile we cannot change parameter dynamically while we can in SPFile.
  • PFile can be maintained by OS Editor whereas for SPFile we need a Oracle Server.

13) Enlist the various Oracle Database Object.

  • Tables
  • Tablespaces
  • Views
  • Indexes
  • Synonyms

14) Give the different type of module in Oracle Forms.

  • Form module
  • Menu module
  • Pl/SQL Library module
  • Object Library module

15) Describe Stackspack tool of Oracle?

It is a package set of SQL,PL/SQL and SQL*Plus scripts which allows collection, automation, storage and view of performance of data.

16) What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

17) 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 demand for resources and adjusts supply accordingly.

18) What are the components of SGA?

  • Library cache
  • Data Dictionary cache
  • Database Buffer Cache
  • Redo log
  • Buffer cache
  • Shared Pool

19) What is a recovery catalog?

Recovery catalog is an inventory of backup taken by RMAN for the database.The size of the recovery catalog schema depends upon the number of databases monitored by the catalog.

20) What are the different stages of database startup?

Following stages are involved in the startup of database:
  • NoMount: Oracle Instance is available based on the parameters defined in SPFile.
  • Mount: Based on the Information from parameter control files location in spfile, it opens and reads them and available to next stage.
  • Open: Datafiles, redo log files are available to the end users.

21) What are the different methods we can shutdown our database?

Following stages are involved in the startup of database:
  • SHUTDOWN
  • SHUTDOWN TRANSACTIONAL
  • SHUTDOWN IMMEDIATE
  • SHUTDOWN ABORT

22) What is ALERT log file?

Alert log file is a log file that records database-wide events which is used for trouble shooting. We can find the Log file in BACKGROUND_DUMP_DEST parameter.

23) What are different types of locks?

Different types of user locks are given as follows:
  • UL Lock: Defined with dbms_lock package.
  • TX Lock: Acquired once for every transaction. It is a row transaction lock.
  • TM Lock: Acquired once for each object, which is being changed. It is a DML lock. The ID1 column identifies the object being modified.

24) What is large object in oracle?

Large objects (LOB’s) are exclusively used to hold large amounts of data. It can hold data in tetra bytes. Different types of LOBs include internal, external, persistent and temporary. Binary LOB’s are typically used to store graphics, video, or audio data.

25) What is a system change number (SCN)?

  • SCN is a value that is incremented whenever a dirty read occurs.
  • SCN is incremented whenever a deadlock occurs.
  • SCN is a value that keeps track of explicit locks.
  • SCN is a value that is incremented whenever database changes are made.