Oracle Database Interview Questions & Answers Set-10

«« Previous
Next »»

1. How can we delete duplicate rows in a table?
Ans: Duplicate rows in the table can be deleted by using ROWID.

2. What are the attributes of Cursor?
Ans: Attributes of Cursor are

Returns NULL if cursor is open and fetch has not been executed
Returns TRUE if the fetch of cursor is executed successfully.
Returns False if no rows are returned.
Returns NULL if cursor is open and fetch has not been executed
Returns False if fetch has been executed
Returns True if no row was returned
Returns true if the cursor is open
Returns false if the cursor is closed
Returns the number of rows fetched. It has to be iterated through entire cursor to give exact real count.

3. Can we store pictures in the database and if so, how it can be done?
Ans: Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have only on Long Raw data type.

4. What is an integrity constraint?
Ans: An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity and Domain Integrity.

5. What is an ALERT?
Ans: An alert is a window which appears in the center of the screen overlaying a portion of the current display.

6. What is hash cluster?
Ans: Hash Cluster is a technique used to store the table for faster retrieval. Apply hash value on the table to retrieve the rows from the table.

7. What are the various constraints used in Oracle?
Ans: Following are constraints used:

  • NULL – It is to indicate that particular column can contain NULL values
  • NOT NULL – It is to indicate that particular column cannot contain NULL values
  • CHECK – Validate that values in the given column to meet the specific criteria
  • DEFAULT – It is to indicate the value is assigned to default value

8. What is difference between SUBSTR and INSTR?
Ans: SUBSTR returns specific portion of a string and INSTR provides character position in which a pattern is found in a string.

SUBSTR returns string whereas INSTR returns numeric.

9. What is the parameter mode that can be passed to a procedure?
Ans: IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.

10. What are the different Oracle Database objects?
Ans: There are different data objects in Oracle –

  • Tables – set of elements organized in vertical and horizontal
  • Views  – Virtual table derived from one or more tables
  • Indexes – Performance tuning method for processing the records
  • Synonyms – Alias name for tables
  • Sequences – Multiple users generate unique numbers
  • Tablespaces – Logical storage unit in Oracle

11. What is written in Redo Log Files?
Ans: Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file. Redo log files that are required for instance recovery are called active redo log files.

12. How do you control number of Datafiles one can have in an Oracle database?
Ans: When starting an Oracle Database instance, the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This limit applies for the life of the instance.

13. What is a Tablespace?
Ans: A tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data, and serves to allocate storage for all DBMS managed segments.

14. What is the purpose of Redo Log files?
Ans: Whenever something changes in a datafile, Oracle records the change in the redo log. The name redo log indicates its purpose: If the database crashes, the RDBMS can redo (re-process) all changes on datafiles which will take the database data back to the state it was when the last redo record was written.

15. What is a Checkpoint?
Ans: A checkpoint is the writing by the DBWR (database writer) process of all modified buffers in the SGA buffer cache to the database data files. Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks, as well as the control files.

«« Previous
Next »»