Latest C++ And Oracle Interview Questions Part – 2

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.

What Is An Extent?

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

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 Is Rollback Segment ?

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

What Are The Different Type Of Segments ?

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

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.

When And How Oracle Database Creates A Schema?

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

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.

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.

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.

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.

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.

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.

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

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.

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;

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.

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.

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.

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

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.

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.

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.

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)