Where as having clause is used to test some condition

Where as having clause is used to test some condition on the group rather than
on individual rows. Where clause is used to restrict rows. But having clause is
used to restrict groups. Restrict normal query by where Restrict group by
function by having In where clause every record is filtered based on where. In
having clause it is with aggregate records (group by functions).
Posted 12th December 2011 by Prafull Dangore
9. DEC 12

What is the difference between view and materialized view?
Scenario:
What is the difference between view and materialized view?
Solution:
View Materialized view A view has a logical existence. It does not contain data. A
materialized view has a physical existence. Its not a database object. It is a
database object. We cannot perform DML operation on view. We can perform
DML operation on materialized view. When we do select * from view it will fetch
the data from base table. When we do select * from materialized view it will fetch
the data from materialized view. In view we cannot schedule to refresh. In
materialized view we can schedule to refresh.
We can keep aggregated data into materialized view. Materialized view can be
created based

on multiple tables.
Materialized View Materialized view is very essential for reporting. If we don t
have the materializ ed view it will directly fetch the data from dimension and
facts. This process is very slow sinc e it involves multiple joins. So the same
report logic if we put in the materialized view. We can fetch the data directly
from materialized view for reporting purpose. So that we can avoid multiple join
s at report run time. It is always necessary to refresh the materialized view. Then
it can simply perf orm select statement on materialized view.
Posted 12th December 2011 by Prafull Dangore
10. DEC 12
SQL command to kill a session/sid
Scenario: SQL command to kill a session/sid
Solution:
ALTER SYSTEM KILL SESSION 'sid,serial#';

Query to find SID :
select module, a.sid,machine, b.SQL_TEXT,piece from v$session a,v$sqltext b
where status='ACTIVE' and a.SQL_ADDRESS=b.ADDRESS --and
a.USERNAME='NAME' and sid=95 order by sid,piece;
Query to find serial#
select * from v$session where type = 'USER' and status = 'ACTIVE';--t0 get seria
l no
Posted 12th December 2011 by Prafull Dangore
11.

DEC 12
SQL command to find execution timing of a query Like total execution time and
so far time spent
Scenario: SQL command to find execution timing of a query Like total execution
time and so far time spent
Solution:
select target, sofar, totalwork, round((sofar/totalwork)*100,2) pct_done from
v$session_longops where SID=95 and serial#=2020;
Query to find SID :
select module, a.sid,machine, b.SQL_TEXT,piece from v$session a,v$sqltext b
where status='ACTIVE' and a.SQL_ADDRESS=b.ADDRESS --and
a.USERNAME='NAME' and sid=95 order by sid,piece;
Query to find serial#

select * from v$session where type = 'USER' and status = 'ACTIVE';--t0 get seria
l no
Posted 12th December 2011 by Prafull Dangore
12. DEC 12
Query to find the SQL text of running procedure
Scenario: How to find which query part/query of Procedure is running?