Oracle Interview Questions and Answers on SQL Queries and Database Theory

1. What is oracle database ?

Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other

2. 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.

3. 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.

4. 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.

5. 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.

6. What is storage virtualization?

The Oracle Automatic Storage Management (ASM) is a feature of Oracle Database 10g which provides a virtual layer between the database and storage so that group of disks can be treated as a single disk group and disks can be dynamically added or removed while keeping databases online.

Also Read Basic to Advanced Oracle SQL Query Interview Question and Answers

7. What is Grid Management feature?

The Grid Management feature of Oracle Enterprise Manager 10g provides a single console to manage multiple systems together as a logical group.

8. 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.

9. What is an oracle instance?

When you start, the database instance comes into picture into system memory. Combination of the SGA and the Oracle processes is called an Oracle instance.

10. What are the several tools for interacting with the oracle database using sql?

There are several tools for interfacing with the database using SQL:

·         Oracle SQL*Plus and iSQL*Plus 

·         Oracle Forms, Reports, and Discoverer

·         Oracle Enterprise Manager 

·         Third-party tools

11. How oracle works?

·         An instance has started on the database server.

·         A client established a connection to the server, using the proper Oracle Net Services driver.

·         The server creates a dedicated server process on behalf of the user process.

·         The user executes SQL statement and commits the transaction.

·         The server process receives the statement and checks for any shared SQL area that contains a similar SQL.

·         The server process retrieves data from datafile (table) or SGA.

·         The server process modifies data in the SGA area. The DBWn process writes modified blocks permanently to disk. The LGWR process records the transaction in the redo log file.

·         The server process sends a message to the application.

12. What contains oracle physical database structure?

It contains

·         Datafiles

·         Control Files

·         Redo Log Files

·         Archive Log Files

·         Parameter Files

·         Alert and Trace Log Files

·         Backup Files

13. What is a Tablespace?

Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema's and a schema can have multiple tablespace's. Database creates "SYSTEM tablespace" by default during database creation. It contains read only data dictionary tables which contains the information about the database. 

14. What is a Control File ?

Control file is a binary file which stores Database name, associated data files, redo files, DB creation time and current log sequence number. Without control file database cannot be started and can hamper data recovery.

15. What are data blocks?

Oracle stores data in data blocks also called as logical blocks, Oracle blocks or pages. A data block represents specific number of bytes of space on disk.

16. What is an extent?

An extent is a specific number of consecutive data blocks allocated for storing a specific type of information.

17. 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.

18. What is Rollback Segment ? 

Database contain one or more Rollback Segments to roll back transactions and data recovery.

19. What are the different type of Segments ? 

Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment.

20. What is an oracle schema?

A user account and its associated data including tables, views, indexes, clusters, sequences,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc are default schema's. We can create a new Schema/User. But we can't drop default database schema's.

21. When and how oracle database creates a schema?

Oracle Database automatically creates a schema when you create a user.

22. What is a view?

A view is a tailored presentation of the data contained in one or more tables or other views. A view is output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. A view is not assigned any storage space, nor does a view actually contain data.

23. How views are used?

·         It provides security by restricting access to a predetermined set of rows or columns of a table. It hides data complexity. It simplifies statements for the user.

·         An example would be the views, which allow users to select data from multiple tables without actually knowing how to perform a join.

·         It presents the data in a different perspective from that of the base table. It isolate applications from changes in definitions of base tables. It saves complex queries.

24. What are materialized views?

These are schema objects that are used to summarize, compute, replicate, and distribute data. They can be used in various environments for computation such as data warehousing, decision support, and distributed or mobile computing and it also provides local access to data rather than accessing from remote sites. In data warehouses, MVs are used to compute and store aggregated data.

25. What is a dimension?

A dimension is hierarchical relationships between pairs of columns or column sets. Each value at the child level is tied with one value at the parent level. A dimension is a container of logical relationships between columns and it does not contain any data.

26. Explain sequence generator in oracle?

The sequence generator gives a sequential series of numbers.The sequence generator is especially useful for generating unique sequential numbers. Sequence numbers are Oracle integers of up to 38 digits defined in the database. A sequence definition provides information, such as:

·         The sequence name

·         ascending or descending sequence

·         The interval between numbers

·         Whether Oracle should cache sequence numbers in memory

Sequence numbers are generated independent of any tables. The same sequence generator can be used for many tables. Sequence number generation can be used to produce primary keys for your data automatically. Oracle stores the definitions of all sequences for a particular database as rows in data dictionary table in the SYSTEM table- space.

27. What is an index?

Indexes are structures associated with tables and clusters. You can create indexes on one or more columns of a table to enhance the speed of SQL statement execution on that table. Just as the index in Oracle manual helps you to locate information faster than if there were no indexes. An Oracle index provides a faster access path to table data.

28. List out indexing scheme that oracle provides?

Oracle provides several indexing schemes:

·         B-tree indexes

·         B-tree cluster indexes

·         Hash cluster indexes

·         Reverse key indexes

·         Bitmap indexes

·         Bitmap join indexes

29. What is a synonyms?

A synonym is an alias for database objects such as table, view, materialized view, sequence, procedure, function, package. Because a synonym is simply an alias, it does not require storage other than its data dictionary definition.

30. Give an example of synonyms?

CREATE PUBLIC SYNONYM sales FOR jward.sales_data;

After the public synonym is created, you can query the table SALES_DATA with a simple SQL statement:

SELECT * FROM sales;

31. Why synonyms are used?

Synonyms are often used for security and convenience.

32. What are the things synonyms can do?

They can do the following:

·         Mask the name and owner of an object.

·         Provide location transparency for remote objects of a distributed database.

·         Simplify SQL statements for database users.

·         Enable restricted access similar to specialized views when exercising fine-grained access control.

33. What is public synonyms and private synonyms?

·         A public synonym is owned by a user group named PUBLIC and every user in a database can access it.

·         A private synonym is a specific user who has control over its availability to others.

34. Explain briefly oracle instance memory structure?

The oracle database server consists of two things namely oracle database and an oracle instance. When an oracle database is started, a system global area (SGA) is assigned memory and oracle background processes are started. Oracle instance is combination of background processes and memory buffers.

All memory structures exist in the main memory of the computers that constitute the database. Two basic memory structures are there in Oracle database, the system global area (SGA), and the program global area (PGA).

35. What is SGA?

A system global area (SGA) is a set of shared memory structures that contain data and control information for one oracle database instance. When many users are connected to the same instance at the same time, then the data in the instance's System Global Area is shared among the users. SGA is sometimes called as shared global area.

36. What are the data structures does SGA contains?

The SGA contains the following data structures:

·         Database buffer cache

·         Redo log buffer

·         Shared pool

·         Java pool

·         Large pool (optional)

·         Streams pool

·         Data dictionary cache

·         Other miscellaneous information

37. What are Database Buffers?

It is the portion of the System Global Area that holds copies of data blocks read from datafiles. All user processes are connected to the instance concurrently and share access to the database buffer cache.

38. What is Redo Log Buffer?

The Redo Log Buffer is a circular buffer in the System Global Area that holds information about changes made to the database. Redo entries contain the information to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.

39. What is Shared Pool?

The Shared Pool part of the System Global Area contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures.

40. What is PGA?

A PGA i.e Program Global Area is a memory region that contains data and control information for a server process.It is a memory created by Oracle when a server process is started and it is non shared. Access to it is exclusive to that server process and is read and written by Oracle code.

The total PGA memory allocated by each server process associated to an Oracle instance is also known as aggregated PGA memory allocated by the instance.

41. What are the contents of PGA?

·         Content of the Program Global Area : The content of the Program Global Area memory varies, depending on whether the instance is running the shared server option. But in general, the PGA memory can be classified in the following manner.

·         Private SQL Area : A private SQL area contains information such as bind information and runtime memory structures. 

·         Cursors and SQL Areas : Session memory is the memory assigned to hold a session's variables (logon information) and other session information. The session memory is shared and not private for a shared server.

·         SQL Work Areas: For complex queries such as decision-support queries, a big portion of the runtime area is dedicated to work areas allocated by memory- intensive operators.

42. What are dedicated and shared server process?

Server Configurations:

·         Dedicated server process

·         Shared server process

The server process created on behalf of each user process is called a dedicated server process (or shadow process).Shared server architecture removes the need for a dedicated server process for each connection.

A dispatcher routes multiple incoming network session requests to a pool of shared server processes.
An idle shared server process from a pool of shared server processes picks up a request from a common queue, which does mean a small number of shared servers can do the same amount of processing as many dedicated servers.

43. What are server processes in oracle?

Oracle creates server processes to serve user processes requests, connected to the instance. In some situations, when the application and Oracle are on the same system, it is possible to combine the user process and corresponding server process into one process for reducing system overhead. However, when the application and Oracle operate on different computers, a user process always talks to Oracle through a separate server process.

44. What server processes can perform created on behalf of each user’s application?

Server processes can perform one or more of the following:

·         Parses and executes SQL statements fired through the application.

·         Reads data blocks from datafiles on disk into the shared database buffers of the System Global Area, if the blocks are not already present in the System Global Area.

·         Return results in such a way that the application can process the information

45. What are background processes in oracle?

In order to enhance performance and accommodate many users, a multi-process Oracle system uses some additional Oracle processes called background processes.

46. What are the processes does background processes in an oracle includes?

The background processes in an Oracle instance can include the following:

·         Database Writer Process (DBWn)

·         Log Writer Process (LGWR)

·         Checkpoint Process (CKPT)

·         System Monitor Process (SMON)

·         Process Monitor Process (PMON)

·         Recoverer Process (RECO)

·         Job Queue Processes

·         Achiever Processes (ARCn)

·         Queue Monitor Processes (QMNn)

47. What is Database Writer Process?

It writes the contents of buffers to data files.The DBWn processes are responsible for writing modified buffers in the database buffer cache to disk.

However one database writer process (DBW0) is enough for most systems, you can configure other processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if your system manipulates data very much. These additional DBWn processes are not useful on uniprocessor systems.

48. What is Log Writer Process?

It is responsible for writing the redo log buffer to a redo log file on disk. Log Writer writes all redo entries which is copied into the buffer since the last time it wrote.

When a user issues a commit statement, Log Writer issues a commit record in the redo log buffer and writes it to disk immediately, along with the transaction’s redo entries. The corresponding changes to data blocks are delayed until it is more efficient to write them. This is called a fast commit mechanism.

49. What is System Monitor Process?

·         It performs recovery, if necessary, at instance startup.

·         If any terminated transactions were skipped during instance recovery due to file-read or offline errors, System Monitor Process recovers them when the tablespace or file is brought back online.

·         With Application Clusters, the System Monitor process of one instance can perform instance recovery for a failed CPU or instance.

50. What is Process Monitor Process?

It performs process recovery when a user process fails. Process Monitor is responsible for cleaning up the database buffer cache and reclaiming resources that the user process was using. Process Monitor also registers information about the instance and dispatcher processes with the network listener.

51. 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.

52. 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.

53. 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 Archiver 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.

54. Explain briefly shared server architecture?

This architecture removes the need for a dedicated server process for each connection. A dispatcher routes various incoming network session requests to a shared server processes pool. An idle shared server process from a shared pool of server processes chooses a request from a common queue, which means a small number of shared servers can do the same amount of processing as many dedicated servers.

It is because of the amount of memory required for each user is comparatively small, less memory and process management are required, and more users can be supported.

55. What is concurrency in oracle?

The multiuser database management system's concern is how to control concurrency, which is the concurrent access of the same data by multiple users. Without sufficient concurrency controls, data could be updated or changed improperly, trading off with data integrity. To manage data concurrency is to make each user wait for a turn.

The goal of a database management system is to decrease the waiting time so it is either nonexistent or negligible to each user. The data manipulation language statements should proceed with as little intervention as possible, and destructive interactions among concurrent transactions should be prevented.

Destructive interaction is any interaction that updates data or alters underlying data structures incorrectly. Neither performance nor data integrity can be sacrificed. Oracle solves such problems by using various types of locks and a multi-version consistency model.