Oracle SQL Questions for Newbies

I thought, it would be useful if i post some sample SQL questions for newbies. These bunch of SQL questions would be helpful for beginners who wanted to learn Oracle SQL.

Here are some tables which needs to be created to practice SQL questions. Just for learning process, you could use SCOTT schema to practice SQL questions. I am posting 101 Oracle SQL questions with answers.

First step would be, we need to create the below tables and insert relevant data to practice the questions.

Create the below tables:

CREATE TABLE DEPT (
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

ALTER TABLE DEPT ADD PRIMARY KEY(DEPTNO);

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));

ALTER TABLE EMP ADD CONSTRAINT FK_EMP_01
FOREIGN KEY(DEPTNO) REFERENCES DEPT;

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE SALGRADE(
GRADE NUMBER(2),
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES(1, 700,1200);
INSERT INTO SALGRADE VALUES(2, 1201,1400);
INSERT INTO SALGRADE VALUES(3, 1401,2000);
INSERT INTO SALGRADE VALUES(4, 2001,3000);
INSERT INTO SALGRADE VALUES(5, 3001,9999);

Question & Answers:

1) Display all the records in emp table?
select * from emp;
2) Display all the records in emp table where employee belongs to deptno 10?
select * from emp where deptno = 10

3) Display all the records in emp table where employee does not belong to deptno 30?

select * from emp where deptno != 30;

4) Display total number of records in Emp table?

select count(*) from emp;

5) Display emp table with salary descending order?

select * from emp order by sal desc

6) Display first five records in employee table?

select * from emp where rownum <= 5
7) Display all the records in emp table order by ascending deptno, descending salary?
select * from emp order by deptno asc, sal desc

8) Display all employees those who were joined in year 1981?

select * from emp where to_char(hiredate,'YYYY') = 1981;

9) Display COMM in emp table. Display zero in place of null.

select nvl(comm,0) from emp

10) Display the records in emp table where MGR in 7698,7566 and sal should be greater then 1500

select * from emp where mgr in(7698,7566) and sal > 1500

11) Display all employees where employees hired before 01-JAN-1981

select * from emp where hiredate < '01-JAN-1981'
12) Display all employees with how many years they have been servicing in the company?

select hiredate,round((sysdate-hiredate)/360) as years from emp

13) Display all employees those were not joined in 1981?

select * from emp where to_char(hiredate,'YYYY') != 1981;

14) Display all employees where their hiredate belongs to third quarter?

select * from emp where to_char(hiredate,'Q') = 3;

15) Display all employees where their salary is less then the Ford’s salary?

select * from emp where sal <(select sal from emp where ename='FORD');
16) Display all the records in EMP table along with the rowid?

select ename,rowid from emp;

17) Display all records in EMP table those were joined before SCOTT joined?

select * from emp where hiredate <(select hiredate from emp where ename='SCOTT')
18) Display all employees those who were joined in third quarter of 1981?

select * from emp where to_char(hiredate,'Q') = 3 and to_char(hiredate,'YYYY') = 1981

19) Add 3 months with hiredate in EMP table and display the result?

select hiredate, add_months(hiredate,3) from emp

20) Display the date for next TUESDAY in hiredate column?

select next_day(hiredate,'TUESDAY') from emp;

21) Find the date, 15 days after today’s date.

select sysdate+15 from dual

22) Write a query to display current date?

select sysdate from dual;
select current_date from dual;

23) Display distinct job from emp table?

select distinct job from emp

24) Display all the records in emp table where employee hired after 28-SEP-81 and before 03-DEC-81?

select * from emp where hiredate between '28-SEP-81' and '03-DEC-81'

25) Write a query that displays the employee’s names with the first letter capitalized and all other letters lowercase for all employees whose name starts with J, A, or M

select initcap(ename) from emp where ename like 'J%' or ename like 'A%' or ename like 'M%'

26) Display all jobs that are in department 10. Include the location of department in the output.

select job, loc from emp,dept where emp.deptno = dept.deptno and emp.deptno =10

27) Write a query to display the employee name, department name of all employees who earn a commission

select ename,dname from emp,dept where emp.deptno = dept.deptno and comm is not null;

28) Display the empno, ename, sal, and salary increased by 15%.

select empno, ename, sal actual_sal, (sal * 15/100) as Increased_sal from emp

29) Display ename, sal, grade. Use emp, salgrade table

select ename,sal,grade from emp,salgrade where sal between losal and hisal;

30) Display all employees and corresponding managers

select w.ename,w.sal,m.ename,m.sal from emp w, emp m where w.mgr = m.empno;

31) Display all the departments where employee salary greater then average salary of that department.

select ename,deptno, sal from emp a where sal > (select avg(sal) from emp where emp.deptno = a.deptno) order by deptno;

32) Display all employees whose salary greater then the manager salary?

select w.ename,w.sal,m.ename,m.sal from emp w, emp m where w.mgr = m.empno and w.sal > m.sal

33) Display employees where length of ename is 5

select * from emp where length(ename) =5

34) Display all employees where ename start with J and ends with S

select * from emp where ename like 'J%S'

35) Display all employees where employee does not belong to 10,20,40

select * from emp where deptno not in(10,20,40)

36) Display all employees where jobs does not belong to PRESIDENT and MANAGER?

select * from emp where job not in('PRESIDENT','MANAGER');

37) Display the maximum salary in the emp table

select max(sal) from emp

38) Display average salary for job SALESMAN

select avg(sal) from emp where job = 'SALESMAN'

39) Display all three figures salary in emp table

select * from emp where sal < = 999;
select * from emp where length(sal) = 3;
40) Display all records in emp table for employee who does not receive any commission

select * from emp where comm is not null

41) Display all ename where first character could be anything, but second character should be L?
select * from emp where ename like '_L%'

42) Display nth highest and nth lowest salary in emp table?

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
select distinct sal from (select ename,sal,dense_rank() over(order by sal desc) dr from emp) where dr = &x ;
43) Display all the departments where department has 3 employees?

select deptno from dept a where deptno in(select deptno from emp group by deptno having count(*)=3)

44) Display emp name and corresponding subordinates. Use CONNECT BY clause.

select lpad(' ',level+12)+ename from emp connect by prior empno = mgr start with mgr is null

Note: Please replace pipe symbol in the place of + sign for question 44. Pipe symbol is not displaying the blog. This is the reason, i used Plus sign here.

45) Display sum of salary for each department. The output should be in one record

select sum(decode(deptno,10,sal)) dept10, sum(decode(deptno,20,sal)) dept20, sum(decode(deptno,30,sal)) dept30, sum(sal) total_sal from emp

46) Display all department with Minimum salary and maximum salary?

select min(sal),max(sal) from emp;

47) Display all ename, sal, deptno,dname from emp, dept table where all department which has employees as well as department does not have any employees. This query should include non matching rows.

select dname,b.deptno, ename,sal from emp a, dept b where a.deptno(+) = b.deptno;
select dname,b.deptno, ename,sal from emp a right outer join dept b on a.deptno = b.deptno;

48) Display all ename, sal, deptno from emp, dept table where all employees which has matching department as well as employee does not have any departments. This query should include non matching rows.
Note: In the below query, employee will always have matching record in dept table. Emp, dept table may not be good example to answer this question.
select dname,b.deptno, ename,sal from emp a, dept b where a.deptno = b.deptno(+);
select dname,b.deptno, ename,sal from emp a left outer join dept b on a.deptno = b.deptno;
49) Display all ename, sal, deptno from emp, dept table where all employees which has matching and non matching department as well as all departments in dept table which has matching and non matching employees. This query should include non matching rows on both the tables.
Note: In the below query, employee will always have matching record in dept table. Emp, dept table may not be good example to answer this question.
select dname,b.deptno, ename,sal from emp a full outer join dept b on a.deptno = b.deptno
50) Display all ename, empno, dname, loc from emp, dept table without joining two tables
select * from emp,dept;

51) Display all the departments where department does not have any employees

select deptno from dept where not exists(select 1 from emp where emp.deptno = dept.deptno);

select deptno from dept where deptno not in(select deptno from emp);

52) Display all the departments where department does have atleast one employee

select * from dept a where exists(select 1 from emp b where b.deptno = a.deptno)

select * from dept a where deptno in(select deptno from emp b where a.deptno = b.deptno)

53) Display all employees those who are not managers?

select ename from emp a where not exists (select 1 from emp b where b.mgr = a.empno);

select ename from emp a where empno not in (select mgr from emp b where b.mgr = a.empno and mgr is not null);

54) Display ename, deptno from emp table with format of {ename} belongs to {deptno}

select ename+' belongs to '+deptno from emp

Note: Please replace pipe symbol in the place of + sign for question 44. Pipe symbol is not displaying the blog. This is the reason, i used Plus sign here.

55) Display total number of employees hired for 1980,1981,1982. The output should be in one record.

select
count(decode(to_char(hiredate,'YYYY'), 1980,hiredate)) total_hire_1980,
count(decode(to_char(hiredate,'YYYY'), 1981,hiredate)) total_hire_1981,
count(decode(to_char(hiredate,'YYYY'), 1982,hiredate)) total_hire_1982
from emp

56) Display ename, deptno from employee table. Also add another column in the same query and it should display ten for dept 10, twenty for dept 20, thirty for dept 30, fourty for dept 40

select ename,deptno, (case deptno
when 10 then 'Ten'
when 20 then 'Twenty'
when 30 then 'Thirty'
when 40 then 'fourty'
else 'others' end) as dept
from emp

57) Display all the records in emp table. The ename should be lower case. The job first character should be upper case and rest of the character in job field should be lower case.

select lower(ename) as ename, initcap(job) as job from emp
58) Display all employees those who have joined in first week of the month ?

select * from emp where to_char(hiredate,'W') = 1;

59) Display all empoyees those who have joined in the 49th week of the year?

select * from emp where to_char(hiredate,'WW') = 49;

60) Display empno, deptno, salary, salary difference between current record and previous record in emp table. Deptno should be in descending order.

SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp;

61) Create table emp1 and copy the emp table for deptno 10 while creating the table

Create table emp1 as select * from emp where deptno=10

62) Create table emp2 with same structure of emp table. Do not copy the data

create table emp2 as select * from emp where 1=2

63) Insert new record in emp1 table, Merge the emp1 table on emp table.

insert into emp1 values(9999,'PAUL','MANAGER',7839,SYSDATE,8900,NULL,10);

MERGE
INTO emp tgt
USING emp1 src
ON ( src.empno = tgt.empno )
WHEN MATCHED
THEN
UPDATE
SET tgt.ename = src.ename,
tgt.job = src.job,
tgt.mgr = src.mgr,
tgt.hiredate = src.hiredate,
tgt.sal = src.sal,
tgt.deptno = src.deptno
WHEN NOT MATCHED
THEN
Insert(
Tgt.empno,
Tgt.Ename,
Tgt.Job,
Tgt.Mgr,
Tgt.Hiredate,
Tgt.Sal,
Tgt.Comm,
Tgt.Deptno)
values (src.empno,
src.ename,
src.job,
src.mgr,
src.hiredate,
src.sal,
src.comm,
src.deptno);

64) Display all the records for deptno which belongs to employee name JAMES?

select * from emp where deptno in(select deptno from emp where ename = 'JAMES')

65) Display all the records in emp table where salary should be less then or equal to ADAMS salary?

select * from emp where sal <= (select sal from emp where ename='ADAMS')

66) Display all employees those were joined before employee WARD joined?

select * from emp where hiredate < (select hiredate from emp where ename='WARD')

67) Display all subordinate those who are working under BLAKE?

Select ename from emp where mgr = (select empno from emp where ename='BLAKE')

68) Display all subordinate(all levels) for employee BLAKE?

select ename from emp start with empno = (select empno from emp where ename='BLAKE')
connect by prior empno = mgr

69) Display all record in emp table for deptno which belongs to KING's Job?

select * from emp where deptno in(select deptno from emp where job= (select job from emp where ename = 'KING'))

70) Display the employees for empno which belongs to job PRESIDENT?

select * from emp where empno in(select empno from emp where ename in(select ename from emp where JOB = 'PRESIDENT'));

71) Display list of ename those who have joined in Year 81 as MANAGER?

select * from emp where to_char(hiredate,'YYYY') = 1981 and job = 'MANAGER';

72) Display who is making highest commission?

select * from emp where comm = (select max(comm) from emp);

73) Display who is senior most employee? How many years has been working?

select * from emp where trunc(sysdate-hiredate)/365 = (select max(trunc(sysdate-hiredate)/365) from emp);

select * from emp where hiredate =(select min(hiredate) from emp)
74) Display who is most experienced and least experienced employee?

select * from emp where trunc(sysdate-hiredate)/365 = (select min(trunc(sysdate-hiredate)/365) from emp);

select * from emp where hiredate =(select max(hiredate) from emp)

75) Display ename, sal, grade, dname, loc for each employee.

select empno,ename,b.deptno,dname,grade from
emp a,dept b, salgrade c
where a.deptno = b.deptno
and sal between losal and hisal;

76) Display all employee whose location is DALLAS?

SELECT emp.ename, emp.JOB, emp.deptno
FROM emp
WHERE EXISTS
(SELECT 'x'
FROM dept d
WHERE d.DEPTNO = emp.DEPTNO
AND d.LOC = 'DALLAS') ;

select emp.ename, emp.job, emp.deptno
from emp
where deptno in(select deptno from dept where loc='DALLAS');

77) Display ename, job, dname, deptno for each employee by using INLINE view?

SELECT emp.ename,
emp.JOB,
emp.deptno,
dnames.dname
FROM emp
JOIN (select dname, deptno
from dept ) dnames ON emp.deptno = dnames.deptno

78) List ename, job, sal and department of all employees whose salary is not within the salary grade?

select ename, job, sal, dname
from emp, dept
where emp.deptno = dept.deptno
and not exists
(select ‘x’ from salgrade
where emp.sal between losal and hisal);

79) Use EMP and EMP1 table. Query should have only three columns. Display empno,ename,sal from both tables inluding duplicates.
select empno, ename, sal from emp
union all
select empno, ename, sal from emp1

80) Delete emp table for detpno 10 and 20.
delete emp where deptno in(10,20);

81) Delete all employees those are not getting any commission?
delete emp where comm is null;

82) Delete all employees those who employeed more then 28 years
delete emp where trunc(sysdate - hiredate)/365 > 28;

83) Add duplicate records in emp1 table. Delete the duplicate records in emp1 table.
insert into emp1 select * from emp1 where rownum <=1; commit; delete emp1 a where a.rowid <>(select min(b.rowid) from emp1 b where a.empno = b.empno);

84) Delete the employees where employee salary greater then average salary of department salary?
delete emp a where sal > (select avg(sal) from emp where emp.deptno = a.deptno);

85) Delete all employees those who are reporting to BLAKE?
Delete emp where ename in(Select ename from emp where mgr = (select empno from emp where ename='BLAKE'))

86) Delete all levels of employees those who are under BLAKE?
Delete emp where ename in(select ename from emp start with empno = (select empno from emp where ename='BLAKE')
connect by prior empno = mgr)
87) Delete all employees those who are only managers?
delete emp where ename in(select ename from emp a where empno in (select mgr from emp b where b.mgr = a.empno and mgr is not null))

88) Remove the department in dept table where dept does not have any employees?

delete dept where deptno not in(select deptno from emp where deptno is not null)

89) Remove all grade 2 employees in emp table?

delete emp where empno in(select empno from emp,salgrade where sal between losal and hisal and grade = 2)

90) Remove all the employees in SMITH's department

delete emp where deptno = (select deptno from emp where ename = 'SMITH')

91) Remove least paid employee who are reporting to BLAKE ?

delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')) and
ename in(select ename from emp where mgr =
(select empno from emp where ename = 'BLAKE'))
92) Remove all employees who were joined before SMITH joined?
delete emp where hiredate < (select hiredate from emp where ename='SMITH');
93) Rename the employee name JONES to ANDY

update emp set ename = 'ANDY' where ename = 'JONES'

94) Change the WARD's hiredate to one day ahead

update emp set hiredate = hiredate + 1 where ename = 'WARD'

95) Update MARTIN salary same as SMITH's salary

update emp set sal = (select sal from emp where ename = 'SMITH') where ename='MARTIN'

96) Increase the salary 5% for employee those who are earning commission less then 1000

update emp set sal = sal + (sal * (5/100)) where comm between 0 and 1000

97) Increase 250$ commission for BLAKE's team

update emp set comm = nvl(comm,0)+250 where mgr = (select empno from emp where ename='BLAKE');
98) Increase 100$ for employee who is making more then averge salary of his department?

update emp a set sal = sal + 150 where sal > (select avg(sal) from emp b where b.deptno = a.deptno)
99) Increase 1% salary for employee who is making lowest salary in dept 10

update emp set sal = sal + (sal* 1/100)
where
sal = (select min(sal) from emp where deptno = 10)
and deptno = 10

100) Reduce the commission amount from employee salary for each employee who were joined after ALLEN joined.

update emp set sal = sal - NVL(comm,0)
where empno in(select empno from emp where hiredate > (select hiredate from emp where
ename = 'ALLEN'))

101) Increase commission 10$ for employees those who are located in NEW YORK.

update emp a set comm = NVL(COMM,0) + 10
where deptno = (select deptno from dept where loc='NEW YORK');

Hope you enjoy all these questions... Please let me know if you have any comments or clarification... Feel free to click advertisement to maintain this blog. Thank you for visiting my Bog!!!.

Please refer more questions for PLSQL

Oracle11g OCP Exam Passed

I am glad that, i just passed my Oracle11g OCP exam last week. I just wanted to share my preparation with other folks...

Here are the my preparation steps.

1. Read Oracle11g New features Ebook(Download the book here)
2. Read the oracle-base link. Click
3. Went through testking question (Please contact me if you need)
4. Ofcourse, My practical experience helped me to pass this exam.

At the end, i successfully passed 1ZO-050 exam with 99% score.

Hope it helps.

Where to download OCP Logo?

Some of the folks, they customize the logo to upload in their resume without knowing the oracle registered logo. I thought, it would be useful to provide the links to download the logo. There are several source which we can use to download the logo.

Here is the google link to download the logo... Google Link

Hope you enjoy this link to download the OCP logo... Have a fun.

How do we use Ref Cursor?

What is REF CURSOR? A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The main purpose of the ref cursor is, we can return the query result in the front end(Java, .Net, VB, reporting tools etc). For instance, if we want to return the query result set in Cognos reporting tool, then we can use the ref cursor to return the values. The primary use of ref cursor is to pass the result set back to calling application. Ref cursor can be passed as a variable to another subroutine. The powerful cabability in ref cursor is, cursor can be opened and passed to another block for processing, then returned to original block to be closed.

This article is tested in oracle9i and it should work in oracle10g and further versions... All the below PLSQL code in this article is successfully tested in oracle 9.2.0.8. I tested the code in Scott schema with emp and dept tables.

What is the difference between REF CURSOR and Normal Cursor?

1. The cursor concept is same between regular cursor(PLSQL cursor) and ref cursor. But normal cursor, we can declare and define the cursor in the declaration part. In the declaration itself, the select statement is tied up with the cursor. So the cursor structure is known in the compile time. It is static in definition.

In ref cursor, we just declare the variable as SYS_REFCURSOR data type. We are not tying with any select statement in the declaration. But inside the procedure, we can tie up the same ref cursor variable with any number of select statement. It is dynamic and dynamically opened in the procedure, based on the logic or condition.

Let us demonstrate this... I have stored procedure and i am passing input 1 or 2. If i pass 1, then i wanted to display emp table records. If i pass input 2, then i wanted to display dept table. I am demonstrating this in normal cursor and ref cursor.

Here is the way to accomplish this in Normal cursor.

create or replace procedure Test_refcursor(p_choice NUMBER) is
cursor c1 is select * from emp;
cursor c2 is select * from dept;
begin
if p_choice = 1 then
for i in c1 loop
dbms_output.put_line(i.ename);
end loop;
elsif p_choice = 2 then
for i in c2 loop
dbms_output.put_line(i.dname);
end loop;
end if;
END;
/

Here is the way to accomplish this in ref cursor.

Create or replace procedure test_refcursor(p_choice number) is
c1 sys_refcursor;
v_ename emp.ename%type;
v_dname dept.dname%type;
procedure gen_cur(chc IN number,b IN OUT sys_refcursor) is
str varchar2(1000);
begin
if chc = 1 then
str:= 'select ename from emp';
elsif chc = 2 then
str:= 'select dname from dept';
end if;
open b for str;
end;
begin
gen_cur(p_choice,c1);
if p_choice = 1 then
loop
fetch c1 into v_ename;
exit when c1%notfound;
dbms_output.put_line(v_ename);
end loop;
elsif p_choice = 2 then
loop
fetch c1 into v_dname;
exit when c1%notfound;
dbms_output.put_line(v_dname);
end loop;
end if;
end;
/

2. Ref cursor output can be returned to client(java, .Net, VB, reporting tool etc) application. But normal cursor(PLSQL cursor) output can not be returned to client application.

3. Normal cursor can be global. For example, we can declare the normal cursor in the package specification. It can be used in all procedure/functions in the same package as well as outside the package. But ref cursor can not be declared outside of the procedure.

4. Normal cursor can not be passed from one subroutine to another subroutine. But ref cursor can be passed from one subroutine to another subroutine.

How do we declare ref cursor? There are two type of ref cursor variable declaration. One is weak type declaration and another one is strong type declaration. Weak typed declaration does not tell us return data structure. Strong type declaration will tell us what type of data will be returned. Strongly typed cursor has less flexibilities and less prone to programming errors. Weakly typed cursors has more flexibilities and it can return different structure of of the data.

type emp_cursor is ref cursor; -- Weak typed declaration

type emp_cursor is ref cursor
returning emp%rowtype; -- Strong typed declaration

Once cursor type is defined, then cursor variable can be assigned to cursor type.
c1 emp_cursor;

Sample program for ref cursor... Pass the employee name as a input to procedure and return all subordinates.

CREATE OR REPLACE PROCEDURE testrefcursor(
p_ename IN VARCHAR2,
curename OUT SYS_REFCURSOR) IS
sql_text VARCHAR2(4000);
BEGIN
sql_text := 'SELECT ENAME
FROM EMP
WHERE ENAME != :1
START WITH ENAME = :2
CONNECT BY PRIOR EMPNO = MGR';
IF curename%ISOPEN THEN
CLOSE curename;
END IF ;
OPEN curename
FOR sql_text USING p_ename,p_ename;
END;
/

Let us accomplish the same above task through Function.

CREATE OR REPLACE FUNCTION testrefcursor(p_ename IN VARCHAR2)
RETURN SYS_REFCURSOR IS
sql_text VARCHAR2(4000);
curename SYS_REFCURSOR;
BEGIN
sql_text := 'SELECT ENAME
FROM EMP
WHERE ENAME != :1
START WITH ENAME = :2
CONNECT BY PRIOR EMPNO = MGR';
IF curename%ISOPEN THEN
CLOSE curename;
END IF ;
OPEN curename
FOR sql_text USING p_ename,p_ename;
RETURN curename;
END;
/

How do we display ref cursor result through SQL*PLUS? It is simple. Let us go with some sample code and demonstrate how to display the output in SQL*PLUS? Let us display the output for the above procedure testrefcursor.

SQL> DECLARE
2 refcursor SYS_REFCURSOR;
3 v_ename EMP.ENAME%TYPE;
4 BEGIN
5 testrefcursor('KING',refcursor);
6 loop
7 fetch refcursor into v_ename;
8 exit when refcursor%notfound;
9 dbms_output.put_line(v_ename);
10 end loop;
11 end;
12 /
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER

PL/SQL procedure successfully completed.

SQL>

Here is the way, we can display the above function output....

SQL> select testrefcursor('KING') from dual;

TESTREFCURSOR('KING'
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ENAME
----------
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER

13 rows selected.

We can also define SQL*PLus variables of type REFCURSOR...

SQL> create or replace function getemplist(dno in number)
2 return sys_refcursor
3 is
4 return_value sys_refcursor;
5 begin
6 open return_value for
7 select ename from emp where deptno = dno;
8 return return_value;
9 end;
10 /

Function created.

SQL> var rc refcursor
SQL> exec :rc := getemplist(30)

PL/SQL procedure successfully completed.

SQL> print rc

ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

6 rows selected.

SQL>

When and what circumstances we should use ref cursor? Ref cursor is classic option when we want to return the query output into any front end application. We can use ref cursor when you are not able to accomplish the task through normal cursor. REF CURSOR is flexibility feature, but not performance feature. Ref cursor is bad choice when performance is a concern and able to accomplish the task by using regular cursor. Ref cursor always consumes time to process the query compared to normal cursor. So ref cursor should be used only place where it is required.

Performance comparision between ref cursor and regular cursor? Ref cursor is not a good option if performance is a concern. but ofcourse, there are place you can not avoid ref cursor. Ref cursor is not an option when you want to process the records inside the PLSQL procedure. Ref cursor is always slower then explicit cursor and implicit cursor. Let me demonstrate how process time between ref cursor, explicit cursor and implicit cursor...

As per the below example, the ref cursor consumes more time then the explicit cursor and implicit cursor.... So in netshell, use ref cursor only if it is required. otherwise, try to use regular cursor..

Note : For some reason, in the below code, pipe symbol is not appearing in the blog. So i replaced the pipe symbol with # symbol. In case if you want to run the below code in your database, please replace back # with pipe sign.

SQL> CREATE OR REPLACE PROCEDURE stp_refcursor_comparison AS
2 l_loops NUMBER := 10000;
3 l_dummy dual.dummy%TYPE;
4 l_start NUMBER;
5 CURSOR c_dual IS
6 SELECT dummy
7 FROM dual;
8 l_cursor SYS_REFCURSOR;
9 BEGIN
10 -- Time explicit cursor.
11 l_start := DBMS_UTILITY.get_time;
12 FOR i IN 1 .. l_loops LOOP
13 OPEN c_dual;
14 FETCH c_dual
15 INTO l_dummy;
16 CLOSE c_dual;
17 END LOOP;
18 DBMS_OUTPUT.put_line('Explicit: ' #
19 (DBMS_UTILITY.get_time - l_start));
20 -- Time ref cursor.
21 l_start := DBMS_UTILITY.get_time;
22 FOR i IN 1 .. l_loops LOOP
23 OPEN l_cursor FOR SELECT dummy FROM dual;
24 FETCH l_cursor
25 INTO l_dummy;
26 CLOSE l_cursor;
27 END LOOP;
28 DBMS_OUTPUT.put_line('REF CURSOR: ' #
29 (DBMS_UTILITY.get_time - l_start));
30 -- Time implicit cursor.
31 l_start := DBMS_UTILITY.get_time;
32 FOR i IN 1 .. l_loops LOOP
33 SELECT dummy
34 INTO l_dummy
35 FROM dual;
36 END LOOP;
37 DBMS_OUTPUT.put_line('Implicit: ' #
38 (DBMS_UTILITY.get_time - l_start));
39 END stp_refcursor_comparison;
40 /

Procedure created.

SQL> set serveroutput on
SQL> execute stp_refcursor_comparison;
Explicit: 53
REF CURSOR: 67
Implicit: 35

PL/SQL procedure successfully completed.

SQL>

Star schema vs Snowflake Schema

What is star schema? Star schema is data warehousing data model which resembles to star. There are one or more fact table connected with multiple dimensional tables. Center of the star consist of one or more fact table and fact is pointing to different dimension tables. Dimension tables have a simple primary key, while fact tables have a composit primary key consisting of the aggregate of relevant dimension keys.

Fact table consists of Measurements or facts of business process. It is centeralized table in star schema, called FACT. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact table contains the content of the Datawarehouse. A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables). In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called as Factless Fact tables.

Factless Fact means only the key available in the Fact and there are no measures available. Factless fact can have only keys or keys with count of occurrences/ events (For instance, no of accident in a month, no of policies has been closed in a month). It is used to support negative analysis report. For example a Store that did not sell a product for a given period. It is used to join the dimension tables.

Dimension table is a parent table which is connected with Fact table. Dimension has attributes which are normally descriptive and textual values. For instance, SALES table is fact table and possible dimension tables are TIME, PRODUCT, REGION, SALESPERSON, etc. Dimension never have foreign key in star schema data model.

Slowly changing dimension(SCD) applies to cases where the attribute for a record varies over time. We have three type of SCD, Type1, Type2 and Type3.

Type1: The new record replaces the original record. No trace of the old record exists

Type2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.

Type3: The original record is modified to reflect the change

To learn more about SCD, please click SCD

Here is sample data model for star schema. In the below data model, the dimensions are products, customers, time and locations. The fact table is sales and it is connected with all dimension. Now we can see the fact data with different dimensions. The fact table Foreign key will be connected with primary key of dimension table. We never connect the one dimension table to another dimension table. Oracle optimizer understand the star query and generate the different execution plan to improve the performance.

What is snowflake schema? The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy.

Here is sample data model for snowflake schema.. Here customer dimension is normalized into another lookup table customertype. The same way, we can also normalize the customer dimension into another lookup table as country. Location dimension is normalized into territory. Product dimension can also be normalized into another lookup table as supplier...

Advantages of Snowflake...

1. No redundancy and hence more easy to maintain and change.
2. When we normalize the dimension, the dimension will not be sparsed.
3. It reduce the disk space since there is no redundancy. But in real world scenario, dimension is small table and disk space is not a major issue.

Disadvantages of Snowflake...

1. You need to join more tables when we write the query. It is less easy to understand.
2. In snowflake model, query process time will increase, since we are joining multiple tables.

When should we go for snowflake schema? Dimension has many columns, and the data are very sparse, most of the fields has no data, also disk space is a concern, then we can think of snowflake. Since dimension tables hold less space, snow flake schema approach may be avoided in some organization. We can go for snowflake model when we have small data mart or datawarehouse. Star schema model is good for bigger data mart/warehouse. To learn more about snowflake schema, please click.

To learn more about dimensional data modeling, please see these links. Link1 and Link2

Temporary tablespace

What is temporary tablespace?

Temporary tablespace is used to store temporary information which is used for sort operation. Data that is only used for the duration of a session is stored in a temporary tablespaces. Let us say, we are joining two large tables, then sorting may not be able to complete in memory. Apparently oracle use the temporary segment to complete the sorting. Here are some SQL statement which might require disk sorting.

CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc

How do we create temporary tablespace?

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP.DBF'
SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. RMAN never backup the temporary tablespace data files.

How do we monitor temp tablespace?

Temp files are listed in V$TEMPFILE and DBA_TEMP_FILES. V$SORT_SEGMENT and V$SORT_USAGE can be used to find out who occupies the space in temporary Tablespaces. DBA_FREE_SPACE does not record free space for temporary tablespaces. We can use V$TEMP_SPACE_HEADER to find out the free space and used space.

Default temporary tablespace(Oracle9i feature)

When DBA create user, he/she should assign temporary tablespace. Prior to oracle9i, if DBA forgot to assign the temporary tablespace, then oracle use SYSTEM tablespace for sorting. To avoid overloading SYSTEM tablespace, oracle9i introduced default temporary tablespace. So every database has default temporary tablespace since Oracle9i. In case, if we forgot to assign temporary tablespace while creating user, then oracle use default temporary tablespace for sorting. Here is the way, we can assign default temporary tablespace for entire database.

SQL> alter database default temporary tablespace temp1;

To see the default temporary tablespace for a database, execute the following query:

SQL> select property_value from DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Restriction on Default temporary tablespace:

1. You can drop a default temporary tablespace until after you have
created new default temporary tablespace.

2. You can not take default temporary tablespace offline

3. You can not change a default temporary tablespace into a permanent tablespace

Temporary tablespace Groups(Oracle10g feature)

In nutshell, temporary tablespace group is a shortcut or synonym for a list of temporary tablespaces. A temporary tablespace group can have only temporary tablespaces as members. A temporary tablespace group consist of at least one temporary tablespace. A temporary tablespace group can not be empty. After last member of a temporary tablespace group has been dropped, the temporary tablespace group no longer exists. The temporary tablespace group is created when the first temporary tablespace is added to the group.

When ever a temporary tablespace can be referenced, a temporary tablespace group can be referenced as well. Therefore, temporary tablespace, temporary tablespace group share the same namespace. Temporary tablespace can not have the same name as a temporary tablespace group. The new view DBA_TABLESPACE_GROUPS shows the members of each temporary tablespace.

The advantage of this feature is, we can tailor user load to individual temporary tablespaces. We can allow large sort operations to span and consume multiple temporary tablespaces if needed. We can Reduce contention when multiple temporary tablespaces are defined.

How temporary tablespace group works?

Temporary tablespaces allow a single user with multiple session to potentially use a different temporary tablespace in each session. The user SCOTT is assigned the temporary tablespace group TEMPGRP consisting of TEMP1, TEMP2, TEMP3. The user SCOTT in session #1 may use actual temporary tablespace TEMP1, and user SCOTT in session#2 may use the actual temporary tablespace TEMP3. Not only does this prevent large tablespace operations from running out of temporary space, it also allows parallel operations within a single session to potentially use more than one actual temporary tablespace, even though in all the previous scenarios, SCOTT was assigned the TMPGRP temporary tablespace group. Logically the same temporary tablespace was used in every session.

Creating and Dropping temporary tablespace Groups:

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP1.DBF' SIZE 20M;

CREATE TEMPORARY TABLESPACE temp3 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP3.DBF' SIZE 20M;

CREATE TEMPORARY TABLESPACE temp4 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP4.DBF' SIZE 20M;

alter tablespace temp1 tablespace group tempgrp;
alter tablespace temp3 tablespace group tempgrp;
alter tablespace temp4 tablespace group tempgrp;
alter database default temporary tablespace tempgrp;

You can not drop the temporary tablespace group, but however, we can drop one of the members of the group as below.

alter tablespace temp3 tablespace group '';

We can also create temporary tablespace and we can immediately add it to an existing group, or create new group with one member as below.

CREATE TEMPORARY TABLESPACE temp6 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF' SIZE 20M
tablespace group tempgrp2;

Assigning temporary tablespace group to users:

Assigning a temporary tablespace group to a user is identical to assigning a temporary tablespace to user.

Create user test identified by test
Default tablespace users
Temporary tablespace tempgrp;

Note that, if you did not specify a temporary tablespace for TEST, then we can still assign the temporary tablespace group.

Alter user test temporary tablespace tempgrp;

How do we resize the temporary data file?

In many database configurations, the DBA will choose to allow their temporary tablespace to auto extend. A bad query can easily chew up valuable space on the disk. The DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF' resize 250M;
alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF'
resize 250M
*
ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

Ooops.... It did not work!!!. There are several other method exists to resize the temp files. But there is obvious method exists in all the version which is dropping and recreating temp files. Dropping and recreating the temp file is straight forward when the temporary tablespace is not a default temporary tablespace.

Since there are several method exists, i am going to discuss few ways about how to drop and recreate the temp files when temp file becomes bigger..... It is recommended to do this operation in off peak hours...

Method1a... Let us assume, some one ran the bad query and the temp file is bigger. Now how do we resize the temp file to smaller size. The temporary tablespace is default temporary tablespace. In this scenario, temporary tablespace is not part of tempoary tablespace group. The temporary tablespace name is TEMP.

Step1: create a new temporary tablespace with reasonable size.

CREATE TEMPORARY TABLESPACE tempspace TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMPSPACE.DBF' SIZE 20M;

Step2: Modify the newly created temporary tablepsace as default temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPSPACE

Step3: Drop the bigger temporary tablespace. But before dropping, make sure, no one is using the temporary tablespace. Just make sure, the below query returns zero records.

SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
SELECT COUNT(*) FROM v$tempseg_usage WHERE tablespace = 'TEMP'

Step4: Once the above(step3) query returns zero records, then drop the temporary tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Method1b... Let us assume, some one ran the bad query and the temp file is bigger. Now how do we resize the temp file to smaller size. The temporary tablespace is default temporary tablespace. In this scenario, temporary tablespace is part of temporary tablespace group. The temporary tablespace name is TEMP and group name is TEMPGRP.

Step1: Unlink the bigger tablespace from temporary tablespace group.

alter tablespace temp tablespace group '';

Step2: Just make sure, no one is using temporary tablespace. Below query should return zero records.

SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
SELECT COUNT(*) FROM v$tempseg_usage WHERE tablespace = 'TEMP'

Step3: Once the above(step3) query returns zero records, then drop the temporary tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Step4: Create new tablespace and add the tablespace to group.

CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP.DBF' SIZE 20M tablespace group tempgrp;

How do we recover the temporary data file?

When ever, we the temporary tablespace temp file is corrupted or deleted accidently, then it is easy to recover. RMAN never backup the temporary tablespace. Till oracle9i, we make the temp file offline and drop the temp file at the database level(alter database datafile 'c:/oracle/oradata/temp.dbf' offline drop). Once it is dropped, then drop the temporary tablespace and recreate new one. Oracle10g introduced new feature which we will create the temp file automatically when we restart the database.

Here is the steps in oracle10g. The database is running in windows OS.

Step1: Let us delete the temp file to simulate that the temp file is corrupted.

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 08:50 PM 20,979,712 TEMP01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
6 File(s) 917,553,152 bytes
0 Dir(s) 15,348,064,256 bytes free

D:\oracle\product\10.2.0\oradata\orcl>del TEMP01.DBF

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
5 File(s) 896,573,440 bytes
0 Dir(s) 15,369,043,968 bytes free

D:\oracle\product\10.2.0\oradata\orcl>

Step2: Let us restart the database. In oracle9i, the database will not open. But in oracle10g, when we start the database, it creates the temp file automatically and open the database. Just click to know more info.

D:\oracle\product\10.2.0\oradata\orcl>set oracle_sid=orcl

D:\oracle\product\10.2.0\oradata\orcl>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 14 21:41:51 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1248552 bytes
Variable Size 88081112 bytes
Database Buffers 188743680 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL>

Step 3: Database opened successfully. Let us check the DB directory...

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 09:42 PM 20,979,712 TEMP01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
6 File(s) 917,553,152 bytes
0 Dir(s) 15,348,047,872 bytes free

D:\oracle\product\10.2.0\oradata\orcl>

Logical standby vs Physical standby

Standby database was introduced in oracle8i for high availability solution. Oracle9i enhanced the standby database and introduced Data Guard. Generally there are two type of standby database. One is physical standby and another one is logical standby. This article is written as per the version Oracle10gR2.

1. What is physical standby database?
2. What is logical standby database?
3. What is the difference between logical standby and physical?
4. At what circumstances, physical standby is useful?
5. At what circumstances, logical standby is useful?

1. What is physical standby database?

Physical standby database is physically identical to the primary database. It is block by block copy of the primary images. The archived redo log files are shipped to standby database and applied the archived redo log files on the standby database. So standby database should be always in recovery mode. This is like, DBA is sitting in remote location and recovering the primary database in different server by applying the archived redo log files.

2. What is logical standby database?

Logical standby database is logically identical to the primary database. Oracle use the logminer technology to transforms standard archived redo logs(by default) or redo logs(if real-time apply enabled) into SQL statements and applies them to the logical stand by database. A logical standby database can remain open and the same time its tables are updated from the primary database, and those tables are simultaneously available for read access. Oracle9i introduced logical standby database.

3. What is the difference between logical standby and physical standby?

Physical standby database is always in managed recovery mode. We can only open the physical standby database in read only mode. But archive redo logs can not be applied while physical standby database is in read only mode.

Logical standby database is always in open mode. The SQL statements which is generated from primary database is applied on logical standby while the database is in open mode. We can have additional materialized views, indexes and tables added for faster performance in logical standby.

Physical standby schema matches exactly the source database. Logical standby database does not have to match the schema structure of the source database.

3. At what circumstances, physical standby is useful?

We can use Physical standby when we need to go for High availability solutions. Physical standby is the most commonly used for disaster recovery or failure.

5. At what circumstances, logical standby is useful?

We can use logical standby if we want to off-load some of the reporting overhead in primary database. The logical standby database is an excellent choice to do queries, summations, and reporting activities, thereby off-loading the primary database from those tasks and saving valuable CPU and I/O cycles.

Note: Oracle recommends not to apply by DML operations on logical standby tables maintained by SQL Apply. This will introduce deviations between the primary and standby databases that will make it impossible for the logical standby database to be maintained.

As per Oracle10gR2, Some of the SQL statments will not be shipped from primary database to logical standby database. Some sample SQL statements are......

1. CREATE or ALTER or DROP MATERIALIZED VIEW
2. CREATE or ALTER or DROP MATERIALIZED VIEW LOG
3. ALTER SESSION
4. CREATE PFILE
5. CREATE or DROP DATABASE LINK

Please click this link if you need to read more about standby DB.