What do you mean by ROW_NUMBER(), RANK(), DENSE_RANK() differentiate them?

asit

• Jun 9th, 2012

THE BELOW FUNCTIONS ARE OLAP FUNCTIONS...

rank() function skips the continuity but dense_rank() function gives the sequence.It is always advised to use dense_rank for viewing nth highest/lowest value. row_num() gives the serial no in the order how they are inserted in to a table.

Answer Question  Select Best Answer

What is log switch?

asit

• Jun 9th, 2012

log switch ::suppose we have two redo log files LOG A, LOG B. when transaction(DML) are occurred in Database then they are recorded in LOG A. When LOG A is filled up, a log switch occurs, All new tran...

Answer Question  Select Best Answer

What is an exception in Oracle?

asit

• Jun 9th, 2012

Exceptions are nothing but the errors occurred at the run time. To handle the exception we use exception block in the PLSQL.

Answer Question  Select Best Answer

What is an Archiver in Oracle?

venkatesh

• Jun 8th, 2012

Its background processes in oracle, it will write offline redolog group information into archive log files

Answer Question  Select Best Answer

I developed like below.Any experts query ....please send.selecttable_name,constraint_namefrom user_cons_columns where column_name=(select a.COLUMN_NAME from user_cons_columns a,user_constraints bwhere a.TABLE_NAME=b.TABLE_NAME and b.constraint_type in ('P')and a.TABLE_NAME='DEPT')and constraint_name like '%FK%'

asheesh

• Jun 2nd, 2012

select table_name from USER_CONSTRAINTS where r_constraint_name= (select CONSTRAINT_NAME from USER_CONSTRAINTS where table_name=INPUT_PARENT_TABLE and CONSTRAINT_TYPE=P) and CONSTRAINT_TYPE=R;

sen_sam86

 Profile Answers by sen_sam86 Questions by sen_sam86

• Aug 18th, 2009

Try this script, Example SELECT child.table_name AS "TABL_NAME_2", 'Is Parent Of' AS ...

Answer Question  Select Best Answer

Transaction is unit of work done. So a database management system will have number of transactions. There may be situations when two or more transactions are put into wait state simultaneously .In this position each would be waiting for the other transaction to get released. Suppose we have two transactions one and two both executing simultaneously. In transaction numbered one we update student table...

Chittaranjan Kotian

• May 16th, 2012

Two Oracle Sessions attempting to obtain a lock on a rows that is being locked by each other. For example Session A has locked a row for update in a transaction. Session B is trying to obtain a lock o...

rohit

• Apr 22nd, 2012

It is absolutely wrong.you can read data simultaneously but you cant write data ......you can write data one at a time only...

Answer Question  Select Best Answer

A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common.

ravinder

• May 4th, 2012

Clustered table store data of multiple table based on common columns.

View is virtual table or which has attached select query.Its not actual table.

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Feb 13th, 2012

A cluster is a group of tables which share the same data blocks. Clusters are an optional method of storing table data. Cluster share common columns and are often used together. It is a powerful met...

Answer Question  Select Best Answer

tej

• Apr 23rd, 2012

The Oracle version starting of I. The starting in 1999 with version 6i, 8i and 9i, I signify "Internet" means stands for "Internet" and Oracle added the "I" to the version name to reflect support for ...

pradeep

• Aug 2nd, 2011

Oracle 9i and Oracle 10g....there are more implementation differences. In terms of architecture, 9i is based on Internet technology while 10g is grid computing based one. Many DBA features like Automa...

Answer Question  Select Best Answer

Create a matrix query to display the job, the salary for that job based on department number and the total salary for that job, for departments 20,50,80, and 90,giving each column and appropriate heading.

sachin

• Apr 18th, 2012

Select job,
sum(decode(deptno,10,sal)) deptno10,
sum(decode(deptno,20,sal)) deptno20,
sum(decode(deptno,30,sal)) deptno30,
sum(decode(deptno,40,sal)) deptno40
from emp group by job order by 1;

Neha

• Sep 25th, 2011

Select job in job, sal in salary, sum(salary) in total_sal
where job=(select job in job where deptno=20 and 50 and 80 and 90)
;

Answer Question  Select Best Answer

How do I reverse a dtring in Oracle without using PL/SQl and without using the reverse function??

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Apr 13th, 2012

Are you looking for something like this?"plsql function revstring(instring varchar2) return varchar2 is rstring varchar2(750) := ; iCnt pls_integer; begin for icnt in reve...

Answer Question  Select Best Answer

Ashish

• Mar 24th, 2012

See, in oracle to maintain read consistency oracle keeps a copy in undo section of database for the data which is under modification by some transaction. And so readers do not wait for writers and ca...

Kranthi

• Jul 13th, 2011

In before update/delete trigger if we use select statement for fetching the records.in this scenario will wet mutating trigger error.using row level trigger we can avoid instead of statement level trigger. we can accurate results.

Answer Question  Select Best Answer

PK

• Feb 29th, 2012

There is one Oracle function ROLLUP, the same can be used for required output

QUERY:
SELECT GENDER,COUNT(GENDER)
FROM TABLE_NAME GROUP BY ROLLUP(GENDER);

neelapu

 Profile Answers by neelapu Questions by neelapu

• Apr 16th, 2010

SELECT COUNT(decode(sex1,'F',1)) "female count", COUNT(decode(sex1,'m',1)) "male count", COUNT(*) "total count"  FROM

Answer Question  Select Best Answer

How to see materialized view occupied memory space?

PK

• Feb 29th, 2012

There is a data dictionary called as ALL_MVIEWS.
You can check the query and the length of the query in this data dictionary as

Select * from ALL_MVIEWS where MVIEW_NAME = MVIEWNAME

Answer Question  Select Best Answer

How to join two columns which is having different datatype? Say Coulmn1 with Varchar2 datatype and Coulmn2 with long datatype.

PK

• Feb 28th, 2012

You can join the columns with two different data types by using to_char, to_number to_date functions.
For e.g. one field is a varchar which stores number and other field is a number then you can use.
to_number(var_column) = number_column

Answer Question  Select Best Answer

Iyappan

• Feb 24th, 2012

Select job, count(job) from emp group by job;

z_ashwini

 Profile Answers by z_ashwini Questions by z_ashwini

• Jan 13th, 2012

Code
 

Answer Question  Select Best Answer

What is the use of mentioning of constraint_name along with NOT NULL Constraint _type while creating a table?

naizy_dba

 Profile Answers by naizy_dba Questions by naizy_dba

• Feb 18th, 2012

Constraint are easy to reference if you give them a meaningful name. It is not necessary to assign name to constraint. If you do not assign any name to constraint Oracle will assign a name to constrai...

jimmy.katiyar11

 Profile Answers by jimmy.katiyar11 Questions by jimmy.katiyar11

• Nov 29th, 2011

Hi, Mainly use constraints for a businessmen rule that restrict enter the record in a table. IN oracle there are 5 type of constraints --- 1) Primary key, 2) foreign key 3) Unique key 4) Not NUll...

Answer Question  Select Best Answer

Neer.ocp10g

 Profile Answers by Neer.ocp10g Questions by Neer.ocp10g

• Jan 21st, 2012

The SCN is an important quantifier that oracle uses to keep track of its state at any given point in time. The SCN is used to keep track of all changes within the database, its a logical timestamp tha...

Answer Question  Select Best Answer

Hi,

What is alternative to TK prof (used in oracle 9i) in 10g and 11g?

Regards,
Shashi

geekano

 Profile Answers by geekano Questions by geekano

• Jan 4th, 2012

Trace Analyzer Traditionally, tkprof has been the best tracing diagnostics tool available. That is, until the introduction of Trace Analyzer which is everything tkprof is and more. However, as ...

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Nov 29th, 2011

You can make use of PL/SQL based TRCA

Other notable alternatives are
SQLTXPLAIN
Method-R profiler
OraSRP
TVD$XTAT

Answer Question  Select Best Answer

shaik shamsheer

• Dec 28th, 2011

In case of varchar it store upto 2000 bytes and in case of varchar2 it stores 4000 bytes
varchar is occupies space for the null value and varchar2 does not occupy space for null values.

venkat

• Dec 4th, 2011

"varchar" allocates memory in dynamic fashion where as "varchar2" also allocates memory in dynamic fashion but it also supports garbage collection.
"varchar" is developed along with SQL where as "varchar2" is developed by Oracle.

Answer Question  Select Best Answer