How to collect values from multiple rows into a single, comma delimited string (using vm_concat ,stragg)

SQL> select deptno, job, ename from emp order by 1,2;
DEPTNO JOB ENAME
---------- --------- ----------
10 PRESIDENT KING
20 ANALYST FORD
20 CLERK ADAMS
20 MANAGER JONES
30 CLERK JAMES
30 MANAGER BLAKE
30 SALESMAN ALLEN

7 rows selected.

SQL> col names format a80 heading "JOB/EMPLOYEE_NAME LIST"
SQL> select deptno, wm_concat(job||','||ename) names
2 from emp
3 group by deptno
4 order by deptno
5 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- --------------------------------------------
10 PRESIDENT,KING
20 ANALYST,FORD,MANAGER,JONES,CLERK,ADAMS
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,ALLEN

3 rows selected.

SQL> select deptno,
2 substr(replace(sys_connect_by_path(name,'/'),'/',','),2) names
3 from ( select deptno, job||','||ename name,
4 row_number() over(partition by deptno order by job, ename) rn
5 from emp
6 )
7 where connect_by_isleaf = 1
8 connect by prior deptno = deptno and prior rn = rn-1
9 start with rn = 1
10 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- ----------------------------------------------------------------------
10 PRESIDENT,KING
20 ANALYST,FORD,CLERK,ADAMS,MANAGER,JONES
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,ALLEN

3 rows selected.

SQL> select deptno,
2 trim (both ',' from
3 replace(
4 replace(
5 max(decode(job,'ANALYST',job))||','||max(decode(job,'ANALYST',ename))||','||
6 max(decode(job,'CLERK',job))||','||max(decode(job,'CLERK',ename))||','||
7 max(decode(job,'MANAGER',job))||','||max(decode(job,'MANAGER',ename))||','||
8 max(decode(job,'PRESIDENT',job))||','||max(decode(job,'PRESIDENT',ename))||','||
9 max(decode(job,'SALESMAN',job))||','||max(decode(job,'SALESMAN',ename)),
10 ',,,',','),
11 ',,',',')
12 ) names
13 from emp
14 group by deptno
15 order by deptno
16 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- --------------------------------------------------------------------------------
10 PRESIDENT,KING
20 ANALYST,SCOTT,CLERK,SMITH,MANAGER,JONES
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,WARD

3 rows selected.


---------------------------------------------------------------
----another Example - 2

create table temp
(
source_1 varchar2(2),
period varchar2(4),
id_l number
)

insert into temp values ('A','2008',1);
insert into temp values ('A','2008',2);
insert into temp values ('A','2008',3);
insert into temp values ('A','2008',7);
insert into temp values ('B','2008',4);
insert into temp values ('B','2008',5);
insert into temp values ('B','2008',6);


select source_1,period,
rtrim(replace(replace(xmlagg(xmlelement("a",id_l)).getstringval(),'',NULL),'',','),',')
from temp
group by source_1,period


gives the output that you asked for..

A 2008 1,2,3,7
B 2008 4,5,6,8

======================

select deptno,ename,wm_concat(ename||' '||job) over (partition by deptno order by deptno) "ename/job"
from emp

Rows to columns wise query

Oracle 9i xmlagg

In Oracle 9i we can use the xmlagg function to aggregate multiple rows onto one column:

select
deptno,
rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from
emp
group by
deptno
;

DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD



Use 11g SQL pivot for single row output


The SQL pivot operator allows you to take multiple rows and display them on a single line.

select *
from
(select fk_department
from employee)
pivot
(count(fk_department)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

'INT' 'WEL' 'CEN' 'POL'
---------- ---------- ---------- -------
7 6 0 8


Use SQL within group for moving rows onto one line and listagg to display multiple column values in a single column

In Oracle 11g, we have the within group SQL clause to pivot multiple rows onto a single row. We also a have direct SQL mechanism for non first-normal form SQL display. This allows multiple table column values to be displayed in a single column, using the listagg built-in function :

select
deptno,
listagg (ename, ',')
WITHIN GROUP
(ORDER BY ename) enames
FROM
emp
GROUP BY
deptno
/
DEPTNO ENAMES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


Use the SYS_CONNECT_BY_PATH operator



select
deptno,
substr(SYS_CONNECT_BY_PATH(lname, ','),2) name_list
from
(
select
lname,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by lname) seq
from emp
where
deptno is not null)
where
seq=cnt
start with
seq=1
connect by prior
seq+1=seq
and prior
deptno=deptno;

DEPTNO NAME_LIST
1 Komers,Mokrel,Stenko
2 Hung,Tong
3 Hamer
4 Mansur


Use a Cross join


Matt contributed this handy SQL techniques to pivot one row of several columns into a single column with several row, using the Oracle Cross join syntax. Matt notes that the Cross join "has other uses in conjunction with a WHERE clause to create triangular result sets for rolling totals etc (though thanks to analytic functions those things are very nice and easy)".

SELECT
ite,
case
when ite = 'item1' then item1
when ite = 'item2' then item2
when ite = 'item3' then item3
end as val
FROM
(
SELECT
pivoter.ite,
item1,
item2,
item3
FROM
someTable
CROSS JOIN
(
select 'item1' as ite from dual
UNION ALL
select 'item2' as ite from dual
UNION ALL
select 'item3' as ite from dual
)pivoter
)



Use the Oracle analytic Lag-Over Function


Analytic functions have a pronounced performance improvement since they avoid an expensive self-join and only make one full-table scan to get the results. This site shows an example of using the Oracle LAG function to display multiple rows on a single column:

SELECT
ename,
hiredate,sal,LAG (sal, 1, 0)
OVER (ORDER BY hiredate) AS PrevSal
FROM
emp
WHERE
job = 'CLERK';

Use the SQL CASE operator to pivot rows onto one line

You can use the CASE statement to create a crosstab to convert the rows to columns. Below, the Oracle CASE function to create a "crosstab" of the results, such as this example from SearchOracle:

select Sales.ItemKey
, sum(Sales.QtySold) as Qty
, sum(
case when OH.MOHClass = 'Fixed'
then OH.Amt
else .00 end ) as MOHFixed
, sum(
case when OH.MOHClass = 'Var'
then OH.Amt
else .00 end ) as MOHVar
, sum(
case when OH.MOHClass = 'Cap'
then OH.Amt
else .00 end ) as MOHCap
from Sales
left outer
join OH
on Sales.ItemKey = OH.ItemKey
group
by Sales.ItemKey

how to find out, who lock the same row?

session-1
=====================

create table LOCK_TEST (COL1 varchar2(1), COL2 varchar2(1));

Table created.

insert into LOCK_TEST values (12,'DATA1');

1 row created.

insert into LOCK_TEST values (54, 'DATA2');

1 row created.

select * from LOCK_TEST ;

commit ;


select * from LOCK_TEST for update ;



Session-2
================

update tstlock
set COL1='DATA1'
where COL1='DATA1';





Session-3(DBA)
================
select * from v$lock ;


select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2



select /*+ no_query_transformation first_rows */ l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1 ;


==========================================JUST ENOUGH=============================================

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

---SYS@WORKGROUP\USER-15FC74B60E ( SID=150 ) is blocking SYS@WORKGROUP\USER-15FC74B60E ( SID=148 )


select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=:SID--148
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;



select * from tstlock
where rowid='AAANpsAABAAAPNCAAA' ;

=====================================================================


============================Information==============================

Lock type and the ID1 / ID2 columns
====================================
In this case, we already know that the blocking lock is an exclusive DML lock,
since we are the ones who issued the locking statement. But most of the time,
you wont be so lucky. Fortunately, you can read this information from the v$lock
table with little effort.

The first place to look is the TYPE column. There are dozens of lock types,
but the vast majority are system types. System locks are normally only held
for a very brief amount of time, and its not generally helpful to try to tune
your library cache, undo logs, etc. by looking in v$lock!


There are only three types of user locks,

1. TX,
2. TM
3. and UL.

1. UL is a user-defined lock a lock defined with the DBMS_LOCK package.

2. The TX lock is a row transaction lock; its acquired once for every transaction
that changes data, no matter how many objects you change in that transaction.
The ID1 and ID2 columns point to the rollback segment and transaction table
entries for that transaction.

3. The TM lock is a DML lock. It's acquired once for each object that's being changed.
The ID1 column identifies the object being modified.


Lock Modes
=====================
You can see more information on TM and TX locks just by looking at the lock modes.
The LMODE and REQUEST columns both use the same numbering for lock modes,
in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock.
A session must obtain an exclusive TX lock in order to change data; LMODE will be 6.
If it cant obtain an exclusive lock because some of the rows it wants to change
are locked by another session, then it will request a TX in exclusive mode;
LMODE will be 0 since it does not have the lock, and REQUEST will be 6.
You can see this interaction in the rows we selected earlier from v$lock:


Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6),
point back to the rollback and transaction entries for Session 1.
Thats what lets us determine the blocking session for Session 2.


You may also see TX locks in mode 4, Shared mode. If a block containing rows
to be changed doesnt have any interested transaction list (ITL) entries left,
then the session acquires a TX lock in mode 4 while waiting for an ITL entry.
If you see contention for TX-4 locks on an object, you probably need to
increase INITRANS for the object.

TM locks are generally requested and acquired in modes 3,
Taka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock.
(Note that CREATE TABLE doesnt require a TM lock -- it doesn't need to
lock any objects, because the object in question doesnt exist yet!) DML
requires a Shared-Row Exclusive lock. So, in the rows we selected earlier
from v$lock, you can see from the TM locking levels that these are DML locks:


Identifying the locked object
===================================

Now that we know that each TM row points to a locked object,
we can use ID1 to identify the object.


SQL> select object_name from dba_objects where object_id=55514 ;


Sometimes just knowing the object is enough information; but we can dig even deeper.
We can identify not just the object, but the block and even the
row in the block that Session 2 is waiting on.


Identifying the locked row
=============================

We can get this information from v$session by looking at the v$session
entry for the blocked session:


SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=148 ;

This gives us the object ID, the relative file number, the block in the datafile,
and the row in the block that the session is waiting on. If that list of
data sounds familiar, its because those are the four components of an extended ROWID.
We can build the rows actual extended ROWID from these components using
the DBMS_ROWID package. The ROWID_CREATE function takes these arguments
and returns the ROWID:


SQL> select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=:148
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;


And, of course, this lets us inspect the row directly.


SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;



select * from v$lock
where type='TM'


select object_name from dba_objects where object_id=55916 ; --(v$lock.id1 OF TM LOCK )


select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=148 --(SID OF TM LOCK )

---------------------------------------------------------------------------
===========================================================================


select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

---SYS@WORKGROUP\USER-15FC74B60E ( SID=150 ) is blocking SYS@WORKGROUP\USER-15FC74B60E ( SID=148 )


select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=:SID--148
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;



select * from tstlock
where rowid='AAANpsAABAAAPNCAAA' ;

move table

SELECT 'ALTER TABLE '|| OWNER ||'.'|| TABLE_NAME || CHR(10) ||
'MOVE TABLESPACE '||TABLESPACE_NAME ||';'
FROM DBA_TABLES
WHERE OWNER IN('TEST')
AND TEMPORARY ='N'

Move Index and rebuild index

---first create a folder C:/Tuning

set echo off
set feedback off
set pagesize 0
set verify off
PROMPT Enter the name of the application owner:
ACCEPT app_owner
PROMPT Enter the name of the new tablespace for the application indexes:
ACCEPT new_idx_tablespace

spool C:\TUNING\STIMG_MoveIndexes.tmp

-- rebuild all indexes on the moved tables, even those not owned
-- by the specified user because moving the tables will set their
-- status to UNUSABLE (unless they are IOT tables)

SELECT 'ALTER INDEX '||I.owner||'.'||I.index_name||CHR(10)||
'REBUILD TABLESPACE '||I.tablespace_name||' ONLINE PARALLEL;'
FROM DBA_INDEXES I,DBA_TABLES T
WHERE I.table_name = T.table_name
AND I.owner = T.owner
AND T.owner = UPPER('&app_owner');

-- rebuild any other indexes owned by this user that may not be on
-- the above tables

SELECT 'ALTER INDEX '||owner||'.'||index_name||CHR(10)||
'REBUILD TABLESPACE &new_idx_tablespace ONLINE PARALLEL;'
FROM dba_indexes
WHERE owner = UPPER('&&app_owner');

spool off

set echo on
set feedback on
set pagesize 60

spool C:\TUNING\STIMG_MoveIndexes.log

@C:\TUNING\STIMG_MoveIndexes.tmp

spool off