Top 50 Oracle Interview Questions For [95% SUCCESS]

Last updated on 15th Jun 2020, Blog, Interview Questions

Oracle PL SQL holds a 2.2% market share, according to research. Therefore, there is still room for you to advance in the Oracle PL SQL Development field. A collection of 100 Oracle interview questions and their responses is provided below. For beginners, new hires, and seasoned Oracle workers alike, the list includes questions that are helpful.

1. What is Oracle?

Ans: 

  Oracle’s database management systems (DBMS), which businesses employ to store, manage, and retrieve enormous volumes of organized and unstructured data, are among its most well-known products. The performance, scalability, security, and dependability of Oracle’s database technology are highly recognized.

2. Explain about Oracle database

Ans: 

A relational database management system (RDBMS) called an Oracle database is a highly developed and popular RDBMS created by Oracle Corporation. It acts as a complete and potent solution for storing, organizing, and retrieving organized and unstructured data. Because of its scalability, dependability, security, and powerful features, Oracle databases are a popular choice for businesses of all sizes and in all sectors.

3. Describe the Oracle instances.

Ans: 

An Oracle instance refers to the operational environment in which a single Oracle database runs. It consists of memory structures and background processes that work together to manage and serve the data stored in the database. Each Oracle instance corresponds to a specific database and runs on a single server or host machine.

4. Explain Oracle’s parameter file.

Ans: 

Oracle’s parameter file, often referred to as the initialization parameter file or simply “init.ora” (or “spfile.ora” for the server parameter file), is a configuration file that contains a set of initialization parameters used to configure various aspects of an Oracle database instance. 

5. What is the Oracle Server parameter file?

Ans: 

The Oracle Server Parameter File (SPFILE) is a binary version of the initialization parameter file (init.ora) used to configure various aspects of an Oracle database instance. 

6. Describe the System Global Area (SGA) of Oracle.

Ans: 

The System Global Area (SGA) is a crucial part of the memory architecture of an Oracle database instance. A variety of data and control information necessary for the database’s operation are stored in this shared memory area. 

7. Describe a user account using Oracle.

Ans: 

A user account in Oracle represents a specific user or application that communicates with the database. The privileges, roles, and access permissions associated with each user account dictate the actions the person is permitted to take within the database.

8. What are the five query types that Oracle supports?

Ans: 

SELECT Queries

UPDATE Queries

INSERT Queries

DELETE Queries

MERGE Queries

9. What exactly do you mean by a transaction?

Ans: 

 A transaction, in the context of databases and computer science, refers to a sequence of one or more operations performed on a database that are treated as a single unit of work. 

10. Differentiate between function and procedure in Oracle.

Ans: 

Function: A function in Oracle is primarily used to compute and return a single value. It’s often used in expressions or SELECT statements to calculate values based on input parameters and return the result.

Procedure: A procedure is a collection of SQL and PL/SQL statements that carry out a single action or group of actions.

11. Can a PL/SQL block have more than one function with the same name?

Ans: 

No, a PL/SQL block cannot have more than one function with the same name. Each function in PL/SQL must have a unique name within its scope. This is a fundamental rule in programming to avoid ambiguity and ensure that each function can be uniquely identified when called or referenced.

12. Can functions be overloaded? Describe overloading.

Ans: 

  Yes, functions in Oracle PL/SQL can be overloaded. The feature of function overloading allows you to design numerous functions with the same name but distinct argument lists. Each function may have its own set of parameters, which may differ in terms of data type, number of parameters, or parameter order.

13. Give the structures of a package, function, or method.

Ans: 

Package:

A package is a technique of grouping together similar procedures, functions, variables, and other PL/SQL structures. It offers a modular and ordered approach to code structure.

Function:

A named PL/SQL block that returns a single value is referred to as a function. It can be given parameters, conduct computations, and then return a result.

Method:

Methods inside a type or object type can be defined in the context of object-oriented programming in PL/SQL. Methods are processes or functions that are linked to objects.

14. Why do not you drop and rebuild procedures instead of creating new ones?

Ans: 

 Dropping and rebuilding procedures, rather than creating new ones, is often favored in database development for several compelling reasons. This practice ensures efficient code management by allowing developers to modify existing procedures while retaining their original structure.

15. Explain implicit cursor.

Ans: 

  An implicit cursor is a concept in Oracle PL/SQL that allows you to perform database operations without explicitly declaring and managing a cursor. It simplifies the process of fetching data from a SELECT statement or managing the impact of data modification statements (INSERT, UPDATE, DELETE) within your PL/SQL code.

16. Describe the purpose of an Oracle index and explain when you would use one.

Ans: 

 An Oracle index is a database object that enhances the efficiency of data retrieval by providing a way to quickly locate rows within a table based on the values in one or more columns. 

17. What are the ACID characteristics, and why do database transactions need to consider them?

Ans: 

The ACID characteristics are a set of properties that define the behavior and guarantees of a database transaction.

Atomicity: A transaction is viewed as a single, indivisible unit of work thanks to atomicity.

Consistency: A transaction must move the database from one consistent state to another in order to be consistent.

Isolation: The use of isolation makes sure that one transaction’s operations are kept separate from those of other concurrent transactions.

Durability: Durability ensures that changes made by a transaction once it has successfully finished will remain in the database even in the event of a system failure, crash, or power loss.

18. Does SQL*Plus have a PL/SQL Engine?

Ans: 

SQLPlus is a command-line interface and interactive tool supplied by Oracle for dealing with Oracle databases through SQL and PL/SQL. However, SQLPlus does not have a PL/SQL engine.

19. What is the limitation on the block size of PL/SQL?

Ans: 

A block is a fundamental building block of code in Oracle PL/SQL that can have numerous statements inside of it. There is a maximum number of statements that can be contained in each PL/SQL block. The maximum PL/SQL block size, which is expressed in terms of characters, establishes this restriction.

20. How does PL/SQL read/write files?

Ans: 

The UTL_FILE package in Oracle PL/SQL allows for reading and writing files. Interacting with files on the server’s file system is made possible via the UTL_FILE package’s procedures and functions.

21. Describe the procedures used to protect the PL/SQL source code.

Ans: 

Protecting the PL/SQL source code is important to safeguard your intellectual property and ensure the security of your application’s logic. 

22. Give the various exception types.

Ans: 

Parts of a database trigger are:

• ArithmeticException
• NullPointerException
• ArrayIndexOutOfBoundsException
• FileNotFoundException
• IOException

23. Describe the components of a database trigger.

Ans: 

• Trigger Name
• Trigger Event
• Trigger Timing
• Trigger Body/Logic
• Trigger Condition (Optional)
• Trigger Scope/Table

24. How many different kinds of database triggers are there?

Ans: 

Database triggers can generally be categorized into two main types based on when they are executed in relation to the triggering event: “Before Triggers” and “After Triggers.” Each of these types serves a different purpose and is used to achieve specific goals in database management.

25. How would you modify the triggers for inserting, deleting, and updating existing and new values?

Ans: 

When modifying triggers for inserting new values, you can implement logic to validate data or generate default values before the actual insertion takes place. For updating existing values, triggers can be adjusted to enforce business rules or log changes, utilizing the original and updated values for comparison. Deleting triggers can be tailored to perform tasks such as logging deletions or ensuring data integrity through referential checks. 

26. What are cascading triggers?

Ans: 

Cascading triggers refer to a situation where the firing of one trigger leads to the automatic firing of another trigger. This can create a cascade effect where multiple triggers are executed sequentially in response to a single event in a database.

27. What are Mutating triggers?

Ans: 

  A mutating trigger, also known as a “mutating table trigger,” is a term used in the context of database triggers to describe a situation where a trigger attempts to modify the same table from which the trigger is fired.

28. Define constraining triggers.

Ans: 

Triggers that are specifically designed to impose restrictions on data alterations are known as constraint triggers. These restrictions may include different business rules, validation tests, and requirements that must be met before data changes are permitted in the database.

29. List the benefits and drawbacks of clusters.

Ans: 

Benefits of Clusters

• Improved Data Retrieval Performance
• Reduced Disk I/O
• Optimized Range Queries
• Data Locality
• Minimized Disk Fragmentation

Drawbacks of Clusters:

• Insert and Update Performance
• Data Skew
• Maintenance Overhead
• Increased Locking and Blocking

30. What is self-referential integrity and how may it be used?

Ans: 

   Self-referential integrity, also known as self-referential constraint or self-referential relationship, refers to a specific type of relationship in a database where a table’s column references another column within the same table. This kind of relationship is often used to establish connections or hierarchies within the same entity.

31. Give the different rollback segment states.

Ans: 

• Active
• Inactive
• Extent
• Full
• Offline
• Shrinking

32. Is it possible to return to any savepoint?

Ans: 

Yes, it is typically possible to return to any savepoint within a transaction in most relational database management systems (RDBMS). Savepoints allow you to create named markers within a transaction so that you can later roll back to a specific point without affecting the entire transaction.

33. What is the maximum number of columns that a table can have?

Ans: 

 The maximum number of columns that a table can have is determined by the database management system (DBMS) you are using. Different DBMSs have different limits based on their architecture, storage mechanisms, and design considerations. 

34. Explain the importance of the PL SQL & and && operators.

Ans: 

 The PL/SQL & and && operators hold significant importance in enhancing the functionality and usability of PL/SQL code within the context of Oracle Database. These operators facilitate parameterization, interactivity, and adaptability of code.

35. Can a cursor be given a parameter?

Ans: 

In standard SQL, a cursor cannot be directly given a parameter like a function or a stored procedure. Cursors are typically declared and defined to retrieve data from a specific query or a predefined result set without the need for parameters.

36. Describe the various kinds of rollback segments.

Ans: 

Public Rollback Segments: When a database is first built, public rollback parts are often generated.

Private Rollback Segments: Private rollback portions are sometimes employed when public segments get too busy or congested.

Optimal Rollback Segments: Optimal Rollback Segments (ORS) are a sort of public rollback segment that is controlled automatically by the system.

37. What does Oracle’s ceiling and floor mean?

Ans: 

 In Oracle SQL, the functions CEIL and FLOOR are used to perform mathematical rounding operations on numeric values. These functions help you round numbers up or down to the nearest integer or a specified number of decimal places.

38. What is RAW datatype?

Ans: 

The RAW datatype is used in Oracle Database to hold binary data or byte strings. It’s commonly used to store data like photos, music files, and other binary data that doesn’t require character set conversion. 

39. What does BLOB datatype mean?

Ans: 

A BLOB datatype, or “Binary Large Object,” is used in database administration to hold significant volumes of binary data. Images, audio files, video files, documents, and other sorts of binary data that are normally too vast to be saved as standard character data can all be handled by this datatype.

40. What does Oracle’s NULL value mean?

Ans: 

In Oracle Database, a NULL value represents the absence of a value or an unknown value in a database column. It’s a way to indicate that a particular data point is missing, undefined, or not applicable. NULL is not the same as an empty string or zero; it’s a distinct marker for missing or unknown data.

41. Describe the WITH CHECK OPTION.

Ans: 

The WITH CHECK OPTION is a clause used in SQL statements to enforce constraints on data modifications made through a view. It’s used to ensure that any data changes made using the view adhere to the conditions specified in the view’s defining query.

42. What differentiates the Varchar and Varchar2 data types from one another?

Ans: 

 Both are used to store variable-length character strings, but VARCHAR2 is the preferred choice due to its improved characteristics. In earlier versions, VARCHAR stored trailing spaces as non-null characters, leading to storage inefficiencies. 

43. What function does NVL have?

Ans: 

The NVL function in Oracle Database may be used to replace a NULL value with a predefined default value. It’s especially beneficial when you wish to manage NULL values in expressions or computations by replacing them with a relevant alternative value.

44. How can we get a table’s field details?

Ans: 

To access a table’s field details, you typically use SQL queries to retrieve information from the data dictionary or system catalog views provided by the database management system. These views store metadata about database objects, including tables and their columns.

45. Describe an ALERT.

Ans: 

An alert, in the context of information technology and system monitoring, refers to a notification or message generated by a monitoring system to inform administrators or users about specific events, conditions, or issues that require attention.

46. What is the quickest way to query a table to retrieve data?

Ans: 

The quickest approach to query a table and obtain data is to use efficient indexing and well-structured queries. Indexes are data structures that improve the performance of data retrieval operations by helping the database system to easily identify certain rows inside a table.

47. What is the parameter mode that a process can accept?

Ans: 

These are the typical parameter modes:

In: Data supplied to a function is passed in this mode.

Out: Data from a function’s output is passed using this mode.

InOut (also known as In/Out): This mode combines the In and Out modes.

By Value: In this method, the function is given the parameter’s actual value.

48. What exactly is a hash cluster?

Ans: 

A hash cluster is a database storage strategy used in some relational database management systems (RDBMS) to enhance data storage and retrieval using a hash function. It is intended to increase the efficiency of data retrieval activities by lowering disk I/O and eliminating data fragmentation.

49. How do SET operators work?

Ans: 

In SQL (Structured Query Language), SET operators are used to combine the result sets of two or more SELECT queries. These operators let you conduct set-related actions on data collected from many tables or queries.

50. What exactly is a view?

Ans: 

Views are constructed using SQL queries and may be thought of as stored, pre-defined queries that allow you to get and alter data in a more convenient or understandable manner.

51. What is Oracle, and what are the many editions of it?

Ans: 

The Oracle Database is built to securely store, handle, and retrieve enormous volumes of data. Organizations of all sizes utilize it for a range of activities, including as data warehousing, analytics, and managing business-critical applications.

• Oracle Database Standard Edition
• Oracle Database Enterprise Edition
• Oracle Database Express Edition (XE)
• Oracle Database Cloud Service
• Oracle Database Personal Edition

52. Why are Oracle’s aggregate functions used?

Ans: 

 Oracle’s aggregate functions are used to perform calculations on a set of values and return a single value that summarizes the data in some way. These functions are particularly useful when working with large datasets in a database, as they allow you to retrieve summarized information without having to process the data manually in your application code.

53. What are the set operators meant to do?

Ans: 

In SQL, set operators are used to join the results of two or more SELECT queries into a single result set. You can use these operators to execute set-based operations on data from one or more tables. SQL has three main set operators: UNION, INTERSECT, and EXCEPT (or MINUS in some systems such as Oracle).

54. Can we convert a date to a character in Oracle, and if yes, what is the syntax?

Ans: 

Yes, you can convert a date to a character (string) representation in Oracle using the TO_CHAR function. The TO_CHAR function allows you to format a date value as a string according to a specified format model.

Syntax: TO_CHAR(date_value, format_model)

55. What exactly is a database transaction, and what TCL commands are accessible in Oracle?

Ans: 

 

A database transaction is a logical unit of work made up of one or more SQL statements. These statements are run as a single, indivisible entity. 

COMMIT: The COMMIT command is used to make all the changes made within the current transaction permanent. 

ROLLBACK: The ROLLBACK command is used to undo all the changes made within the current transaction. 

SAVEPOINT: The SAVEPOINT command creates a point within the current transaction to which you can later roll back.

ROLLBACK TO SAVEPOINT: The ROLLBACK TO SAVEPOINT command is used to undo changes made since a specific savepoint was established, effectively rolling back the transaction to that point.

56. What do you understand by a database object? Can you list a few of them?

Ans: 

A database object, as used in the context of databases, is a structured item found within a database schema and used to store, arrange, and edit data or information. Database objects are essential parts that allow for data storage and retrieval and provide different types of information a structure. 

The datatypes are :

• Tables
• Indexes
• Views
• Sequences
• Synonyms

57. What is a nested table and how is it different from a normal table?

Ans: 

In the context of databases, a nested table is a form of data structure that enables you to store a group of components inside a single column of a table. A nested table column may hold a whole collection of values, which can be of different data types, in contrast to a conventional (flat) table, which only allows each cell to carry a single atomic value.

58. Is it possible to save images in a database, and if so, how?

Ans: 

Yes, it is feasible to store photos in a database. Binary large objects (BLOBs), which are appropriate for storing binary data including photos, music files, movies, and other multimedia information, are one of the many data types that database systems handle. You may save the actual binary data for the image in the database thanks to BLOBs.

59. What do you mean by database schema and what does it contain?

Ans: 

A database schema is the conceptual framework that designates how data, including as tables, views, indexes, constraints, and other connected objects, are organized inside a database

A database schema contains the following components:

• Tables
• Columns
• Primary Keys
• Foreign Keys
• Indexes
• Constraints

60. What is a data dictionary?

Ans: 

A data dictionary, sometimes referred to as a metadata repository or data catalog, is a central repository that houses metadata on the data pieces in a database or information system. It gives a thorough explanation of the structure, connections, qualities, limitations, and other features of the data in a database.

61. What exactly is a deadlock situation?

Ans: 

A deadlock is a form of concurrency issue that can arise in a multi-threaded or multi-process environment, such as a database system. A deadlock scenario occurs when two or more processes (or threads) each wait for a resource that another process in the deadlock group is holding.

62. What qualities are there in a CURSOR?

Ans: 

Implicit Cursor: Automatically created for every SQL DML statement (SELECT, INSERT, UPDATE, DELETE) executed. It’s used to handle single-row queries and provides basic error handling.

Explicit Cursor: Created explicitly by the programmer using the DECLARE…OPEN…FETCH…CLOSE syntax. Used for more advanced processing and handling multi-row queries.

Forward-Only Cursor: Can only navigate through the result set in a forward direction (from the first row to the last).

Scrollable Cursor: Allows navigating both forward and backward through the result set and repositioning the cursor.

63. Why do we use %ROWTYPE & %TYPE in PL SQL?

Ans: 

In PL/SQL, %ROWTYPE and %TYPE are special attributes that provide a way to declare variables, parameters, and record types based on the structure of an existing table or data type. These attributes help improve code maintainability, reduce errors, and ensure that your PL/SQL code remains synchronized with the underlying database schema.

64. Why do we write PL/SQL stored procedures and functions, and how are they different?

Ans: 

PL/SQL stored procedures and functions are used to encapsulate business logic, improve code modularity, and enhance the performance and maintainability of database applications.

Stored Procedures:

Stored procedures are blocks of PL/SQL code that can be stored in the database and executed as a single unit.

Modularity and Reusability: Stored procedures allow you to encapsulate a set of actions into a single unit, promoting modularity and code reusability. 

Performance Optimization: Complex operations that involve multiple queries or data manipulation steps can be optimized within a stored procedure.

Functions:

Functions in PL/SQL are similar to stored procedures, but they are designed to return a single value or a single result set. 

Data Transformation: Functions are commonly used to perform data transformations, calculations, or lookups. 

SQL Integration: Functions can be used within SQL queries to compute values on the fly. 

65. Which parameters are permitted to be sent through a stored procedure?

Ans: 

PL/SQL stored procedures and functions are used to encapsulate business logic, improve code modularity, and enhance the performance and maintainability of database applications.

IN Parameters: An IN parameter is used to pass values from the calling program (or the caller) to the stored procedure.

OUT Parameters: An OUT parameter is used to return a value from the procedure back to the caller.

IN OUT Parameters: An IN OUT parameter allows data to be passed both into and out of the procedure.

66. What is an alias in SQL statements?

Ans: 

In SQL, an alias is a temporary name assigned to a table or a column in a query. Aliases are often used to make the output of a query more readable or to provide a shorthand way of referring to tables and columns, especially when dealing with complex queries involving multiple tables or self-joins.

67. What is a Literal?

Ans: 

A literal is a notation that directly represents a fixed value, such as a string, number, boolean, or date. Literals are used to provide constant values directly within code, expressions, or data structures.

68. Define the order of Precedence used in executing SQL statements.

Ans: 

The order of precedence in SQL refers to the order in which distinct operators and functions are evaluated while executing a SQL query. The order of precedence influences how expressions are processed and executed to get the final result.

69. Describe types of Constraints in brief?

Ans: 

Primary Key Constraint: Ensures the uniqueness and integrity of a column or a set of columns, which are designated as the primary key. 

Unique Constraint: Ensures the uniqueness of values in a column or a set of columns, but unlike the primary key, a unique constraint does not automatically enforce the not-null requirement.

Foreign Key Constraint: Defines a relationship between two tables by enforcing referential integrity.

Check Constraint: Defines a condition that must be satisfied by the data in a column. It restricts the values that can be inserted or updated in that column.

70. What differentiates a unique key from a primary key in terms of importance?

Ans: 

Primary Key: Each row in a table is uniquely identified by a primary key. The main key columns in no two rows can have the identical values.

Unique Key: A unique key enforces uniqueness within its columns, just like a primary key. However, multiple unique keys can exist within a table.

71. What differentiates simple from complex views?

Ans: 

Simple Views: Simple views are derived from a single base table and may include a subset of the columns from that table, along with any calculated columns, expressions, or aliases.

Complex Views: Complex views are derived from multiple base tables, often involving joins, unions, or subqueries to combine data from different sources.

72.What restrictions do DML operations on Views have?

Ans: 

DML (Data Manipulation Language) operations, such as INSERT, UPDATE, and DELETE, on views are subject to various restrictions imposed by the database management system. These restrictions are designed to maintain data integrity, security, and consistency. Views that involve complex operations, such as joins with multiple tables, aggregates, or subqueries, might not be directly updatable.

73. What is a relational database management system?

Ans: 

RDBMS is a piece of software made specifically for building, managing, and modifying relational databases. Based on the relational model’s guiding principles, it offers a structured and organized approach to store and retrieve data.

 74. What exactly is normalization?

Ans: 

The process of normalization in database architecture is arranging and structuring data in a way that eliminates data anomalies, cuts down on data redundancy, and assures data integrity.

75. What is SQL, and what are its main components?

Ans: 

SQL provides a standardized approach for users to interface with databases, allowing them to do operations like as accessing data, modifying records, designing structures, and controlling access privileges. 

The main components of SQL are:

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Query Language (DQL)

Data Control Language (DCL)

76. What is the Discard File?

Ans: 

In the context of Oracle databases, a “discard file” typically refers to a file used during the import process of data using the Oracle Data Pump utility (impdp). The discard file can be helpful for troubleshooting and understanding why certain data might not have been imported as expected.

77. What is REF CURSOR ?

Ans: 

In Oracle PL/SQL, a REF CURSOR (short for “reference cursor”) is a data type that acts as a pointer or a handle to a result set (a set of rows returned by a query). It allows you to dynamically execute a query and retrieve its result set, which can then be processed further within PL/SQL code.

78. What are the types of Ref Cursors?

Ans: 

In Oracle PL/SQL, there are two main types of REF CURSORs they are Strong REF CURSORs and Weak REF CURSORs. 

79. What is Overloading Procedures?

Ans: 

Overloading procedures is a feature in PL/SQL (Procedural Language/Structured Query Language) that allows you to define multiple procedures with the same name but different parameter lists. 

80. What is Global Variables?

Ans: 

Global variables are variables that are declared at a global scope in the context of programming and software development. This means that they may be accessed and updated from any area of a program, regardless of where they are defined. 

81. What is a Forward Declaration?

Ans: 

A forward declaration, often referred to as a forward reference, is a declaration made in a programming language that presents the name of a variable, function, class, or other symbol prior to its actual definition. 

82. Explain what PL/SQL package consists of?

Ans: 

A PL/SQL package is a container that groups related procedures, functions, variables, cursors, and other PL/SQL constructs together as a single unit.A package consists of two main components: 

Package Specification

Package Body

83. Explain what the benefits of PL/SQL Packages are?

Ans: 

Here are some key advantages of using PL/SQL packages:

Modularity and Encapsulation

Code Reusability

Namespace Management

Data Encapsulation

Performance Optimization

84. Explain different methods to trace the PL/SQL code?

Ans: 

Oracle provides various methods for tracing PL/SQL code execution to gather diagnostic information. Here are some common methods:

• DBMS_OUTPUT
• UTL_FILE
• TRACE Procedure
• DBMS_PROFILER
• SQL Developer Debugger

85. What does it mean by PL/SQL Cursors?

Ans: 

A cursor in PL/SQL (Procedural Language/Structured Query Language) is a database object used to obtain and modify rows from a result set, often produced as a consequence of carrying out a SELECT command.        

86. What differentiates explicit cursors from implicit cursors?

Ans: 

Implicit cursors are automatically managed by the database when executing simple SQL statements, such as SELECT INTO statements for fetching single values. They don’t require explicit declaration and are suitable for straightforward operations. 

On the other hand, explicit cursors are declared by the programmer using the CURSOR keyword. Explicit cursors require manual steps to open, fetch rows, and close, making them suitable for scenarios where you need to process a result set iteratively. 

87. What is a trigger?

Ans: 

A trigger in a database is a named PL/SQL block (stored procedure) that is automatically executed or fired in response to specific data modification events, such as INSERT, UPDATE, or DELETE operations on a table. 

88. What are the uses of database triggers?

Ans: 

Here are some common uses of database triggers:

• Enforcing Data Integrity
• Auditing and Logging
• Automated Calculations
• Data Replication and Synchronization
• Custom Notifications and Alerts
• Business Rules Enforcement

89. Name the two exceptions in PL/SQL?

Ans: 

In PL/SQL (Procedural Language/Structured Query Language), there are two types of exceptions:

Predefined Exceptions

User-Defined Exceptions

90. Which command is used to delete the package?

Ans: 

To delete a package in PL/SQL, you use the DROP PACKAGE command. This command removes the package and its associated objects (package specification and package body) from the database.

91. What is the process for PL/SQL compilation?

Ans: 

 In order for the Oracle database to comprehend and execute PL/SQL code, it must be converted through the PL/SQL compilation process. Syntax checking, compiled code creation, and database storage are all parts of the compilation process. 

92. Differentiate between DDL, DML, and DCL statements in SQL.

Ans: 

The structure of database objects is defined and modified using DDL.

The data kept in the database is manipulated using DML.

Access and permissions pertaining to data alteration are controlled by DCL.

93. What distinguishes homogenous from heterogeneous plans in relation to DAC accomplishments?

Ans: 

Homogeneous Plan: A homogeneous plan in DAC refers to an ETL execution plan where all the Informatica sessions within that plan run against a single instance of the Informatica PowerCenter server.

Heterogeneous Plan: A heterogeneous plan in DAC refers to an ETL execution plan where different Informatica sessions within that plan run on different instances of the Informatica PowerCenter server.

94. What do you mean by an authentication file?

Ans: 

An authentication file, in the context of Oracle and database administration, refers to a file that contains sensitive information used for authenticating and connecting to a database securely.

95. How to export and import DAC architecture?

Ans: 

Export DAC Configuration:

• Log in to the DAC client.
• Go to the “Admin” tab and select “Archive Configuration.”
• Choose the components you want to export (like subject areas, tasks, execution plans, etc.).
• Specify the export directory.
• Click “Archive.”

Import DAC Configuration:

• Log in to the DAC client on the target system.
• Go to the “Admin” tab and select “Unarchive Configuration.”
• Specify the import directory where you placed the exported files.
• Click “Unarchive.”

 96. How does RMAN differ from user-managed backup recovery?

Ans: 

RMAN (Recovery Manager) and user-managed backup and recovery are two different approaches to managing backups and restoring data in an Oracle database. 

97. How do you recover a lost control file?

Ans: 

Recovering a lost control file in Oracle involves restoring a backup copy of the control file and then updating it with information about the current database status.

98. How would you differentiate between cluster and grid?

Ans: 

Cluster: A cluster refers to a group of interconnected computers, servers, or nodes that work together closely to achieve a common goal. Clusters are used to enhance performance, availability, and fault tolerance by distributing tasks across multiple nodes. 

Grid: A grid, often referred to as a “computing grid” or “grid computing,” involves the coordinated use of distributed computing resources from multiple locations to solve complex problems or perform large-scale tasks.

99. What do you understand from Cache Fusion?

Ans: 

Cache Fusion is a term used in the context of Oracle Real Application Clusters (RAC), a technology that allows multiple servers to access a single Oracle database. Cache Fusion refers to the high-speed data sharing and synchronization mechanism used to maintain data consistency and coherency among the individual server instances (nodes) in an Oracle RAC cluster.

100. What are the many tools Oracle offers to support performance monitoring?

Ans: 

Oracle provides a variety of tools to help with database product performance tweaking, monitoring, and optimization. 

• Oracle Enterprise Manager (OEM)
• Oracle Performance Hub (formerly Cloud Control)
• SQL Tuning Advisor
• SQL Access Advisor
• Real-Time SQL Monitoring
• Enterprise Manager Performance Pages
• Database Performance and Tuning Guide