Oracle Sql Interview Questions and Answers
1. Explain the difference between a hot backup and a cold backup and the benefits associated with each?
Answer: A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
2. What packages (if any) has Oracle provided for use by developers?
Answer: Level: Intermediate to high Expected answer: Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.
3. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
Answer: A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
4. What is the difference between the SQL*Loader and IMPORT utilities?
Answer: These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files. (e Learning Portal)
5. Explain iostat, vmstat, and netstat?
Answer: Iostat reports on the terminal, disk, and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape, and CPU activity.
Netstat reports on the contents of network data structures.
6. Explain the difference between a FUNCTION, PROCEDURE, and PACKAGE?
Answer: A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package, on the other hand, is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
7. How would you go about increasing the buffer cache hit ratio?
Answer: Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
8. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
Answer: I would create a text-based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
9. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Answer: Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
10. Explain the difference between SQL and SQL PLUS?
Answer: SQL represents Structured Query Language and is used to manage the database. It is a Non-Procedural Programming Language and a Standard Language for Relational Database Management System (RDBMS). However, SQL *PLUS is an Oracle-specific program that executes SQL commands using PL/SQL blocks.
11. What is the difference between Grant command and Revoke command?
Answer: A Grant command permits the End-User to perform certain activities onto the database whereas a Revoke command prevents the End-User from making any changes to the Database.
12. Explain about Package in short?
Answer: A Package is a Schema Object which assembles logically relate PL/SQL Datatypes and Sub-Programs. It is actually a combination of Procedures, Functions, Record Type and Variables. It enhances Application Development and this provides Modular Programs. It also provides Encapsulation which hides data from Unauthorized Users.
13. What are the properties of a Database?
Answer: The properties of a Database are also known as ACID properties. These are Atomicity, Consistency, Isolation, and Durability.
14. What is a Row Level Trigger?
Answer: A Statement Level Trigger is executed whenever a statement or a command affects a row in a table by Database Manipulation Command (DML) statements like Delete, Insert or Update.
15. What is an Active Set?
Answer: The set of rows that a Cursor holds at a single point of time is called an Active Set.
16. What is Mutating Table Error?
Answer: It occurs when a Trigger tries to update a row that is currently in the execution stage. So it is solved out by using temporary tables and views.
17. What is %rowtype used for?
Answer: This is used if you want to create a new variable which needs to fetch in a variable value from the database and you don’t know the Datatype of that variable. %rowtype will automatically change its datatype to the one in the database.
18. What is Stored Procedure?
Answer: A stored procedure is a sequence of a statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as a schema object. It can be nested, invoked and parameterized.
19. What are the benefits of PL/SQL packages?
Answer: It provides several benefits like
Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
Object persistence: Objects declared in a package specification behaves like global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
Object-oriented design: The package gives developers stronghold over how the modules and data structures inside the package can be used
Guaranteeing transaction integrity: It provides a level of transaction integrity
Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.
20. Explain the uses of a database trigger?
Answer: A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
Audit data modifications.
Log events transparently.
Enforce complex business rules.
Maintain replica tables
Derive column values
Implement Complex security authorizations
Any of the constant, variable or parameter has a data type depending on which the storage constraints, format, and range of values and operations are determined.
21. What is Overloading Procedures?
Answer: Overload refers to the same name can be used for a different purpose, in oracle we can also implement overloading procedure through the package. Overloading procedure having the same name with a different type or a different number of parameters.
22. What is the difference between Simple and Complex Views?
Answer: The main differences between the two views are:
- Simple Views Vs Complex Views
- 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.
23. What is the main difference between CHAR and VARCHAR2?
Answer: CHAR pads blank spaces to a maximum length, whereas VARCHAR2 does not pad blank spaces.
24. What is a SET UNUSED option?
Answer: SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column. A select * query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column.
25. What is a DEFAULT option in a table?
Answer: A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.
26. What is the difference between Commit, Rollback, and Savepoint?
Answer: COMMIT: Ends the current transaction by making all pending data changes permanent.
ROLLBACK: Ends the current transaction by discarding all pending data changes.
SAVEPOINT: Divides a transaction into smaller parts. You can rollback the transaction to a particular named savepoint.
27. What is the difference between Entity, Attribute, and Tuple?
Answer: Entity: A significant thing about which some information is required. Forex: EMPLOYEE (table). Attribute: Something that describes the entity. Forex: empno, empname, emp address (columns). Tuple: A row in a relation is called Tuple.
28. Describe different types of General Function used in SQL?
Answer: General functions are of following types:
NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.
NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.
Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.
29. What is the difference between USER TABLES and DATA DICTIONARY?
Answer: USER TABLES: Is a collection of tables created and maintained by the user. Contain USER information. DATA DICTIONARY: Is a collection of tables created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user.
30. In what scenario you can modify a column in a table?
Answer: During modifying a column:
- You can increase the width or precision of a numeric column.
- You can increase the width of numeric or character columns.
- You can decrease the width of a column only if the column contains null values or if the table has no rows.
- You can change the data type only if the column contains null values.
- You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size.
31. What is a Candidate Key?
Answer: The columns in a table that can act as a Primary Key are called Candidate Key.
32. What are the restrictions of DML operations on Views?
Answer: 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.
33. What is the Discard File?
Answer: This file extension is .dsc
Discard file we must specify within control file by using the discard file clause.
Discard file also stores reflected record based on when clause condition within the control file. This condition must be satisfied into table name clause.
34. What is the Global Variables?
Answer: In oracle, we are declaring global variables in Package Specification only.
35. What is the basic structure of PL/SQL?
Answer: PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
Master PL/SQL, in this PL/SQL certification training.
36. How can you Rollback a particular part of a procedure or any PL/SQL Program?
Answer: It can be done using Savepoints during the definition of a PL/SQL program.
37. What are the different components of a PL/SQL trigger?
Answer: Trigger Action, Trigger Restriction, and Trigger Action are the different components of a PL/SQL Trigger.
38. What is Context Area in PL/SQL?
Answer: Oracle processes the executed SQL Statement in a separate memory zone called a Context Area. It contains information regarding SQL Query, number of rows accessed by it, information retrieved from database tables/records and many more.
39. Why is closing the Cursor required during explicit cursor development?
Answer: It is important because it will consume system memory while it is the inactive state and if it is not closed or terminated then it won’t let the other things in the memory as memory will be occupied and later on it will be full. Hence, deletion is necessary.
40. Explain about SQLERRM and SQLCODE and their importance?
Answer: SQLERRM Returns the Error Message for the Last Error that came across. SQLERRM is useful for WHEN OTHERS Exception. They can be used for Reporting purpose in Error Handling. They can also store the Error in the Code and store it in the Error Log Table. SQLCODE returns the value of the Error Number for the previous error.
41. Explain about Pragma Exception_Init?
Answer: It allows us to handle Oracle Pre Defined Messages wherein we can replace our own Message. We can, therefore, instruct the compiler to link the user-specified message to Oracle Pre Defined Message during Compilation Time.
Syntax: Pragma Exception_Init (Exception_Name, Error_Code).
42. Enlist the Attributes of a Cursor in PL/SQL?
Answer: Rowcount: This attribute checks the number of rows that are updated, deleted or fetched.
Isoprene: This attribute checks whether the Cursor you want to access is currently open or closed.
Found: This attribute checks if the Cursor fetched a row. It returns a TRUE if any row is fetched. %NotFound: This attribute checks if the Cursor fetched any row or not. It returns a TRUE value if any row is not fetched.
43. Explain Bulk Collect?
Answer: It is a way of fetching a very big collection of data. With the help of Oracle Bulk Collect, the PL/SQL Engine indicates the SQL Engine to collect more than one row at a single point of time and stores them into a collection. Then it switches back to the PL/SQL Engine. During the Bulk Collect, Context Switch at one point. The performance improvement would be better with the more number of rows fetched into the collection.
44. Enlist the Types of Triggers and its combinations?
Answer: There can be various types of combinations used in Triggers which are After, Before, Insert, Update, Delete, Row, Table, and other such combinations.
45. When is a declare statement needed?
Answer: The DECLARE statement is used in PL/SQL anonymous blocks such as with stand-alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
46. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each?
Answer: ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.