45+ Oracle Performance Interview Question & Answers

Last updated on 04th Jul 2020, Blog, Interview Questions

Oracle performance involves the optimization and efficient management of database operations to ensure fast and reliable access to data. Key aspects include query optimization, which uses the cost-based optimizer to determine the most efficient execution plan for SQL queries, and indexing, which speeds up data retrieval. Proper use of memory structures like the System Global Area (SGA) and Program Global Area (PGA) is crucial for performance, as is efficient disk I/O management.

1. What are database objects in Oracle?

Ans:

Various Oracle database items include:

• Tables – a collection of components organized vertically and horizontally
• Tablespaces – logical storage units.
• View – A virtual table created from one or more tables.
• Indexes are a performance-tweaking approach for record processing.
• Synonyms: names for tables

2. Explain the ANALYZE command in Oracle.

Ans:

The “ANALYZE” command allows the user to conduct a variety of operations on an index, table, or cluster. It helps: To identify migrated and chained rows in the table or cluster. To validate the structure of an object. To collect statistics on objects used by the user, which are then stored in the data dictionary. To remove statistics utilized by an object from the data dictionary.

3. What join kinds are applied to subquestions?

Ans:

A join is  Used to compare and combine specific rows of data from two or more database tables. The numerous joins include:

• Self Join – to connect a table to itself.
• Outer Join – to combine matching and dissimilar data from more than one table.
• Equi-join: a join condition that returns only rows with equivalent values.

4. What is the definition of DML?

Ans:

• We use data manipulation language (DML) to access and manage data in existing objects. DML statements include select, insert, update, and delete.
• RAW datatype in Oracle.
• RAW datatypes store variable-length binary data or byte strings. The maximum size for a row in a table is 32767 bytes.

5. What’s the difference between hot and cold backups?

Ans:

Aspect	Hot Backup	Cold Backup
Operational State	Performed while the database is running and available for use.	Performed when the database is shut down and not available for use.
Availability	Allows continuous access to the database for users and applications during the backup process.	Database is completely inaccessible to users and applications during the backup process.
Consistency	Requires careful management to ensure data consistency, often using features like redo logs and archive logs.	Naturally ensures data consistency since no transactions occur during the backup.
Use Cases	Suitable for high-availability environments where downtime is not acceptable.	Suitable for environments where downtime can be scheduled, such as during maintenance windows.

6. Explain how indexes improve query performance in Oracle.

Ans:

• Indexes act like pre-sorted catalogs for specific columns in a table.
• When a query uses indexed columns, it can locate relevant data rows more efficiently, reducing search time.
• Strategically choose indexed columns based on frequent query patterns for optimal benefit.

7. Describe techniques for optimizing SQL queries in Oracle.

Ans:

Avoid unnecessary use of SELECT *, and specify only the required columns. Use appropriate join types (e.g., INNER JOIN, LEFT JOIN) based on desired results. Employ efficient filtering conditions in the WHERE clause. Consider rewriting complex queries into more straightforward, more optimized forms.

8. How can you optimize table structures for better performance?

Ans:

• Normalise tables to reduce data redundancy and improve data integrity.
• Choose appropriate data types for columns based on the kind of data they store (e.g., NUMBER for numeric values).
• Utilise partitioning for large tables to enable efficient data retrieval based on specific criteria.

9. How do you approach performance tuning for complex applications with high transaction volume?

Ans:

• Identify performance bottlenecks using a combination of tools and techniques mentioned above.
• Focus on optimizing the most critical queries impacting overall application performance.
• Implement changes incrementally and monitor their impact.
• Consider database scaling options like partitioning or replication for high-volume scenarios.

10. What is MERGE in Oracle, and how do I merge two tables?

Ans:

The merge statement enables the subsequent joining of two tables’ worth of data. Based on the query, it selects data from the source table and inserts or updates it in the target table. Conditions. The merge command is helpful for data warehousing applications.

11. What are some factors that can impact Oracle database performance?

Ans:

• Hardware: CPU, memory, disk I/O throughput, storage bottlenecks.
• Software: Inefficient queries, improper indexing, database configuration settings.
• Data Volume and Growth: Large datasets, complex queries, frequent inserts/updates.
• Concurrency: Multiple users accessing data simultaneously.
• Database Design: Poor normalization, redundant data storage.

12. How do you identify performance bottlenecks in an Oracle database?

Ans:

• Analyse execution plans: Identify inefficient queries with high execution times or poor execution plans.
• Monitor database statistics: Track wait events, I/O wait times, and cursor cache hit ratios.
• Use performance monitoring tools: Utilise tools like Oracle Statspack or Automatic Workload Repository (AWR) reports for detailed analysis.

13. Explain the concept of database indexing and its role in performance.

Ans:

• Indexes: Data structures that optimize retrieval of specific data subsets.
• Benefits: Faster data access for queries using indexed columns, especially for WHERE clause conditions.
• Drawbacks: Indexes require additional storage space and maintenance overhead.

14. Differentiate between a full table scan and an index scan in Oracle.

Ans:

• Full Table Scan: Scans all rows in a table, regardless of indexes, which can be slow for large tables.
• Index Scan: Uses the index structure to locate specific rows, resulting in faster performance efficiently.

15. How can you optimize SQL queries for better performance?

Ans:

Identify unnecessary joins and complex logic. Utilize appropriate WHERE clause conditions to leverage indexes. Avoid functions within the WHERE clause (often negates index benefits). Use bind variables for dynamic SQL to improve execution plan reuse.

16. Describe cursor caching and its impact on performance.

Ans:

• Cursor cache: Stores frequently used SQL statements and their execution plans for faster retrieval.
• Benefits: Reduces parsing overhead for repeated queries, improving performance.
• Considerations: Monitor cursor cache hit ratios and implement strategies to increase utilization.

17. What are some best practices for efficient data manipulation (DML) operations (INSERT, UPDATE, DELETE)?

Ans:

• Batch commits Group multiple DML statements into a single commit to minimize redo log overhead.
• Bulk loading techniques: Consider tools like SQL*Loader for efficient data insertion of large datasets.
• Minimise data manipulation within triggers: Complex logic in triggers can impact performance.

18. Explain the role of database statistics in query optimization.

Ans:

• Statistics: Up-to-date information about data distribution and table sizes.
• Importance: The optimizer uses statistics to choose the most efficient execution plan for queries.
• Regularly gather and update statistics: Outdated statistics lead to suboptimal execution plans.

19. What are some strategies for handling database deadlocks?

Ans:

• Prevention: Proper transaction isolation levels and minimizing lock hold times can help prevent deadlocks.
• Deadlock detection and resolution: Monitor for deadlocks and implement automated rollback or timeout mechanisms.

20. What is the NULL value in Oracle?

Ans:

Represents Missing Data: It indicates that a specific value in the column is unknown or unavailable for that record.

Not Equal to Anything: You cannot perform comparison operations like = or <> with NULL directly. It doesn’t equal itself or any other value. Equality with Other NULLs: However, Oracle considers two NULL values to be equal when evaluating conditions within functions like DECODE. Impacts Calculations: Including NULL values in mathematical operations can lead to unexpected results. Functions like SUM or AVG often ignore NULL values by default.

21. When is the SYSTEM tablespace created?

Ans:

It’s the first tablespace established and serves as the foundation for storing crucial database metadata. This includes:

• Data dictionary tables (information about tables, users, etc.)
• Redo logs and undo segments (for data manipulation tracking)
• Temporary segments (used for sorting and other operations)

The SYSTEM tablespace is always online and cannot be dropped or renamed due to its critical role in database function.

22. What does each number indicate in Oracle 9.3.0.5.0?

Ans:

Oracle version numbers signify the Oracle version numbers indicate the following:

• Major database release 
• Database maintenance release  
• Application server release 
• Component-specific release 
• Platform-specific release number.

23. What is a bulk copy in Oracle?

Ans:

Bulk copy, or BCP, is used to import and export data from tables and views. It does not replicate the structure of the same data. It offers a rapid copying process and allows users to back up their data quickly. Direct Path Loading: Bypasses the SQL engine, loading data directly into database blocks.

24. What is the Integrity Constraint?

Ans:

An integrity constraint is a declaration that defines a business rule for a particular table column. It guarantees that data is accurate and consistent.  Common types are:

• Domain Integrity and 
• Referential Integrity

25. What is SQL?

Ans:

SQL (Structured Query Language) is utilized for communication. The server allows data access, manipulation, and control.

• Retrieving & Manipulating Data
• Structured Approach
• Standardized Language

26. What are the various sorts of SQL statements?

Ans:

There are five types of SQL statements:

• Language for Data Definition (DDL): CREATE, DROP, RENAME, ALTER, and TRUNCATE
• Data Manipulation Language (DML): Insert, Delete, Update, and Combination: Data Control Language (DCL): ACKNOWLEDGE, REVOKE
• Data retrieval: SELECT Transaction control statements: COMMIT, SAVEPOINT, and ROLLBACK

27. What is PL/SQL?

Ans:

PL/SQL is an extension of SQL utilized by Oracle. It combines SQL’s data manipulation power with the processing power of a procedural language to produce powerful SQL queries. It instructs the compiler what to accomplish using SQL and how to do it procedurally.

28. What is Literal?

Ans:

A literal is a string composed of a character; select a list that does not include a column name or alias.

• Explicit Value: It’s a specific value you write directly in the code, not a variable referencing another location.
• Various Types: Literals can represent numbers (e.g., 42, 3.14), text strings (enclosed in quotes), Boolean values (TRUE/FALSE), or NULL (representing missing data).
• Easy to Understand: Literals make code more readable because the value is readily apparent.

29. What distinguishes SQL from iSQL*Plus?

Ans:

While iSQL*Plus is an environment, SQL is a language. iSQL*Plus is a command-line program that allows you to type SQL commands and execute them directly against the Oracle database. iSQL*Plus employs commands to format data, whereas SQL uses functions.

30. What are the SQL functions?

Ans:

SQL functions can accept arguments but will always return a specific value. There are two types of SQL functions: Single-row functions include character, number, date, conversion, and general. Multiple-row functions include average, count, sum, max, min, standard deviation, and variance. SQL General functions include NVL, NVL2, NULLIF, COALESCE, and Conditional Expressions.

31. What is a Subquery and What is a VArray ?

Ans:

A subquery is a SELECT statement embedded into a clause of another SELECT statement. It can appear in the WHERE, HAVING, and FROM clauses. VArray (variable-sized array) is an Oracle data type that holds columns with multi-valued properties. It can store a bounded variety of values.

32. What is the quickest way to retrieve data from a table using a query?

Ans:

Using an Index:

• If your query involves filtering data based on a column with an existing, well-maintained index, it can significantly speed up retrieval.
• The index acts like a pre-sorted directory, allowing Oracle to locate relevant rows quickly.

33. What are Oracle’s Aggregate functions?

Ans:

In an aggregate function, numerous rows or Oracle handles data value transformation. On peut utiliser les fonctions d’agrégation MIN, MAX, SUM, AVG, COUNT, VARIANCE et STDDEV en complément de la fonction FIRST ou LAST pour agir sur un ensemble de valeurs provenant d’un ensemble de références qui sont classées FIRST ou LAST selon une spécification de classification spécifique.

34. Define Trigger?

Ans:

• Event-Driven: Executes pre-defined code when a designated event occurs on a table.
• Actions and Logic: Can perform actions like inserting data, sending notifications, or enforcing data integrity rules.
• Flexibility: Offers various trigger types (BEFORE, AFTER, INSTEAD OF) for customization.
• Automation: Automates database tasks and streamlines data management.

35. What’s the distinction between TRANSLATE and REPLACE?

Ans:

TRANSLATE substitutes a character with another character, whereas REPLACE substitutes a character with a word.

• TRANSLATE: Replaces characters one by one within a string.
• It finds all occurrences of a specified character and replaces them with another character based on your defined mapping.
• REPLACE: Replaces an entire substring with another string.
• It locates a specific sequence of characters (the old substring) and entirely substitutes it with a new string.

36. What is cross-join?

Ans:

Cross-join is the Cartesian product of records from the tables involved in the Join.

• Cartesian Product: Each row from Table A is paired with every row from Table B.
• New Rows Generated: The resulting table contains the product of the number of rows in each joined table.
• May Lead to Large Datasets: Be cautious, as cross joins can produce a massive dataset, impacting performance and potentially containing irrelevant information.

37. How do you identify performance bottlenecks in an Oracle database?

Ans:

• Utilise tools like EXPLAIN PLAN to analyze query execution plans and identify inefficient steps.
• Monitor database wait events to pinpoint resource contention issues (e.g., CPU, I/O).
• Analyse database statistics to assess table and index usage for optimization opportunities.

38) What is a BCP, which stands for bulk copy in Oracle?

Ans:

Bulk copy, or BCP in Oracle, is used to import or export data from tables and views, but it does not duplicate the data’s structure. The critical advantage of BCP is its rapid data handling process and its ability to back up data quickly.

39) What is the link between the database, the tablespace, and the data file?

Ans:

Oracle databases are made up of one or more logical storage units known as tablespaces. These tablespaces collectively store all of the data in the database, and each tablespace in an Oracle database is made up of one or more files known as data files. These data files are physical structures that confirm with the operating system.

40. What is the distinction between hot and cold backups in Oracle? Also, discuss their merits.

Ans:

BCP stands for bulk copy in Oracle. They are sometimes known as online backups because they occur while the database is live. Some websites are unable to shut down their databases while producing a backup copy because they are in use 24 hours a day, seven days a week. Cold backup (offline backup) – A cold backup, also known as an offline backup, is performed after the database has been shut down with the SHUTDOWN standard command. 

41) How many memory tiers exist in the Oracle shared pool?

Ans:

Oracle shared pools have two tiers.

• Library cache.
• Data dictionary cache.

Save points are used to break down a transaction into smaller portions and enable its rollback. A maximum of five save points is permitted.

42. Explain how materialized views can improve query performance.

Ans:

• Materialized views are pre-computed copies of frequently used queries stored as database objects.
• Subsequent queries referencing the materialized view can retrieve data faster than re-executing the original complex query.
• Effective for read-heavy workloads where the same data is accessed repeatedly.

43. What are the different Oracle database objects?

Ans:

Oracle database objects are the structures and components within an Oracle database that store, organize, and manage data. These objects include tables, which hold raw data in rows and columns; indexes, which improve data retrieval performance; and views, which are virtual tables created from queries to present data in a specific format. Sequences generate unique numeric values, often used for primary keys. Synonyms provide alternative names for other objects, simplifying access. 

44) What’s the distinction between pre-select and pre-query?

Ans:

A pre-query trigger fires before the query executes and once when you attempt to query. This Trigger allows you to adjust the where clause component dynamically. Pre-select queries are executed during the execute query and count query processes after Oracle forms create the chosen statement to be issued but before the statement is actually issued. The pre-query trigger fires before the pre-select Trigger.

45) What are the different module kinds in Oracle forms?

Ans:

The following are the various modules in Oracle. Forms include: 

• Form module – Menu module
• PL/SQL Library module.
• Object Library module.

46) In what situation is the ANALYZE command used? Oracle?

Ans:

The ANALYZE command is used to conduct a variety of operations on an index, table, or cluster. The following list specifies the use of the ANALYZE command in Oracle:

• It identifies migrating and chained rows within a table or cluster.
• It validates the object’s structural integrity.
• It aids in gathering statistics about the item used by the optimizer. They are then included in the data dictionary.
• It removes object-specific statistics from the data dictionary.

47) Is it possible to build synonyms without a table? What is the use of synonyms?

Ans:

• Synonyms can conceal the proper name and ownership of a thing.
• It allows public access to an object.
• Additionally, it offers location transparency for distant database tables, views, and program units.
• This simplifies SQL statements for database users.

48) What are the various types of database objects?

Ans:

Here is a list of the various types of database objects: Tables organize elements vertically and horizontally. Tablespaces are logical storage units in Oracle. Views are virtual tables that combine data from multiple tables. Indexes are used to optimize record processing performance. Synonyms: This is a word for tables.

49) How are Save Points used in Oracle databases?

Ans:

• Using Save Points, a transaction can be divided into smaller halves, and you can roll back part of it. 
• Oracle Database supports a maximum of five save points. 
• When an error occurs, it is possible to roll back to the point where the SAVEPOINT was saved.

50) What’s the difference between post-database and post-form commits?

Ans:

The post-database commit trigger is run after Oracle forms issue the transaction commit, whereas the post-form commit is called during the post-and-commit transaction process following the database commit.

51) What is a logical backup in Oracle?

Ans:

• Schema-Level: Backs up tables, views, procedures, and other schema elements.
• Portable: This can be restored on different Oracle environments, unlike physical backups.
• Export Tools: Uses tools like EXP or Data Pump (expdp) to export data in a compressed format.
• Selective Backup: Allows backing up specific objects or subsets of data within a schema.

52) What do you mean by “Redo Log file mirroring”?

Ans:

Mirroring is the process of keeping a copy of the Redo log files. This is accomplished by grouping log files. This guarantees that LGWR immediately distributes them to every person in the online redo log group as of right now. If the group fails, the database will automatically transition to the next group. It reduces the performance.

53) What is the definition of recursive hints in Oracle?

Ans:

A recursive hint occurs when a dictionary table is called repeatedly by different processes. This happens due to the tiny capacity of the data dictionary cache.

• The goal is to apply optimizer hints to tables referenced within a recursive query or procedure, impacting the entire execution path.
• Standard Limitations: Regular optimizer hints typically only affect the table they’re specified on, not nested references.
• Workaround: The concept of recursive hints involves strategically placing hints on higher-level tables to influence the optimization of nested references indirectly.

54. What are the components of the logical database structure in an Oracle database?

Ans:

Oracle’s logical database structure consists of the following components:•: A database primarily consists of Logical Storage Units or tablespaces. This tablespace contains a collection of similar logical structures. To be more specific, tablespace groups are linked to logical structures. Items of database schemas: A schema is an assembly of database items that belong to a particular user. These objects include tables, indexes, views, stored procedures, and other components. 

55. Describe an Oracle table.

Ans:

A table is the fundamental unit of data storage in the Oracle database. A table stores all of a user’s available information in rows and columns. To build a new table, enter the “CREATE TABLE” statement in the database. First, you must name the table, describe its columns, and provide the datatype for every column.

56. Explain the relationship between the database, tablespace, and data file.

Ans:

Oracle databases have one or more logical storage units named tablespaces. Each tablespace is made up of one or more files, known as data files. These tablespaces store all of the data in databases. Datafiles are the physical structure that indicates the operating system in which the Oracle program is running.

57. What are the different Oracle database objects?

Ans:

Here are the Oracle Database Objects:

• Tables: A collection of components grouped vertically and horizontally.
• Tablespaces: Oracle logical storage unit.
• Views are virtual tables created from one or more tables.
• Indexes: This is a performance-tuning strategy for processing records.
• Synonyms: It refers to tables.

58. Can you explain the ANALYZE command in Oracle?

Ans:

The “Analyze” command is Used to perform various tasks on an index, table, or cluster. The following list specifies the use of the ANALYZE command in Oracle:

• The Analyze command identifies migrated and chained rows within a table or cluster.
• It validates an object’s structure.
• This collects and stores statistics about the object used by the user in the data dictionary.
• It also allows for the deletion of statistics from the data dictionary.

59. What types of joins are used while composing follow-up questions?

Ans:

A join is used to compare and combine data, which means it joins and returns specified rows from two or more tables in a database. There are three types of SQL joins that are used to create subqueries. Self Join: When a table’s foreign Key refers to its primary Key, it gets connected with itself. An equijoin is a join condition that includes an equality operator. An equation returns only rows with comparable values in the specified columns.

60. Define the RAW data type in Oracle

Ans:

• Binary Data Storage: Ideal for raw bytes representing images, audio files, compressed data, etc.
• No Interpretation: Oracle doesn’t attempt to understand the data’s meaning, treating it as a sequence of bytes.
• Variable Length: RAW columns can store a variable amount of data, with a maximum size of 2000 bytes per column in the database table.

61. Describe the Indexing Strategies for Performance in Indexing.

Ans:

• Targeted Indexing: Create indexes on frequently used columns in WHERE clauses for faster data retrieval.
• Uniqueness & Cardinality: Consider unique or primary critical indexes for efficient lookups and avoid redundant indexes on low-cardinality columns.

62. Explain the Temporal data types in Oracle.

Ans:

Oracle generally provides the following temporal data types:

• Date Data Types: Various date formats.
• TimeStamp Data Type: Available in multiple formats.
• Data Type: Interval of dates and times.

63. What is a view?

Ans:

A view is a logical table created from one or more tables or views. A View is also known as a user-defined database object that stores the results of an SQL query and can be accessed later. Views hold data as a virtual table rather than physically; hence, they are sometimes known as logical tables. The associated tables on which the views are represented are called Base Tables, and this does not include any data.

64. How do I store pictures in the database?

Ans:

BLOB (Binary Large Object):

• It is ideal for storing pictures, audio, or any binary data.
• SQL commands like INSERT with the BLOB datatype are used to store the image data.

External File Reference:

• Store the image as a separate file on the database server’s file system.
• Create a table with a reference (e.g., filename) to the image location.

65. Where do you apply DECODE and CASE statements?

Ans:

Both Decode and Case will function similarly to the if-then-else statement, and they serve as alternatives for each other. Oracle uses these functions to alter data values. For instance, the decode function Choose OrderNum. Orders; DECODE (Status,’ O,’ ‘Ordered,’ ‘P,’ ‘Packed,’ S,” Shipped,’ ‘A,” Arrived’) Function of the case

66. What do you mean by Merge in Oracle, and how can you combine two tables?

Ans:

Merge statements combine data from two tables. They choose data from the source table and insert/update it in the other table based on the condition specified in the query. This is also handy for data warehousing purposes.

67. Can you explain the integrity constraint?

Ans:

An integrity constraint is a declaration that specifies a business rule for a table column. It is used to ensure that the data in the database is accurate and consistent. It is also known as a declarative approach to defining a business rule for a table column. There are three types: Domain Integrity, Referential Integrity, and Domain Integrity.

68. What is SQL, and can you describe the many types of SQL statements?

Ans:

SQL stands for Structured Query Language. SQL allows you to interface with the server in order to access, manipulate, and control data. There are five sorts of SQL statements accessible. They are:

• Select Data Retrieval.
• DML (Insert, Update, Delete, and Merge)
• Use DDL to create, alter, drop, rename, and truncate data.
• Transaction Control Statements (Commit, Rollback, and Savepoint)
• Grant and revoke Data Control Language (DCL).

69. Can you briefly describe what Literal is? Give an example of how it could be put to use.

Ans:

A string including a character, integer, or date is called a literal. From the Select list, it is not a column name or alias. Also, the date and character literally must be wrapped together in a single. In contrast, numeric literals do not require quotes (‘ ‘). For example: Choose last_name||’is a’||job_id. In the example of “emp details” from an employee, the word “is a” is used literally.

70. How do I display row numbers beside the records?

Ans:

To display row numbers alongside record numbers, you can perform the following: 1 Select row num from the table; This query displays row numbers and field values from the provided table. This query will display the row numbers and field values from the specified table. 

71. Describe strategies for database caching in Oracle.

Ans:

• Utilize the database buffer cache to store frequently accessed data blocks for faster retrieval.
• Leverage the library cache to store parsed SQL statements, reducing parsing overhead for subsequent executions.
• Optimise cache sizes and configuration based on workload patterns for optimal performance.

72. What are the SQL functions? Describe in brief the various types of SQL functions.

Ans:

Single-row functions return one result per row. Examples of single-row functions include:

• Character 
• Number 
• Date.
• Conversion.
• General

Multiple-row methods return one result per group of rows. Types of Multiple-Row Functions: 

• Average
• Count.
• Maximum. 
• Minimum. 
• Sum.
• Standard Deviation
• Variance.

73. Describe the various sorts of General Functions used in SQL.

Ans:

General functions are classified into the following types:

• NVL: Converts a null value to an actual value. NVL (exp1, exp2)
• NVL2: Returned exp2 if exp1 is not null, and exp3 if exp1 is null.

The contention exp1 is capable of being any data type. NVL2 (exp1, exp2, exp3) equal or the first expression if not equal. NULLIF (exp1, exp2) 4. COALES CASE Expression and DECODE Function.

74. What is a Subquery? Can you describe the different kinds?

Ans:

A subquery is an expression in SELECT that is contained within a clause of another SELECT statement. A subquery can be placed in the where and from clauses. Guidelines for Using Subqueries:

• Enclose the sub-queries in parenthesis.
• Add these subqueries to the right side of the comparison condition.
• Use single-row operators for subqueries.
• Use multiple-row operators for subqueries.

Types of Subqueries:

• Single-Row Subquery: Returns only one row from an inner select statement. Single-row comparison operators include =, >, >=, <, <=, and <>.
• Multiple-Row Subquery: These queries return multiple rows from an inner Select expression, and so will you. Locate subqueries with multiple columns that yield more than one column using the inner select statement. Operators include IN, ANY, and ALL.

75. What is the significance of the double ampersand (&&) in SQL queries? Give an example.

Ans:

This query selects all customers from the “customers” table who are:

• Located in the city ‘New York’ (checked by the first condition)
• AND also older than 25 years old (checked by the second condition)
• The double ampersand ensures that only rows meeting both criteria are returned.

76. Describe VArray.

Ans:

VArray is an Oracle data type that has columns with multivalued characteristics and can carry a bounded array of values. All Varrays are made up of contiguous memory regions. The first element refers to the lowest address. At the same time, the highest address corresponds to the last. A Varray has an index for each of its elements. It has a maximum. The size (max_size) can be modified dynamically.

77. What are the characteristics of the Cursor?

Ans:

Each Cursor in Oracle contains a collection of characteristics that enable a testing application to its condition. You may utilize the attributes to determine whether the Cursor is open or closed, whether it was found or not found, and the number of rows.

78. Name the different limitations utilized in Oracle.

Ans:

The following limitations were used:

• The NULL symbol indicates that a column may contain NULL values.
• NOT NULL: Indicates that a column cannot have NULL values.
• Validate column values to ensure they satisfy particular criteria.
• DEFAULT: Indicates a default value.

79. What is the fastest query method for retrieving data from the table?

Ans:

The Row ID is the fastest query method for retrieving data from a table. A row can be retrieved from a table using RowID. There are no differences between these Joins. Cartesian and cross joins are the same. Cross-connect produces a cartesian product of two tables, which means that the rows from the first table are multiplied by the rows from another table, which is known as the cartesian product.

80. How does the ON-DELETE-CASCADE statement function?

Ans:

When a record is deleted from the parent table, this On Delete Cascade immediately deletes the record in the child table as well. This statement. It can also be utilized with foreign keys. You can add the On Delete Cascade option to a table that already exists. Syntax: Alter Table: Child_T1 ADD Constraint Child_Parent_FK References Parent_T1(Column1) ON DELETE CASCADE.

81. What is PL SQL?

Ans:

An expansion of Structured Query Language (SQLis written in PL/SQL, which Oracle uses. It combines SQL’s data manipulation power with the processing power of procedural languages to generate powerful SQL queries. PL SQL refers to instructing the compiler on what to do with SQL and how to accomplish it procedurally.

82. Describe the properties of PL/SQL.

Ans:

There are numerous properties of PL/SQL. Notable examples include:

• PL/SQL is a block-structured language. 
• It is portable across all settings.
• Support Oracle.
• PL/SQL is connected with Oracle’s data dictionary.
• Stored procedures improve application sharing.

83. What data types are accessible in PL/SQL?

Ans:

PL/SQL supports two data types. Specifically, they are:

• Scalar data types.
• Examples include Char, Varchar, Boolean, and so forth.
• Composite data types.
• Example: record, table, etc.

84. What is the utility of a database trigger

Ans:

Triggers are programs that are automatically executed when certain events occur.

• Provide sophisticated security authorizations.
• Determine column values.
• Keep duplicate tables.
• Implement sophisticated business rules.
• Increase transparency in log events.

85. Demonstrate how functions and procedures are called within a PL SQL block

Ans:

A procedure can include a return statement to return control to the caller block, but the return statement is unable to yield any values. Though procedures can be called from another block or select statements, they cannot be called straight from a few chosen statements using the EXEC keyword. The procedure might be referred to in the following ways:

86) What two virtual tables are available during database trigger execution?

Ans:

The columns are referred to as Then.column_name and Now.column_name.

• For INSERT triggers, only Now.column_name values are available.
• For DELETE triggers, only Then.column_name values are available.
• Update. Related triggers are provided for both Table columns.

87. What is the difference between a primary key and a unique key?

Ans:

Unique key primary Key

A table can have several Unique Keys. A table can only have one primary Key. A unique key column can store null values. A primary key column cannot include NULL values. Individually identify each value in a column. Uniquely identify each row in a table.

88. Explain the function of the%TYPE and%ROWTYPE data types using an example.

Ans:

%ROWTYPE and % TYPE are PL/SQL attributes that can inherit a table’s data types as defined in a database. The primary objective of using these properties in Oracle is to ensure data independence and integrity. Note that if any of the database’s datatypes change, the PL/SQL code automatically updates to reflect the changes.

89. What is the difference between triggers and constraints?

Ans:

Triggers and Constraints differ in the following ways:

TriggersConstraints only apply to rows created after the Trigger is enabled. When the constraint is enabled, it affects all rows in the table, including existing ones. Triggers are used to implement sophisticated business rules that cannot be enforced through integrity constraints. Constraints ensure integrity.

90. Describe the Monitoring and Tuning for Efficiency:

Ans:

• Analyze Execution Plans: Identify inefficient queries with high cost or full table scans using EXPLAIN PLAN.
• Monitor Wait Events: Utilize tools like V$SESSION_WAIT to pinpoint bottlenecks like CPU waits or I/O delays

91. Explain PL/SQL?

Ans:

When an error occurs in PL/SQL, the appropriate exception is raised. This also implies that error-handling code is included in the application to manage undesirable circumstances in which PL/SQL scripts stop unexpectedly. In PL/SQL, all exception handling codes are located in the Exception section.

92. What is the difference between the “VERIFY” and “FEEDBACK” commands?

Ans:

• The main distinctions between the instructions for verification and feedback are:
• Verify Command: You can use this command to confirm changes in the SQL statement, which may contain both old and new values as defined by Set Verify On/OFF.
• Feedback Command: It shows the amount of records returned by a query.

93. What is the difference between commit, rollback, and savepoint?

Ans:

The main distinctions between these are noted below.

• Commit: Completes the transaction by making all pending data modifications permanent.
• Rollback: Ends the current transaction by discarding or removing any outstanding data modifications.
• Savepoint: Splits a transaction into smaller sections. You can roll back the transaction until you find a specific savepoint.

94. What is the distinction between SUBSTR and INSTR?

Ans:

SUBSTR returns a specified section of a string, whereas INSTR returns the character position in which a pattern appears in a string. SUBSTR returns a string, while INSTR returns numerical values.

95. Explain the distinction between the data dictionary and the user tables.

Ans:

• User tables: This is a collection! of tables created and managed by the user. It also includes user information.
• Data Dictionary: This is a set of tables created and maintained by the Oracle Server. It includes database information. The SYS user controls all data dictionary tables.