Oracle Interview Questions - Orapoint

Oracle is a highly secure database commonly utilized by international corporations. The following are some of the most commonly asked questions from the Oracle database.

Q1. What components make up Oracle’s physical database structure?

Ans. The physical database structure’s components are listed below.

• There are one or more data files.
• Two or more redo log files are required.
• There may be one or more control files.

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

Ans. Varchar can hold up to 2000 bytes, while varchar2 can hold up to 4000. Varchar will take up space for NULL values, however Varchar2 would not. In terms of space, they are unlike.

Q3. What is the definition of a tablespace?

Ans. Tablespaces are logical storage units found in databases. A tablespace is a collection of logical structures that are linked together. In reality, a tablespace is a collection of related logical entities.

Q4. What’s the connection between a database, a tablespace, and a data file?

Ans. An Oracle database has one or more tablespaces, which are logical storage units. These tablespaces store the entire data of databases, and each tablespace in an Oracle database is made up of one or more datafiles. These datafiles are physical structures that Oracle uses to communicate with the operating system.

Q5. What are nested tables, and how do you use them?

Ans. In Oracle, a nested table is a data type that is used to accommodate columns with multiple values. It can also accommodate a full sub table.

Q6. What is an Oracle database save point?

Ans. Save points are used to break up a large transaction into smaller chunks. It enables the reversal of a transaction. There are a total of five save points available. It is used to save our data; if a mistake occurs, you can roll back to the point where your SAVEPOINT was saved.

Q7.What exactly is the distinction between pre-select and pre-query?

Ans. A pre-query trigger fires once before the query executes and once while the query is being executed. With the aid of this trigger, you can dynamically change the where clause component.

After Oracle forms generate the choose statement to be issued, but before the statement is issued, the pre-select query fires during the execute query and count query processing. The Pre-query trigger is triggered before the Pre-select trigger.

Q8. What exactly is DML?

Ans. Data Manipulation Language (DML) is a programming language that allows you to access and alter data in existing objects. Insert, select, update, and delete are DML statements that do not commit the current transaction.

Q9. What is the purpose of Oracle’s Aggregate functions?

Ans. The aggregate function is a function that joins the values of numerous rows or records to produce a single value output. The following are some examples of aggregate functions.

• Average
• Count
• Sum

Q10. In Oracle, what is the purpose of a control file?

Ans. For database recovery in Oracle, a control file is used. When an ORACLE database instance is started, the control file is also used to identify the database and redo log files that must be opened in order for database operations to proceed.

Q11. What are synonyms and how do you use them?

Ans. Synonym can be used to conceal an object’s true name and owner. It makes an object accessible to the whole public. It also provides location transparency for distant database tables, views, and program units. It makes SQL statements easier to understand for database users.

Q12. What are the benefits of using Save Points in an Oracle database?

Ans. Save Points are a way to break down a transaction into smaller chunks. It allows you to undo a portion of a transaction. Oracle Database allows for a maximum of 5 save points. It is possible to rollback from the point where the SAVEPOINT was saved whenever an error occurs.

Q13. what is difference between rename and alias?

Ans. Alias is a temporary name for a table or column, whereas Rename is a permanent name for a table or column. A rename is simply a name change, but an Alias is a different name for the table or column.

Q14. What are cursor attributes, and how do you use them?

Ans. In Oracle, each cursor contains a set of properties that allow an application program to test the cursor’s state. The properties can be used to determine whether the cursor is open or closed, whether it is found or not, and how many rows are present.

Q15. What exactly do you mean when you say “Redo Log file mirroring”?

Ans. The process of having a copy of Redo log files is known as mirroring. It’s done by putting together a collection of log files. This ensures that LGWR sends them to all members of the current on-line redo log group automatically. If a group fails, the database shifts to the next group automatically. It has a negative impact on performance.

Q16. What is the definition of an integrity constraint?

Ans. A business rule for a table column is defined by an integrity constraint, which is a declaration. Integrity constraints are used to verify that data in a database is accurate and consistent. Domain Integrity, Referential Integrity, and Domain Integrity are the three categories.

Q17. What are the different types of constraints in Oracle?

Ans. The following are the constraints that were applied:

• NULL – This symbol denotes that a column may contain NULL values.
• NOT NULL – This specifies that a column cannot have any NULL values.
• CHECK — Verify that the values in the supplied column fulfil the specified requirements.
• DEFAULT – This indicates that the value is set to the default value.

Q18. What are the distinctions between SUBSTR and INSTR?

Ans. INSTR returns the character position in which a pattern is found in a string, while SUBSTR returns a specified section of a string.

INSTR returns numeric, but SUBSTR returns string.

Q19. What’s the difference between List Item and LOV?

Ans. List items are regarded separate items, whereas LOV is a property. The list of things is configured to be a collection of lists of lists of lists of lists of lists of lists of lists of lists of lists of lists of lists of lists of lists A list item can only have one column, whereas LOV can have one or more.

Q20. What are recursive hints in Oracle and what do they mean?

Ans. Recursive hint refers to the amount of times a dictionary table is called again by various processes. Due to the short capacity of the data dictionary cache, recursive hint occurs.

Q21. What are the differences between privileges and grants?

Ans. Privileges are the permissions to run SQL statements – this includes the ability to connect and disconnect. Grants are assigned to objects so that they can be accessed appropriately. Grants can be given by the object’s owner or creator.

Q22. What is the DUAL table’s data type?

Ans. The DUAL table in the Oracle database is a one-column table. A single VARCHAR2(1) column named DUMMY has a value of ‘X’ in the table.

Q23. In Oracle, how can you find the current date and time?

Ans. Oracle’s SYSDATE() function is used to determine the current date and time of the database’s operating system.

Q25. What are the actual and formal parameters?

Ans. Actual parameters are the variables or expressions that are referenced in a subprogram’s parameter list.

Consider the following procedure call, which has two real-world parameters named empno and amt:

raise_sal(empno, amt);  

Variables defined in a subprogram specification and referenced in the subprogram body are known as formal parameters.

Two formal parameters, empid and amt, are declared in the following procedure:

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL; 

Q26. What are the CHECK constraint’s limitations?

Ans. The CHECK constraint’s principal limitation is that the condition must be a Boolean expression evaluated using the values in the inserted or changed row, and it cannot contain sub queries.

Q27. What does the GRANT option in the IMP command do?

Ans. GRANT is a program that allows you to import object grants.

Q28. In Oracle, how can I convert a date to a char? Give a specific example.

Ans. To convert a date to a character, use the to char() method. You have the option of specifying the output format.

SELECT to_char ( to_date (’12-12-2012′, ‘DD-MM-YYYY’) , ‘YYYY-MM-DD’) FROM dual;  

Q29. How can we see the most recent record added to a table?

Ans. The last record can be added to a table using the command –

SELECT * FROM (SELECT * FROM EMPLOYEES ORDER BY ROWNUM DESC) WHERE ROWNUM<2;

Q30. What extensions do Oracle reports use?

Ans. Oracle reports are used to give businesses with the ability to securely share information at any level within or outside the company. REP and RDF file extensions are used by Oracle reports.

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

Ans. The types of parameters that can be supplied to a process are

• IN
• OUT
• INOUT

Q32. Is there a limit to how many triggers can be assigned to a single table?

Ans. The number of triggers that can be applied to a single table is limited to 12.

Q33. What exactly is an ALERT?

Ans. An alert is a popup that pops up in the middle of the screen and covers a section of the current display.

Q34. What exactly is a hash cluster?

Ans. Hash Cluster is a table storage mechanism that allows for faster retrieval. To retrieve the rows from the table, apply a hash value to the table.

Q35. What are the various kinds of synonyms?

Ans. Synonyms or aliases can be divided into two categories:

Private in the sense that only the owner has access to it.

Public: Any database user has access to it.

Q36. In Oracle, what is the BLOB data type?

Ans. The BLOB data type is a binary string of changeable length. It has a capacity of two gigabytes of memory. The length of a BLOB data type must be given in bytes.

Q37. What is the best way to store photos in a database?

Ans. Yes, the Long Raw Data type can be used to store images in a database. This data type is used to hold binary data with a length of 2 gigabytes. The table, however, can only have one Long Raw data type.

Q38. What are Cursor’s attributes?

Ans. Cursor’s attributes are as follows:

%FOUND

• If the cursor is open and no fetch has been performed, %FOUND returns NULL.
• If the cursor fetch is successful, TRUE is returned.
• If no rows are returned, False is returned.

%NOT FOUND

• If the cursor is open and no fetch has been performed, NULL is returned.
• If fetch has been performed, this method returns False.
• Returns If no row was returned, this value is true.

%ISOPEN

• If the cursor is open, percent ISOPEN returns true.
• If the cursor is closed, this method returns false.

%ROWCOUNT

• The number of rows fetched is returned.
• To get an exact true count, it must be iterated through the full cursor.

Q39. What exactly is ROWID?

Ans. ROWID is a pseudo column that is associated to each table row. Blockno is 18 characters long Rownumber. The components of ROWID are filenumber.

Q40. What does $ORACLE BASE and $ORACLE HOME mean?

Ans. ORACLE HOME is located beneath the base folder, which contains all Oracle products. Oracle base is the main or root directory of an Oracle.

Q41. What exactly do you mean when you say GROUP BY Clause?

Ans. In a select statement, a GROUP BY clause can be used to collect data from several records and group the results by one or more columns.

Q42. What exactly is a cross join?

Ans. The Cartesian product of records from the tables in the join is known as a cross join. The consequence of a cross join is that each row from the first table is combined with each row from the second table.

Q43. In Oracle, what is the difference between a hot backup and a cold backup? Also, mention their advantages.

Ans. Online backup (hot backup): Because it is performed while the database is active, a hot backup is also known as an online backup. Some websites are unable to shut down their databases while performing backups since they are in use 24 hours a day, seven days a week.

A cold backup (also known as an offline backup) is performed when the database is shut down using the SHUTDOWN normal command. If a database is shut down unexpectedly due to an unknown condition, it should be restarted in RESTRICT mode and subsequently shut down with the NORMAL option.

The following files must be backed up for a complete cold backup.

• Init.ora file
• All datafiles
• All control files
• All online redo log files (optional)
• All datafiles (you can recreate it manually).

Q44. The Oracle shared pool has how many memory layers?

Ans. There are two tiers to Oracle shared pools:

• Data dictionary cache
• Library cache

Q45. In Oracle, what is a NULL value?

Ans. The NULL value denotes data that is either absent or unknown. This is used as a placeholder or portrayed as a default entry to indicate that no data is available.

Q46. How can we get rid of duplicate rows from a table?

Ans. ROWID can be used to eliminate duplicate rows in a table.

Q47. What exactly are the various Oracle Database objects?

Ans. Oracle has a number of different data items.

• Tables are a collection of items that are arranged vertically and horizontally.
• Views are a type of virtual table that is made up of one or more tables.
• Indexes are a performance tweaking approach for records processing.
• Tables’ alias name is Synonyms.
• Sequences – Each user generates a different number.
• Oracle tablespaces are logical storage units.

Q48. How can I show row numbers beside the records?

Ans. The row numbers and field values from the specified table will be displayed by this query.

SELECT ROWNUM, <FIELDNAMES> FROM TABLE;

Q49. What exactly is a VIEW?

Ans. A logical table based on one or more tables or views is called a view. Base Tables are the tables that the view is built on, and they don’t contain any data.

Q50. What exactly is the COALESCE function?

Ans. The COALESCE function is used to return the value in the list that is set to not be null. The coalesce function will return NULL if all of the values in the list are null.

COALESCE(VALUE1, VALUE2,VALUE3,…)