GeekInterview.com
Series: Subject: Topic:

Oracle Basics Interview Questions

Showing Questions 1 - 20 of 85 Questions
First | Prev | | Next | Last Page
Sort by: 
 | 

What is the difference between rownum,rowid

Asked By: senthil | Asked On: Jul 26th, 2006

Star Read Best Answer

Editorial / Best Answer

Answered by: rishipahuja

View all answers by rishipahuja

Member Since Aug-2006 | Answered On : Aug 6th, 2006

rowid has a physical significance i.e you can read a row if you know rowid. It is complete physical address of a row.

While rownum is temporary serial number allocated to each returned row during query execution.

Answered by: Sunny on: Mar 25th, 2013

Row num is a sequential number allocated to each returned row for query execution. It is nothing but a numeric values. It is a temporary values. But row id is a physical address of the rows. It is permanent.

Answered by: Chittaranjan Kotian on: May 16th, 2012

ROWNUM is a pseudocolumn returning a sequential number along with the rows retrieved, whereas rowid (also a pseudocolumn) contains the actual physical address of the data block containing the row

Create a query that will display the total no.Of employees and, of that total, the no.Of employees hired in 1995,1996,1997, and 1998. Create appropriate column headings.

Asked By: adarsh_sp | Asked On: Oct 3rd, 2006

Answered by: Sudarsan on: Mar 14th, 2013

SELECT Hiredate,Count(*)
FROM emp
WHERE to_char(hiredate,yyyy) in (1995,1996,1997,1998)
GROUP BY Hiredate;

Answered by: P.R.KHUNTIA on: Mar 19th, 2012

"sql select deptno,count(to_char(hiredate,yyyy),1980,empno) as yr1980, count(to_char(hiredate,yyyy),1981,empno) as yr1981, count(to_char(hir...

Tablespace management

Asked By: arunakumaris | Asked On: Mar 22nd, 2010

What are the ways tablespaces can be managed and how do they differ?

Answered by: Affrayam on: Mar 8th, 2013

The difference between LMTS & DMTS is if the extent management is local(LMTS) the information about free extents and free blocks will be maintained in DATAFILE HEADER it self AND if the extent managem...

Answered by: ashdba on: Sep 5th, 2010

Tablespace can be managed by specifying Extent management local or Extent management dictionary, segment space management auto or manually by using the key words pctfree, pctused, pctincrease during tablepsace creation.

In Oracle varchar2 takes dynamic space for storage then why char is still in Oracle?

Asked By: Beena | Asked On: Sep 16th, 2005

Answered by: Paras on: Feb 23rd, 2013

To Provide backward compatibility. i.e. program written in older version of Oracle might have CHAR used in it. The newer version should be compatible with it. Thats why CHAR is still there in Oracle ..

Answered by: Prasad on: Aug 18th, 2011

Since Char is fixed length , the processing speed will be more as there is no need of extra work to measure how much an instance of column is used the memory space. But in Varchar, the processor has ...

What is the diffrence between and constraints and triggers?

Asked By: Interview Candidate | Asked On: Sep 9th, 2005

Answered by: vishnuvardhanarao on: Nov 14th, 2012

Check constraints it cant work multiple tables

Answered by: Chittaranjan Kotian on: May 16th, 2012

Constraints and triggers are both used to enforce business rules at the database level. Constraints are primary key, unique key, foreign key, not null contraints etc, while triggers can be defined at the database and table level to do certain tasks depending on data entered

What is pro*c? What is oci?

Asked By: Beena | Asked On: Sep 19th, 2005

Answered by: Adas on: Nov 1st, 2012

Can anyone tell the exact diff b/w Pro*C and OCI

Answered by: Nisikant on: Mar 3rd, 2009

Pro *C is a Oracle database pre-compiler. It acts as a database connectivity. We can also embed the SQL statement within C or C++ programming code which will execute successfully.

Flow in Oracle database.

Asked By: Bharatsubnis | Asked On: Apr 13th, 2012

What happens when a query is submitted in Oracle? Please give the complete flow as to which all processes act and how the data is submitted / retrieved to / from the database .

Answered by: himanshu on: Sep 27th, 2012

1. RDBMS checks if a copy of the parsed SQL statement exists in the library cache. If parsed copy exists, then steps 2 to 6 are skipped. 2. RDBMS validates the syntax of the statement. 3. RDBMS ensure...

Answered by: Chittaranjan Kotian on: May 16th, 2012

A query is first checked for semantics, and is then parsed. Parse can be hard parse or soft parse. Oracle will check if the parsed statement exists in the library cache. If it exists the existing stat...

What is normalization? What is the advantage of normalization?

Asked By: Interview Candidate | Asked On: Sep 8th, 2005

Answered by: Chittaranjan Kotian on: May 16th, 2012

Normalization is a process of reviewing a data model to remove data redundency, maintain data intergrity, and make the data model as simple as possible. There are 3 normalization forms that are genera...

Answered by: Muhammad Wali Ullah on: Feb 21st, 2012

The Process of Simplifying the structure of data. Normalization increase granularity and Granularity is the scope of a definition for any particular thing.The more granular a data model is the easier...

What is meant by deadlock in database?

Asked By: GeekAdmin | Asked On: Sep 23rd, 2006

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

Answered by: Chittaranjan Kotian on: 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...

Answered by: rohit on: 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...

Creating a matrix query

Asked By: zecar | Asked On: Sep 14th, 2006

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.

Answered by: sachin on: 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;

Answered by: Neha on: 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)
;

Write a query to display the no.Of people with the same job

Asked By: zecar | Asked On: Sep 14th, 2006

Answered by: Iyappan on: Feb 24th, 2012

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

Answered by: z_ashwini on: Jan 13th, 2012

Code
  1. SELECT count(*), job
  2. FROM emp
  3. GROUP BY job

What is the difference between replace and translate

Asked By: Srinu | Asked On: Mar 22nd, 2006

Star Read Best Answer

Editorial / Best Answer

Answered by: Saumendra Mohanty

Answered On : May 16th, 2006

Both Replace and Translate are single row functions in Oracle 9i.

The Replace Function replaces single character with multiple characters.

But in Translate Function replaces sinlge character with sinlge character only.

Answered by: on: Jan 8th, 2012

The Replace Function can replace the old substring whose length are not same to the length of new substring. (length of newsub DONT NEED eaqual tolength of oldsub ) But in Translate Function only can...

Answered by: #emadri on: Jan 3rd, 2012

It is used to replace one string with another string, and
translate used to translate one character to another char.

Alternative to tk prof in Oracle 11g

Asked By: ishashi | Asked On: Aug 22nd, 2011

Hi, what is alternative to tk prof (used in Oracle 9i) in 10g and 11g? regards, shashi

Answered by: geekano on: 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 ...

Answered by: Lokesh M on: Nov 29th, 2011

You can make use of PL/SQL based TRCA

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

What is difference between varchar and varchar2

Asked By: Srinu | Asked On: Mar 9th, 2006

Answered by: shaik shamsheer on: 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.

Answered by: venkat on: 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.

What is the maximum number of columns in a table in Oracle?What is the maximum number of canvases in a form in forms 6i an forms 9i?

Asked By: sujit murumkar | Asked On: Mar 16th, 2006

Answered by: venkat on: Dec 4th, 2011

From Oracle 9i it allows maximum 1000 columns in a table. In Oracle 8i, it only allows 256 columns in a table.

Answered by: ambinu on: Jun 14th, 2011

MySQL: Maximum number of columns in one table - 3398; size of a table row - 65534 (BLOB and TEXT not included). Oracle: Unlimited rows@table. Maximum number of columns in one table - 1000. Up to 32 columns in index key. PostgreSQL: Rows - unlimited, columns - 1600; size of a table row - 1.6TB.

Can we create sequence to a view??

Asked By: rajanipriya | Asked On: May 9th, 2006

Answered by: suganya on: Nov 30th, 2011

no....because sequence is used for creating the integer sequence values only at the time of table creation...
but view is a subset of data for the base table.....so cant create the sequence for the view

Answered by: shamkohli on: Jun 17th, 2008

Sequence is used to generate numbers which can be stored in tables. View does not have data of its own and is generally used to query data. So sequence can not be created to a view.

What is the difference between primary key, unique key, surrogate key?

Asked By: Interview Candidate | Asked On: Aug 27th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: Kolta Sam

View all answers by Kolta Sam

Member Since Jul-2011 | Answered On : Jul 10th, 2011

Primary Key:

It is a visible key
It generated by user or application.
It could be changed by the user or application.
It could be queried
It used to form a relation between tables
It shouldn’t contain null value
It resemble table row
It is a unique identifier for a table object.
It contains only one key
It could contain numeric and strings characters.
It is an unique key which each row contain a distinct different key.
Example for it is a customer_Id.
It always starts by number one and second is two and so on but can starts with a different number.
Could created on one or more columns
No duplicate records

Secondary Key:

It used to form a relation between tables.
It is alternate table key.
It used to search data with primary key
It could contains null value
It could contains more than one secondary key for each table
Created only on one columns
No duplicate records
It creates index clustered by default


Surrogate Key:

It is invisible key for the user or the application.
It resembles database entity.
It generated by the system so it is invisible for user and application.
It shouldn’t contain null values
Only one surrogate key for each data entity
Its value is unique system wide.
Its value is never manipulated by the user or the application.
It never reused
It is frequently sequential number
It called synthetic key, an entity identifier, a system-generated key, a database sequence number, a factless key, a technical key, or an arbitrary unique identifier
No duplicate records

Answered by: Lucky on: Nov 29th, 2011

We can create only one primary key per table where as we can create numbers of unique key in a table. Primary key does not accept any null value but unique key accepts maximum of one null value. Pri...

Answered by: Rajakumar B V on: Sep 11th, 2011

Primary key
1) It creates clustered index by default
2) It doesn't allow nulls

Unique Key:
1) It creates non-clustered index by default
2) It allows only one null value

Application of cursors in applications specifically

Asked By: phanindra170531 | Asked On: Oct 20th, 2011

Dynamic cursors are used in airline reserVATion system as changes made must be reflected in result set as most updated information is required for booking. can anyone tell me the exact use of static cursor where most update changes are not required? In which application system it is useful?

Answered by: vannalas on: Nov 10th, 2011

Hi,

Can you give the examples of static and dynamic cursor ?
I will answer your question.

What is synapshot?

Asked By: senthil | Asked On: Jul 26th, 2006

Answered by: murali on: Nov 8th, 2011

snapshot is just like a image of the previous data for our reference and it's useful for analysing purpose in ADDM

Answered by: jeyam on: Nov 2nd, 2011

snapshot is just like a image of the previous data for our reference and it's useful for analysing purpose in ADDM

What are the Oracle 10g tools

Asked By: abhijitjena9 | Asked On: Aug 26th, 2011

Answered by: natu4u1979 on: Oct 18th, 2011

EMC,Sqlplus,Datapump and Import/export

Answered by: David Lozano Lucas on: Oct 18th, 2011

EMC, sqlplus, datapump...

First | Prev | | Next | Last Page

 

 

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

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.