Oracle Basics Interview Questions

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.

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

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

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

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

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.

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

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

Check constraints it cant work multiple tables

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

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

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.

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

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

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

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

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

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

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;

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

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

Code
SELECT count(*), job
FROM emp 
GROUP BY job

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

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

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

You can make use of PL/SQL based TRCA

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

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.

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

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

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.

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

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.

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

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

Hi,

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

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

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

EMC,Sqlplus,Datapump and Import/export

EMC, sqlplus, datapump...