Series: Subject: Topic:

Oracle Interview Questions

Sub Categories (+ View)
Showing Questions 1 - 20 of 203 Questions
First | Prev | | Next | Last Page
Sort by: 

Why we use materialized view ?

Asked By: Rakesh057 | Asked On: Jul 9th, 2014

If it reduce the traffic then please give an example for that one

Answered by: Pradeep on: Sep 23rd, 2014

Materialized view is mainly used to improve the performance. When you call the normal view, It will run the select query and then fetch the results returned by select query. This is time consuming and...

Which one is faster delete/truncate? Why?

Asked By: SaratKumar | Asked On: Oct 10th, 2005

Answered by: SQL Guy on: Aug 21st, 2014

This is crazy .. the original question was posted in 2005 and people are still answering through 2014 with the same answers, come on guys whats wrong with you.

By the way all the ones saying that you cant roll back a truncate you are wrong. It can be rolled back without a problem.

SQL guy

Answered by: Bharath on: Feb 18th, 2014

Truncate will be faster than delete. Because truncate wont make any backup of deleted rows where in delete it will take backup of deleted records for purpose of rollback. In other words Truncate will...

Write the query

Asked By: Koonal saxena | Asked On: Dec 20th, 2012

A employee getting variable salary like jan 3000,feb 5000,mar 1000 ,apr 7000,may 500 write query to display his next salary "increse" or "decrese" as compare to previous month ,three column in the employee table empname ,salarydate,salary ?

Answered by: bharahi on: Aug 5th, 2014

It can be done by analytical function using the lead function.

Answered by: tinku981 on: Jun 14th, 2013

This can be done by analytical function using the LEAD function.

What is the difference between ref cursor & normal cursor?

Asked By: SaratKumar | Asked On: Oct 10th, 2005

Answered by: Annaiah on: Jun 27th, 2014

Ref cursor is typically used to return a record set or cursor from a stored procedure.ref cursor is basically data type ref cursor can be changed dynamically at run time normal cursor is static cursor...

Answered by: rammohan on: Apr 8th, 2013

REF cursor is a dynamic cursor where as normal cursor is static cursor,In dynamic cursor single segment are process multiple SELECT statements dynamically at run time, where as in normal cursor we process only one select statement

What is materialised view?

Asked By: rekha | Asked On: Nov 17th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: Renu

Answered On : Jan 26th, 2007

Materialized View stores both - definition of the view and rows resulted from execution of the viewMain disadvantage of materialized view is that, its contents get outdated when underlying base table is modified .U can refresh Materialized View using DBMS_MVIEW.REFRESH

Answered by: thiru on: Jun 25th, 2014

Its a one of the oracle object,its physical storage on the disk then (dbms_refresh)

Answered by: shanitha on: Sep 11th, 2012

Materialized view is a database object that contains the results of a query..

How the bitmap indexes can be useful for fact tables ?

Asked By: Ram | Asked On: Jul 20th, 2007

Star Read Best Answer

Editorial / Best Answer

Answered by: thiru

Answered On : Jun 25th, 2014

Using for low cardinality purpose

Answered by: thiru on: Jun 25th, 2014

Using for low cardinality purpose

What is the difference between create index and create unique index.?

Asked By: | Asked On: May 5th, 2006

Answered by: JP on: May 25th, 2014

When create primary constraints oracle automatically creates Unique Index .
Using "Create index" by default oracle will create B tree index .
To create other indexes like Bit map,IOT index you need mention the Index name while creating index
ex "create Bitmap index "

Answered by: Sant_parkash on: Jan 31st, 2008

Index will only help you in searching the data but unique index will provide uniqueness to the indexed column. But in both the cases NULL values are allowed to insert.

What is difference between tsnnames.Ora & SQLnet.Ora & listener.Ora

Asked By: hasanvtu | Asked On: Oct 20th, 2006

Answered by: Jp on: May 25th, 2014

Lister is Network Service to establish connection between client machine and server .

Lister are stored at server side .Each lister listen to client request and establish connection.

Answered by: Innila on: Dec 4th, 2006

Oracle uses all three files (tnsnames.ora, sqlnet.ora, listener.ora) for network configuration.tnsnames.ora A tnsnames.ora file maps net service names to connect descriptors. In simple words, tnsnames...

Transactions during hot backup

Asked By: satish220 | Asked On: Mar 16th, 2009

Where will the transactions be stored during hot backup? during hot backup we specify the tablespace during beginning backup mode and now if a user perform some transactions then where such transactions are stored?

Answered by: pavanintouch on: May 20th, 2014


check this

Answered by: abdul_dy on: Jun 15th, 2012

In HOT backup mode users transaction/data is goes in their respective default tablespaces only since the datafile header will not update til the time you end the backup mode

Write a query to display all the odd numbers from a table

Asked By: Sudhir | Asked On: Jun 21st, 2006

Answered by: AMRUT SWAGAT DWIVEDY on: Apr 18th, 2014


  1. TRUNCATE table table_name;

Answered by: Rakesh Anand on: Aug 24th, 2013

  2. WHERE MOD (RN, 2) <> 0;

What is different between Oracle 9i and 10g and 10g and 11g ?

Asked By: Rpradeep | Asked On: Nov 30th, 2013

Answered by: Amrut Swagat Dwivedy on: Apr 18th, 2014

These are different types of upgraded tools

What is the difference between trim and truncate functions in Oracle

Asked By: Josna | Asked On: Aug 13th, 2006

Answered by: Deepika S Verma on: Mar 14th, 2014

TRUNC applies to numeric and Dates values Syntax- ------- TRUNC( number, [ decimal_places ] ) TRUNC ( date, [ format ] ) Example - --------- TRUNC(125.815) would retur...

Answered by: shiiva on: Aug 6th, 2013

We cannot compare Truncate & Trim. Truncate is a DDL command which deletes the content of a table completely, without effecting the table structure.

Trim is a function which alters the output of one of the column output of a select query.

Difference between varray and nested table

Asked By: Kanhucharan | Asked On: Apr 10th, 2008

What is the difference between varray and nested table?

Answered by: Vishnu Bharath on: Jan 8th, 2014

Varrays are generally dense , that mean you cannot delete elements of the varray.But the elements of the Nested Table are generally sparse in nature and hence the elements can be deleted in between us...

Answered by: Sheraz Baig on: Dec 3rd, 2013

Both are type of collections in Oracle. In Varray because its not stored in database columns so no DML operation can be implemented but on other hand as Nested tables we can perform DML operations

What do you mean by conceptual?

Asked By: sprajarajan | Asked On: Mar 22nd, 2008

How its helps? Why we go for conceptual model?

Answered by: vivekanand on: Dec 9th, 2013

Conceptual modeling is the very first level of data modeling once gathering the Business requirements. It tells about only the Entity and attributes , not the relations(constraints) or physical tables...

Answered by: Sheraz Baig on: Dec 3rd, 2013

When we talk about Conceptual Model , so we definitely mean the High Level of information. Unlike Physical Model, Conceptual model doesn't discuss the data, but it discuss the process. as we told its ...

No_data_found or too_many_row

Asked By: jkverma29 | Asked On: Aug 19th, 2008

In PL/SQL if we write select statement with into clause it may return two exceptions no_data_found or too_many_row .To avoid these execeptions. How do you write SQL statement in alternative way?

Answered by: Ananda Kumar Behera on: Nov 12th, 2013

To avoid use:

  1. DECLARE a variable
  2. SELECT COUNT(*) INTO num WHERE <Condition OF your data retreival FOR SELECT INTO statement>;
  3. IF num==1 THEN
  4. EXECUTE your SELECT INTO statement
  5. END IF

Answered by: tinku981 on: Jun 16th, 2013

We can use explicit cursor to avoid Oracle exception - NO_DATA_FOUND and TOO_MANY_ROWS.

SQL query to find nth maximum of nth row?

Asked By: DEBPROSAD BANERJEE | Asked On: Jul 14th, 2006

1. Queries for nth maximum nth row?2. In one table there are 5 fields. Empno, ename, deptcd, managr_id, salary.Select the departments whose sum of the salary greater than the sum of salaries of any department?3. When index will be usd in the query?

Answered by: mahesh on: Aug 12th, 2013

  2. over(ORDER BY price DESC)
  3. odrd,products.* FROM products)
  4. WHERE odrd =5;

Answered by: priyank on: May 10th, 2012

Use Case in update statement

When we give select * from emp; how does Oracle respond:

Asked By: SaratKumar | Asked On: Oct 10th, 2005

Answered by: Rahul on: Jul 11th, 2013

It retrieve or fetch all data from Emp table,and show all data of table like Emp as result.

Answered by: on: Dec 13th, 2012

First it will check the syntax and semantics in library cache, after that it will create execution plan. Already data in the buffer cache it will directly return to the client. If not it write the fetched to the dB buffer cache after that it will send server. Server sends to the client.

Compute sum

Asked By: rudhra97 | Asked On: Jun 9th, 2008

In order to 'compute sum' clause in the SQL reports which of the following is mandatory1) break on 2) order by 3) set page size 4) set feedback

Answered by: tinku981 on: Jun 13th, 2013

Break and compute are required, but for spooling output you should be using all the another 3 functions.

Answered by: Gaurav Saxena on: Apr 24th, 2013

Break on is compulsory for compute function.

Open cursors

Asked By: ranjith12 | Asked On: Apr 11th, 2009

If you have an open cursor on a table, modify the same table and commit, what would happen?

Answered by: tinku981 on: Jun 14th, 2013

There will not be any impact on the output even if we will insert (and commit) the data after cursor is opened. The output will be same as of the record set when cursor was opened and new row will not be selected in that operations.

Answered by: tinku981 on: Jun 14th, 2013

There will not be any impact on the output even if we will insert (and commit) the data after cursor is opened. The output will be same as of the record set when cursor was opened and new row will not...

Passing a function name in parameter

Asked By: srividhya_85 | Asked On: Jan 7th, 2013

Can we pass a function name as a parameter value?? Will it have any effect in performance degradation when used in a large online transaction process?

Answered by: tinku981 on: Jun 14th, 2013

About performance, it depends upon the computations you will be performing inside that function. Also, if we are using (user-defined) function in SQL (rather than creating PL/SQL), then it will give better performance.

While using user-defined functions, we need to avoid Mutating functions error.

Answered by: murugesan on: Jan 19th, 2013

Yes we can pass but the function should return a value of type appropriate for the calling function



First | Prev | | Next | Last Page





twitter fb Linkedin GPlus RSS


Question Categories

Oracle Backup and Recovery Interview Questions

Oracle Basics Interview Questions

Oracle Architecture Interview Questions

D2K Interview Questions

Data Access Interview Questions

Oracle DBA Interview Questions

Oracle security Interview Questions

Database Tuning Questions

Oracle Distributed Processing Interview Questions

Oracle Concepts Interview Questions

Oracle Memory Management Interview Questions

Oracle Forms Interview Questions

PL/SQL Interview Questions

Programmatic Constructs Interview Questions

RMAN Interview Questions

Real time Oracle Interview Questions

Oracle SQL Interview Questions

SQL*Plus interview Questions

Oracle System Architecture Interview Questions

Interview Question

 Ask Interview Question?


Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.