Oracle Interview Questions

Use Case in update statement

window function

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

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 ;

Check and resolve join with the largest cost

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

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...

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

etc/oratab

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.

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

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...

giving privilege to the user

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.

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

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...

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 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.

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

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.

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)

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...

Stand Alone Procedure is not part of an Package.

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...

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.

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...!

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.

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.

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...

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...

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 ...

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

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...