To get diff data 's of similar id in a row

-----------

create or replace type string_agg_type as object (
total varchar2(4000),

static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,

member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,

member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,

member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
);
/

create or replace type body string_agg_type
is

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;

end;
/

CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

select deptno, stragg(ename)
from scott.emp
group by deptno
/

From 10g onwards No need for user defined function and type , We can use Oracle supplied function

select deptno, wmsys.wm_concat(ename)
from scott.emp group by deptno

In Oracle 10g  wm_concat is an oracle undocumnented function 
 
From 11g onwards , We can use Oracle supplied listlagg function .

select distinct listagg(orcl, ',')   within group(order by orcl) over(partition by 1) orcl  from
 ( select 'ORACLE9i' orcl from dual
 union
 select 'ORACLE10g' from dual
union select 'ORACLE11g' from dual )  c ;

Instead of wm_concat  in Oracle 10 g

SELECT DEPARTMENT_ID , RTRIM(XMLAGG(XMLELEMENT(E, FIRST_NAME || ',')).EXTRACT('//text()'), ',') ENAMES FROM EMPLOYEES GROUP BY DEPARTMENT_ID ;

 SELECT SUBSTR(SYS_CONNECT_BY_PATH(username, ';'), 2) csv FROM (SELECT username, ROW_NUMBER() OVER(ORDER BY username) rn, COUNT(*) OVER() cnt FROM all_users where rownum <= 3) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1;

To delete duplicate records

DELETE FROM INTERSECTION e
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM INTERSECTION f
WHERE e.NUM = f.NUM AND E.NAME=F.NAME);

This query will not work for a version-enabled  view .

Audit feature in Oracle 10g

TO audit what users are doing in Database

Assuming that the "audtest" user is to be audited:

CONNECT sys/password AS SYSDBA

AUDIT ALL BY audtest BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE,DELETE TABLE BY audtest BYACCESS;
AUDIT EXECUTE PROCEDURE BY audtest BY ACCESS;
-----------------------------------------
These options audit all DDL & DML issued by "audtest", along with some system events.

* DDL (CREATE, ALTER & DROP of objects)
* DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
* SYSTEM EVENTS (LOGON, LOGOFF etc.)

------------------------------------------------------------
select s.name,d.userid,d.userhost,d.terminal,d.obj$name,d.spare1,d.ntimestamp#
from sys.aud$ d,sys.audit_actions s
where d.obj$creator=user_name and s.action=d.action# ;

--------------------------
select username,
terminal,
action_name,
to_char(timestamp,'DDMMYYYY:HHMISS') timestamp,
to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time,
returncode
from dba_audit_session
-----------------------------------------
select username,
priv_used,
obj_name,
to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'

Some Good Triggers

Trigger to detect SQL Injection Attacks  
Problem with Trigger

Overlap,Explain Plan

Overlap

If any of the condition in where clause of a query overlaps , with the parameter in overlap function query returns value else not

select 'a' from dual where (sysdate,sysdate+1) overlaps (sysdate,sysdate+1);

---------------------------------------------
SQL> set autotrace on explain

Then execute the SQL script that you want to get the explain plan

--------------------------------------------------------------------------------------------------------
EXPLAIN PLAN
------------------
EXPLAIN PLAN statement determine the execution plan Oracle Database follows to execute a specified
SQL statement.


TO INSERT INTO PLAN TABLE

EXPLAIN PLAN
SET STATEMENT_ID = 'Raise in Tokyo'
INTO plan_table FOR SELECT * FROM DUAL;

DISPLAYING  PLAN_TABLE OUTPUT

select * from plan_table t
----------------------------------------------

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",object_name
"Object",position, BYTES,CARDINALITY,COST from plan_table start with id = 0
connect by prior id=parent_id;
---------------------------------------
select * from table(dbms_xplan.display);
-----------------------------------------

SELECT  *  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'BASIC'));

BASIC :   Display only the minimum set of information

TYPICAL: This is the default. Display most information  of the explain plan (operation id, name and option,#rows, #bytes and optimizer cost).

ALL: Maximum user level, like typical with additional informations (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

Some words about PLAN_TABLE resides in sys user with name PLAN_TABLE$ ;which is a  ON COMMIT PRESERVE ROWS GLOBAL TEMPORARY TABLE
 
------------------------------------
How to Read Explain Plan
------------------------------------
select * from sys.v_$sql_workarea t
--------------------------------------
select * from sys.v_$sql_plan t
-------------------------------