Oracle Database Interview Questions & Answers Set-5

1. What is an Oracle index? 
Ans: An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table

2. What are the different index configurations a table can have?
  • A table can have one of the following index configurations 
  • No indexes 
  • A clustered index 
  • A clustered index and many nonclustered indexes 
  • A nonclustered index 
  • Many nonclustered indexes.

3. What is difference between UNIQUE constraint and PRIMARY KEY constraint? 
Ans: A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls. A table can have only one primary keys.

4. What is BCP? When does it used?
Ans: BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

5. How to know which index a table is using?
Ans: SELECT table_name,index_name FROM user constraints.

6. What is SYSTEM tables pace and when is it created?
Ans: Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

7. Which date function returns number value? Which date function returns number value? 
Ans: months_between

8. What is integrity constrains? 
Ans: Integrity constraints are used to ensure accuracy and consistency of data in a relational database.

9. What is the difference between Explicit and Implicit Cursors?
Ans: An Implicit cursor is one created "automatically' for you by Oracle when you execute a query. It is simpler to code An Explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others DBA_DATA_FILES.

10. What are primary keys? 
Ans: Primary key are used to uniquely identify each row of the table. A table can have only one primary key.

11. How to convert a date to char in Oracle? Give one example.
Ans: The to_char() function is used to convert date to character. You can also specify the format in which you want output.

SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;

SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;

12. What are the extensions used by Oracle reports?
Ans: Oracle reports are use to make business enable with the facility to provide information of all level within or outside in a secure way. Oracle report uses REP files and RDF file extensions.

13. How to convert a string to a date in Oracle database?
Ans: Syntax: to_date (string , format)

Let us take an example :

to_date ('2012-12-12', 'YYYY/MM/DD')
It will return December 12, 2012.

14. How do you find current date and time in Oracle?
Ans: The SYSDATE() function is used in Oracle to find the current date and time of operating system on which the database is running.


15. What will be the syntax to find current date and time in format "YYYY-MM-DD"?