The few Oracle interview questions listed here are ones that we usually overlook, but if you are familiar with them, it will help you stand out from the competition.

Table of Contents

1. What is tablespace in Oracle database?

One or more tablespaces, or logical storage units, make up an Oracle database. The tablespaces of the database house all of the data in the database collectively. Oracle physically stores data in datafiles connected to each tablespace, and it stores data logically in tablespaces.

• The SYSTEM Tablespace
• Users Tablespaces
• Online and Offline Tablespaces
• Read-Only Tablespaces
• Temporary Tablespaces

2. What is SCN in Oracle database?

Oracle Database uses a logical internal time stamp called a system change number (SCN). In order to ensure that a transaction satisfies the ACID characteristics, SCNs arrange events that take place within the database.

SCNs appear in a series that increases monotonically. Because an observed SCN represents a logical moment in time and repeat observations yield values that are equal to or greater than one, Oracle Database can employ a SCN similarly to a clock. An event is considered to have occurred earlier in relation to the database if its SCN is lower than that of another event. It is possible for multiple events to have the same SCN, indicating that they happened simultaneously in the database.

3. What is row chaining and row migration in Oracle database?

Row Chaining

If an insert results in a row that cannot be included entirely in one data block, the row may be chained to another block, which is when a portion of the row is relocated to another data block and a pointer is made to point to that block. Put another way, a row will be chained if its length exceeds the size of an Oracle data block size.

We typically encounter row chaining when a database row’s size exceeds the size of the database block used to store it. In this scenario, the row is split across multiple database blocks, requiring the system to access multiple database blocks when you need to access the row. This leads to additional I/O operations.

Note: Index reads and entire table scans are impacted by chained rows.

• Row chaining is typically caused by insert operations
• Performance is lowered by SQL statements that construct or query chained rows because they require more I/O operations.
• To troubleshoot migrated or chained rows, query the V$SYSSTAT view with the analyze command.
• Use the alter table move command to set a greater PCTFREE value in order to remove linked rows.

Row Migration

The data for the entire row is migrated to a new data block, providing the new block can accommodate the entire row, if an update to a data block results in an increase in the overall row length and the block’s empty space being fully filled. The ROWID of a migrated row remains unchanged; instead, Oracle keeps the original row segment of the migrated row to point to the new block containing the migrated row.

When an update to a row makes it no longer fit on the block (with all of the other data that is currently available), we will migrate that row. A migration entails moving the entire row and leaving behind the forwarding address, so the original block only contains the rowid of the new block and the entire row is moved.

Note: A migrated row is essentially a particular instance of a chained row; a chained row can either be or not be migrated. A migrated row is a chained row.

How to avoid row chaining and migration

• Migrating rows can be prevented by raising PCTFREE. The row has greater room to expand if there is more open space left in the block.
• Additionally, you can construct or rearrange tables and indexes with high deletion rates.
• Using the ALTER TABLE… MOVE statement, you can move data from a non-partitioned table or from a partition of a partitioned database onto a new segment, and optionally onto a different tablespace that you have quota for.
• Rebuild the Table’s Indexes

4. What is PCTFREE/PCTUSED Oracle database?

When a schema object is created or modified, it is possible to specify physical attributes called PCTFREE and PCTUSED. You have the ability to manage using these options how a data block’s available space is utilized. Data rows can be updated and inserted into this available free space.

PCTFREE: – The proportion of a block that is kept free (reserved) in case updates are made to rows that are already included in the blocks

PCTUSED: – Oracle does not consider a data block to be available for the insertion of new rows until the percentage of the block being used drops below the parameter PCTUSED.

5. What is Schema?

A schema is a collection of database objects. A schema bears the same name as the database user who owns it.

Logical structures that make direct references to the data in the database are called schema objects. Schema objects include things like user defined types, tables, views, synonyms, indexes, clusters, materialised views, triggers, procedures, functions, packages, and database connections.

6. What is User? How it is differ from Schema?

Users and schemas are practically the same thing in Oracle. You might think of a schema as the collection of objects (tables, views, etc.) that are associated with a particular user, and a user as the account that you use to login to a database.

Using the create user statement, you create users. Additionally, this “creates” the originally empty schema; a schema cannot be created in and of itself because it is dependent on the user. After a user is formed, an administrator can give it capabilities so that it can do select queries, insert, create tables, and other operations.

7. Can objects belongs to a schema present in multiple tablespaces?

Yes, we can have objects from same schema to be part of different tablespaces within the database.

8. Is it possible for tablespace to hold objects from different schemas?

Yes, a tablespaces can hold objects from different schemas as well as objects from same schema can be part of different tablespaces.

9. What is data blocks in Oracle database?

Oracle uses what are known as data blocks to manage the storage capacity in a database’s datafiles. It is the smallest I/O unit that a database uses. In contrast, all data is kept in bytes at the operating system’s physical level. A block size is a feature shared by all operating systems. Instead of using operating system blocks, Oracle requests data in multiples of Oracle data blocks.

Every Oracle database has a data block size that you specify when the database is created. To prevent needless I/O, this data block size should be a multiple of the operating system’s block size, up to the maximum (unique to the operating system). The smallest storage units that Oracle can use or allocate are called data blocks.

10. What is extents in Oracle database?

An extent is a logical unit used to allocate database storage space that consists of several connected data blocks. One or more extents make up a segment. Oracle assigns a new extent to a segment when all of its current space is utilized.

When you create a table, Oracle assigns an initial length of a certain number of data blocks to the table’s data segment. The Oracle data blocks corresponding to the initial extent are reserved for the rows in that table, even though no rows have been entered yet. When a segment’s original extent of data blocks fills up and additional space is needed to accommodate new data, Oracle automatically allots an incremental extent for that segment. A subsequent extent in that segment that is the same size or larger than the previously allotted extent is known as an incremental extent.

11. What is segments in Oracle database?

Within a tablespace, a segment is a collection of extents that holds all the data for a certain logical storage structure. For instance, Oracle allots one or more extents to form the data segment for each table, and it allots one or more extents to construct the index segment for each index.

The four segment types used by Oracle databases are explained in the following sections:

• Data Segments
• Index Segments
• Temporary Segments
• Rollback Segments

Learn more about Oracle and SQL

• What Are the Most Common SQL Tricks Everyone Should Know?
• Are You Good Enough in PLSQL? Test your knowledge right now
• Learn SQL by Oracle
• SQL Interview Questions for Beginner Level
• Oracle 19C Database
• NoSQL Vs SQL Databases: An Ultimate Guide To Choose