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?

debjit20

 Profile Answers by debjit20 Questions by debjit20

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

Lakshmi84

 Profile Answers by Lakshmi84 Questions by Lakshmi84

• 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)

Answer Question  Select Best Answer

A table which consist of atleast one primary or Unique key is called as Parent Table.
True / False (Justify)

psingla

 Profile Answers by psingla Questions by psingla

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

taraldesai

 Profile Answers by taraldesai Questions by taraldesai

• Feb 23rd, 2009

Wrong and RightDepends 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

Answer Question  Select Best Answer

Explain what are system tablespace and default tablespace? How do they differ from one another?

taraldesai

 Profile Answers by taraldesai Questions by taraldesai

• Mar 4th, 2009

System for oracle oracle metadata and default for when you create any user without providing any default tablespace clause will use this tablespace for objects

Punithavel

 Profile Answers by Punithavel Questions by Punithavel

• Mar 3rd, 2009

System Tablespace is the default tablespace for Oracle Database where it will store all the database dictionaries and DB related TablesWhereas Default tablespace is the tablespace created for each user.

Answer Question  Select Best Answer

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

ayansmile07

 Profile Answers by ayansmile07 Questions by ayansmile07

• Mar 24th, 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...

macca

 Profile Answers by macca Questions by macca

• Mar 5th, 2009

Stand Alone Procedure is not part of an Package.

Answer Question  Select Best Answer

How to find out how much space a tablespace has remaining?

aravindhreturns

 Profile Answers by aravindhreturns Questions by aravindhreturns

• Apr 22nd, 2010

The total size of the tablespace can be determined through DBA_DATA_FILES. Likewise, free size of the tablespace can be determined through DBA_FREE_SPACE. So you can simply do this, Subract you can si...

gopal_1980

 Profile Answers by gopal_1980 Questions by gopal_1980

• Apr 6th, 2010

We can get tablespace free space from DBA_FREE_SPACE

Answer Question  Select Best Answer

How to find the startup time of instance?

kishore

• Nov 15th, 2011

How to get instance startup time


>select to_char(startup_time,'dd-mon-yyyy HH12:MI:SS ')"db startup time"
2 from sys.v$instance;

aravindhreturns

 Profile Answers by aravindhreturns Questions by aravindhreturns

• Apr 22nd, 2010

Use this simple command in SQLPlus or an a query retreival tool of your choice. select To_char(startup_time, 'Mon-dd-yyyy hh24:mi AM') as STARTUP_TIME from v$instance;

Answer Question  Select Best Answer

In which file we can see start up information?

aravindhreturns

 Profile Answers by aravindhreturns Questions by aravindhreturns

• Apr 22nd, 2010

I will give you three options as im unsure of what your requirement is:-If you're talking about a flatfile which contains the startup information, Well it is obviously the alert logfile. It can be fou...

gopal_1980

 Profile Answers by gopal_1980 Questions by gopal_1980

• Apr 6th, 2010

Alert log file

Answer Question  Select Best Answer

You have a batch job which has hundreds of SQL queries in it. Earlier is was finishing in expected time. However, now it is taking twice the time. There is no CPU utilization issue due to other jobs. How would you know that which of the queries are taking more time now and why?

Nagaraj Teli

• Jul 22nd, 2012

Please find the below methods for finding one of the query which is taking longer time in the batch script. Methods :- 1) Put the dbms_output after each of the SQL query. 2) Create a logfile using th...

koushikinath

 Profile Answers by koushikinath Questions by koushikinath

• May 24th, 2012

1. At first enable the trace, at the beginning of the sqls.
2. Then check the trace file.
3. Using tkprof create the output file.
4. According to explain plan check the elapsed time for each query.
5. Then tune them

Answer Question  Select Best Answer

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?

tinku981

 Profile Answers by tinku981 Questions by tinku981

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

murugesan

• Jan 19th, 2013

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

Example

round(trunc(100))

Answer Question  Select Best Answer

What is the use of IGNORE option in the Export and Import?

viswanath

• Jul 30th, 2015

When you put IGNORE=Y it is simply ignore the previous backup. That means, while during backup where you can stop and add another backup or file. It store the that adding file only, not whole backup.

Balakumar

• Jul 17th, 2015

For getting input and output

Answer Question  Select Best Answer

How does CURSOR and BULK COLLECT work logically?

SATHISH S

• Jul 28th, 2017

Cursor is storage area which stores the queried results whereas bulk collect will get bulk amount of data from cursor and insert into some cursor variables.

anil

• Apr 26th, 2017

Cursor is record by record process coming to bulk collect clause select column data at a time

Answer Question  Select Best Answer

jpundalik

 Profile Answers by jpundalik Questions by jpundalik

• Dec 26th, 2009

Components of SGA- Shared Pool (Library Cache and Data Dictionary Cache)- Data Base Buffer- Redo Log Buffer

pchandna

 Profile Answers by pchandna Questions by pchandna

• Jul 22nd, 2009

DB buffer cache, Redo Log cache and Shared PoolShared Pool --> data dictionary cache, Library cache and User Global area

Answer Question  Select Best Answer

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?

Divakar

• Oct 31st, 2014

During hot backup, once we issue the Begin Backup command respective datafile header will be freezed, if users performs transaction it will be stored in the datafiles but the SCN # will not updated as...

pavanintouch

 Profile Answers by pavanintouch Questions by pavanintouch

• May 20th, 2014

Http://orcl11gdba.blogspot.in/2014/03/taking-hot-bakcup-database-in-begin.html

check this

Answer Question  Select Best Answer

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

Mohammad Jarwan

• Apr 9th, 2012

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

dhiren21

 Profile Answers by dhiren21 Questions by dhiren21

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

Answer Question  Select Best Answer

roniprasan

 Profile Answers by roniprasan Questions by roniprasan

• Sep 2nd, 2007

Raise_application_error(error number,error discription)

aseemnaithani

 Profile Answers by aseemnaithani Questions by aseemnaithani

• May 3rd, 2006

Hi,,it is Raise_Application_error

Answer Question  Select Best Answer

mani

• Mar 6th, 2007

Connect as sysdba and typeselect * from v$sgainfo;

rajatm2

 Profile Answers by rajatm2 Questions by rajatm2

• Dec 1st, 2006

connect sqlplus using sys or system or any user .and type show sgaregardsRajat

Answer Question  Select Best Answer

sumer77

 Profile Answers by sumer77 Questions by sumer77

• Mar 22nd, 2009

Sum of bytes from dba_extents will provide the database size.      

Sridhar.K

• Oct 15th, 2007

COLUMN "Total Mb" FORMAT 999,999,999.0COLUMN "Redo Mb" FORMAT 999,999,999.0COLUMN "Temp Mb" FORMAT 999,999,999.0COLUMN "Data Mb" FORMAT 999,999,999.0PromptPromp...

Answer Question  Select Best Answer

Innila

 Profile Answers by Innila Questions by Innila

• Mar 22nd, 2007

Hi,Lookup tables are like master tables which are static & will be referred by other Base tables for reference.Lookup tables are basically used for referring a predefined data which are stored in these tables.*** Innila ***

Red

• Mar 11th, 2007

A look up table is a table whose values are seldom or never updated. They are basically used for references. A good example of a look-up table is a 'country's' table.

Answer Question  Select Best Answer

bhanuchander333

 Profile Answers by bhanuchander333 Questions by bhanuchander333

• Jan 5th, 2008

RAISE statement is used to raise exception explicitly with in the EXECUTABLE sectionwhere: Exception is previously declared in declaration section as 'exception'.

ramaswamy80in

 Profile Answers by ramaswamy80in Questions by ramaswamy80in

• Sep 26th, 2007

No   you  use 'raise' to raise  exceptions   and  we  raise exceprions in Executable sectionso we are most likely to see the ' raise ' in   Eecutable sectoin

Answer Question  Select Best Answer

What is the difference between Varray And Nested Table?

Vishnu Bharath

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

Sheraz Baig

• 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

Answer Question  Select Best Answer