Top 50+ Oracle Interview Questions & Answers - 2020 [95% SUCCESS]

Top 50 Oracle Interview Questions For [95% SUCCESS]

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

About author

Sankar (Sr Cloud Engineer Developer )

High level Domain Expert in TOP MNCs with 10+ Years of Experience. Also, Handled Around 36+ Projects and Shared his Knowledge by Writing these Blogs for us.

(5.0) | 16589 Ratings 1230

According to research, Oracle PL SQL has a market share of about 2.2%. So, You still have the opportunity to move ahead in your career in Oracle PL SQL Development. Here is a list of 100 interview questions about Oracle with their answers. The list contains questions useful for basic, freshers and experienced oracle professionals.

1. What is Oracle?

Ans: 

  Oracle is a database server that is used to handle data in a structured manner. It lets its users to retrieve and to store data in a way that multiple users can access similar data at the same time. Oracle achieves this with high efficiency. Many security checks are followed to limit access to authorized users only and to retrieve data in case of accidental data loss.

2. Define Oracle database

Ans: 

An Oracle database can be termed as a compilation of data housed in a database server and treated like a large unit.

3. Explain Oracle instances.

Ans: 

Each operating Oracle database is associated with an Oracle instance. As soon as a database server starts the database, it is assigned a memory area by Oracle called System Global Area (SGA) and starts one or more than one processes. The SGA and Oracle processes together are referred to as an Oracle instance. The process and memory of an instance are used to efficiently handle data used by multiple users.

4. Explain Parameter file in Oracle.

Ans: 

Parameter file is a file having a track of initialization parameters and the matching values. The two types of Oracle supported parameter files are:

  • Initialization parameter file: Text version and
  • Server parameter file: Binary version

Based on individual installations users can specify their individual initialization parameters.

5. Explain Oracle’s Server parameter file.

Ans: 

It is a file which has a binary nature and contains initialization parameter’s list. It is located on machines where the database server executes. Initialization parameters of server parameter file are persistent.

6. Explain Oracle’s System Global Area (SGA).

Ans: 

It is the memory area which contains shared data like SQL statements shared pool and buffer cache, between all users. As soon as an Oracle database instance starts the SGA is allocated. Value changes become effective during subsequent startup.

7. Explain a user account with reference to Oracle.

Ans: 

Every user is given particular attributes identified by a username termed as a user account. The below can be incorporated into the user attributes:

  • Passwords to access database
  • Roles and privileges
  • Default tablespace containing database objects and
  • Default temporary tablespace facilitating query handling workspace.

8. Which are the five query types available in Oracle?

Ans: 

The five query types in Oracle are as follows:

  • Compound queries
  • Nested queries
  • Correlated queries
  • Sub queries
  • Normal queries.

9. What do you mean by a transaction?

Ans: 

  A group of SQL statements flanked by any 2 ROLLBACK and COMMIT statements is a transaction.

10. Differentiate between function and procedure in Oracle.

Ans: 

  A function is used to return a single value whereas a procedure doesn’t return any value. It returns multiple variables. This is achieved by passing variables by reference through OUT parameters.

11. Can there be more than one function with a similar name in a PL/SQL block?

Ans: 

Yes

12.  Explain overloading. Can functions be overloaded?

Ans: 

  Overloading happens when an object is performing various functions based on the number or data types of the parameters passed through it. Yes, Functions can be overloaded.

13. Give the constructs of a package, function or a procedure.

Ans: 

The constructs for a package, function or a procedure are:

  • Exceptions
  • Cursors
  • Variables and constants

14. Why do you create or replace procedures rather than drop and recreate.

Ans: 

 In order to prevent Grants from getting dropped we create and replace procedures rather than drop and recreate.

15.  Explain implicit cursor.

Ans: 

It’s a cursor formed by Oracle internally for individual SQL.

16. From the following identify the non schema object: packages, triggers, public synonyms, tables and indexes.

Ans: 

Public synonyms.

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

Ans: 

No. SQL*Plus doesn’t contain a PL/SQL engine, unlike Oracle Forms. Because of which all PL/SQL is sent to the database engine to get executed which increases the efficiency. Each SQL statement is individually sent to the database and not stripped off.

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

Ans: 

As of now a compiled/ parsed block of PL/SQL has a maximum size limitation of 64K and max. code size being 100K. The statement for querying existing procedure or the package size is as follows:

SQL> select * from dba_object_size where name = ‘procedure_name’

19. How to read/write files from PL/SQL?

Ans: 

Oracle 7.3 has a UTL_FILE package included in it which is used to read/ write files. The directory where you want to write to has to be in the INIT.ORA file. Prior to Oracle 7.3, DBMS_OUTPUT with the SQL*Plus SPOOL command was the only way to write a file.

20. Explain the methods used to protect source code of PL/SQL.

Ans: 

  Source codes of PL/SQL V2.2 made available with Oracle 7.2 are protected by implementing the binary wrapper. A stand-alone function does this by transforming the source code of PL/SQL to a portable binary object code. Thus the software can be distributed without the proprietary methods and algorithms getting exposed. Such scripts can still be understood and executed by SQL*DBA and SQL*Plus. The only precaution to be taken is that “decode” command shouldn’t be available.

    Subscribe For Free Demo

    21. Give the various exception types.

    Ans: 

    There are two exception types:

    • User-defined &
    • Pre defined.

    22. List the parts of a database trigger.

    Ans: 

    Parts of a database trigger are:

    • Trigger statement or event,
    • Trigger restriction and
    • Trigger action

    23. How many types of database triggers exist?

    Ans: 

    There exist 12 different types of database triggers. They are made up of different combinations of:

    • Statement and row triggers,
    • Before and after triggers,
    • Update, delete and insert triggers.

    24. How would you change old and new values in an insert, delete and update triggers?

    Ans: 

    Changing old and new values in an insert, delete and update triggers:

    • INSERT : new = new value, old = NULL
    • DELETE : new = NULL, old = old value
    •  UPDATE : new = new value, old = old value

    25. Explain cascading triggers.

    Ans: 

    When a trigger is fired due to a stamen in another trigger body then the triggers are termed as cascading triggers. There can be a maximum of 32 cascading triggers.

    26. Explain mutating triggers.

    Ans: 

    Triggers giving SELECT to the table on which they are written are termed as mutating triggers.

    27.  Explain constraining triggers.

    Ans: 

    Triggers giving Update/Insert to the table possessing referential integrity constraint on the triggering table are termed as constraining triggers.

    28. Give the advantages and disadvantages of clusters.

    Ans: 

    Clusters reduce the access time for joins and increase it for insert.

    29. How can you use check constraints for self referential integrity?

    Ans: 

    In a table, a check condition of a column can reference another column in the same table thus providing self referential integrity.

    30. Give the various rollback segment states.

    Ans: 

    The various rollback segment states are:

    • Invalid
    • Needs recovery
    • Partly available
    • Offline
    • Online.

    31. Is a rollback possible to any savepoint?

    Ans: 

     Yes a rollback is possible to any save point.

    32. What is the maximum limit on the number of columns in a table?

    Ans: 

    A table can have a maximum of 254 columns.

    33. Explain the significance of the & and && operators in PL SQL.

    Ans: 

    The & operator signifies that a user input is needed for the PL SQL block variable. The && operator signifies that this variable’s value must be the same as inputted earlier by the user for the same variable.

    34. Can a parameter be passed to a cursor?

    Ans: 

    Yes, Parameters can be passed to explicit cursors. A cursor parameter could appear wherever a constant appears in a query.

    Example:

    CURSOR c1 (median IN NUMBER) IS

    SELECT job, ename FROM emp WHERE sal > median;

    35. Give the different types of rollback segments.

    Ans: 

    The different types of rollback segments are:

    • Private Available to particular instance and
    • Public Available to all instances
    Course Curriculum

    Learn Expert-led Oracle Training with Dedicated Lab Environment

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    36. What is ceil and floor in Oracle?

    Ans: 

    Ceil and Floor are the functions of Oracle and both of them are completely opposite to each other.

    • Oracle CEIL function: This function is used to return the smallest value of integer to the greater or equal value of the supplied number. In short, it rounds up the digit to the nearest whole number. Ceil is the short form used for the ceiling.
    • Oracle FLOOR function: This function of Oracle is used to round down the value to the nearest whole number. The main purpose of using floor function is to return the value of the highest integer to the greater or equal of the lowest value of the supplied number.

    37. What is RAW datatype?

    Ans: 

    RAW data type is used in storing values in binary data format. The maximum size of a RAW in a table is 32767 bytes.

    38. What is BLOB datatype?

    Ans: 

    A BLOB data type is a binary string with a varying length which is used in storing two gigabytes memory. Length should be stated in Bytes for BLOB.

    39. What is NULL value in Oracle?

    Ans: 

    NULL value represents unknown or missing data. It is used as a place holder or represented as a default entry indicating that no actual data is present.

    40. What is WITH CHECK OPTION?

    Ans: 

    The WITH CHECK option clause specifies the level of check to be done in DML statements. It aids in preventing changes to a view that would produce results not contained in the sub query.

    41. What is the difference between varchar and varchar2 data types?

    Ans: 

    Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes. While Varchar will occupy space for NULL value, Varchar2 will not occupy any space. They are differentiated by space.

    42. What is the use of NVL function?

    Ans: 

    The NVL function is used for replacing NULL values with given or another value. E.g.  NVL(Value, replace value)

    43. How do we get the field detail of a table?

    Ans: 

    To get the field of a specified table use, Describe <Table_Name>

    44. What is an ALERT?

    Ans: 

    An alert is a window which appears in the center of the screen and overlays a portion of the current play.

    45. What is the fastest query method to fetch data from the table?

    Ans: 

    You can use ROWID to fetch Row from the table. The use of ROW ID is the fastest query method for fetching data from the table.

    46. What is the parameter mode that can be passed to a procedure?

    Ans: 

    The parameter modes that can be passed to a procedure are IN, OUT and INOUT

    47. What is a hash cluster?

    Ans: 

    Hash Cluster is a technique used for storing the table to make it faster to retrieve. In order to retrieve the rows from the table, apply the hash value on the table.

    48. What are SET operators?

    Ans: 

    SET operators are used with two or more queries. The operators are Union, Union All, intersect and Minus.

    49. What is a view?

    Ans: 

    View is a logical table that is based on one or more tables and views. The tables which the view is based upon are called Base Tables and it contains no data.

    50. What is Oracle and what are its different editions?

    Ans: 

    Oracle is one of the popular databases provided by Oracle Corporation, which works on relational management concepts and hence it is referred to as Oracle RDBMS as well. It is widely used for online transaction processing, data warehousing, and enterprise grid computing.

    Course Curriculum

    Get On-Demand Oracle Cloud Training & Certification Course

    Weekday / Weekend BatchesSee Batch Details

    51. What is the use of Aggregate functions in Oracle?

    Ans: 

    Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations.

    These are:

    • AVG
    • MIN
    • MAX
    • COUNT
    • SUM
    • STDEV

    52. What are the set operators UNION, UNION ALL, MINUS & INTERSECT meant to do?

    Ans: 

    The set operator facilitates the user to fetch the data from two or more than two tables at once if the columns and relative data types are the same in the source tables.

    • UNION operator returns all the rows from both the tables except the duplicate rows.
    • UNION ALL returns all the rows from both the tables along with the duplicate rows.
    • MINUS returns rows from the first table, which does not exist in the second table.
    • INTERSECT returns only the common rows in both the tables.

    53. Can we convert a date to char in Oracle and if so, what would be the syntax?

    Ans: 

    We can use the TO_CHAR function to do the above conversion.

    Syntax:

    • SELECT to_char (to_date (’30-01-2018′, ‘DD-MM-YYYY’), ‘YYYY-MM-DD’) FROM dual;

    54. What do you mean by a database transaction & what all TCL statements are available in Oracle?

    Ans: 

    Transaction occurs when a set of SQL statements are executed in one go. To control the execution of these statements, Oracle has introduced TCL i.e. Transaction Control Statements that use a set of statements.

    The set of statements include:

    • COMMIT: Used to make a transaction permanent.
    • ROLLBACK: Used to roll back the state of DB to last the commit point.
    • SAVEPOINT: Helps to specify a transaction point to which rollback can be done later.

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

    Ans: 

     Objects used to store the data or references of the data in a database is known as a database object. The database consists of various types of DB objects such as tables, views, indexes, constraints, stored procedures, triggers, etc.

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

    Ans: 

    A nested table is a database collection object, which can be stored as a column in a table. While creating a normal table, an entire nested table can be referenced in a single column. Nested tables have only one column with no restriction of rows.

    For Example:

    • CREATE TABLE EMP (
    • EMP_ID NUMBER,
    • EMP_NAME  TYPE_NAME)

    Here, we are creating a normal table as EMP and referring to a nested table TYPE_NAME as a column.

    57. Can we save images in a database and if yes, how?

    Ans: 

    BLOB stands for Binary Large Object, which is a data type that is generally used to hold images, audio & video files or some binary executables. This data type has the capacity of holding data up to 4 GB.

    58.  What do you understand by database schema and what does it hold?

    Ans: 

    Schema is a collection of database objects owned by a database user who can create or manipulate new objects within this schema. The schema can contain any DB objects like table, view, indexes, clusters, stored procs, functions, etc.

    59 What is a data dictionary and how can it be created?

    Ans: 

    Whenever a new database is created, a database-specific data dictionary gets created by the system. This dictionary is owned by the SYS user and maintains all the metadata related to the database. It has a set of read-only tables and views and it is physically stored in the SYSTEM tablespace.

    60. What is meant by a deadlock situation?

    Ans: 

    Deadlock is a situation when two or more users are simultaneously waiting for the data, which is locked by each other. Hence it results in all blocked user sessions.

    61. What are the attributes that are found in a CURSOR?

    Ans: 

      A CURSOR has various attributes as mentioned below:

    %FOUND:

    • Returns INVALID_CURSOR if the cursor has been declared but closed.
    • Returns NULL if fetch has not happened but the cursor is open only.
    • Returns TRUE, if the rows are fetched successfully and FALSE if no rows are returned.

    %NOT FOUND:

    • Returns INVALID_CURSOR if the cursor has been declared but closed.
    • Returns NULL if fetch has not happened but the cursor is open only.
    • Returns FALSE, if rows are fetched successfully and TRUE if no rows are returned

    %ISOPEN:

    • Returns TRUE, if the cursor is OPEN else FALSE

    %ROWCOUNT:

    • Returns the count of fetched rows.

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

    Ans: 

    %ROWTYPE & %TYPE are the attributes in PL/SQL that can inherit the datatypes of a table defined in a database. The purpose of using these attributes is to provide data independence and integrity.

    If any of the data types or precision gets changed in the database, PL/SQL code gets updated automatically with the changed data type. %TYPE is used for declaring a variable that needs to have the same data type as of a table column.

    While %ROW TYPE will be used to define a complete row of records having a structure similar to the structure of a table.

    63.  Why do we create Stored Procedures & Functions in PL/SQL and how are they different?

    Ans: 

    A stored procedure is a set of SQL statements that are written to perform a specific task. These statements can be saved as a group in the database with an assigned name and can be shared with different programs if permissions are there to access the same.

    Functions are again subprograms that are written to perform specific tasks but there are differences between both of them.

    Stored Procedures Functions
    Stored Procedures may or may not return a value and can return multiple values as well. Function will always return only a single value.
    Stored Procedures can include DML statements like insert, update & delete. We cannot use DML statements in a function.
    Stored Procedures can call functions. Functions cannot call stored procedures.
    Stored Procedures support exception handling using Try/Catch block. Functions does not support Try/Catch block.

    64. What are the parameters that we can pass through a stored procedure?

    Ans: 

    We can pass IN, OUT & INOUT parameters through a stored procedure and they should be defined while declaring the procedure itself.

    65.  What is an alias in SQL statements?

    Ans: 

    Alias is a user-defined alternative name given to the column or table. By default column alias headings appear in upper case. Enclose the alias in a double quotation mark (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.

    For ex: Select emp_name AS name from employee; (Here AS is a keyword and “name” is an alias).

    66. What is a Literal? Give an example of where it can be used?

    Ans: 

    A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks (‘ ‘), number literals need not.

    For ex: Select last_name||’is a’||job_id As “emp details” from the employee; (Here “is a” is a literal).

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

    Ans: 

    Order of Precedence used in executing SQL statements

    Order Evaluated Operator
    1 Arithmetic operators (*, /, +, -)
    2 Concatenation operators (||)
    3 Comparison conditions
    4 Is[NOT] NULL, LIKE, [NOT] IN
    5 [NOT] BETWEEN
    6 NOT Logical condition
    7 AND logical condition
    8 OR logical condition

     68. Describe types of Constraints in brief?

    Ans: 

    NOT NULL: NOT NULL Constraint ensures that the column contains no null values.

    UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a column or set of columns must be unique, that is, no two rows of a table can have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than one column, that group of columns is called a Composite Unique Key. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates an index on the column it is created.

    PRIMARY KEY: Uniquely identifies each row in the Table. Only one PRIMARY KEY can be created for each table but can have several UNIQUE constraints. PRIMARY KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY KEY is called a Parent key.

    FOREIGN KEY: Is also called Referential Integrity Constraint. FOREIGN KEY is one in which a column or set of columns take references of the Primary/Unique key of the same or another table. FOREIGN KEY is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be null.

    CHECK KEY: Defines a condition that each row must satisfy. A single column can have multiple CHECK Constraints. During CHECK constraint following expressions is not allowed:

    • References to CURRVAL, NEXTVAL, LEVEL and ROWNUM Pseudo columns.
    • Calls to SYSDATE, UID, USER and USERENV Functions

    69. What is the main difference between Unique Key and Primary Key?

    Ans: 

    The main difference between Unique Key and Primary Key are:

    Unique Vs Primary Key

    Unique Key Primary Key
    A table can have more than one Unique Key. A table can have only one Primary Key.
    Unique key column can store NULL values. Primary key column cannot store NULL values.
    Uniquely identify each value in a column. Uniquely identify each row in a table.

    70. What is the difference between Simple and Complex Views?

    Ans: 

    The main differences between two views are:

    Simple View Complex View
    Derives data from only one table. Derives data from many tables.
    Contains no functions or group of data Contain functions or groups of data.
    Can perform DML operations through the view. Does not always allow DML operations through the view.

    71. What are the restrictions of DML operations on Views?

    Ans: 

      Few restrictions of DML operations on Views are:

    You cannot DELETE a row if the View contains the following:

    • Group Functions
    • A Group By clause
    • The Distinct Keyword
    • The Pseudo column ROWNUM Keyword.

    You cannot MODIFY data in a View if it contains the following:

    • Group Functions
    • A Group By clause
    • The Distinct Keyword
    • The Pseudo column ROWNUM Keyword.
    • Columns defined by expressions (Ex; Salary * 12)

    72.  What is PL/SQL, Why do we need PL/SQL instead of SQL, Describe your experience working with PLSQL and What are the difficulties faced while working with PL SQL and How did you overcome it?

    Ans: 

    • PL/SQL is a procedural language extension with SQL Language.
    • Oracle introduced PL/SQL
    • It is a combination of SQL and Procedural Statements and used for creating applications.
    • Basically PL/SQL is a block structured programming language whenever we are submitting PL/SQL
    • Blocks then all SQL statements are executed separately by using sql engine and also all procedure statements are executed separately.
    • Explain your current and previous projects along with your roles and responsibilities, mention some of the challenging difficulties you’ve faced in your project while working with PL/SQL. 

    73. What are the different functionalities of a Trigger ?

    Ans: 

    Trigger is also the same as stored procedure & also it will automatically invoked whenever DML operation performed against table or view.

    There are two types of triggers supported by PL/SQL:

    • Statement Level Trigger.
    • Row Level Trigger

    Statement Level Trigger: In statement-level trigger, the trigger body is executed only once for DML statement.

    Row Level Trigger: In row level trigger, the trigger body is executed for each row DML statement. It is the reason, we are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers :old, :new, are also called as records type variables.

    These qualifiers are used in trigger specification & trigger body.

    Syntax:

                 :old.column_name

    Syntax:

                :new column_name

    When we use these qualifiers in trigger specification then we are not allowed to use “:” in forms of the names of the qualifiers.

     74. What is the Discard File?

    Ans: 

    • This file extension is .dsc
    • Discard file we must specify within the control file by using the discard file clause.
    • Discard file also stores reflected records based on when clause conditions within the control file. This condition must be satisfied into the table tablename clause.

    75. What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR ?

    Ans: 

    Oracle 7.2 introduced ref cursor, This is an user-defined type which is used to process multiple records and also this is a record by record process.

    In static cursor database servers executes only one select statement at a time for a single active set area where in ref cursor database servers executes a number of select statements dynamically for a single active set area that’s why those cursors are also called as dynamically cursor.

    Generally we are not allowed to pass static cursor as parameters to use subprograms where as we can also pass ref cursor as parameter to the subprograms because basically refcursor is an user defined type in oracle we  can also pass all user defined type as parameter to the subprograms.

    Generally static cursor does not return multiple records into client applications whereas ref cursors are allowed to return multiple records into client applications (Java, .Net, php, VB, C++).

    This is a user defined type so we are creating it in 2 steps process i.e first we are creating a type then only we are creating a variable from that type that’s why this is also called a cursor variable.

    Oracle Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    76. What are The Types of Ref Cursors?

    Ans: 

    In all databases having 2 ref cursors:

    • Strong ref cursor
    • Weak ref cursor

    Strong ref cursor is a ref cursor which has a return type, whereas weak ref cursor has no return type.

    Syntax:

    •  Type typename is ref cursor return record type data type;
    • Variable Name typename

    Syntax

    •  Type typename is ref cursor
    • Variable Name typename

    In Weak ref cursor we must specify a select statement by using open for clause this clause is used in the executable section of the PL/SQL block.

    Syntax:

    •  Open ref cursor varname for SELECT * FROM table_name condition;

    77. What is Overloading Procedures?

    Ans: 

    Overload referring to the same name can be used for different purposes, in oracle we can also implement overloading procedure through package. Overloading procedure having same name with different type or different number of parameters.

    78. What is Global Variables?

    Ans: 

    In oracle we are declaring global variables in Package Specification only.

    79.  What is a Forward Declaration?

    Ans: 

    In oracle declaring procedures within the package body is called forward declaring generally before we are calling private procedures into public procedure first we must implement private into public procedure first we must implement private procedure within body otherwise use a forward declaration within the package body.

    80. Explain what PL/SQL package consists of?

    Ans: 

    • PL/SQL consists of two major parts, they are: package specification and package body.
    • Package specification: it acts as a public interface for your application which includes procedures, types, etc.
    • Package Body: It contains the code which  required to implement the Package Specification.

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

    Ans: 

    These are the benefits of PL/SQL Packages:

    • We can store functions and procedures in a single unit called a package.
    • Packages provide security to grant privileges.
    • Functions and procedures, within the package, share a common variable among them.  
    • Packages support even if the functions are overloaded.
    • Packages enhance the performance even when the multiple objects loaded into memory.

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

    Ans: 

    Tracing code is a necessary technique to test the performance of the code during runtime.  We have different methods in PL/SQL to trace the code, which are:

    • DBMS_ TRACE
    • DBMS_ APPLICATION_INFO
    • Tkproof utilities and trcsess  
    • DBMS_SESSION and DBMS_MONITOR

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

    Ans: 

    PL/SQL to retrieve and process more it requires a special resource, and that resource is known as Cursor. A cursor is defined as a pointer to the context area. Context area is an area of memory which contains information and SQL statements for processing the statements.    

    84. What is the difference between Implicit and Explicit Cursors?

    Ans: 

    Implicit cursor used in PL/SQL to declare, all SQL data manipulation statements. An implicit cursor is used to declare SQL statements such as open, close, fetch etc.

    An explicit cursor is a cursor and which is explicitly designed to select the statement with the help of a cursor. This explicit cursor is used to execute the multirow select function. An explicit function is used PL/SQL to execute tasks such as update, insert, delete, etc.

    85. What is a trigger?

    Ans: 

    It is a program in PL/SQL, stored in the database and executed instantly before or after the UPDATE, INSERT and DELETE commands.          

    86. What are the uses of database triggers?

    Ans: 

    Triggers are programs which are automatically fired or executed when some events happen and are used for:

    • To implement complex security authorizations.
    • To drive column values.
    • To maintain duplicate tables.
    • To implement complex business rules.
    • To bring transparency in log events. 

    87. Name the two exceptions in PL/SQL?

    Ans: 

    Error handling part of PL/SQL is called an exception.  We have two types of exceptions, and they are User-defined and predefined.

    88. Which command is used to delete the package?

    Ans: 

    To delete the ‘Package’ in PL/SQL we use the DROP PACKAGE command.

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

    Ans: 

    The compilation process consists of syntax check, bind and p-code generation. It checks the errors in PL/SQL code while compiling. Once all errors are corrected, a storage address is allocated to a variable which stores this data. This process is called binding. P-Code consists of a list of rules for the PL/SQL engine. It is stored in the database and triggered when next time it is used.

    90.  Illustrate the various types of DAC Repository Objects that are usually held in the containers related to the source system

    Ans: 

    There exist multiple kinds of DAC repository objects that are essential in order to make sure that the data warehousing tasks are being accomplished in a proper manner. The following are the various objects related to the DAC Repository.

    • Tables: They are also known by physical tables in DAC.
    • Assignments: They are also popularly referred to as tasks and are usually known as a unit of work loading tables.
    • Indexes: Indexes are also known as the physical DAC indexes which facilitates the value assignment of data.
    • Data structure Tables: Data structure tables play a pivotal role in the proper measurement of various types of data. They are also referred to as the indexes related to DAC.
    • Group of Tasks: The classification of assignments can be collected to run as a particular collective group.
    • Plans related to the execution process: A data alteration technique that is usually defined on the places of a subject. These sites of an issue are the core places that need to be altered at various frequencies in a particular frame of mind.
    • Time schedules or time-frequency: It is associated with the correct assessment of the running of the execution process.

    91. Illustrate the process of how DAC usually keeps track of the time of refresh for the source tables and target tables.

    Ans: 

      The dates related to refresh are usually tracked for the tables. The tables can be a primary source or can also be a prime target. These primary and source target tables are typically based on the completed trial of particular plans related to accomplishments. The DAC can even run the full load command for the assignments that are based on a table which is a primary source of a target. It also runs the entire load of powers that are usually assigned to duties in case the date of refresh against the table is null. In case there are a lot of multiple sources, the refresh dates would always trigger an incremental load. DAC would run the full load command structure, in fact, the source tables have no refresh dates. 

    92. How can you differentiate between homogeneous and heterogeneous plans related to accomplishments in DAC?

    Ans: 

      A different plan related to accomplishments refers to the fact that it can extract data from one or more chances of source systems that are dissimilar. For instance, a business organization can have a score of Siebel 7.8 in one location. On the other hand, an example of Oracle EBS 11 can be in another position. One can also opt for staggering the timing of data extraction when the professional is using this type of plans related to accomplishments.  

    On the other side, homogenous plans related to accomplishments also pull database systems from various instances of the system that have originated from the same source. A suitable example, in this case, can be in the form of the fact that a business can have Oracle EBS 11 in one place and time zone and another portion of EBS 11 in another position and time zone. In the cases mentioned above, data extraction timing can be altered so that the business needs of an organization can be met. 

    93. What do you mean by an authentication file?

    Ans: 

    The file of authentication usually authenticates the database in which the repository lies. On the other hand, if you opt for creating an authentication file, then you have the liberty to specify the particular table and password for a specified set of the database.

    94. How to export and import DAC architecture?

    Ans: 

    DAC export and import are primarily used for backup or repository metadata. The logical system and runtime objects can facilitate essential and commodity.

    95. How is RMAN better than the user-managed backup recovery process?

    Ans: 

    Recovery Manager (RMAN) is an Oracle built-in utility that can automate database backup & recovery processes and administrate backup strategies as well. In user-managed backups, the user needs to perform backup manually. RMAN backup time will be less when compared to user-managed backups as RMAN maintains all the metadata in the Central Repository and can quickly retrieve the same.

    RMAN does incremental backup rather than taking full file backups which are done by user-managed backups, which again saves time. RMAN creates backup and recovery scripts that can be reused and scheduled and does not need manual intervention.

    RMAN can detect corrupted data blocks automatically during the backup process and recover them, whereas it doesn’t happen in user-managed backups.

     96. How do you recover a lost control file?

    Ans: 

    If one of the control files is lost or gets corrupted, we can use another copy of the control file maintained at the OS level. If all the copies of control files are lost or if a user is maintaining only one copy of the control file which gets lost, then a user can

    • Manually create a control file.
    • Restore it from the backup control file using the below command.
    • Restore using RMAN backup by using the below commands.
      • setdbid XX;
      • restorecontrolfile;

    97. How would you differentiate between cluster and grid?

    Ans: 

    Clustering is an integral part of grid infrastructure and focuses on a specific objective.

    The grid, which may or may not consist of multiple clusters, possesses a wider framework that enables sharing of storage systems, data resources and remaining others across different geographical locations.

    A cluster will have single ownership but the grid can have multiple ownership based on the number of the cluster it holds.

    98. What do you understand from Cache Fusion?

    Ans: 

    Cache fusion is the process of transferring data from one instance buffer cache to another at a very high speed within a cluster. Instead of fetching data from a physical disk which is a slow process, the data block can be accessed from the cache.

    For Example: Instance A wants to access a data block, owned by instance B. It will send an access request to instance B and hence can access the same using the other instance B’s buffer cache.

    99. What are the different tools that are provided by Oracle to assist performance monitoring?

    Ans: 

    Various tools include:

    • AWR(Automatic Workload Repository)
    • ADDM(Automated Database Diagnostics Monitor)
    • TKPROF
    • STATSPACK
    • OEM(Oracle Enterprise Manager)

    100. How can we identify the resources for which the sessions are waiting?

    Ans: 

    We can find it out using v$session_waits and v$ system _waits.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free