GeekInterview.com
Series: Subject: Topic:

Oracle Interview Questions

Showing Questions 21 - 40 of 89 Questions
First | Prev | | Next | Last Page
Sort by: 
 | 

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: priyank on: May 10th, 2012

Use Case in update statement

Answered by: mksakeesh on: Mar 6th, 2012

window function

Code
  1. SELECT* FROM (SELECT rank() over(ORDER BY price DESC) odrd,products.* FROM products) WHERE odrd =5;

Large SQL query performance check

Asked By: VNeha | Asked On: Apr 11th, 2012

You have a very large query say of 2000 lines. How would you check if the indexes are being used or not. Would you read all the lines of explain plan in plan table to get it? If yes, will it be feasible? Do you have any other method of checking this?

Answered by: raghuvir Khanna on: May 10th, 2012

We can moniter the use of indexed with the help of view V$object_usage simple we need to use
Alter index monitoring usage;
select tablename,indexname ,used from V$object_usage ;

Answered by: Sam on: May 7th, 2012

Check and resolve join with the largest cost

PL/SQL autonomous transaction

Asked By: tbala_soft | Asked On: Aug 29th, 2009

If in PL/SQL autonomous transaction, you get error then how will you role back the main transaction?

Answered by: Mohammad Jarwan on: Apr 9th, 2012

Used in table trigger to execute update statement for same table.

Answered by: dhiren21 on: Sep 20th, 2009

Before this Ask a Question to your Self... What are you Updating?When You Updated this?How you are updating?Then  Check the Commit Point if commited not possible to rollback normally.Steps :::  Restor...

Dynamic lov

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

How to create lov dynamically at runtime & attach to text field?

Answered by: SHAHNAWAZ MUZAFFAR on: Mar 10th, 2012

We can use Dynamic LOV through assigning into a global variable and using this global variable into our sql statement.

Find total number of databases

Asked By: prakashkartan | Asked On: Feb 24th, 2011

How will you find the total number of databases present in our system via LINUX os?

Answered by: Richard Naveen on: Oct 31st, 2011

etc/oratab

Answered by: subrahmanyam pattapu on: Jul 20th, 2011

we can find which databases are running in server. by using the following command.

ps-ef|grep smon

It will show the databases is up and running.

Repair tnsnames.Ora file

Asked By: dell1979 | Asked On: Jun 24th, 2008

How to repair the tnsnames.Ora file. file is completely corrupted and you get network adapter errors and tns resolve error after restarting the machine.

Answered by: dbxplorer on: Jul 21st, 2011

Delete or rename the older file and try to recreate it using "netmgr" using proper information of your database.

PriVATe,public database link

Asked By: annu.aa | Asked On: Oct 29th, 2010

How to give priVATe and public database links ?What is syntax for that ?What means mutation in Oracle??

Answered by: dbxplorer on: Jul 21st, 2011

Database link is the pointer that allow users to access other user's object in remote database. This is one way communication path using database links. Private db links: CREATE DATABASE LINK link_na...

What is the advantage of specifying with grant option in the grant command?

Asked By: samarendra161 | Asked On: Jul 2nd, 2010

Answered by: vinoth on: Jul 15th, 2011

giving privilege to the user

Answered by: sreekanth.g on: Jun 22nd, 2011

assume that table 'emp' is created by user scott.
by using grant command he can grant privileges to other users.
If he want to provide a privileges to grant the privileges to other users
he has to grant a privileges with grant option.

Rac database

Asked By: ap_mg | Asked On: Aug 21st, 2009

What difference does it make to a Oracle developer while using rac ? Would/should he realise that it is a rac database ?

Answered by: suji on: Mar 21st, 2011

It makes no difference for the developer and RAC technology is server side and affects the way DBA do they day to day job. Thanks, Suji

What are the advantages and disadvantages, compared to the standard SQL and SQL*plus ?

Asked By: Sushanta K. Mishra | Asked On: Feb 24th, 2006

Answered by: mrrichardson on: Jul 11th, 2010

Structured Query LanguageSQL- Is a languagekeywords cannot be abbreviatedSQL *PLUS - More complex than SQLIs a command line tool propietary to Oracle where you can run both SQL and PL/SQL stateme...

Answered by: Jeremy on: Aug 24th, 2007

SQL*Plus is a client program and interactive interpreter designed for interacting with Oracle databases. SQL is a standard data access language used to describe data access to a variety of databases. ...

Parent table

Asked By: svp.kiran | Asked On: Sep 28th, 2008

A table which consist of atleast one primary or unique key is called as parent table. true / false (justify)

Answered by: psingla on: Apr 20th, 2010

Parent tables: are tables that are referenced(foreign key) by at least one table(child).

As foreign key can reference only primary or unique key,
so the only tables containing any of these keys can be parent keys.

Answered by: taraldesai on: Feb 23rd, 2009

Wrong and Right

Depends how you take it. If i have emp table without primary key and i search dept table through emp id. Then this would be wrong. So, depends how you take it all i say is it's not verdict but it's an concept

Identify dml error

Asked By: svp.kiran | Asked On: Sep 28th, 2008

Merge into sas f using (select prodid, prodid+10 f, descrip from product) l on (f.Prodid=l.Prodid) when matched then update set f.Prodid=l.F when not matched then insert values (l.F, l.Descrip)why does the above give error?

Answered by: debjit20 on: Apr 5th, 2010

The Error will be:

Columns used in ON condition cannot be used in UPDATE.
You cannot update the same column which you are using in the ON clause.

Answered by: Lakshmi84 on: Aug 12th, 2009

I think Error lies in the following statement

INSERT VALUES (L.F, L.DESCRIP) 

THE STATEMENT SHOULD BE LIKE THIS

  INSERT (F.PRODID,F.DESCRIP)
VALUES(L.F,L.DESCRIP)

Oracle stand alone procedure

Asked By: saineeru | Asked On: Jan 19th, 2009

How does a stand alone procedures differ from that of other procedures?

Answered by: ayansmile07 on: Mar 23rd, 2010

These are the 4 differences between a Stand Alone Procedure and a Packaged ProcedureCreation - A standalone procedure is created using the CREATE PROCEDURE statement. A packaged procedure is created a...

Answered by: macca on: Mar 4th, 2009

Stand Alone Procedure is not part of an Package.

How to check intact, state of a particular index, what are the impacts of invalid index? How to recover it?

Asked By: reddy | Asked On: Apr 7th, 2006

Answered by: aStewart on: Feb 23rd, 2010

The index status can be found in the dba_indexes, dba_objects, or the equivalent  user_ view. The possibilities are VALID, INVALID, UNUSABLE, or N/A.  Invalid or unusable indexes will not be...

Answered by: Asim Jamal on: Apr 13th, 2006

The current status and details of an index could be accessed by

system table sys.dba_indexes. The status could be VALID,INVALID,N/A.

The invalid indexes unnecessarily occupies the space in a Tablespace.

Multi row sub-queries

Asked By: svp.kiran | Asked On: Sep 28th, 2008

Query 1. select sal from emp e where sal < any (select sal from empwhere empno=e.Mgr) (o/p:11 row) query 2. select sal from emp e where sal < any (select sal from empwhere mgr=e.Empno) (o/p:1 row) what is the difference between the above 2 queries.

Answered by: sen_sam86 on: Aug 27th, 2009

how this query is possible i dont know, how the emp number and manager number will be same, i think the query in meaning less r am not understanding properly i dont know,,,, Let some one explain this...!

Answered by: todeepakmalhotra on: Sep 30th, 2008

These both are correlated subqueries.

In the first one, we are knowing the salaries of employees who are earning less than there manager.


In the second one we are knowing about the salaries of managers who are earning less than their employees whom they manage.

Query performance

Asked By: kphvrl | Asked On: Jan 8th, 2009

Which one gives better performance, co-related subquery or inline views? Why?

Answered by: Lakshmi84 on: Aug 12th, 2009

Inline views works better than correlated subqueries in 8i.

 But things have changed in 9i.  This is because Oracle has changed their logic while processing a correlated  subquery.

What are the date, character, converstion functions in Oracle?

Asked By: razaa | Asked On: Jul 23rd, 2007

Answered by: Waseem Mehmood on: Aug 11th, 2009

There are three types of conversions 1-  TO_DATE2-  TO_CHAR3-  TO_NUMBERTO_DATE:use to convert charector into data by giving format mask.TO_CHAR:use to convert number into cha...

Different values for system.Message_level

Asked By: kalyankmohanta | Asked On: Feb 13th, 2008

What are the different values for :system.Message_level and its impact?

Answered by: Waseem Mehmood on: Aug 11th, 2009

There are 25 Levels of Message in an Oracle Forms.How much cretical error or message is0 -- all errors or message12...25 -- don't shows anyIf we don't wannat show any error  or message to...

What is your server configration ?What is your sga size ?What is shared pool size ?How much size have you configured for Oracle_block ?

Asked By: Sanjeev | Asked On: Dec 12th, 2005

Answered by: parveshdata on: Jun 26th, 2009

Use show sga to know all the important parameters that are there for you by default setting of oracle or may be DBA has set at the time of installing the database in your init.ora or sp file. Many of ...

Answered by: orafighter on: Sep 6th, 2007

Hello
sql>show sga , then it shows these parameters
---- Total system global area
---- Fixed size
----Variable size
----Database buffers
----Redo buffers 

or can query from select * from v$sga

Identifies GLobally

Asked By: todeepakmalhotra | Asked On: Sep 30th, 2008

While creating a new user we set his password as identified GLobally, what does it mean?

Answered by: Paramjeet Singh on: Dec 12th, 2008

We dont set user's password as "identified globally". We write "identified globally" to decide user's scope.Details:In Oracle, we can create user in three ways:1. local user2. external user3. Global u...

First | Prev | | Next | Last Page

 

 

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

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

Ads

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.